Build an Excel Dashboard…
In under 5 minutes!
Spreadsheets are highly underrated. Sure for serious data exploration and ML projects I’m turning to python or R, but if I just want to rapidly build out a dashboard to tell a story for a presentation, Excel is often a reasonable option. So, here’s how to build a dashboard in under 5 min.
Data Source: Time Series of ICU Occupied Beds By State
Initial Data Set:
Turn it into a table to make it easier to work with:
- Select all your data
- Click “Format as Table” from the home tab
3. Select a cell in your newly formatted table
4. Select the “Insert tab” and then “PivotChart”
5. Build out a Pivot Chart. If you aren’t comfortable with PivotCharts, I have a future article that will help you get there.
6. Open a new worksheet and call it “Dashboard”
7. Select all cells (ctrl+shift+right arrow, ctrl+shift+down arrow)
8. Pick a fill color for your background.
9. In the Page Layout tab, uncheck “Gridlines View”. Your Dashboard Should look like this:
10. Add a title, merge cells to make the title the correct size, and pick a good font
11. Select out a few landing cells and make them a different fill color.
12. Now copy your pivot chart and paste it into your dashboard.
13. Rinse and repeat with your next pivot table, play with the aesthetics and your done.
Sure, it’s not an amazing Tableau or Qlik dashboard. It didn’t require any python imports from Plotly or Bokah, but honestly, sometimes you just need a 5 min solution to get a point across.
Till the next time
-MB
