Building Dashboards in Excel From SQL Output Step by Step

Someone asks for "a dashboard" and means one page they can open on Monday morning to see how the business is doing. Excel can be that page. It is available to everyone, it can combine tables and charts, and a scheduled script can fill it from SQL. It has limits, and knowing them is what keeps a spreadsheet dashboard from turning into a maintenance burden. This article builds one in layers: data, presentation and documentation, with the checks that make the numbers trustworthy. It applies the ideas of charts from data pulled out of databases and building Excel reports from SQL queries people trust to a single-page layout.

Decide what the page must answer

A dashboard without questions becomes a display of everything available. Start by writing the five questions the reader asks on Monday. For an operations lead: how many orders came in last week against the week before? How many are late? Which warehouse is slowest? What is the backlog? Are returns rising? Each question becomes one visual or one number. If a question has no query yet, write the query first (designing a report query before touching Excel).

Five questions means five to six items. Resist the extra tab of "other useful metrics". The goal is that the reader sees the state of things and knows where to look next.

Layer one: data sheets

Create one sheet per query, named for what it holds: Orders_weekly, Late_orders, Warehouse_speed. Write each result as a table starting at A1, with a header row and no gaps.

queries = {
    "Orders_weekly": "sql/orders_weekly.sql",
    "Late_orders": "sql/late_orders.sql",
    "Warehouse_speed": "sql/warehouse_speed.sql",
}
with pd.ExcelWriter("ops_dashboard.xlsx", engine="xlsxwriter") as xw:
    for sheet, path in queries.items():
        df = pd.read_sql_query(text(open(path).read()), conn, params=params)
        df.to_excel(xw, sheet_name=sheet, index=False)
        frames[sheet] = df

One sheet per query keeps changes local. If one query returns more columns next month, it does not push another table out of place. Put the data sheets after the dashboard in the tab order, and set their tab color to gray so readers know they are plumbing. Do not hide them: a hidden sheet is a surprise waiting for the next person (hidden sheets and metadata leaking out of shared workbooks).

Make each query return exactly what the visual needs, aggregated, sorted and gap-filled. A dashboard is a poor place to compute; do the work in SQL.

Layer two: the presentation sheet

One sheet, named Dashboard, first in the workbook, set to open on load. Plan the grid on paper before writing code. A workable layout for a widescreen laptop is a row of four tiles at the top, two charts in the middle and two below.

Tiles are cells showing a number, with a label above and a comparison below. Link them to the data sheets by formula:

ws.write_formula("B3", "=Orders_weekly!C2", num_big)          # this week
ws.write_formula("B4", '=TEXT(Orders_weekly!C2/Orders_weekly!C3-1,"+0.0%;-0.0%")&" vs last week"', small)

The tile's number is a formula pointing at the data sheet, so the dashboard has no hard-coded values. Make the number large (24 to 32 points), the label small and gray, and the comparison color-coded with a sign as well as a color. A tile is three cells, merged only if you must; plain cells with a border are easier to maintain.

Charts read the data sheets. Give them the same width and align their edges to cell boundaries. Titles state what the chart shows. Use the same palette for the same categories everywhere. Keep to a line for trend, a sorted bar for ranking and a stacked bar for composition (choosing Excel chart types for query results).

ch = wb.add_chart({"type": "line"})
ch.add_series({"name": "Orders", "categories": ["Orders_weekly", 1, 0, n, 0],
               "values": ["Orders_weekly", 1, 2, n, 2], "line": {"width": 2.25}})
ch.set_title({"name": "Orders per week, last 13 weeks"})
ch.set_legend({"none": True})
ch.set_size({"width": 560, "height": 280})
ws.insert_chart("B8", ch)

Hide gridlines on the dashboard sheet (ws.hide_gridlines(2)), set a background fill only on the header band, and freeze nothing; a dashboard should fit a screen. Set the zoom (ws.set_zoom(90)) and the print area so that it prints on one wide page with fit-to-width settings.

Layer three: the README and the as-of date

Put the as-of date on the dashboard itself, near the top, in a clear place: "Data as of 2026-10-29 06:00 UTC." Without it, readers cannot judge freshness. Add a README sheet with the definition of each tile, the query names and commit, the refresh schedule and the contact (putting a README sheet into every generated workbook). A tile called "Late orders" is ambiguous until someone writes that late means shipped more than 48 hours after payment, excluding weekends.

Refreshing the data

There are three ways to refresh, and the right one depends on who opens the file.

Regenerate by script. The job runs the queries and writes a new workbook on a schedule, then replaces the file in a shared location. This is the dependable option: no credentials in the workbook, reproducible, testable. Readers always open a complete, fresh file.

Connections inside the workbook. Excel can hold connections to a database (ODBC or a native connector) and refresh them when the file opens or on a timer. It saves the script and puts a connection string, and often credentials, in the file (the connection and credential articles in this cluster cover the setup). Every reader needs access and a driver. Use this for a small team on a managed network.

Power Query. Queries recorded in the workbook refresh on demand and can be scheduled with Power Automate or a server refresh (refreshing Power Query data on a schedule). A fair middle ground when analysts maintain the logic.

For the script route, name the output by date and keep the last good file; overwrite a fixed "latest" name only after the checks pass.

Check that the tiles tie out

A dashboard multiplies mistakes, because many people look at it and few check it. Build in the checks.

• Every tile equals a value recomputed from a separate simple query. A script compares the two and fails the build on a mismatch.

• Weekly orders on the chart sum to the total on the tile.

• No data sheet is empty, and the row counts are within the expected range.

• The as-of date is within the schedule's tolerance.

assert abs(frames["Orders_weekly"]["orders"].iloc[-1] - control_orders) == 0, "tile does not tie"

When a check fails, send the failure to the owner and leave the previous dashboard in place. A stale dashboard with a visible date is better than a fresh one with a wrong number.

Interactivity: how much, and at what cost

Slicers and pivot charts let readers filter without writing anything. They need pivot tables, which store a copy of the data inside the file (the pivot cache), and they refresh only in Excel, not from a script without it. A middle path is to generate several fixed views, one tab per region, from the same queries, and let the reader click tabs. Hyperlinks between sheets give a menu. Data validation dropdowns that drive formulas (INDEX and MATCH on the chosen region) work without pivots and keep the file small. Decide how much interaction the audience needs; more of it costs more to build and to explain.

Keep it small and keep it fast

Dashboards slow down when the data sheets grow. Aggregate in SQL so each sheet holds hundreds of rows, not hundreds of thousands. Avoid whole-column formulas, volatile functions and chains of lookups on the presentation sheet. File size matters too: a workbook that opens in two seconds gets used, one that opens in 40 gets abandoned (trim unused data and formatting).

Protect against accidents

Protect the presentation sheet so that formulas and charts are not moved by accident, with the sheet protection that Excel offers. Remember that this prevents slips, not determined editing (protection prevents slips and is not security). Send it as a read-only link where your tools allow, or as a PDF when the reader only needs to look.

When an Excel dashboard is the wrong container

An Excel dashboard is the wrong container when many people need the same live numbers, because every emailed copy goes stale and the versions multiply. It is wrong when the audience needs drill-down into millions of rows, which a BI tool with a query engine behind it handles. It is wrong when row-level security matters, since a workbook shows what it contains to anyone who opens it. And it is wrong when the dashboard has become the product that people depend on every hour; at that point, give it the infrastructure of a product. For a small audience with a weekly rhythm, a generated workbook with a handful of tiles and charts is a sound and cheap answer. The XlsxWriter charts and layout examples and the openpyxl chart documentation are useful for the mechanics.