Following the theme of data viz, dashboards, and infographics this month, this entry will be on 3 tips for users dashboarding on Excel. Before reading this post, please read or watch a video on the basics of building dashboards. You can watch one here by My Online Training Hub.
I will put it upfront: I am not a pro but I have created Excel dashboards before. The following tips, which are some that I have adopted while working on Excel/Sheets to create dashboards, may not be shared in basic tutorials. Although there may be so-called better apps to build dashboards. Excel is good if you and your company are looking for a relatively affordable and readily available way to build a dashboard. Second, data can also be kept all in a place. If you have 365 or Teams, the dashboards on Excel can be shared easily.
1. Use Excel Sheet as a Canvas with Gridlines
When users insert charts into the sheet for the dashboard, users can drag and drop wherever the charts they want. Tapping onto the gridlines for the cells, users can adjust and scale each chart accordingly. Nonetheless, as a rule of thumb to keep the dashboards clean in Excel, remove the gridlines from view after setting up the dashboard.
2. Use Cells as Tables with Charts
By using Excel, users can tap into the formulas and conditional formatting that they are familiar with in Excel. As a best practice, it may be good to keep all your working data in a separate sheet from the sheet for the dashboard. However, using formulas, users can pull the data from the other sheet (e.g. LOOKUP function) to create a table for the audience quick view with conditional formatting of what to note (e.g. highlighting in green or red). As it is recommended to remove the gridlines from view, users can add borders for the table to direct the audience's attention that they are looking at a table.
3. Use Invisible Background to Create Dual Axis Charts
Microsoft Excel may provide some default charts and they may not be as easily manipulated as in other tools specifically built for dashboarding. For a multi-faceted analysis, like one with multiple axes that use a combination of candlesticks and bar/line charts, users can make the layers of both charts transparent to align them to get a single chart. Nevertheless, users have to keep in mind that only the top layer is responsive to clicking, despite both charts are possible to be filtered using a slicer.
All in all, these are 3 quick tips for dashboarding on Excel. Considering similarities between the tools, users can tap on them for Google Sheets too.