Most of us employ Excel to analyze data in search of some insights. For us, this is an enjoyable step. The not-so-pleasing step is the one that follows data exploration: presenting our findings. It is not an easy job to decide which chart-type will depict our analysis in the best possible way to the end user. The problem arises due to varying levels of technical capability across the users of our spreadsheets.
Well, our very own Excel MVP, Jordan Goldmeier, has an interesting suggestion for us. Why not let the end user decide which chart(s) they want to see? Of course, we will make it as easy as clicking a button. Moreover, you don’t need to know VBA to implement this. Hurray!
Instead of choosing between chart-types, create all the charts you think are informative. No need to decide on which chart will be the most useful for the end user.
This is an easy step. Just write “Chart Type” or something to this effect in a cell. Now write down the different chart types you want to display underneath it. Now resize those cells to the size of the charts you would want to display.
Once you are done with this, add an auto-filter on the “Chart Type” cell from Home tab > ‘Editing’ section > Sort & Filter dropdown > Filter. The overall result should look like that in the image on the right.
This step is simple:
Now start dragging and fixing these charts exactly into the cells with their names. You will notice them ‘snapping’ to the gridlines. The result should be something like the image on the right shows.
Click on the dropdown menu names “Chart Type” and select only the pie chart. Now click OK. Magic, right? The other charts just disappeared, and all you are left with is the chart you wanted to see.
This truly adds a dynamic nature to our charts and makes our dashboards look beyond awesome.
Hungry for more? Try this out and check out other tips too.
And… do not forget to share this awesome tip with your friends and colleagues.
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.