Level 7 · Boss: a one-page daily dashboard

Three questions, three charts

A dashboard is not a wall of charts. Each chart answers one question someone asks every morning, and each sits on its own small summary table that refreshes when the data does. Priya’s three questions are: what sold yesterday, where did the visitors come from, and is revenue trending up? Each step has its own sheet and its own live chart.

In Excel you put the charts side by side on one sheet (often called Dashboard), keep the summary tables on a second sheet, and, if the tables are PivotTables, add a Slicer (PivotTable Analyze → Insert Slicer) so one click filters every chart wired to it. Slicers can’t run on this page; the charts and the numbers behind them can.

Step 1: a PivotChart of revenue by category

A PivotChart is a chart drawn from a PivotTable, not from a range you typed. Build the PivotTable at H1: category in Rows, revenue in Values as a Sum. It fills H1:I8, with the six categories in alphabetical order and the Grand Total in row 8. The chart reads H2:I7, so it draws as soon as the PivotTable exists.

In Excel: click any cell of the PivotTable, then PivotTable Analyze → PivotChart, pick Column and OK. The chart and the PivotTable stay in sync: filter one and the other follows, and Refresh updates both when tomorrow’s lines are pasted in. For a clean dashboard, right-click a field button on the chart → Hide All Field Buttons.

On October 21 Furniture led with 2,038.00, then Lighting (1,939.74) and Outdoor (1,415.98). Decor brought in 670.78, Textiles 445.89 and Kitchen 321.90, for a day’s total of 6,832.29 from 40 order lines. A chart sorted largest first reads faster: in Excel, sort the PivotTable by Sum of revenue (right-click a value → Sort → Largest to Smallest) and the PivotChart reorders with it.

Step 2: traffic by source and device

The 42 website sessions of the day are on this step’s sheet, one row each. The chart needs a grid: one row per source, one column per device.

H2   =COUNTIFS($D$2:$D$43,$G2,$C$2:$C$43,H$1)

Fill it to J6. The grid should add up to 42. In Excel, select G1:J6 and use Insert → Charts → Column → Stacked Column: each column is a source’s total, split into desktop, mobile and tablet. The page draws them side by side instead (clustered); the counts are the same.

Direct visits were the largest source with 11 (3 desktop, 7 mobile, 1 tablet), then organic with 9, paid and social with 8 each, and email with 6. Mobile was the biggest device in every source but social, and 26 of the 42 sessions overall, against 11 on desktop and 5 on tablets. All 6 email visits came from phones: people open the newsletter on their phone. With counts this small, one day is a snapshot; the same chart over a month says more.

Step 3: a trend line you can trust

Daily revenue jumps around: weekdays, weekends, one big furniture order. A 7-day moving average smooths that out, because each point averages a full week, every weekday once:

D8   =AVERAGE(C2:C8)

Filled down, the window slides: D9 averages C3:C9, D15 averages C9:C15. Rows 2 to 7 stay empty, because they don’t have seven days behind them. The chart draws revenue and the average as two lines; the empty cells simply don’t plot.

Daily revenue ranged from 1,923.74 (Sun Oct 19) to 13,462.58 (Fri Oct 10), a swing of seven to one, so the raw line alone says little. The 7-day average says more: it peaks at 7,579.48 on October 15 and slides to 5,306.63 on October 21, its lowest point, after a weak Sunday and Monday (1,923.74 and 2,018.70). Tuesday’s 6,832.29 is above that average, a good day inside a softening week. That is the headline for leadership: watch the trend, not the single day.

Common mistakes

• Charting the Grand Total. Including row 8 in the PivotChart adds a “Grand Total” column that dwarfs the categories. A PivotChart built in Excel leaves it out by itself.
• Relative references in the COUNTIFS grid, which slide off the data when filled.
• Locking the moving-average window ($C$2:$C$8), which repeats the first week’s average on every row.