Excel dashboards
What kind of reporting executives prefer? One that can fit on a single page! And to be timely, clear, beautiful and above all accurate – that goes without saying! Reporting table usually consist of tables, charts, or a combination of these elements with help of several important filters. Excel allows you to easily create beautiful and functional dashboards. This way it can replace or at least to postpone the purchase of expensive specialized software.
When we talk about dashboards, keep in mind that this is first of all about the concept, not the final solution. Excel offers a variety of tools for their creation. Which one are we going to use depends on the nature of data and personal preferences. We can create dashboards by using:
- traditional tables and charts
- structured tables, charts and slicers
- Pivot table reports and PivotCharts with Slicers and Timeline filters (Traditional Pivot/PowerPivot)
- PowerView
- 3D maps
- PowerBI
Correct me if I have missed something! Most users still create dashboards with Pivot table reports, Pivot Charts, Timeline and Slicer filters. In fact, ideal combination are charts and interactive filters.
The first step is to create one or more Pivot table reports. It is not incorrect to create multiple Pivot tables based on a single source of data. Moreover, it is sometimes effective way to look at this information from several aspects (eg. Sales to amount to: the manufacturer, brand, sales group, classification, etc.). We should always create Pivot tables on separate worksheet (which we subsequently hide), and PivotCharts on a separate worksheet intended to be a dashboard. To do that, after creating PivotChart we should select it, go to the Design ribbon and choose Move Chart option. The process of adding Pivot tables and charts is repeated until we create all needed charts.
Then you need to add interactive filters: Slicers & Timeline! We can achieve that in several ways, and one of the fastest is to select a PivotChart, so that the panel PivotChart Fields appears, right-click over one of the dimensions and choose the option Add as a Slicer or Add as a Timeline. When you’re done adding these elements is still necessary to check how they relate to the charts. For example, to achieve this we should click on one of the slicers, select Options ribbon and then option Report Connections. A dialog box will appear with a list available chart reports and we should only check or uncheck which reports are controlled by slicer.
Finally it’s necessary to put in some work on appearance of the dashboard. We can set a wallpaper, add images, WordArts, shapes, hyperlinks… The creative process takes until we get a report that is functional and beautiful. In the end, think you should consider two important details: think of a screen size and resolution of a user. Also, do not overdo it with the details, because “less is often more” …
