Slicers often change the data shown in a chart, but Edwin asks can you change the chart type with a slicer? Today’s video shows how this can be done.
Edwin writes, “Most presentation of metrics people compose multiple slides or pages in a worksheet and can easily lose trail of conversation due to questions and back and forth…then the scroll searching here and there. It would be cool that instead of navigating sheet per sheet or scrolling up or down….to have a Slicer where that one chart is displayed and upon click it changes to another chart. So, it is a dynamic chart, but not of the comments, but changing from Chart A to Chart B.
The solution in the video – Episode #1853 – is to use the Camera Tool (now known as a Linked Picture). The source of the camera tool is a named formula that includes the OFFSET function to show one from a series of charts. How can the Slicer change the OFFSET??? By using a disconnected pivot table – a pivot table based on a small data set with two columns – the name of each chart and the number of rows down from a starting location that the chart can be found. Some creative arranging of the slicers from the original pivot table and the disconnected table make it seem like you are both changing the content in the chart and the chart type from the two slicers.
“Microsoft 2013 InDepth” – by Bill Jelen. Excel 2013 In Depth is the beyond-the-basics, beneath-the-surface guide for everyone working with Excel 2013. Excel expert and MVP Bill Jelen provides specific, tested, proven solutions to the problems Excel users run into every day: the types of challenges other books ignore or oversimplify. Jelen thoroughly covers all facets of working with Excel 2013.
“The Learn Excel from MrExcel Podcast Series”
Visit us: MrExcel.com for all of your Microsoft Excel Needs!