Healthcare Analytics Dashboard in Excel

Transform messy patient data into an interactive Excel dashboard.

Introduction

30 Second Summary

Hospitals collect mountains of patient data every day. The challenge is turning that raw data into clear, actionable insights that help teams make better decisions about care, costs, and capacity.

In this project, you will build an interactive healthcare analytics dashboard in Excel that transforms messy patient records into professional visualizations with dynamic filtering.

What You'll Build

A fully interactive Excel dashboard that visualizes patient treatment data across multiple hospitals using Power Query, Pivot Tables, the PIVOTBY function, and Slicers.

By the end of this project, you'll have:

A clean Excel workbook with Power Query transformations that turn raw patient data into analysis-ready tables with one-click refresh.
Working examples of both traditional Pivot Tables and the modern PIVOTBY formula function, with a clear understanding of when to use each.
A polished interactive dashboard with KPI cards, charts, and slicers that filter everything simultaneously.
Secret Mission: a Top Doctors leaderboard powered by PIVOTBY with a conditional formatting heat map for instant pattern recognition.

Are there any prerequisites?

You just need basic familiarity with Excel (opening files, entering data, navigating the ribbon). No prior experience with Pivot Tables or Power Query is required.

Set Up Your Workspace and Data

Every dashboard starts with structured, reliable data. Before you can build charts, slicers, or KPI cards, you need a workspace with clean, well-organized information that Excel's analytics tools can read.

In this step, you'll create a new workbook, generate a 200-row healthcare dataset using Excel formulas, and convert it into a proper Excel Table. This table becomes the foundation for everything you build in later steps.

In this step, get ready to:

Confirm Power Query is accessible in your Excel ribbon.
Create a 200-row healthcare dataset with patient, department, and cost data.
Convert the data into a named Excel Table for seamless analytics.

Confirm Power Query is accessible

You already know you have a compatible version of Excel. Now you need to visually confirm that Power Query is available in the ribbon so you can use it in the next step.

Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
Type Excel and press Enter to open it.
Click the Data tab in the ribbon.
Look for a section labelled Get & Transform Data (or a Get Data button).

If you see either option, Power Query is ready to go.

Don't see Get & Transform Data?

This group only appears in Microsoft 365, Excel 2024, or Excel 2021. If you're on an older version, you'll need to upgrade before continuing.

On Mac, the button may simply say Get Data rather than showing the full group name. As long as you see that button in the Data tab, you're all set.

help me find Power Query in my version of Excel

Create the workbook and generate sample data

Your dashboard needs realistic patient data to work with. You'll use Excel formulas to generate 200 rows of randomized healthcare records covering multiple departments, insurance providers, and admission types.

In Excel, create a new blank workbook by pressing Cmd+N (macOS) or Ctrl+N (Windows).
Save the workbook as Healthcare_Dashboard.xlsx by pressing Cmd+S (macOS) or Ctrl+S (Windows).
Double-click the sheet tab at the bottom (currently named Sheet1) and rename it to Data.

Now add the column headers that define the structure of your dataset.

Click cell A1 and type the following headers across row 1, one per cell from A1 to L1:

Patient_ID | Name | Age | Gender | Department | Admission_Type | Insurance_Provider | Treatment_Cost | Hospital_Stay_Days | Recovery_Score | Date_of_Admission | Doctor_Name

What do these columns represent?

Patient_ID is a unique number identifying each patient record.
Department and Admission_Type let you slice data by hospital unit and urgency level.
Treatment_Cost, Hospital_Stay_Days, and Recovery_Score are the numeric metrics your dashboard will visualize.
Date_of_Admission enables time-based trend analysis.

Next, you'll use formulas to auto-generate 200 rows of realistic patient records.

Enter the following formulas in row 2, one formula per cell from A2 to L2:

