Project Finance Dashboard in Excel with PivotTables and Slicers

This project finance dashboard in Excel shows project value, costs, savings and project status on one interactive page. It is made for organizations running several projects at once, where finance and project teams need the same numbers in front of them. Episode #1 of the tutorial series starts the build with PivotTables and charts, and you can follow along with the free dataset.

The complete file is available as the Dynamic and Interactive Financial Status and Project Milestone Dashboard, if you want the finished result while you learn the build.

What the project finance dashboard covers

The page links financial performance to project progress, so you can see not only how much a project is worth but where it stands.

Financial status

• Project value
• As-built costs
• Budget savings
• PAT / GAP

Projects and milestones

• Total projects
• Projects that are current, completed or canceled
• Submissions, relationships and departmental contributions

Three slicers control the interactive Excel dashboard

Every analysis on the page is connected to three slicers:

1. Yearly slicer to compare one year with another.
2. Departments slicer to focus on a single department's projects.
3. Project status slicer to isolate current, completed or canceled work.

Combining them answers questions in a couple of clicks, for example: what were the budget savings for one department's completed projects in a given year?

How Episode #1 builds the PivotTable dashboard

The title says it plainly: the dashboard is built with PivotTables and PivotCharts. That choice is what makes it dynamic. Here is the logic behind the approach:

• One data table, many PivotTables. Each KPI and chart gets its own small PivotTable built from the same project table. Microsoft's guide to creating a PivotTable to analyze worksheet data covers the basic steps.
• Charts from PivotTables. Because the charts sit on PivotTables, they redraw instantly when a slicer changes.
• Slicers shared across PivotTables. Using Report Connections, the year, department and status slicers are linked to every PivotTable at once, which is how a single click updates the full page.

This first episode is the foundation for the rest of the series, and a full version of the tutorial with voiceover explanation is also available on our YouTube channel. For a deeper look at the chart side, read our guide on how to create and use PivotCharts in Excel.

Follow along with the dataset

The fastest way to learn this is to build it yourself. You can download the finance status and project milestone dataset and repeat each step from the video. For more files, see our free Excel practice datasets.

A few habits that make following along easier:

• Convert the data to an Excel Table (Ctrl+T) before inserting PivotTables, so new rows are picked up on refresh.
• Put all PivotTables on a separate calculations sheet and keep the dashboard sheet for visuals only.
• Give each PivotTable a clear name (for example pvtSavings) so Report Connections are easy to manage.

Questions the dashboard answers in a few clicks

Once the three slicers are connected, the page becomes a quick way to answer the questions that normally need a separate report:

• How many projects does each department have, and how many are still current?
• What is the total project value this year compared with last year?
• How do as-built costs compare with project value for completed projects?
• Which department contributes the most, and how much budget has been saved?
• How many projects were canceled, and in which years?

Instead of rebuilding a table for every question, you change a slicer and read the answer, which is exactly why this style of dashboard is useful in project and finance meetings.

Who should use it

• Project managers who want milestone status and financials side by side.
• Finance teams tracking project value, as-built costs and savings.
• Department heads who need to see their share of the portfolio.
• Anyone presenting to stakeholders who wants clear, filterable visuals instead of long tables.

Tips to adapt it to your own projects

• Keep status values fixed. Use the same three labels (current, completed, canceled) in every row so the status slicer stays clean.
• Add a year column derived from the project date if your data only has full dates. The yearly slicer needs it.
• Check your savings formula. Decide how budget savings are calculated in your organization and apply it consistently in the source table.
• Rename departments once in the data, not on the dashboard, so the slicer, charts and totals always agree.

More project tracking layouts are in our project dashboard templates collection.

Common mistakes when building a project finance dashboard

• Building charts from raw data instead of PivotTables. Regular charts will not respond to slicers, so the page stops being interactive.
• Forgetting Report Connections. A new PivotTable is not linked to existing slicers until you connect it, so one chart silently ignores the filters.
• Mixing labels. Two spellings of the same department or status create two slicer buttons and split the totals.
• Not refreshing. After adding rows, use Refresh All so every PivotTable and chart picks up the new data.

Frequently asked questions

How do I create an interactive dashboard in Excel with PivotTables?

Convert your data to a table, build one PivotTable per KPI or chart, and create PivotCharts from them. Then insert slicers and connect each slicer to all PivotTables with Report Connections.

Which slicers does the project finance dashboard use?

Three: year, department and project status. All the analysis on the page is connected to them.

Is there a dataset to practice with?

Yes. The finance status and project milestone dataset is free to download from our datasets blog.

Get the project finance dashboard template

Want the finished version with all three slicers already connected? Get the financial status and project milestone Excel dashboard. You can also browse all our Excel dashboard templates for more ready-made files.