Source: https://oguzerol.com/en/excel-dashboard-setup/

[![Example scenario: regional rows in orders.xlsx are summarised on the management dashboard as 38 pending orders and a West low-stock alert, while the reorder step waits for approval.](https://oguzerol.com/gorseller/senaryo/excel-dashboard-setup-senaryo-en.webp)

View full size](https://oguzerol.com/gorseller/senaryo/excel-dashboard-setup-senaryo-en.webp)

# Excel Dashboard: What It Is, How to Build One, When You Need a Web Panel

[Oğuz Erol](https://oguzerol.com/en/about-me/)  |  16 min read

An Excel dashboard is a summary page that shows the state of a piece of work on one screen: how many orders are waiting, which warehouse is low on stock, which region is behind. A solid one is built in this order: a raw data sheet nobody edits by hand, an Excel table on top of it (Ctrl+T), Power Query to clean it, formulas that define each figure once, then PivotTables, slicers and a chart. Dashboards that skip this order break in the same place: data is pasted by hand, a formula points at a fixed range, and new rows silently fall outside it.

Where Excel stops being enough is managerial, not technical: a regional manager should see only their region, data entry needs rules, and someone needs to know who changed what. At that point I weigh Power BI first, then a custom web panel.

## Example: a three-region order dashboard that should have shown 38 orders, not 33

The company and figures are fictional.

A distributor of industrial pump and valve spare parts runs three regional warehouses: North, West and Central. Every morning the planner pastes the ERP’s order export into the dashboard’s “Data” sheet. Years ago, the PivotTable’s source was set as a fixed range, `Data!A1:H121`, so it reads at most 120 rows.

One morning the export has 130 rows. The dashboard shows 33 pending orders (North 15, West 8, Central 10). In reality 38 are pending (North 18, West 9, Central 11). There is no error: the PivotTable correctly summarises its range, and the last 10 rows sit outside it.

Rebuilt on a table, the dashboard shows this (amounts net, ex-VAT, in the company’s currency):

| Region | Pending orders | Pending amount | Older than 3 working days | Critical stock items |
| --- | --- | --- | --- | --- |
| North | 18 | 1,310,430 | 1 | 0 |
| West | 9 | 705,310 | 4 | 1 |
| Central | 11 | 1,002,990 | 1 | 0 |
| **Total** | **38** | **3,018,730** | **6** | **1** |

All four of West’s orders older than 3 working days wait for the same part: the M45 mechanical seal. West holds 3 units against a minimum of 8; the four orders need 11. The reorder request is 11 − 3 + 8 = 16 units. The dashboard shows the request but doesn’t approve it: purchasing approval is a separate step, and it waits.

The 5 missing orders were 3 in North, 1 in West and 1 in Central, all entered that morning. So the M45 decision doesn’t come from them: the old dashboard also showed 4 old West orders. The fixed range distorted the pending count and amount.

## How do you build a solid dashboard in Excel?

A solid Excel dashboard has six layers, each only reading from the one below: raw data, table, cleaning query, figures, PivotTables and filters, and the dashboard sheet. Menu names are from Microsoft 365.

### 1. Raw data sheet: hands off

The ERP export sits on its own sheet as it arrives: one row per record, one heading per column, no colours, notes, subtotals or merged cells. A row fixed by hand comes back unfixed tomorrow. Correct things at the source or in Power Query.

### 2. Excel table: the cure for fixed ranges

Select the data, press Ctrl+T and name the table (`Orders`). Microsoft’s shortcut list gives Ctrl+T or Ctrl+L on Windows and Ctrl+L in Excel for the web ([Microsoft Support](https://support.microsoft.com/en-us/office/keyboard-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f), read 16 October 2026). Formulas and the PivotTable now refer to the table name, and structured references adjust automatically as the range changes ([Microsoft Support](https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e), read 16 October 2026). This is the step where the example’s 10 missing rows reappear. The `Orders` columns: Order No, Order Date, Region, Item Code, Status, Net Amount, Pulled At. A worksheet holds at most 1,048,576 rows ([Microsoft Support](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3), read 16 October 2026); if your export approaches that, loading it to a sheet is no answer.

### 3. Power Query: refresh instead of paste, several sources

If the export lands in a folder daily, use Data > Get Data > From File > From Folder, then Combine & Transform. Microsoft’s condition: every file needs the same schema, meaning headers, data types and column count ([Microsoft Support](https://support.microsoft.com/en-us/office/import-data-from-a-folder-with-multiple-files-power-query-94b8023c-2e66-4f6b-8c78-6a00041c90e4), read 16 October 2026). In the query, set data types, trim spaces (“West ” vs “West”), filter out blank and subtotal rows, and add a “Pulled At” column with `DateTime.LocalNow()` for the dashboard’s “last data pull” line.

**A second source: stock.** Here stock comes from each warehouse’s own export: three files with the same columns (Region, Item Code, Item, On Hand, Minimum). Stack them with Home > Append Queries into a `Stock` table. To bring a waiting item’s stock onto the order row, use Home > Merge Queries on the shared key Region + Item Code.

**Where you load the query is a decision.** In the dialog opened by Home > Close & Load > Close & Load To…, you either load the data to a worksheet or tick “Add this data to the Data Model” ([Microsoft Support](https://support.microsoft.com/en-us/office/create-load-or-edit-a-query-in-excel-power-query-ca69e0f0-3db1-4493-900c-6279bef08df4), read 16 October 2026). The OneDrive section explains why it matters.

**What if the ERP changes a column?** Per Microsoft, a query stops at the step that names a column that no longer exists (“The column … of the table wasn’t found”) and doesn’t load ([Microsoft Learn](https://learn.microsoft.com/power-query/dealing-with-errors), read 16 October 2026). A newly added column is left out if the query has a Choose Columns step, which Microsoft recommends for resilient queries ([Microsoft Learn](https://learn.microsoft.com/power-query/best-practices), read 16 October 2026). A stopped query freezes the “last data pull” time, the first sign of staleness. To fix it, open the failing step under Applied Steps, correct the column name, refresh and check regional totals against the export.

### 4. Figures defined once: each figure in one cell

“Pending orders” is defined once: status “Pending”, net amount ex-VAT. I explain with a bridge example why the written definition comes first on my [Excel report building page](https://oguzerol.com/en/excel-report-building/). Two calculated columns:

- `Orders[Days Waiting]`: `=MAX(0,NETWORKDAYS([@[Order Date]],TODAY())-1)`. It counts working days; Microsoft’s example counts both ends, hence −1, so an order entered today shows 0. MAX stops a weekend order showing −1. Holidays go in the optional third argument ([Microsoft Support](https://support.microsoft.com/en-us/office/networkdays-function-48e717bf-a7a3-495f-969e-5005e3eb18e7), read 16 October 2026).
- `Stock[Critical]`: `=[@[On Hand]]<[@Minimum]`

Dashboard sheet, regions in A5:A7:

- B5 Pending orders: `=COUNTIFS(Orders[Region],$A5,Orders[Status],"Pending")`
- C5 Pending amount: `=SUMIFS(Orders[Net Amount],Orders[Region],$A5,Orders[Status],"Pending")`
- D5 Older than 3 working days: `=COUNTIFS(Orders[Region],$A5,Orders[Status],"Pending",Orders[Days Waiting],">3")`
- E5 Critical stock items: `=IF(COUNTIF(Stock[Region],$A5)=0,"no data received",COUNTIFS(Stock[Region],$A5,Stock[Critical],TRUE))`
- B8 Total: `=SUM(B5:B7)`, B2 last data pull: `=MAX(Orders[Pulled At])`

If a region’s stock file never arrived, E5 says “no data received” rather than 0, which would read as “nothing critical”. I recalculated this logic in the script on the 130-row export: every figure matched, and with the Central file removed E7 said “no data received”. I haven’t run it in Excel; test on a copy first. In the Data Model, the same definition becomes an explicit measure, covered on the Excel report building page.

### 5. PivotTables, slicers and a chart

From inside the table, choose Insert > PivotTable, keep `Orders` as the source, and place it on its own sheet: Region in Rows, Order No (count) in Values, Status = Pending as a filter. Add a Region slicer with Insert > Slicer on Windows and Mac, or PivotTable Analyze > Slicer on the web, and link it to other PivotTables via Slicer > Report Connections. Slicers only connect to PivotTables sharing the same data source ([Microsoft Support](https://support.microsoft.com/en-us/office/use-slicers-to-filter-data-249f966b-a9d5-4b0f-b31a-12651785d29d), read 16 October 2026), so one still on a fixed range won’t appear in that list. Timelines follow the same rule ([Microsoft Support](https://support.microsoft.com/en-us/office/create-a-pivottable-timeline-to-filter-dates-d3956083-01be-408c-906d-6fc99d9fadfa), read 16 October 2026).

PivotTables don’t refresh themselves; Auto Refresh is for now in the Insider programme, otherwise “Refresh data when opening the file” in PivotTable Options refreshes on every open ([Microsoft Support](https://support.microsoft.com/en-us/office/refresh-pivottable-data-6d24cece-a038-468a-8176-8b6568ca9be2), read 16 October 2026). On Windows, Ctrl+Alt+F5 refreshes all workbook data (shortcut page above, read 16 October 2026).

### 6. The dashboard sheet: four figures, one chart, a time and a definition

The most common mistake I see in dashboard builds is trying to put every piece of data on the dashboard; nobody reads the result. The example answers one question: which region needs attention today? Layout:

- **Top rows:** A1 title, A2 “Last data pull” with the time in B2, A3 the definition (net orders ex-VAT, status Pending). Without a time, yesterday’s number reads as today’s.
- **Figure block:** headers A4:E4, regions A5:E7, totals in row 8.
- **Conditional formatting:** select D5:E7, Home > Conditional Formatting > New Rule > Use a formula, `=AND(ISNUMBER(D5),D5>0)`, red fill. ISNUMBER keeps “no data received” out; a Text that Contains rule gives it its own colour. I haven’t tested these rules.
- **Chart:** to the right of the figure block (e.g. from G4), one clustered column chart of pending orders by region via PivotTable Analyze > PivotChart, with the Region slicer above it.
- **Screen and print:** turn off Gridlines (View). On Page Layout, choose Landscape, set the Print Area to cover the block and chart and scale to 1 page wide.

[![Example screen: a regional order dashboard in Excel. The last data pull time and the definition sit at the top; below, North 18, West 9 and Central 11 pending orders; cells for older than 3 working days and critical stock turn red above zero; beside it, a pending-orders-by-region chart and a Region slicer.](https://oguzerol.com/gorseller/senaryo/excel-dashboard-setup-pano-en.webp)](https://oguzerol.com/gorseller/senaryo/excel-dashboard-setup-pano-en.webp)

Example screen of the layout above. The figures match the example table.

## Where do Excel dashboards most often break?

Mostly from habits along the data path. Hand pasting (step 3), fixed ranges (step 2), copied “Dashboard_final_West.xlsx” versions (one file, one location) and “which file is right?” (time and definition on top) are closed above. Two more:

**Merged cells.** Merging deletes the other cells’ contents, and the cells you merge must not be formatted as an Excel table ([Microsoft Support](https://support.microsoft.com/en-us/office/merge-and-unmerge-cells-5cbd15d5-9375-4540-907f-c673a93fcedf), read 16 October 2026). An export with merged headers must be fixed before it becomes a table.

**A file sent with a filtered PivotTable.** It still carries the other regions in the raw data and the PivotTable’s cache. Microsoft says even the “Save source data with file” option shouldn’t be used to manage data privacy ([Microsoft Support](https://support.microsoft.com/en-us/excel/pivottable-options), read 16 October 2026).

**So who sees what in Excel?** Staying in Excel, a workable route is a separate file per region holding only its rows, from a region-filtered query. As regions and people grow, maintaining those files becomes a job. The next options are Power BI’s row-level security and a web panel.

## What changes when you share it on OneDrive or SharePoint?

It solves the copy problem, but co-authoring and browser refresh have documented limits (Microsoft pages, read 16 October 2026):

- **Co-authoring** needs OneDrive, OneDrive for Business or SharePoint Online and, on the desktop, .xlsx/.xlsm/.xlsb with Microsoft 365; on-premises SharePoint isn’t supported ([Microsoft Support](https://support.microsoft.com/en-us/office/collaborate-on-excel-workbooks-at-the-same-time-with-co-authoring-7152aa8b-b791-414c-a3bb-3024e46fb104)).
- **One user can lock everyone out** by opening the file in a version without co-authoring.
- **In a conflict, the last save wins**, with some exceptions.
- **The change trail has gaps.** Show Changes lists who changed which cell, but not formatting, PivotTable operations, hiding or filtering, and an edit in an older Excel clears it ([Microsoft Support](https://support.microsoft.com/en-us/excel/get-help-with-show-changes-in-excel)).
- **Power Query in the browser** doesn’t yet refresh queries loaded to the Data Model, third-party cloud files or gateway sources ([Microsoft Support](https://support.microsoft.com/en-US/excel/power-query-data-sources-in-excel-versions)).
- **Browser slicers** can only be created for local PivotTables ([Microsoft Support](https://support.microsoft.com/en-us/office/use-slicers-to-filter-data-249f966b-a9d5-4b0f-b31a-12651785d29d)).

The fifth point holds a contradiction: the Data Model is recommended for measures defined once, yet a browser-based team can’t refresh it, so the dashboard stays stale until someone refreshes on the desktop. My preference: for such a team, I pick a worksheet table in Close & Load To and keep definitions in cells. I use the Data Model only when one person refreshes on the desktop; that person and the refresh time are written at the top of the dashboard.

In Google Sheets, slicer selections stay private unless set as the default ([Google Help](https://support.google.com/docs/answer/9245556?hl=en), read 16 October 2026), so filtering doesn’t change others’ screens, but it doesn’t stop anyone seeing another region either.

## When is Power BI enough?

If you need reporting and sharing inside the Microsoft ecosystem, Power BI is usually worth trying before a custom panel; row-level security (RLS) can show a regional manager only their region. Four details:

- **Licensing.** On 16 October 2026 Microsoft’s pricing page showed $14 per user per month for Pro and $24 for Premium Per User, paid yearly ([Microsoft](https://www.microsoft.com/en-us/power-platform/products/power-bi/pricing)). It states no currency or update date, and Microsoft says prices may vary by region. Outside Premium or Fabric F64+ capacity, viewers also need Pro or PPU ([Microsoft Learn](https://learn.microsoft.com/power-bi/explore-reports/end-user-shared-with-me), read 16 October 2026).
- **RLS** restricts only the Viewer role; Admin, Member and Contributor see everything ([Microsoft Learn](https://learn.microsoft.com/fabric/security/service-admin-row-level-security), read 16 October 2026).
- **Writing from the report, the Fabric route.** Translytical task flows let a report button add, edit or delete records in Fabric SQL databases, warehouses or lakehouse files through Fabric User Data Functions, or post an approval request to Teams ([Microsoft Learn](https://learn.microsoft.com/power-bi/create-reports/translytical-task-flow-overview), read 16 October 2026). Creating those functions requires a Fabric capacity ([Microsoft Learn](https://learn.microsoft.com/fabric/data-engineering/user-data-functions/create-user-data-functions-portal), read 16 October 2026).
- **Writing from the report, the Power Apps route.** Data is entered through a Power Apps visual embedded in the report; its app is shared separately ([Microsoft Learn](https://learn.microsoft.com/power-apps/maker/canvas-apps/powerapps-custom-visual), read 16 October 2026). Check licensing separately.

So the example’s rules can be built in the Microsoft ecosystem too; the price is separate Fabric or Power Apps setup and licensing.

### Excel, Power BI or a web panel?

| Your situation | Tool | Why |
| --- | --- | --- |
| One person prepares it, everyone may see all regions, follow-up runs elsewhere | Excel (six layers) | Nobody enters data or approves on it |
| Different views per region, no data entry | Power BI (RLS) | The job is reading and visibility |
| Rule-based entry and approval, Microsoft lock-in is fine | Power BI + Fabric or Power Apps | Report write-back works, with extra capacity and setup |
| Regional views, entry, approvals and audit trail together, no ecosystem tie | Web panel | Visibility, entry and approval share one permission rule |

In the first row, your own team can build this; you don’t need me. What closed the 5-order gap was a table, not a web panel.

## Where does the move to a web panel begin?

When the dashboard stops being a page people read and becomes a screen people work on. In the example, five needs cross that line:

1. **Different data per person.** The West manager’s request returns only West’s rows; it’s a query rule, not on-screen hiding.
2. **Rule-based data entry.** Warehouse staff enter counts on their own screen; a defined business rule is applied as data is entered, rejecting negative stock or unknown item codes. I describe branch-scoped entry on the [branch-based data entry page](https://oguzerol.com/en/branch-based-data-entry-system/).
3. **Audit trail.** Who opened the M45 request, changed the minimum, approved it: each change kept with time and name, as a row the application doesn’t let anyone delete. Excel’s Show Changes pane shows no approval record.
4. **Automatic refresh.** The export is pulled on schedule; a failed pull shows “no data received” and the last good time.
5. **Mobile.** Counts entered and alerts seen on a phone.

### Role table

The basic rule I recommend: nobody approves a request they opened.

| Role | Sees | Enters | Approves |
| --- | --- | --- | --- |
| General manager | All regions | Nothing | Reorders above the threshold |
| Regional manager | Own region | Order notes, reorder requests | Nothing |
| Warehouse staff | Own warehouse | Stock counts | Nothing |
| Purchasing manager | Critical stock, all regions | Supplier delivery dates | Regional requests |
| Auditor | Everything, plus audit trail | Nothing | Nothing |

Here the West manager opens the 16-unit M45 request and purchasing approves it. The threshold is the company’s call.

### What does AI do, and what doesn’t it do?

AI produces no numbers; every figure comes from a query. It suggests notes such as “all four old West orders wait for the M45 seal”, built from rows the user can see, and each note waits for the manager’s approval.

## What I look at first, and what you get if a panel is chosen

First I look at your dashboard, a few days of exports (customer names masked) and the decisions taken from it. The written output: definitions, breaking points, my Excel-or-panel recommendation and the first version’s scope. If we stay in Excel, what you receive is your own workbook, with the scope set in writing in the first step.

If a panel is chosen, the application runs on a server (VPS) that can be opened in your company’s name, the data is yours and you can export it as Excel or CSV at any time, there are no per-user licence fees for a small or mid-sized team (the cost is the server plus maintenance), and the full source code sits in a repository you can access, with run instructions, a dependency list and a list of known limits. If personal data is involved, access, security, hosting and maintenance are assessed separately. Screens are built to suit your needs; the cost follows the scope and comes in writing in the first step. More examples are on the [custom web apps for your team page](https://oguzerol.com/en/custom-web-apps-for-your-team/).

**What if your ERP changes?** Old exports, definitions and the audit trail stay. How the new export maps onto old columns is set in writing in the first step.

Send a copy of your dashboard and one day’s export through the [contact page](https://oguzerol.com/en/contact/); in the first meeting we’ll find where the numbers go missing.

## Frequently Asked Questions

### Which version of Excel do I need for a dashboard?

Microsoft’s Power Query page lists Excel 2016, 2019, 2021, 2024 and Microsoft 365, with the tools under Get & Transform Data on the Data tab; Excel 2016 and 2019 for Mac lack Power Query ([Microsoft Support](https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a), read 16 October 2026). Desktop co-authoring needs Microsoft 365, and one person in an older version can lock the file.

### How does the dashboard look on a phone?

Excel for Android and iOS opens .xlsx files on OneDrive or SharePoint and supports co-authoring ([Microsoft Support](https://support.microsoft.com/en-us/office/collaborate-on-excel-workbooks-at-the-same-time-with-co-authoring-7152aa8b-b791-414c-a3bb-3024e46fb104), read 16 October 2026). I haven’t measured it; I’d expect a landscape layout to need scrolling. For phone readers, I suggest copying A1:E8 to a single-column sheet without chart or slicer. If the phone is the main workplace, that screen needs its own design, one reason to move to a web panel.

### Can our existing dashboard file be fixed, or does it need rebuilding?

Usually fixed. I mark fixed ranges, merged cells and pasted-into sheets; charts and layout stay while the data path moves to a table and Power Query. If the export changes columns daily or the dashboard has split into copies, rebuilding the data layer is quicker. I say which in the first step’s output.

[Back to top ↑](https://oguzerol.com/en/excel-dashboard-setup/#icerik)

## You may also find these useful

[Combine Excel Files with Power Query or an Import AppCombine Excel files using Power Query or a controlled import screen. Map columns, trace source rows and check duplicate records before publishing results.](https://oguzerol.com/en/automatically-combine-excel-files/)[Excel Allow Users to Edit Ranges: Permissions ExplainedSet up Excel Allow Users to Edit Ranges, check Windows domain and Mac limitations, and distinguish editing rights from private record access.](https://oguzerol.com/en/excel-cell-permissions-panel/)[Mobile Alternative to an Excel Form: Recording Goods-In Inspection from a PhoneWhen field staff type into a shared spreadsheet on their phones and records slip: required fields, controlled lists, linked photos and approval. A sample month.](https://oguzerol.com/en/mobile-excel-form-alternative/)

[Browse all articles →](https://oguzerol.com/en/articles/)

WHAT YOU CAN ASK FOR

## How do I work?
Work-hour packages

Tell me about your business and what you need on WhatsApp or by phone; we agree on **a work block of somewhere between 5 and 40 hours** and I start doing what is needed. What you get is not a calendar window — it is net working hours spent directly on your business. I can use 40 hours working three days straight, or deliberately spread it out and finish it over a year — whatever the work calls for. I do not spend my time estimating how long something will take, I spend it doing the work; every hour I spend leaves a lasting result in your business. We keep going for as long as you find it useful.

WORK BLOCKS

## Why not monthly?
Is 40 hours a week?

On a fixed monthly fee both sides are watching the wrong thing: you watch a fixed cost, the other side watches how many more contracts like this they can line up. Working by the hour puts us both in front of the same thing — the real value of the work that got done. I know not having an upfront answer to "how much will this cost in total?" is the natural cost of this model. But instead of paying a fixed fee for months to a setup that is not producing results, you only pay for the qualified time spent on your work — and you see exactly where that time went, start to finish. That is why my clients keep coming back.

5hours300€ + VAT60€ per hour[Choose this block](https://wa.me/905496546489?text=Hi%2C%20I%27d%20like%20info%20about%20the%205-hour%20work%20block.)

10hours500€ + VAT50€ per hour[Choose this block](https://wa.me/905496546489?text=Hi%2C%20I%27d%20like%20info%20about%20the%2010-hour%20work%20block.)

20hours800€ + VAT40€ per hour[Choose this block](https://wa.me/905496546489?text=Hi%2C%20I%27d%20like%20info%20about%20the%2020-hour%20work%20block.)

40hours1,400€ + VAT35€ per hour[Choose this block](https://wa.me/905496546489?text=Hi%2C%20I%27d%20like%20info%20about%20the%2040-hour%20work%20block.)