A2: =1000+ROW()-1
B2: =CHOOSE(RANDBETWEEN(1,10),"James","Maria","Robert","Sarah","David","Emily","Michael","Jennifer","William","Lisa")&" "&CHOOSE(RANDBETWEEN(1,10),"Smith","Johnson","Williams","Brown","Jones","Garcia","Miller","Davis","Martinez","Wilson")
C2: =RANDBETWEEN(18,85)
D2: =CHOOSE(RANDBETWEEN(1,2),"Male","Female")
E2: =CHOOSE(RANDBETWEEN(1,5),"Cardiology","Orthopedics","Neurology","Pediatrics","Oncology")
F2: =CHOOSE(RANDBETWEEN(1,3),"Emergency","Elective","Urgent")
G2: =CHOOSE(RANDBETWEEN(1,5),"Medicare","Cigna","Blue Cross","UnitedHealthcare","Aetna")
H2: =RANDBETWEEN(5000,100000)
I2: =RANDBETWEEN(1,30)
J2: =RANDBETWEEN(50,100)
K2: =DATE(2024,RANDBETWEEN(1,12),RANDBETWEEN(1,28))
L2: =CHOOSE(RANDBETWEEN(1,10),"Dr. Chen","Dr. Patel","Dr. Thompson","Dr. Rodriguez","Dr. Kim","Dr. Johnson","Dr. Williams","Dr. Ahmed","Dr. Murphy","Dr. Singh")

What do these formulas do?

Each formula uses RANDBETWEEN to pick a random value within a realistic range. CHOOSE maps the random number to a specific text value from a predefined list.

Together, they produce one complete patient record with a mix of departments, insurance providers, and numeric metrics that mirror real healthcare data.

Now you need to extend these formulas down to create 200 total rows of data.

Select the entire row 2 by clicking cell A2, then pressing Cmd+Shift+Right Arrow (macOS) or Ctrl+Shift+Right Arrow (Windows) to highlight through L2.
Copy the selection by pressing Cmd+C (macOS) or Ctrl+C (Windows).
Select the range A3:A201 by clicking A3, then pressing Cmd+Shift+End won't work here since the sheet is empty below. Instead, click the Name Box (left of the formula bar), type A3:L201, and press Enter.
Paste by pressing Cmd+V (macOS) or Ctrl+V (Windows).

You should now see 200 rows of patient data in rows 2 through 201. The values will keep changing because RANDBETWEEN recalculates every time Excel refreshes. You need to freeze the data by replacing the formulas with their current values.

Select all your data by clicking A1, then pressing Cmd+Shift+End (macOS) or Ctrl+Shift+End (Windows) to select everything through L201.
Copy the selection with Cmd+C (macOS) or Ctrl+C (Windows).
Open Paste Special by pressing Cmd+Shift+V (macOS) or Ctrl+Alt+V (Windows).
Select Values and click OK.

Why paste as values?

Pasting as values replaces the formulas with static numbers and text. Without this step, your data would keep changing every time you edit the workbook, making your dashboard metrics unreliable.

Save the workbook with Cmd+S (macOS) or Ctrl+S (Windows).
Click on any cell with data (for example E5) and check the formula bar. You should see a plain text value like Cardiology instead of a formula.

Still seeing formulas in the formula bar?

The Paste Special step may not have applied correctly. Make sure you selected the entire data range (A1 through L201) before copying, and chose Values in the Paste Special dialog.

If your data looks blank or shows errors, undo with Cmd+Z (macOS) or Ctrl+Z (Windows) and try the copy-paste-values sequence again.

help me freeze my formula data as static values in Excel

Convert your data to an Excel Table

An Excel Table is a structured data object that automatically expands when you add rows. Pivot Tables and Power Query reference Tables by name, so your analytics will always stay in sync with your data.

Click any cell inside your data (for example A1).
Press Cmd+T (macOS) or Ctrl+T (Windows) to open the Create Table dialog.
Confirm that My table has headers is checked, then click OK.

Your data should now have alternating row colors and filter arrows on each header. Next, give the table a meaningful name.

Click the Table Design tab in the ribbon (it appears when your cursor is inside the table).
On the left side, find the Table Name field.
Replace the default name with tblPatients and press Enter.

Why name the table tblPatients?

Named tables make formulas and Power Query connections easier to read. Instead of referencing a cell range like A1:L201, you can reference tblPatients[Department] to grab an entire column by name.

The tbl prefix is a common naming convention that helps you distinguish tables from named ranges at a glance.

Save the workbook with Cmd+S (macOS) or Ctrl+S (Windows).
Confirm the table name by clicking any cell in the table, then checking the Table Design tab. The Table Name field should show tblPatients.

Don't see the Table Design tab?

The Table Design tab only appears when your cursor is inside the table. Click any cell between A1 and L201 to make it appear.

On some Mac versions, this tab may be called Table instead of Table Design. Look for the Table Name field on the far left of that tab.

help me find the Table Design tab to rename my Excel Table

