Excel Dashboard Template Generator

About this template

A dashboard is the page you look at when you want the answer without the working. This builds one as two sheets: Data, where the numbers live, and Dashboard, which reads them. You never type on the Dashboard sheet. Fill the month rows on Data and every summary on the Dashboard moves.

It is for a small business, a team or a side project that tracks a handful of monthly numbers - revenue, costs, customers, orders, sign-ups, tickets, whatever matters - and wants them summarised without building formulas each month. The metric list is yours: name them on this page and the workbook is laid out for exactly those.

The Data sheet is simple on purpose: row 3 is the header (Month, then one column per metric), rows 4 to 15 are the twelve months with cream input cells, and below them a Total row and an Average row. The Dashboard sheet has one row per metric with eight columns: Metric, Total, Average, Best month, Best value, Latest, Change vs previous, and Trend. Below that block a month-by-month table lists your first metric with a bar next to each month, so the shape of the year is visible at a glance.

There are no chart objects. Trend bars are text - a run of block characters whose length is proportional to the value - written by the REPT function. That is deliberate: a text bar survives every spreadsheet program, prints exactly as it looks, and never detaches from its data the way a chart can when rows are inserted. If you want a real chart afterwards, the Data sheet is already the right shape to select and insert one.

How to use it

1. Name your metrics before you download - List them one per line. The first metric gets the month-by-month bar table on the Dashboard, so put the one you care most about first - usually revenue. Metrics whose names appear in the money list are formatted as currency; the rest as whole numbers.
2. Type monthly figures on the Data sheet - With the defaults that is B4:E15. Fill months as they happen and leave future months blank rather than typing zero: Latest and Change vs previous count the filled cells to find the current month, and a typed zero would be read as a real result.
3. Read the Dashboard - Total and Average cover every filled month. Best month names the month with the highest value and Best value shows it. Latest is the most recent filled month and Change vs previous is that figure minus the month before it. The Trend cell is a bar showing the latest month as a fraction of the best month.
4. Use the month-by-month table for the shape of the year - Below the metric block, each month of your first metric is listed with a bar scaled to the best month. A bar of twenty blocks is the peak; ten blocks is half of it. It is the quickest way to see seasonality without inserting a chart.
5. Add a metric later - Add a column on the Data sheet, then copy an existing Dashboard row and edit its column letters from B to the new column. Every formula on a Dashboard row points at one Data column, so a row is one find-and-replace away from tracking something new.
6. Insert a real chart if you want one - Select A3 to the last metric column and last month row on the Data sheet and use Insert > Chart. The data is already one series per column with a month label in the first column, which is exactly what a line or column chart expects.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =SUM(Data!B4:B15) - Total for the metric, on the Dashboard's first metric row: the twelve month cells of the Revenue column on the Data sheet added together. Blank months count as nothing, so the total is correct part-way through the year.
• =AVERAGE(Data!B4:B15) - The average of the filled months only. AVERAGE ignores blank cells, which is why leaving a future month empty is the right thing to do and typing a zero is not.
• =IFERROR(INDEX(Data!$A$4:$A$15,MATCH(MAX(Data!B4:B15),Data!B4:B15,0)),"") - Best month. MAX finds the highest value in the metric column, MATCH finds which row it is on, and INDEX reads the month name from the same row of column A. IFERROR blanks the cell until at least one month is filled, instead of showing an error.
• =IFERROR(INDEX(Data!B4:B15,COUNT(Data!B4:B15)),0) - Latest. COUNT counts the filled month cells, and INDEX reads the value in that position - the most recent month you have entered. It assumes months are filled in order from the top, which is how the sheet is laid out.
• =IF(COUNT(Data!B4:B15)<2,0,INDEX(Data!B4:B15,COUNT(Data!B4:B15))-INDEX(Data!B4:B15,COUNT(Data!B4:B15)-1)) - Change vs previous: the latest month minus the one before it. The IF guards the first month, when there is no previous figure to subtract, and shows 0 rather than an error.
• =IFERROR(REPT("█",ROUND(10*F4/E4,0)),"") - The Trend bar on a metric row: the latest value divided by the best value, scaled to ten blocks and rounded, then REPT repeats the block character that many times. Ten blocks means the latest month is the best month so far. IFERROR handles the division before any data exists.
• =IFERROR(REPT("█",ROUND(20*Data!B4/MAX(Data!$B$4:$B$15),0)),"") - The bar next to each month in the month-by-month table: that month's value as a share of the best month, scaled to twenty blocks. The MAX range is anchored with dollar signs so every row compares against the same peak.

Customising it

• Make the bars a colour: select the Trend column and set the font colour - the block character takes whatever colour the cell's text is, so a green bar column is one formatting change.
• Turn the month table into a chart when you want one: select the Month and value columns of the table and Insert > Column Chart. The bar column can stay; it costs nothing.
• Track against a target by adding a Target column on the Dashboard and a formula such as =B4/I4 formatted as a percentage, where I4 holds the annual target for that metric.
• Add a year-to-date column on the Data sheet with =SUM($B$4:B4) filled down, if you want a running total beside the monthly figures.
• Shorten the year: generate with fewer months for a quarter or a half-year dashboard. Every formula range is written to match the month count, so a three-month sheet sums three rows, not twelve.