Hands-On: Build a Monthly Expense Dashboard

Professional Excel dashboards separate raw data, analysis, and presentation into distinct sheets. This architecture keeps the data clean, the analysis flexible, and the presentation professional — three goals that cannot be achieved on a single worksheet:

• The Expense Data sheet is the only place where raw expense records live — each row is one transaction with columns: Date, Department, Category, Vendor, Amount. This sheet has validation on the Department and Category columns (dropdown lists) and a whole-number validation on the Amount column to prevent negative entries. This is the only sheet where staff can enter new data. No charts, no PivotTables, no summary tables appear here — just clean, validated data.
• The Analysis sheet contains the PivotTables, SUMIF summary table, and PivotChart — this is where the data is interrogated and summarized. The physician or office manager opens this sheet to explore the data from different angles. PivotTable filters and Slicers live here. Formula cells are protected so the analysis structure cannot be accidentally broken. Staff do not directly edit this sheet.
• The Dashboard sheet is the executive presentation view — a clean, visually organized layout that shows the most important summary numbers (total monthly spend, top category, biggest vendor, month-over-month change) alongside the PivotChart, formatted as a professional one-page report. This is what appears on the projected screen during the leadership meeting. It references cells from the Analysis sheet using cross-sheet formulas and contains no editable cells.

The analysis layer is the core of the dashboard — it transforms the raw expense data into structured summaries that answer the leadership team's questions before they ask them:

• PivotTable 1 — Department vs Category cross-tab: create a PivotTable from the Expense Data table with Department in Rows, Category in Columns, and Sum of Amount in Values. Format all values as Currency. Add a Grand Total row and Grand Total column. This one PivotTable shows the complete spending matrix — what each department spent in each category — which is the most information-dense summary the leadership team needs.
• PivotChart — column chart from PivotTable 1: insert a clustered column chart from the PivotTable showing total spending by Department (one bar per department). Title it 'Monthly Expenses by Department — [Month Year].' Format with the practice's color scheme, clean flat style, no 3D effects, currency data labels on each bar. Place the chart adjacent to the PivotTable on the Analysis sheet.
• SUMIF Summary Table — formula-based alternative: below the PivotTable, build a static summary table using SUMIF: =SUMIF(ExpenseData[Department], 'Medical', ExpenseData[Amount]) for each department. This table serves the physician who prefers to see clean labeled totals rather than interacting with PivotTable fields. Format as an Excel table with a professional style, Currency formatting, and a total row using SUM.
• Add a Slicer for Month — connect it to PivotTable 1 so the physician can filter the analysis to a specific month during the meeting without needing to understand PivotTable mechanics. Place the slicer prominently above the PivotTable with a clear label.

The dashboard is only as reliable as the data and formulas it is built on — protection and validation ensure both remain intact as the file is shared and used over time:

• On the Expense Data sheet: apply dropdown validation to the Department column (Medical, Nursing, Administration, Facilities) and the Category column (Supplies, Equipment, Services, Utilities, Staff Training). Apply whole-number validation (minimum 1) to the Amount column to prevent zero or negative entries. Use Stop error alerts on both dropdown columns. Leave the data entry cells (Date, Department, Category, Vendor, Amount) unlocked and protect the sheet — staff can add new expense rows but cannot change the column headers.
• On the Analysis sheet: lock all cells (the default state) and protect the sheet with a password so PivotTable configuration and SUMIF formulas cannot be accidentally edited. Allow 'Select unlocked cells' so users can click the Slicer buttons and interact with the PivotTable's row/column fields without typing into cells. PivotTable interaction (dragging fields, clicking filter arrows) works even on a protected sheet — only direct cell editing is blocked.
• On the Dashboard sheet: protect all cells with no exceptions — the dashboard is read-only. All values on this sheet come from cross-sheet formulas that pull from the Analysis sheet. Users view but cannot edit. This preserves the professional presentation layout regardless of how the underlying data is updated.