Your workspace is locked in. You have 200 rows of structured healthcare data sitting in a named Excel Table, ready for analysis. Next up, you'll use Power Query to clean and transform this data by adding calculated columns like age groups and cost categories.

Clean and Transform Data with Power Query

Your raw patient data is sitting in a nicely structured table, but it is missing some key columns that your dashboard needs.

For example, you will want to group patients by age range, categorize treatments by cost, and track admissions by month. You could add these columns manually with formulas, but Power Query lets you build a repeatable transformation pipeline that refreshes with one click whenever your data changes.

In this step, get ready to:

Open the Power Query Editor and review your data quality.
Add four calculated columns (Age_Group, Cost_Category, Month, and Year).
Load the transformed data to a new sheet and the Data Model.

Open Power Query Editor and review your data

Before transforming data, you need to load it into the Power Query Editor. This is a separate workspace where you build transformation steps without modifying your original table.

Click any cell inside your tblPatients table on the Data sheet.
Go to the Data tab in the ribbon.
Click From Table/Range (on some versions, this appears under Get Data). This opens the Power Query Editor with your table loaded.

What is the Power Query Editor?

The Power Query Editor is a separate window where you shape and clean data before loading it back into Excel. Every action you take becomes a recorded step in the Applied Steps pane on the right side.

These steps replay automatically whenever you refresh the query. This makes your transformations repeatable without any manual work.

In the Power Query Editor, click the View tab.
Check the Column Quality checkbox. Green bars appear below each column header showing what percentage of values are valid, have errors, or are empty.
Confirm that your columns show mostly green (valid). Red bars indicate errors and grey bars indicate empty values.

Why check Column Quality first?

Column Quality gives you a quick visual summary of data health. Spotting errors before you build calculated columns saves you from debugging transformation failures later.

Add calculated columns for Age_Group, Cost_Category, Month, and Year

Your dashboard needs patients grouped by age range, costs split into categories, and dates broken into month and year. You will add each of these as a custom column using Power Query's M formula language.

In the Power Query Editor, click the Add Column tab in the ribbon.
Click Custom Column. The Custom Column dialog box appears.
In the New column name field, type Age_Group.
In the Custom column formula box, paste the following formula:

if [Age] <= 20 then "0-20" else if [Age] <= 40 then "21-40" else if [Age] <= 60 then "41-60" else "61+"

What does this formula do?

It checks each patient's Age value against four thresholds.
Patients aged 0 to 20 get labelled "0-20", 21 to 40 get "21-40", 41 to 60 get "41-60", and anyone older gets "61+".
Power Query M uses if...then...else syntax instead of nested IF() functions like regular Excel.

Click OK. A new Age_Group column appears at the far right of your data preview.
Confirm the column shows values like "0-20", "21-40", "41-60", or "61+".

Now add the second custom column for cost categories.

Click the Add Column tab, then click Custom Column.
In the New column name field, type Cost_Category.
In the Custom column formula box, paste:

if [Treatment_Cost] > 50000 then "High Cost" else "Low Cost"

What does this formula do?

It labels any treatment costing more than 50,000 as "High Cost" and everything else as "Low Cost". This gives you a simple binary split for your dashboard filters.

Click OK. Confirm the new Cost_Category column appears with "High Cost" or "Low Cost" values.

Next, extract the month number from the admission date.

Click the Add Column tab, then click Custom Column.
In the New column name field, type Month.
In the Custom column formula box, paste:

Date.Month([Date_of_Admission])

Click OK.

Finally, extract the year.

Click the Add Column tab, then click Custom Column.
In the New column name field, type Year.
In the Custom column formula box, paste:

Date.Year([Date_of_Admission])

Click OK.

What do Date.Month and Date.Year do?

These are built-in Power Query M functions that extract the numeric month (1-12) and year (e.g. 2025) from a date value. You will use these columns later to build a monthly admission trends chart on your dashboard.

Check the Applied Steps pane on the right side of the editor. You should see four "Added Custom" steps listed (one for each column you created).
Scroll right in the data preview to confirm all four new columns are visible: Age_Group, Cost_Category, Month, and Year.

Seeing a formula error?

Make sure column names in square brackets match exactly (case-sensitive). [Age] must match your table's column header precisely.
Check that your Date_of_Admission column has a Date or DateTime data type in Power Query. If it shows as Text, the Date.Month function will fail.
If you see "Expression.Error", delete the step by clicking the X next to it in Applied Steps, then re-enter the formula.

help me fix a formula error in Power Query

