Visualize data and calculations with charts
Tables and functions are great for displaying lists and figures.
But if you want to show trends and/or patterns, charts and graphs are the go-to elements.
You can insert a Pivot Chart from the Insert Tab in the same way as with pivot tables.
1. Create a line chart for the total sales like this:
2. Then copy it over to the Dashboard tab and apply your desired formatting.
Explore the many different Excel tools! 🔧🔨
Don’t limit yourself to a simple line chart or graph. Excel offers so many different visual elements for use.
You can select from various charts such as pie charts, bar charts, or even a map!
Select dynamic charts that work best with your data.
For example, instead of listing out the top-selling items, you can display this in a colorful pie chart.
You can also create interactive charts like this clustered column chart. It allows you to filter data by category and date.
Once you have all your Excel dashboard elements in place, you can now move on to formatting and clean-up.
Nice work! 🤩
You now have a working Excel dashboard. This particular example is simple compared to other Excel dashboards.
Quite often, advanced Excel dashboards will have a lot of data and visuals. This can make navigating them difficult.
To overcome this, you can create an interactive Excel dashboard that allows users to change views. So that they can focus on specific data points and visuals.
Let’s see how you can do this.
Using Macros to create views
Revisit the example dashboard outline earlier. Based on it, you want the key metrics for both investors and management visible.
But you also want an option to hide the management-related metrics when needed to streamline your Excel dashboard.
This is easy enough to do by Recording a Macro to hide the last few columns and assigning this macro to a button.
Click here to learn how to record macros. It works so fast that it might blow you away! 💨
Once, you have Show & Hide buttons like the ones above, users can now manage their view of your Excel dashboard.