What a dashboard is and why you'd build one

A dashboard in Excel is a single sheet that pulls numbers and charts from your other sheets and shows you the current state of something at a glance — your household budget, a project timeline, inventory counts, or sales figures. Instead of opening five different sheets and scrolling through rows of data, you see the key numbers and trends on one page.

You build a dashboard by linking cells to data elsewhere in your workbook, then arranging those cells and charts so the important information jumps out. Excel does the updating automatically: when you change a number on a source sheet, the dashboard updates when ready.

The main reason to build one is speed. If you track something regularly — monthly expenses, weekly sales, daily task completion — a dashboard saves you the time of hunting through sheets every time you need the current picture.

Key Takeaways

  • A dashboard pulls data from other sheets using formulas, so changes to your source data update the dashboard automatically.
  • Start by deciding what numbers matter most, then arrange them on a blank sheet with clear labels and formatting.
  • Use straightforward formulas like =SUM() and =AVERAGE() to pull totals from other sheets, and reference cells by sheet name.
  • Charts and conditional formatting (color-coding cells based on their values) make patterns visible without reading numbers.
  • A well-built dashboard takes 30 minutes to an hour the first time, then requires only updates to your source data.

Set up a blank sheet and decide what to show

Create a new sheet in your workbook by right-clicking the sheet tab at the bottom and selecting Insert Sheet. Name it "Dashboard" so you can find it easily. This sheet will stay mostly empty except for the numbers and charts you add.

Before you start building, list the three to five numbers that matter most. If you're tracking a budget, that might be total income, total spending, and remaining balance. If you're tracking a project, it might be tasks completed, tasks remaining, and days until important date. Write these down — they become your dashboard's structure.

The reason to decide first is that a cluttered dashboard defeats its purpose. You want to see the answer in five seconds, not hunt through ten charts. Stick to what you actually check regularly.

Pull data from other sheets using formulas

Click a cell on your dashboard sheet where you want your first number to appear. Type a formula that references a cell on another sheet. The format is =SheetName!CellReference. For example, if your budget data is on a sheet called "Monthly" and your total spending is in cell B15, you would type =Monthly!B15.

If you need to add up numbers across multiple rows, use =SUM(). To add all expenses in column B on the Monthly sheet, type =SUM(Monthly!B2:B50). To find an average, use =AVERAGE() the same way. Press Enter, and the number appears on your dashboard.

The cell now shows the current value from that other sheet. If you change the source data, the dashboard updates when ready. You don't have to do anything — Excel watches for changes and recalculates.

Repeat this for each number you decided to show. Label each one clearly in the cell to the left or above so you know what the number means.

Format your dashboard so numbers stand out

Select the cells with your key numbers and make them larger and bold. Click the font size dropdown (usually showing "11") and choose 18 or 20. Then click the Bold button. This makes the dashboard readable from across the room.

Add labels above or beside each number so anyone looking at the sheet knows what they're seeing. Use a slightly smaller font for labels — 12 or 14 — so the numbers remain the focus.

Consider adding color. Select a cell with a number, then click the paint bucket icon in the toolbar and choose a light background color. This separates your dashboard numbers from the rest of the sheet. Avoid bright colors — pale blue, pale green, or pale yellow work better than neon.

Leave white space around your numbers. A crowded dashboard is harder to read than one with breathing room. Arrange your key numbers in a grid or list with at least one blank row between sections.

Add charts to show trends and patterns

Charts let you see patterns that raw numbers hide. If you have monthly totals on another sheet, a chart shows whether spending is going up or down without you having to compare numbers.

Select the data you want to chart. If your monthly expenses are in cells A1:B12 on your "Monthly" sheet (with month names in column A and amounts in column B), click and drag to select that range. Then click Insert in the menu, then Chart. Excel opens a chart wizard.

Choose a chart type. For most dashboards, a column chart (vertical bars) or line chart works well. Column charts are good for comparing amounts across categories. Line charts are good for showing change over time. Click Next and follow the prompts, then click Finish.

The chart appears on your sheet. Click and drag it to position it on your dashboard. Resize it by dragging the corners. A chart should be large enough to read at a glance but not so large it overwhelms the numbers.

Use conditional formatting to highlight important values

Conditional formatting colors a cell based on its value, so you spot problems when ready. If you're tracking a budget and you want to flag spending that exceeds a limit, conditional formatting can turn that cell red automatically.

Select the cell or cells you want to format. Click Home in the menu, then Conditional Formatting, then Highlight Cell Rules. Choose a rule — for example, Greater Than — and enter the threshold value. If you want to flag any spending over $500, enter 500. Choose a color (red is standard for "watch out") and click OK.

Now that cell turns red whenever its value exceeds 500. You don't have to set this up again — it updates automatically as your data changes. This is especially useful for dashboards you check regularly, because the color catches your eye before you read the number.

You can also use conditional formatting to show progress. If you're tracking project completion and want cells to turn green as they reach 100%, set up a rule for Greater Than or Equal To 100 with a green background.

Test your dashboard and keep it current

Change a number on one of your source sheets and watch your dashboard update. If it doesn't, check that your formula references the right sheet name and cell. A common mistake is typing the sheet name wrong — Excel is picky about spelling and spaces.

Open your dashboard regularly and update the source data. The dashboard itself requires no maintenance; it pulls from your other sheets automatically. Your only job is keeping the source data current.

If you find yourself adding new numbers or charts later, follow the same steps: add a formula or chart, format it to match the rest of the dashboard, and position it on the sheet. A dashboard is not a one-time project — it grows as your tracking needs change.

Frequently Asked Questions

Can I link a dashboard to data in a different Excel file?

Yes, but with a catch. Type the formula as =[FilePath]SheetName!CellReference, with the full file path in square brackets. The link works as long as both files stay in the same location. If you move the source file, the link breaks and you have to fix it manually.

What if my source data is on multiple sheets?

You can pull from as many sheets as you need. Each formula references one sheet at a time, so use separate formulas for each sheet. If you need to add numbers from two different sheets, use =Sheet1!B5+Sheet2!B5.

How do I make a chart update when I add new data?

When you create a chart, select a range that includes empty rows below your current data. For example, select B2:B50 even if you only have data through B12. When you add new data in B13, the chart includes it automatically. If your range is too small, you have to edit the chart later.

Can I password-protect my dashboard so others can't change it?

Yes. Click Review in the menu, then Protect Sheet. Set a password and choose which actions people can take (usually you allow viewing and selecting but block editing). Anyone opening the sheet will see your dashboard but can't change the formulas or formatting.

What's the fastest way to build a straightforward dashboard?

Start with three numbers and one chart. Add labels, format the numbers large and bold, and position them on a blank sheet. This takes 15 to 20 minutes and gives you 80 percent of the value. Add more numbers and charts later if you find you need them.