Load the transformed data to a new sheet

Your transformations are ready. Now you need to load the cleaned data back into Excel so your pivot tables and charts can use it.

In the Power Query Editor, click the Home tab.
Click the small arrow below Close & Load, then select Close & Load To.
In the Import Data dialog box that appears, select Table.
Select New worksheet.
Check the box for Add this data to the Data Model.
Click OK.

What is the Data Model?

The Data Model is an in-memory data engine inside Excel that lets multiple pivot tables share the same data source. Adding your query to the Data Model means slicers can filter all your pivot tables simultaneously in a later step.

Mac users may not see this option

The Data Model and "Close & Load To" options may not be available in all Mac versions of Excel. If you do not see these options, click Home then Close & Load to load your data to a new worksheet. Your pivot tables will still work because they will reference the same output table directly.

Right-click the new sheet tab at the bottom of Excel and select Rename.
Type Clean Data and press Enter.
Click inside the loaded table, then go to the Table Design tab (or Table tab on Mac).
In the Table Name field on the left side of the ribbon, change the name to CleanData and press Enter.

Confirm your Clean Data sheet shows all 12 original columns plus the 4 new ones (Age_Group, Cost_Category, Month, Year).
Verify the table name reads CleanData in the Table Design tab.

Why name the table CleanData?

In later steps, your PIVOTBY formulas will reference columns using the syntax CleanData[Department]. A short, descriptive table name keeps those formulas readable.

Refresh your data anytime

If you ever update the source data on the Data sheet, go to the Data tab and click Refresh All. Power Query re-runs all your transformation steps automatically and updates the Clean Data sheet.

Table loaded but columns are missing?

Scroll all the way to the right of the table. The four new columns appear after the original 12.
If columns are truly missing, go to the Data tab and click Queries & Connections. Double-click your query to reopen the Power Query Editor and check the Applied Steps pane for your four "Added Custom" steps.

help me fix missing columns in my Power Query output

Your data pipeline is complete. Any time your source data changes, a single Refresh All command rebuilds your Clean Data table with all four calculated columns. Next up, you will use this clean data to build pivot tables that summarize patient metrics across departments and time periods.

Build Pivot Tables for Dashboard Metrics

Your data is clean, enriched with calculated columns, and loaded into the Data Model. Now it's time to summarize all 200 rows into meaningful patterns that your dashboard can visualize.

Pivot Tables let you collapse large datasets into focused summaries with just a few clicks. In this step, you'll create four Pivot Tables that each answer a different question about your healthcare data.

In this step, get ready to:

Create Pivot Tables summarizing recovery scores by department and patient counts by admission type.
Build a monthly admissions trends table and a department-by-insurance cross-tab.
Verify all Pivot Tables share the same data source for slicer compatibility.

Create your first two Pivot Tables

Your first Pivot Table will show the average recovery score for each hospital department. The second will count how many patients arrived by each admission type (Emergency, Elective, Urgent).

Switch to the Clean Data sheet in your workbook.
Click any cell inside the CleanData table.
Click the Insert tab in the ribbon.
Click PivotTable.
In the Create PivotTable dialog, confirm the table/range shows your CleanData table.
Select New Worksheet for the destination.
Click OK.

Excel creates a blank Pivot Table on a new sheet with the PivotTable Fields pane on the right.

Rename this new sheet tab to Pivots by double-clicking the tab name at the bottom and typing Pivots.

Now configure this first Pivot Table to show average recovery scores by department.

In the PivotTable Fields pane, drag Department to the Rows area.
Drag Recovery_Score to the Values area.
By default, Excel sums numeric fields. You need the average instead. Click the dropdown arrow on Sum of Recovery_Score in the Values area.
Select Value Field Settings.
Under Summarize value field by, select Average.
Click OK.

What does this Pivot Table show?

You now have a table listing each department (Cardiology, Neurology, Oncology, Orthopedics, Pediatrics) with its average recovery score. This tells you which departments have the best patient outcomes at a glance.

Now create the second Pivot Table on the same sheet to count patients by admission type.

Click an empty cell on the Pivots sheet, a few rows below or to the right of your first Pivot Table.
Click the Insert tab, then click PivotTable.
In the dialog, set the source to the CleanData table.
Select Existing Worksheet and click the cell where you want this second Pivot Table to start.
Click OK.
Drag Admission_Type to Rows.
Drag Patient_ID to Values. Excel should automatically use Count since Patient_ID is a text field. If it shows Sum, change it to Count using Value Field Settings.

