Design a One-Page Manager Dashboard in Excel or Google Sheets

Get a buildable one-page dashboard plan with a layout grid, a spec for each tile (metric, chart, filter, source range, formula) and numbered build steps.

You getLayout grid, tile spec table with columns and build steps

Prompt

Prompt
Act as a reporting analyst who builds one-page management dashboards in Excel and Google Sheets.

Your job: design a dashboard layout I can build directly from my data. Before designing anything, ask me these questions in one message and wait for my answers:
1. Who reads this dashboard, and how often?
2. Which 2-3 decisions should it help them make?
3. What columns does my data have, and what sheet name and range is it in? (I can paste the headers and a few rows.)
4. Excel or Google Sheets, and which version?

Rules:
- Every tile must support one named decision. Drop any metric that does not.
- Use a maximum of 8 tiles, all fitting one landscape page at 100% zoom.
- For each tile, give the exact formula or pivot setup using my real sheet and column names.
- Choose chart type by purpose: trend = line, comparison = bar, part of whole only with 5 or fewer slices. No 3D or gauge charts.
- Put the most important number in the top-left tile.

Output format:
1. Layout grid: a table showing each tile's position as a cell range (e.g., B2:E6).
2. Tile spec table with columns: Tile #, Metric, Decision it supports, Chart type, Filter/slicer, Source range, Formula or pivot setup.
3. Build steps: numbered, 10 steps maximum.
4. Shared filters and refresh steps.

End with one line listing your assumptions about the data and any metric you could not calculate from the columns given.

Example output

At a glanceBuild steps for the one-page sales dashboardCondensed from the 10 numbered build steps
  1. Prepare data and sheetSteps 1-2Confirm Orders table; add Dashboard sheet, landscape, 1 page
  2. Build KPI cardsStep 3Tiles 1-3 as merged cells with large font
  3. Insert pivots and chartsSteps 4-6Pivots for tiles 4-6 on hidden Calc sheet; group by 7 days
  4. Add Region slicerStep 7Connect it to pivots 4 and 6
  5. Add late order listStep 8Enter the tile 7 formula in H23
  6. Format and finishSteps 9-10Tile 3 red above 10; hide gridlines and Calc sheet

Example for: a regional sales manager reviewing weekly in Excel 365; data in an Excel table named Orders (OrderDate, Region, Rep, Product, Units, Revenue, Cost, Status, DueDate); monthly target in Settings!B2.

1. Layout grid

PositionTile
B2:E61. Revenue MTD vs target
F2:I62. Gross margin % MTD
J2:M63. Late open orders
B8:H204. Weekly revenue, last 12 weeks
I8:M205. Revenue by region, this vs last month
B22:G366. Rep margin % ranking
H22:M367. Top 10 late orders

2. Tile specs

#MetricDecisionChartFilterSourceFormula / pivot
1Revenue MTD and % of targetPush for month-end deals?KPI cardRegion slicerOrders[Revenue], Orders[OrderDate]=SUMIFS(Orders[Revenue],Orders[OrderDate],">="&EOMONTH(TODAY(),-1)+1); % = result / Settings!B2
2Gross margin % MTDShift budget between regionsKPI cardRegion slicerOrders[Revenue], Orders[Cost]=1-SUMIFS(Orders[Cost],...)/SUMIFS(Orders[Revenue],...) with the same date test
3Count of open orders past DueDateChase late ordersKPI card, red if >10NoneOrders[Status], Orders[DueDate]=COUNTIFS(Orders[Status],"Open",Orders[DueDate],"<"&TODAY())
4Revenue per weekIs the trend slipping?LineRegion slicerPivot on OrdersRows: OrderDate grouped by 7 days; Values: Sum of Revenue; filter last 84 days
5Revenue by regionShift budget between regionsClustered barMonth slicerPivot on OrdersRows: Region; Columns: Month; Values: Sum of Revenue
6Margin % by repWho needs coachingBar, sorted ascendingRegion slicerPivot on OrdersRows: Rep; calculated field Margin = 1-Cost/Revenue
7Late order listChase late ordersTableNoneOrders=TAKE(SORT(FILTER(Orders[[Rep]:[Revenue]],(Orders[Status]="Open")*(Orders[DueDate]<TODAY())),5,-1),10)

3. Build steps

  1. Confirm Orders is a formatted Excel table.
  2. Add a sheet named Dashboard; set landscape, fit to 1 page.
  3. Build tiles 1-3 as merged cells with large font.
  4. Insert pivots for tiles 4-6 on a hidden Calc sheet.
  5. Create pivot charts and move them into their grid ranges.
  6. Group OrderDate by days = 7 for tile 4.
  7. Add a Region slicer; connect it to pivots 4 and 6.
  8. Enter the tile 7 formula in H23.
  9. Add conditional formatting: tile 3 red above 10.
  10. Hide gridlines and the Calc sheet.

4. Shared filters and refresh

  • One Region slicer at B1 controls tiles 4 and 6; formula tiles ignore it unless you add Region to their SUMIFS.
  • Refresh: paste new rows into Orders, then Data > Refresh All.

Assumptions: Status uses "Open" exactly; the target is a single monthly figure; quota by rep was not available, so attainment per rep is not shown.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Asking about readers and decisions first stops the dashboard from filling up with metrics nobody acts on.
  • Requiring real sheet names, ranges and formulas turns a vague mockup into something you can build in an hour.
  • Fixed chart rules and an 8-tile cap keep the page readable and ban misleading 3D and gauge charts.

Use this when a manager wants a weekly or monthly view and you have the raw data ready. Paste your column headers and a few sample rows into ChatGPT, Claude or Gemini when it asks.

Common edits

  • Change the tile cap to 4 for a phone-friendly executive summary.
  • Ask for Google Sheets QUERY formulas instead of pivot tables.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets