Excel & data
One Page of Numbers Beats Twenty Tabs: Building a Useful Excel Dashboard
A good dashboard answers a few questions at a glance. Excel's PivotTables make that possible when the data underneath is clean.
Start with the questions
A dashboard is not a collection of charts. It is a page that answers a short list of questions: How much did we make this month? Which product earns the most? What is low on stock? If you cannot write the questions down, adding more charts will not help.
Clean data first
Microsoft's guidance for PivotTables is simple. Data should be arranged in columns under a single header row, with no blank rows or columns, and each column should hold one type of data, for example all dates or all currency values. Using an Excel table as the source is recommended because rows added to the table are picked up when you refresh. After you add new data, the PivotTables built on it must be refreshed.
Summarize, then show
A PivotTable summarizes thousands of rows by whatever you drag into it, such as month, product or channel. Charts and key figures then read from those summaries. The result is one page that updates when the data does, instead of twenty tabs that must be edited by hand.
A ready-made example
Our Multi-Channel Seller Profit & Inventory Dashboard is built on this pattern: one sales log, automatic calculations, and a dashboard that shows true profit per order, platform, product and month. More on our approach is on the Excel tools page.