You should now see two Pivot Tables on your Pivots sheet. The first shows average Recovery_Score by Department. The second shows a count of patients by Admission_Type (Emergency, Elective, Urgent).

Don't see the PivotTable Fields pane?

Click any cell inside the Pivot Table. The PivotTable Fields pane only appears when a Pivot Table cell is selected.

If it still doesn't appear, go to the PivotTable Analyze tab and click Field List to toggle it back on.

Having trouble with your Pivot Tables?

Add monthly trends and cross-tab Pivot Tables

Your third Pivot Table tracks how many patients are admitted each month. The fourth creates a cross-tab showing treatment costs broken down by department AND insurance provider simultaneously.

Click an empty cell on the Pivots sheet, away from your existing Pivot Tables.
Click Insert, then PivotTable.
Set the source to the CleanData table and choose Existing Worksheet with the selected cell as the location.
Click OK.
Drag Month to Rows.
Drag Patient_ID to Values (it should default to Count).

This gives you monthly admission trends. Now create the fourth Pivot Table with a two-dimensional cross-tab layout.

Click another empty cell on the Pivots sheet.
Click Insert, then PivotTable.
Set the source to CleanData and choose Existing Worksheet.
Click OK.
Drag Department to Rows.
Drag Insurance_Provider to Columns.
Drag Treatment_Cost to Values. It should default to Sum of Treatment_Cost.

What is a cross-tab Pivot Table?

A cross-tab (also called a matrix) uses both row and column labels to create a grid. Here, each cell in the grid shows the total treatment cost for a specific department-insurance combination.

For example, you can instantly see how much Cardiology patients with Medicare cost compared to Cardiology patients with Cigna.

Your Pivots sheet should now contain four Pivot Tables. You can visually confirm each one by clicking into it and checking the field layout in the PivotTable Fields pane.

Verify all Pivot Tables share the same data source

For Slicers to filter multiple Pivot Tables simultaneously, all tables must share the same underlying data source. Since you created each one from the same CleanData table, they should already be connected. Let's confirm.

Click any cell inside your first Pivot Table (the one showing Average of Recovery_Score).
Click the PivotTable Analyze tab in the ribbon.
Click Change Data Source.
In the dialog that appears, confirm the Table/Range field shows your CleanData table.
Click Cancel to close without making changes.
Repeat this check for the other three Pivot Tables. Each should reference the same CleanData table.

Why does the data source matter for slicers?

Slicers can only control multiple Pivot Tables when those tables share the same pivot cache (the same data source). By creating all four from the same CleanData table, you've set them up to be filtered together from a single slicer in the dashboard.

Seeing a different data source?

If one of your Pivot Tables shows a different source (like tblPatients instead of CleanData), you likely selected the wrong sheet when creating it. Delete that Pivot Table and re-create it from the Clean Data sheet.

Need help fixing a data source mismatch?

Your four Pivot Tables are ready. Next up, you'll create dynamic PIVOTBY formula summaries that update instantly without needing a manual refresh.

Create the PIVOTBY Formula Summary

Your Pivots sheet now has four Pivot Tables covering recovery scores, admission types, monthly trends, and a full cost cross-tab. These give you drag-and-drop flexibility, but they require a manual refresh every time data changes.

In this step, you will write PIVOTBY formulas that produce the same pivot-style summaries using pure cell formulas. PIVOTBY outputs update instantly when the underlying data changes, and the results spill into neighboring cells automatically.

In this step, get ready to:

Write a PIVOTBY formula that summarizes Treatment Cost by Department and Insurance Provider.
Write a second PIVOTBY formula that shows Average Recovery Score by Department and Admission Type, sorted descending.
Understand when to use PIVOTBY versus traditional Pivot Tables.

Enter a PIVOTBY formula for Treatment Cost by Department and Insurance Provider

PIVOTBY is a dynamic array formula that groups data along two axes (rows and columns) and aggregates a value column. The result spills across multiple cells automatically from a single formula entry.

Switch to the Pivots sheet in your workbook.
Click on an empty cell well below or to the right of your existing Pivot Tables (for example, cell H20 or any cell with plenty of empty space around it).
Type the following formula and press Enter:

=PIVOTBY(CleanData[Department], CleanData[Insurance_Provider], CleanData[Treatment_Cost], SUM, , 1, , 1)

What does this formula do?

