Making a dashboard in Microsoft Excel follows a straightforward process, but doing it well requires careful planning before you touch any chart or formula. Start by identifying your audience. A dashboard for a sales manager tracking weekly pipeline looks very different from one built for a finance team monitoring cash flow. The metrics, layout, and level of detail should match whoever will actually use it.
Begin with your data. Store all raw data on one or more dedicated sheets, separate from the dashboard itself. Format each dataset as an Excel Table (Insert > Table) rather than a plain range. Tables automatically expand when new rows are added, which means your pivot tables and charts will pick up new data without manual range adjustments. Name each table something descriptive so formulas are easier to read later.
Build your calculations layer next. Create pivot tables that summarize your raw data into the views you need. Common summaries include totals by time period, averages by category, counts by status, and running comparisons like this month versus last month. Put each pivot table on its own hidden sheet to keep things organized. If you need calculations that pivot tables cannot handle, use helper columns with SUMIFS, COUNTIFS, or INDEX/MATCH formulas on a separate calculations sheet.
Now design the dashboard sheet. Start with a clean, blank worksheet. Place your most important number at the top left, because that is where people look first. Use large font sizes for headline metrics like total revenue, active users, or open tickets. Below that, arrange two to four charts that tell the story behind those numbers. Keep charts the same height and aligned in a grid for a clean look.
Add interactivity with slicers. Connect slicers to your pivot tables so users can filter the entire dashboard by clicking buttons instead of editing cells. You can connect a single slicer to multiple pivot tables using the Report Connections feature, which keeps all charts in sync when someone selects a filter.
Finally, lock down the dashboard. Hide the data and pivot table sheets, protect the dashboard sheet so users cannot accidentally break formulas, and freeze the top rows if your dashboard scrolls. Test it by adding a few rows of new data to confirm everything updates correctly.
If this process feels like a lot of work for something that should be simple, that is because Excel was built as a spreadsheet tool, not a dashboard platform. Tools like AgentUI let you skip the pivot table and slicer setup entirely. You upload or connect your data, describe what you want to track, and get a working dashboard in minutes instead of hours.