Here we have a simple table with meals and nutrients of those meals in grams. Lets say we want show proportion of those nutrients using Pie Chart.
One way is to show one meal in one Chart.
And another meal in the second Chart.
But with some help of those two selection buttons we can have only one chart.
So let’s create those buttons. Firstly from developer Ribbon select insert and choose “Group Box (Form Control). Insert it somewhere in your sheet.
Now insert two option buttons inside that Group Box. Name them accordingly to you needs. And here you have possible to select your option.
Now right mouse click one the first Option Button and choose Cell link somewhere in your file where is the most convenient for you. I made an additional sheet and named is “Add”. If option “Burger” will be selected, it will show 1 in that linked cell. If option “Salad” will be select, it will show 2 in the linked cell. With a help of that number we are going to control our dynamic Pie Chart.
Now lets create two named ranges which are going to feed our Pie Chart.
First named range will be very simple one. It’s nutrients and it is static. It means that it always remains the same. So just lets create new named range, call it “Nutrients” and simply select three cells with nutrients.
Another named range is a bit more challenging. It has to be dynamic depends which meal is select from our two options. In this case we use OFFSET function as we can define how many cells that range should offset.
Formula is as follow =OFFSET(Data!$A$2, 0, Add!$B$2, 3, 1)
One of variables refers to the cell Add!$B$2 and that’s the same linked cell for our option buttons. Anchoring cell in the OFFSET function is A2 so if we have burger selected (option 1), range is shifting one cell to the right. If we have salad selected, range is shifting two cells and we have amount of nutrients for salad.
Here how does it look our name manager after two named ranges are created.
Now time to insert Pie Chart and selected our data. Lets start from Legend Entries (Series). Series name we can pick from sheet “Add”. I made additional cell with the meal which is select with our option button. For that once again I’ve used linked cell and function INDEX.
Formula is as follows =INDEX(Data!$B$1:$C$1, 1, Add!$B$2)
So the value changes depending which meal is selected.
This cell we can enter in the Series name field.
For the series values field we have to use our named range “Meal”. Do to that, you need to type the name of your file, exclamation mark and named range. Press ok and you are done with series.
Same with axis labels. Just for that use “Nutrients”.
Here how does it look data source when all data is select and from meal options burger is selected. Press ok and you are done with that.
Make some additional visual changes and Dynamic Pie Chart in MS Excel is done. I believe that you can make it look even better just be creative and stay smart