CleanData[Department] tells PIVOTBY which column to use as row headers (one row per department).
CleanData[Insurance_Provider] tells PIVOTBY which column to use as column headers (one column per insurer).
CleanData[Treatment_Cost] is the value being aggregated.
SUM is the aggregation function applied to Treatment Cost for each Department-Insurance combination.
The two 1 values set row_total_depth and col_total_depth to Grand Totals, adding a totals row and column.

After pressing Enter, look at the cells around your formula.
You should see a grid of numbers spill out automatically, with department names down the left side, insurance provider names across the top, and a Grand Total row and column.

Seeing a #SPILL! error?

This means other data is blocking the cells where the formula needs to expand. Click the cell showing #SPILL! and look for a dashed border showing the intended spill range.

Move the obstructing data or relocate your PIVOTBY formula to a cell with more empty space around it. The formula needs roughly 7 rows and 7 columns of clear space (5 departments + header + grand total, by 5 insurers + header + grand total).

Help me fix my #SPILL! error in the PIVOTBY formula

Enter a PIVOTBY formula for Average Recovery Score sorted descending

Your second formula will show how departments compare on patient recovery, sorted so the best-performing department appears first.

Click on an empty cell below your first PIVOTBY result, leaving at least 2 blank rows of buffer (for example, cell H30).
Type the following formula and press Enter:

=PIVOTBY(CleanData[Department], CleanData[Admission_Type], CleanData[Recovery_Score], AVERAGE, , 1, -1, 1)

What does this formula do?

CleanData[Department] groups rows by department (same as before).
CleanData[Admission_Type] groups columns by admission type (Emergency, Elective, Urgent).
CleanData[Recovery_Score] is the value being aggregated.
AVERAGE calculates the mean recovery score for each Department-Admission Type combination.
The -1 in the row_sort_order position sorts rows in descending order, so the department with the highest average recovery score appears at the top.
The 1 values for row_total_depth and col_total_depth add Grand Totals to both rows and columns.

After pressing Enter, you should see a new spilled grid with department names sorted from highest to lowest average recovery score.
Save your workbook by pressing Cmd+S (macOS) or Ctrl+S (Windows).

Formula returning #NAME! or #VALUE!?

A #NAME! error usually means PIVOTBY is not available in your Excel version. Confirm you are using Excel for Microsoft 365, Excel 2024, or Excel 2021.

If you see #VALUE!, double-check that CleanData matches your Power Query output table name exactly (case-sensitive in structured references).

Help me troubleshoot my PIVOTBY formula error

Understand when to use PIVOTBY versus Pivot Tables

You now have two ways to summarize data in your workbook. Each approach has strengths that make it ideal for different situations.

Why use both PIVOTBY and Pivot Tables?

PIVOTBY formulas update instantly when data changes. There is no need to click Refresh All. The output is standard cell data that you can reference in other formulas, use in charts, or wrap with functions like TAKE or SORT.

Traditional Pivot Tables require Refresh All after data changes, but they offer drag-and-drop field exploration and native slicer support for interactive filtering. PIVOTBY does not support slicers.

By keeping both in your workbook, you get the best of both worlds. Use Pivot Tables for the interactive slicer-driven dashboard and PIVOTBY for formula-driven summary tables that always stay current.

✔️ Awesome, I've got everything!

Great. Double check you have saved your workbook with both PIVOTBY formulas on the Pivots sheet.

ⓧ I'd like to double check the full formulas

Here are both PIVOTBY formulas you should have on the Pivots sheet:

Formula 1 (Treatment Cost by Department and Insurance Provider):

=PIVOTBY(CleanData[Department], CleanData[Insurance_Provider], CleanData[Treatment_Cost], SUM, , 1, , 1)

Formula 2 (Average Recovery Score by Department and Admission Type, sorted descending):

=PIVOTBY(CleanData[Department], CleanData[Admission_Type], CleanData[Recovery_Score], AVERAGE, , 1, -1, 1)

Your Pivots sheet is now a complete analytics model with both traditional Pivot Tables and modern PIVOTBY formulas. Next up, you will turn all of this into a polished, interactive dashboard with KPI cards, charts, and slicers.

Design the Interactive Dashboard

Your Pivot Tables and PIVOTBY formulas are crunching the numbers behind the scenes. Now it's time to bring those insights to life visually.

In this step, you'll build the presentation layer of your workbook. You'll create KPI cards that surface headline metrics at a glance, insert charts that make trends and patterns obvious, and wire up Slicers so anyone viewing the dashboard can explore the data interactively.

