Edwin asks if you can choose to display a different chart based on a slicer. Today's Learn Excel Podcast episode shows how this can be done using a disconnected pivot table, the camera tool, and a defined name with the OFFSET function.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn excel from Mr.. Excel Podcast. Episode 1853. Use a Slicer to choose the chart type.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Just got back from a week at trainer days in Switzerland and this question came in when i was gone.
This is from Edwin.
Edwin says some workbooks are designed with different charts or dashboards to show various metrics And he talked about instead of navigating you know to the next sheet next sheet next sheet or scrolling up or down wouldn't it be cool to have a filter or a slicer that changes the chart, so not just dynamic chart content but dynamically changing from chart A to chart B to chart C to chart D.
So something like this the regular slicer is changing what's shown in the chart, but then we have a second slicer that completely changes the chart to something else alright.
So I took a little bit of time to set this up, and I'm going to walk through some of the tricks that I use to do this. All right, so there are Well, let's go back to the original data sheet. Here's the original data sheet.
I created a pivot table from that and in that pivot table built the data for the first chart.
Created a pivot chart from this pivot table. To this pivot table I added the slicer that uses sector.
So just insert slicer, and we have our sector slicers that's normal that's been covered in other videos.
Okay, so now I have a pivot table with a chart and a slicer.
Copy that and paste it here.
Lo and behold I don't have to go to slicer connections.
This pivot table is now also controlled by the first slicer.
I've got this one looking right and created a chart.
Then got this one again. Copy this pivot table paste here.
The slicers are already connected and created a chart from this. Now I'm going to scroll down here to show you some of the tricks of how this actually works.
Okay, over here in Column AJ. This cell AJ42.
I named as Start, so a named range there, and then the next three cells that may be very tall and very wide so each cell completely holds, is large enough to completely hold the pivot chart and cut each pivot chart and paste it here.
So here's AJ43 Here's AJ44. Here's AJ45. If you really had 10 charts absolutely go for it and keep doing this concept again and again and again until you have all 10 charts one cell per chart just lined up on top of each other and again the cell right above the first chart is called Start up here in the name box.
Alright, now this next part is a little bit tricky. I created a brand new data set.
Two columns column number 1 is called Value. Those are just the numbers from 1 through how many charts you have 1 2 3 4 5 6 7 8 9 10 and then a field called Chart Type, and that is what we want to appear in the slicer, so come up with nice names here.
But keep them short, so that way they will fit in the slicer. From this dataset these 8 cells or if you have 10 charts, it'd be 22 cells I guess, make a pivot table. I put the pivot table right here and add a slicer to this pivot table that is slicing the value. The values in the rows area and then a slicer by chart type. Now that we want to choose from the chart type slicer the number that I'm gonna get is the corresponding value 1 2 or 3.
Some of you. I just heard I just heard it out. You said, huh, I know where he's going with this.
All right this cell the first cell in the pivot table is called "WhichChart" "WhichChart".
So I have a named cell called "Start" a named cell called "WhichChart" and we go to formulas you will define a name.
I've already defined the name so we'll take a look at it. It's called ShowChart and It is equal to that =OFFSET from the Start. That's the cell above the first chart.
How many cells down, which chart and then zero cells over. The one in the one are optional I put them in just for good measure all right.
So we have a named range now called ShowChart.
As I choose from the slicer this value here is going to change which will change which cell the WhichChart is going to point out all right.
Then make it all work.
Come here to the very first chart. I will Ctrl+C to copy that. Come back to underneath my slicers and I'll put a second one here and on the Home tab we're gonna say Paste Paste as linked picture.
Alright, so we now have a linked picture of that cell and you'll see that when the link picture is selected it is pointing to AJ43.
I would like backspace through that I have to use an either a cell reference or a named range here, so it's =ShowChart Press Enter and now the picture that I get corresponds to the ShowChart name which is getting its data from the pivot table, so when I change to Profit by Region.
That changes the number in this cell to a 3 because that's the value associated with Profit by Region.
That forces the named range ShowChart to grab the third cell from the starting position.
So 1 2 3 and then that gets fed to my camera tool a very interesting way to have a slicer that actually changes the chart and ofcourse the other slicer, this is normal we've seen this before, is changing which data is shown in the chart.
Great idea from Edwin required some tricky things there to make it work, but use it the next time you want a dashboard with just a single chart.
Well hey I want to thank you for stopping by. Will see you next time for another nectcast from MrExcel.
Learn excel from Mr.. Excel Podcast. Episode 1853. Use a Slicer to choose the chart type.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Just got back from a week at trainer days in Switzerland and this question came in when i was gone.
This is from Edwin.
Edwin says some workbooks are designed with different charts or dashboards to show various metrics And he talked about instead of navigating you know to the next sheet next sheet next sheet or scrolling up or down wouldn't it be cool to have a filter or a slicer that changes the chart, so not just dynamic chart content but dynamically changing from chart A to chart B to chart C to chart D.
So something like this the regular slicer is changing what's shown in the chart, but then we have a second slicer that completely changes the chart to something else alright.
So I took a little bit of time to set this up, and I'm going to walk through some of the tricks that I use to do this. All right, so there are Well, let's go back to the original data sheet. Here's the original data sheet.
I created a pivot table from that and in that pivot table built the data for the first chart.
Created a pivot chart from this pivot table. To this pivot table I added the slicer that uses sector.
So just insert slicer, and we have our sector slicers that's normal that's been covered in other videos.
Okay, so now I have a pivot table with a chart and a slicer.
Copy that and paste it here.
Lo and behold I don't have to go to slicer connections.
This pivot table is now also controlled by the first slicer.
I've got this one looking right and created a chart.
Then got this one again. Copy this pivot table paste here.
The slicers are already connected and created a chart from this. Now I'm going to scroll down here to show you some of the tricks of how this actually works.
Okay, over here in Column AJ. This cell AJ42.
I named as Start, so a named range there, and then the next three cells that may be very tall and very wide so each cell completely holds, is large enough to completely hold the pivot chart and cut each pivot chart and paste it here.
So here's AJ43 Here's AJ44. Here's AJ45. If you really had 10 charts absolutely go for it and keep doing this concept again and again and again until you have all 10 charts one cell per chart just lined up on top of each other and again the cell right above the first chart is called Start up here in the name box.
Alright, now this next part is a little bit tricky. I created a brand new data set.
Two columns column number 1 is called Value. Those are just the numbers from 1 through how many charts you have 1 2 3 4 5 6 7 8 9 10 and then a field called Chart Type, and that is what we want to appear in the slicer, so come up with nice names here.
But keep them short, so that way they will fit in the slicer. From this dataset these 8 cells or if you have 10 charts, it'd be 22 cells I guess, make a pivot table. I put the pivot table right here and add a slicer to this pivot table that is slicing the value. The values in the rows area and then a slicer by chart type. Now that we want to choose from the chart type slicer the number that I'm gonna get is the corresponding value 1 2 or 3.
Some of you. I just heard I just heard it out. You said, huh, I know where he's going with this.
All right this cell the first cell in the pivot table is called "WhichChart" "WhichChart".
So I have a named cell called "Start" a named cell called "WhichChart" and we go to formulas you will define a name.
I've already defined the name so we'll take a look at it. It's called ShowChart and It is equal to that =OFFSET from the Start. That's the cell above the first chart.
How many cells down, which chart and then zero cells over. The one in the one are optional I put them in just for good measure all right.
So we have a named range now called ShowChart.
As I choose from the slicer this value here is going to change which will change which cell the WhichChart is going to point out all right.
Then make it all work.
Come here to the very first chart. I will Ctrl+C to copy that. Come back to underneath my slicers and I'll put a second one here and on the Home tab we're gonna say Paste Paste as linked picture.
Alright, so we now have a linked picture of that cell and you'll see that when the link picture is selected it is pointing to AJ43.
I would like backspace through that I have to use an either a cell reference or a named range here, so it's =ShowChart Press Enter and now the picture that I get corresponds to the ShowChart name which is getting its data from the pivot table, so when I change to Profit by Region.
That changes the number in this cell to a 3 because that's the value associated with Profit by Region.
That forces the named range ShowChart to grab the third cell from the starting position.
So 1 2 3 and then that gets fed to my camera tool a very interesting way to have a slicer that actually changes the chart and ofcourse the other slicer, this is normal we've seen this before, is changing which data is shown in the chart.
Great idea from Edwin required some tricky things there to make it work, but use it the next time you want a dashboard with just a single chart.
Well hey I want to thank you for stopping by. Will see you next time for another nectcast from MrExcel.