In this step, get ready to:

Create KPI cards linked to live formulas on the Clean Data table.
Insert Pivot Charts and move them to a dedicated Dashboard sheet.
Add slicers that filter every chart simultaneously.

Create KPI cards with live formulas

KPI cards are the first thing an executive sees on a dashboard. Each card highlights a single headline number pulled from your data.

Right-click any sheet tab at the bottom of the workbook and select Insert.
Rename the new sheet to Dashboard by double-clicking the tab name and typing the new name.
On the Dashboard sheet, go to Insert in the ribbon.
Click Shapes and choose Rounded Rectangle.
Draw a rectangle in the top-left area of the sheet, roughly 3 columns wide by 4 rows tall.
With the shape selected, click inside the formula bar and type the following formula for your first KPI card (Total Patients):

=COUNTA(CleanData[Patient_ID])

Press Enter. The shape now displays the count of all patients in your dataset.
Repeat the process three more times. Draw three additional rounded rectangles next to the first one, and link each to the formulas below (one formula per shape):

=AVERAGE(CleanData[Treatment_Cost])
=AVERAGE(CleanData[Hospital_Stay_Days])
=AVERAGE(CleanData[Recovery_Score])

Add a text label above or inside each shape describing what it shows: Total Patients, Avg Treatment Cost, Avg Hospital Stay, and Avg Recovery Score.

What do these formulas do?

COUNTA counts every non-empty cell in the Patient_ID column, giving you the total number of patient records.

AVERAGE calculates the mean for Treatment_Cost, Hospital_Stay_Days, and Recovery_Score respectively. Because these formulas reference the CleanData table directly, they update automatically whenever Power Query refreshes the data.

Save your workbook.
Confirm each shape displays a number (not an error). Total Patients should show 200 (your row count), and the averages should display decimal values.

Seeing a #REF! or #NAME? error in a shape?

Make sure you click inside the formula bar (not the shape's text area) before typing the formula. If you type directly into the shape, Excel treats it as plain text.

Double-check that your Power Query output table is named CleanData. You can verify this by clicking anywhere in the Clean Data sheet table, then checking the Table Design tab for the table name.

Help me fix a formula error in my KPI shape.

Insert Pivot Charts and move them to the Dashboard

Pivot Charts are charts directly connected to your Pivot Tables. When you filter or refresh a Pivot Table, its linked chart updates instantly.

Switch to the Pivots sheet.
Click any cell inside Pivot Table 1 (Recovery Score by Department).
Go to the Insert tab and click PivotChart.
In the Insert Chart dialog, select Bar Chart (Clustered Bar) and click OK.
Click any cell inside Pivot Table 2 (Patient count by Admission Type).
Go to Insert > PivotChart, select Doughnut chart, and click OK.
Click any cell inside Pivot Table 3 (Monthly Admission Trends).
Go to Insert > PivotChart, select Line chart, and click OK.

Now move all three charts to the Dashboard sheet.

Click the Bar Chart to select it.
Go to the Chart Design tab and click Move Chart.
Select Object in, choose Dashboard from the dropdown, and click OK.
Repeat the Move Chart process for the Doughnut chart and the Line chart, moving both to the Dashboard sheet.
Switch to the Dashboard sheet and arrange the three charts below your KPI cards.

Why use Move Chart instead of copy-paste?

Move Chart preserves the live connection between the chart and its Pivot Table. If you copy-paste a chart, you might break the link and end up with a static image that never updates.

Save your workbook.
On the Dashboard sheet, confirm you can see all three charts: a bar chart showing recovery scores per department, a doughnut showing admission type distribution, and a line chart showing monthly admission counts.

Charts not appearing on the Dashboard sheet?

If the charts are still on the Pivots sheet, make sure you selected Object in (not New sheet) in the Move Chart dialog. New sheet creates a dedicated chart-only sheet, which is not what you want here.

Help me move my Pivot Charts to the Dashboard sheet.

Add slicers and format the dashboard

Slicers are visual filter buttons that let anyone interact with your dashboard without touching formulas or Pivot Table field lists. You'll connect them to all four Pivot Tables so a single click filters everything at once.

On the Dashboard sheet, click on any of the Pivot Charts you just moved.
Go to the PivotTable Analyze tab in the ribbon.
Click Insert Slicer.
In the Insert Slicers dialog, check the boxes for Gender, Department, and Age_Group.
Click OK. Three slicers appear on your dashboard.

By default, these slicers only control the one Pivot Table you selected. You need to connect them to all four Pivot Tables so they filter everything together.

Right-click the Gender slicer.
Select Report Connections.
Check the boxes for all four Pivot Tables in the list, then click OK.
Repeat the right-click > Report Connections process for the Department slicer and the Age_Group slicer, checking all four Pivot Tables each time.
Drag the three slicers to the right side of the dashboard, stacking them vertically.

Why connect slicers to all Pivot Tables?

Without Report Connections, clicking a slicer would only filter one chart. The dashboard would show conflicting views. Connecting all four Pivot Tables means one click on "Cardiology" filters every chart simultaneously, giving a cohesive department-level view.

Finally, clean up the dashboard layout so it looks professional.

Go to the View tab in the ribbon.
Uncheck Gridlines to remove the cell grid from the background.
Go to Page Layout > Themes and select a theme that uses a consistent color palette across your charts and slicers.
Resize and reposition your KPI cards, charts, and slicers so nothing overlaps and the layout is clean.

Save your workbook.
Test your dashboard by clicking a value in the Department slicer (e.g. Cardiology). All three charts should update to show only Cardiology data.
Click the slicer's clear filter button (the funnel icon with an X) to reset it.

Slicer not filtering all charts?

Open Report Connections on the misbehaving slicer and confirm all four Pivot Tables have their checkboxes selected. If a Pivot Table doesn't appear in the Report Connections list, it may be using a different data source. All Pivot Tables must reference the same CleanData table or share the same Data Model connection.

Help me connect my slicers to multiple Pivot Tables.

Your interactive dashboard is complete. Next up, the Secret Mission challenges you to add a Top Doctors leaderboard and a conditional formatting heat map for even deeper insights.

Secret mission

Add a Top Doctors Leaderboard and Heat Map

Your dashboard gives a great overview of hospital performance. But what about individual doctors? Build a leaderboard that ranks doctors by patient recovery outcomes, and add a heat map that instantly reveals where costs concentrate across departments and insurers.

Clean Up Your Resources

This project runs entirely locally in Microsoft Excel, so there are no ongoing costs or cloud resources to worry about.

Decide whether to keep your workbook as-is, or delete it if you no longer need it.

Resources you used:

Healthcare_Dashboard.xlsx workbook (contains Data, Clean Data, Pivots, and Dashboard sheets).
Power Query connection and Data Model (stored inside the workbook file).

Keep everything running

No action needed. Choose this if you want to keep exploring the dashboard, experimenting with new PIVOTBY formulas, or adding more data to your table.

Your workbook stays on your computer wherever you saved it.
You can reopen it anytime and click Data > Refresh All to update your Power Query output if you add new rows to the Data sheet.

Pause - I'll come back to this later

Since everything lives in a single Excel file, there is nothing to pause. Your workbook is already saved to disk and uses no resources when closed.

Close the Healthcare_Dashboard.xlsx file in Excel.
Reopen it whenever you are ready to continue.

Delete - I don't want to use this again

Remove the project workbook from your computer entirely.

Close Healthcare_Dashboard.xlsx if it is still open in Excel.
Delete the file by running the command below for your operating system.

macOS

Open Terminal and run:

rm ~/Desktop/Healthcare_Dashboard.xlsx

Windows

Open PowerShell and run:

Remove-Item "$HOME\Desktop\Healthcare_Dashboard.xlsx"

If you saved the file somewhere other than your Desktop, adjust the path to match the actual location.

Nice Work!

You just built a complete healthcare analytics dashboard from scratch in Excel! Starting with raw patient data, you transformed it into an interactive, executive-ready reporting tool using the full modern Excel analytics stack.

Here's what you learned:

Cleaned and transformed messy healthcare data using Power Query, building a repeatable pipeline with calculated columns for Age Group, Cost Category, Month, and Year.
Summarized patient metrics using both traditional Pivot Tables (with drag-and-drop exploration and slicer support) and PIVOTBY formulas (with instant auto-updating and standard cell output).
Designed a professional interactive dashboard with KPI cards, bar/doughnut/line charts, and slicers connected across multiple Pivot Tables for synchronized filtering.
Secret Mission: Created a Top Doctors leaderboard using PIVOTBY with TAKE and applied conditional formatting heat maps for instant visual pattern recognition on department costs.

Ready to quiz yourself?