Community Care Tracker in Excel

Build an Excel dashboard for safe care coordination, referrals, and outcomes.

Introduction

30 Second Summary

Community teams often support many people who each need something different. When updates live in scattered notes, urgent work can disappear from view.

In this project, you will build a fictional community care tracker in Excel for Microsoft 365 for a nonprofit serving older and frail adults. The tracker follows each client ID from assessment through referral to an outcome check.

What You'll Build

When the workbook is finished, you can trace a fictional client's path from assessment to outcome before showing a manager where the program needs attention.

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

A visible assessment pipeline that surfaces overdue work beside its status and responsible worker.
A confidentiality-safe care journey that follows each fictional ID from priority need to goal, care action, referral, follow-up, and outcome check.
A management dashboard that separates operational activity from evidence of client progress for a focused program briefing.
Secret Mission: Add a caseload and community coverage view to spot workload concentration and travel-area pressure.

Are there any prerequisites?

You need existing access to Excel for Microsoft 365 on Windows. Experience coordinating projects or programs helps you focus on the care workflow.

Before We Start

Before the hands-on work begins, this is your moment to define who the fictional program serves and why its dashboard matters. That purpose keeps every worksheet focused on safe care coordination and useful management decisions.

Set Up a Safe Fictional Caseload Workbook

Your program goal is defined. The workbook now needs a structure that supports care coordination while protecting confidentiality.

In this step, you will create the workbook foundation in Microsoft Excel for Microsoft 365. You will add six fictional records that model a small community caseload.

In this step, get ready to:

Create a saved workbook with four named worksheets.
Add six fictional client records with assessment dates and workflow statuses.
Check that the workbook contains no real client information.

Create and save the workbook

Separate worksheets give each part of the tracker a clear purpose. This structure also prepares the workbook for controlled lists and management reporting later.

Press the Windows key to open the search bar.
Type Excel into the search bar.
Select Microsoft Excel for Microsoft 365 from the search results.
Select Blank workbook on the Excel home screen.

Why separate the worksheets?

The Clients worksheet holds the fictional caseload. The Care_Actions worksheet is reserved for linked goals and follow-up activity.

The Dashboard worksheet is reserved for management reporting. The Lists worksheet will hold controlled status values.

Add three worksheets by selecting the plus icon beside the worksheet tabs three times.
Rename the first worksheet tab to Clients by double-clicking its current name.
Rename the second worksheet tab to Care_Actions by double-clicking its current name.
Rename the third worksheet tab to Dashboard by double-clicking its current name.
Rename the fourth worksheet tab to Lists by double-clicking its current name.
Select the save icon in the top-left corner of Excel.
Enter Community_Care_Program_Tracker.xlsx as the workbook file name.
Select Save in the save window.

You should see Clients, Care_Actions, Dashboard, and Lists across the bottom of the workbook. The title bar should show Community_Care_Program_Tracker.xlsx.

Workbook name or tabs look different?

Rename any incorrect worksheet tab by double-clicking its current name.
Save the workbook again if the title bar does not show Community_Care_Program_Tracker.xlsx.

Ask for help with fixing my Excel workbook name or worksheet tabs.

Add the fictional client records

The Clients worksheet tracks operational responsibilities through fictional IDs. Its due dates include past and upcoming assessments so the future dashboard has visible workflow risk.

Switch to the Clients worksheet.
Select cell A1.
Fill the caseload range by pasting this tab-separated dataset:

Client ID	Community	Assigned Worker	Assessment Due	Assessment Status	Care Plan Status
CC-001	North	Worker A	2026-09-10	Overdue	Not Started
CC-002	Central	Worker A	2026-09-20	In Progress	Active
CC-003	South	Worker B	2026-09-08	Complete	Active
CC-004	North	Worker B	2026-09-25	Not Started	Not Started
CC-005	Central	Worker A	2026-09-12	Overdue	Review Due
CC-006	South	Worker B	2026-09-30	In Progress	Active

What does this dataset do?

The Client ID column uses fictional codes that can link records across worksheets.
The Assessment Due column creates a mix of past and upcoming deadlines.
The status columns establish starting workflow states for every fictional client.

Save the workbook by selecting the save icon.
Check that the populated range occupies cells A1:F7.

You should see one header row above six fictional records. CC-001 should appear first.

The final record should use CC-006. Every assessment date should appear in its own cell.

Did everything paste into one column?

Clear the pasted cells if every value landed in column A. Select A1 before pasting the tab-separated dataset again.

Ask for help with splitting my pasted Excel data into separate columns.

Check confidentiality and workbook structure

A safe demonstration uses operational fields without exposing personal information. This review confirms that the workbook stays suitable for fictional program-management practice.

This check is worth the care because a clean fictional dataset keeps the workbook safe to demonstrate.

Inspect the header row for the six approved operational fields.
Review the Client ID column for IDs from CC-001 through CC-006 only.
Scan all populated cells for names, addresses, diagnoses, contact details, or narrative case notes.
Remove any real-world detail you accidentally entered.

What makes this demonstration safe?

The workbook contains fictional operational data only. It demonstrates ownership and workflow timing without storing direct identifiers.

This workbook is a learning prototype. Agency privacy policies still govern any real service records.

Before the final check, do you expect every worksheet tab to remain visible as you move across the workbook?

Select the Clients worksheet tab.
Verify that CC-001 is the first fictional ID and CC-006 is the last.
Select the Care_Actions worksheet tab.
Select the Dashboard worksheet tab.
Select the Lists worksheet tab.
Save the final workbook by selecting the save icon.

You should see all four worksheet tabs throughout the check. Care_Actions, Dashboard, and Lists should remain blank.

That foundation is in place. Your saved workbook now contains a confidentiality-safe fictional caseload that is ready for workflow controls.

Your fictional caseload is ready. Next up, you will turn its assessment statuses into a controlled workflow with visible overdue risk.

Make Assessment Timelines and Status Visible

Your confidentiality-safe caseload is already saved in Microsoft Excel for Microsoft 365. The six records contain fictional IDs only.

The dates and statuses still look like ordinary cells. Urgent assessments are easy to miss.

An Excel table will organize the six records. Data Validation will keep status choices consistent.

Conditional formatting will make overdue work stand out. A manager ultimately needs deadline visibility plus evidence that each priority need leads to support.

In this step, get ready to:

Format the Clients data as a table named tblClients.
Create controlled lists for assessment and care-plan statuses.
Make overdue assessments visible with a red fill.

Turn the client range into a table

A table keeps the header row connected to every fictional record. It also gives each column a filter control for quicker workflow checks.

Switch back to the Clients worksheet.
Select the populated range from A1 through F7.
Click Insert on the ribbon.
Click Table.
Confirm that the selected range is A1:F7 in the dialog.
Keep the option that treats the first row as headers selected.
Confirm the dialog.

Nice work. You can now see one header row plus six data rows with filter controls at the top.

Select any cell inside the new table.
Find the table-name box on the left side of the table-specific ribbon.
Replace the current table name with tblClients.
Save Community_Care_Program_Tracker.xlsx using the save control in Excel.
Confirm that the name box displays tblClients.

Missing Table Controls?

Repeat the selection with A1:F7 if one fictional record sits outside the table. Confirm that the first row is being used as the header row.

Help me check why my Clients range did not format as an Excel table.

Create controlled status lists

A controlled source list gives every record the same approved vocabulary. Consistent values make later reporting dependable.

Switch back to the Lists worksheet.
Fill A1:A5 with this vertical sequence: Assessment Status, Not Started, In Progress, Complete, Overdue.
Fill B1:B5 with this vertical sequence: Care Plan Status, Not Started, Active, Review Due, Closed.

You should see two headings with four approved status values beneath each one.

Return to the Clients worksheet.
Select the six data cells beneath Assessment Status without selecting the header.
Click Data on the ribbon.
Click Data Validation.
Set Allow to List.
Use the list-source selector to choose A2:A5 on the Lists worksheet.
Confirm the Data Validation settings.
Open the Assessment Status drop-down for CC-002.

You should see Not Started, In Progress, Complete, and Overdue as the available choices.

Select the six data cells beneath Care Plan Status without selecting the header.
Click Data on the ribbon.
Click Data Validation.
Set Allow to List.
Use the list-source selector to choose B2:B5 on the Lists worksheet.
Confirm the Data Validation settings.
Open the Care Plan Status drop-down for CC-005.

You should see Not Started, Active, Review Due, and Closed as the available choices.

What Does Data Validation Control?

Data Validation restricts each status cell to the values in its source list. This prevents small wording differences from splitting one status into several categories during reporting.

Drop-Down Missing?

Check that you excluded the header cell. Confirm that all six data cells were selected before applying Data Validation.

Help me find why my status drop-down is missing.

Before you inspect CC-003, do you think a Complete status can show which need was matched to which support?

Find CC-003 in the Clients table.
Read across the entire CC-003 row.

The row shows a completed assessment plus an active care plan. It contains no fields for a priority need, client goal, planned action, referral, or outcome check.

That shortfall is intentional. A completion status proves workflow progress without proving that support followed the identified need.

Highlight overdue assessments

The drop-down controls how a status is recorded. A formatting rule turns the risk value into a signal that managers can scan quickly.

Select the six data cells beneath Assessment Status.
Click Home on the ribbon.
Click Conditional Formatting in the Styles group.
Select Highlight Cells Rules.
Select Text that Contains.
Enter Overdue as the text to match.
Choose a red fill option.
Confirm the formatting rule.

Excellent. The Assessment Status cells for CC-001 and CC-005 now stand out with a red fill.

No Red Fill?

Confirm that the rule text is Overdue. Check that the rule covers all six Assessment Status data cells.

Help me troubleshoot why my Overdue cells are not red.

Before you test the rule, what do you expect the formatting to do when the selected value is Overdue?

Open the Assessment Status drop-down for CC-001.
Select Overdue.

You will see the cell highlighted with the red fill. The CC-005 Assessment Status cell remains red because it also contains Overdue.

Save Community_Care_Program_Tracker.xlsx using the save control in Excel.

Your assessment workflow now makes timing risk visible with controlled status choices. Next, you will connect each fictional client's need to a goal, action, referral, follow-up, and outcome check.

Connect Care Actions to Referrals and Outcomes

Your controlled Clients table now flags overdue assessments and tracks care-plan status. A status of Complete still leaves the next part of the client's story hidden.

In Excel for Microsoft 365, you will build a linked register for person-centred care coordination. Each fictional ID will carry a goal, action, referral, follow-up status, and outcome check.

In this step, get ready to:

Test what an assessment-only record can prove.
Create tblCareActions with six confidentiality-safe records.
Control referral, follow-up, and outcome values with drop-down lists.

Expose the traceability gap

An assessment marked Complete is an operational output. Evidence of client progress needs a stated goal.

It also needs an outcome check linked to that goal.

Before you inspect CC-003, do you think its Clients row can tell you which referral supports its goal?

Switch back to the Clients worksheet from earlier.
Select the cell containing CC-003 in tblClients.
Read across the row to identify its goal, referral, follow-up status, and outcome check.

The row shows Complete for Assessment Status and Active for Care Plan Status. It contains no goal, referral, follow-up, or outcome evidence.

What did this test prove?

The assessment table shows where work sits in the workflow. A linked care-action register provides the missing chain from an identified need to evidence of progress.

Build the linked care-action register

A table in Excel keeps every field aligned as the register grows. One row can then show the full coordination journey for one fictional client.

Keep the prototype confidentiality-safe

These records use fictional IDs and operational service language. The workbook remains a demonstration instead of a client record.

Switch back to the blank Care_Actions worksheet from earlier.
Select cell A1.
Enter these headers across row 1 from A1 through K1: Client ID, Community, Action Owner, Priority Need, Client Goal, Planned Action, Referral Service, Due Date, Referral Status, Follow-up Status, and Outcome Check.
Enter the six fictional records from the reference below into rows 2 through 7.

Fictional Records to Enter

Each vertical bar separates one column. Enter each record on its own row.

CC-001 | North | Worker A | Mobility support | Move safely in the community | Arrange mobility-support information and check access | Community Mobility Hub | 2026-09-15 | Sent | Due | Progressing.
CC-002 | Central | Worker A | Meal support | Maintain regular access to meals | Refer to meal-support service and confirm intake | Neighbourhood Meals Program | 2026-09-22 | Accepted | Not Due | Progressing.
CC-003 | South | Worker B | Social connection | Take part in a preferred social activity | Connect with local social group | Community Connections Network | 2026-09-14 | Sent | Complete | Achieved.
CC-004 | North | Worker B | Caregiver support | Strengthen caregiver support options | Share caregiver-resource referral and review uptake | Caregiver Support Centre | 2026-09-27 | Not Sent | Due | Not Measured.
CC-005 | Central | Worker A | Home-safety coordination | Feel safer managing daily activities at home | Coordinate a home-safety support referral | Safe Home Coordination Service | 2026-09-16 | Waitlisted | Due | Needs Revision.
CC-006 | South | Worker B | Mobility support | Maintain confidence attending local activities | Plan accessible transport support follow-up | Accessible Community Transport | 2026-10-01 | Declined | Not Due | Achieved.

Read down the Client ID column to confirm that CC-001 through CC-006 each appear once.
Select the range A1:K7.
Select the Insert tab on the ribbon.
Select Table.
Confirm that the selected range includes one header row with no blank rows.
Press Enter to create the table.

You should see banded rows with filter controls across all eleven headers. The visible formatting confirms that the range is now an Excel table.

Click any cell inside the new table.
Select the Table Design tab on the ribbon.
Replace the default value in the Table Name box with tblCareActions.
Press Enter to apply the table name.
Press Ctrl+S to save Community_Care_Program_Tracker.xlsx.

Good progress. The Table Name box now shows tblCareActions, so the linked register is ready for controlled statuses.

Table Name Not Sticking?

Click a cell inside the banded range before looking for the Table Design tab. Confirm that tblCareActions contains no spaces.

Why can't I rename my Excel table?

Control statuses and verify the link

Controlled source lists keep reporting categories consistent. Data Validation limits each status column to the approved teaching taxonomy.

Switch back to the Lists worksheet from earlier.
Add the three source lists from the reference below in the first three empty columns.

Approved Status Values

Referral Status | Not Sent | Sent | Accepted | Declined | Waitlisted.
Follow-up Status | Not Due | Due | Complete.
Outcome Check | Not Measured | Progressing | Achieved | Needs Revision.

Read down each new source list to confirm that every approved value occupies its own cell.
Return to the Care_Actions worksheet.
Select cells I2:I7 in the Referral Status column.
Select the Data tab on the ribbon.
Select Data Validation.
Set Allow to List.
Use the source selector to choose the five values beneath Referral Status on the Lists worksheet.
Press Enter to apply the validation.

Open a drop-down in the Referral Status column.

You should see Not Sent, Sent, Accepted, Declined, and Waitlisted.

Apply the same Data Validation workflow to J2:J7 using the three values beneath Follow-up Status on the Lists worksheet.
Open a drop-down in the Follow-up Status column.

You should see Not Due, Due, and Complete.

Apply the same Data Validation workflow to K2:K7 using the four values beneath Outcome Check on the Lists worksheet.
Open a drop-down in the Outcome Check column.

You should see Not Measured, Progressing, Achieved, and Needs Revision.

Missing a Drop-Down?

Confirm that the six data cells are selected before applying Data Validation. Check that Allow is set to List.

Why is my Data Validation drop-down missing values?

Before you check CC-003, which parts of its coordination journey do you expect to see on one row?

Select the cell containing CC-003 in tblCareActions.
Read across the row from Client ID through Outcome Check.

Strong work. CC-003 now has a complete trace from priority need through outcome check.

Press Ctrl+S to save the completed register in Community_Care_Program_Tracker.xlsx.

Your care coordination chain is now traceable from need to outcome. Next up, you will turn these records into a management overview.

Build the Program Oversight Dashboard

Your care-action register in Excel for Microsoft 365 now traces each fictional client from a priority need to an outcome check. That record gives coordinators a clear thread through the work.

Individual records support case coordination. Managers need a concise dashboard that separates operational outputs from evidence of client outcomes.

In this step, get ready to:

Summarize assessment workload by status.
Compare referral outputs with client outcome checks.
Add an accountable management note.

Build the assessment workload view

A PivotTable groups repeated statuses into counts. This turns six client rows into a workload snapshot that a manager can scan.

Switch back to the Clients worksheet from earlier.
Select any cell inside tblClients.

You should see a cell selected inside the client table. This tells Excel which table to summarize.

Select Insert on the Excel ribbon.
Select PivotTable.

The PivotTable dialog opens with tblClients as the source. This keeps the summary tied to the controlled client table.

Choose Existing Worksheet.
Set cell A3 on Dashboard as the destination.

The destination now points to the blank reporting worksheet.

Confirm the dialog to place the blank PivotTable at A3.

You should see an empty PivotTable layout on Dashboard.

Enter Assessment Workload in cell A1 on Dashboard.
In the PivotTable Fields pane, drag Assessment Status to Rows.

You should see the four assessment statuses appear as row labels.

Drag Client ID to Values.

You should see Complete as 1, In Progress as 2, Not Started as 1, and Overdue as 2.

Missing the assessment counts?

Confirm that tblClients is the source table.
Place Assessment Status in Rows.
Place Client ID in Values.

Help me fix my assessment PivotTable.

Save Community_Care_Program_Tracker.xlsx.

Separate outputs from outcomes

Referral-status counts show activities such as Sent or Accepted. Outcome checks show whether client goals are progressing.

Return to the Care_Actions worksheet from earlier.
Select any cell inside tblCareActions.

You should see a cell selected inside the care-action table. Excel can now use that table as the reporting source.

Select Insert on the ribbon.
Select PivotTable.

The dialog opens with tblCareActions as the source.

Choose Existing Worksheet.
Set cell D3 on Dashboard as the destination.

This destination places referral progress beside assessment workload.

Confirm the dialog to place the blank PivotTable at D3.

You should see a second empty PivotTable on Dashboard.

Enter Operational Outputs in cell D1.
Drag Referral Status to Rows.

You should see each controlled referral status appear as a row label.

Drag Client ID to Values.

You should see Accepted as 1, Declined as 1, Not Sent as 1, Sent as 2, and Waitlisted as 1.

Switch back to the Care_Actions worksheet.
Select any cell inside tblCareActions.

The same table can power a separate view of client progress.

Select Insert on the ribbon.
Select PivotTable.

The PivotTable dialog opens with tblCareActions as the source again.

Choose Existing Worksheet.
Set cell G3 on Dashboard as the destination.

This destination keeps outcome evidence separate from referral activity.

Confirm the dialog to place the blank PivotTable at G3.

You should see a third empty PivotTable on Dashboard.

Enter Client Outcome Checks in cell G1.
Drag Outcome Check to Rows.

You should see the four outcome-check statuses appear as row labels.

Drag Client ID to Values.

You should see Achieved as 2, Needs Revision as 1, Not Measured as 1, and Progressing as 2.

Do the care-action counts look different?

Confirm that both new PivotTables use tblCareActions as their source.
Place the relevant status field in Rows.
Place Client ID in Values.

Help me fix my care-action PivotTables.

Save Community_Care_Program_Tracker.xlsx.

Add the management note and test refreshes

A management note turns the dashboard into a decision prompt. It names the risk, evidence, accountable owner, and next review point.

Switch back to the Dashboard worksheet.
Enter this note in cell J1: Urgent workflow risk: two assessments are overdue for CC-001 and CC-005. Evidence: Assessment Status is Overdue for both records. Accountable owner: Worker A. Next review point: 2026-09-15.
Save Community_Care_Program_Tracker.xlsx.

You should see a management note that connects the two overdue records to Worker A and the next review date.

Before you test the dashboard, which referral counts do you expect to move when CC-001 changes from Sent to Accepted?

Switch back to the Care_Actions worksheet.
Open the Referral Status drop-down for CC-001.
Select Accepted.

The source row now records Accepted for CC-001. This gives the dashboard a real source-data change to process.

Return to the Dashboard worksheet.
Right-click inside the Operational Outputs PivotTable.
Select Refresh.

You should now see Accepted as 2 and Sent as 1. That changed count proves the PivotTable can refresh from edited source data.

Switch back to the Care_Actions worksheet.
Open the Referral Status drop-down for CC-001.
Select Sent to restore the original fictional record.

The source table now matches the original confidentiality-safe dataset.

Return to the Dashboard worksheet.
Right-click inside the Operational Outputs PivotTable.
Select Refresh.

You should see Accepted return to 1 and Sent return to 2.

Save Community_Care_Program_Tracker.xlsx.

Final Dashboard Check

Assessment Workload shows Complete 1, In Progress 2, Not Started 1, and Overdue 2.

Operational Outputs shows Accepted 1, Declined 1, Not Sent 1, Sent 2, and Waitlisted 1.

Client Outcome Checks shows Achieved 2, Needs Revision 1, Not Measured 1, and Progressing 2.

The management note identifies overdue assessments for CC-001 and CC-005 as the urgent risk.

Excellent work. Your dashboard now turns client-level coordination records into a refreshable management briefing.

Secret mission

Add a Caseload and Community Coverage View

Add a Capacity view that shows unresolved action counts by worker and community. Use the pattern to recommend a team review without disclosing client details or mandating reassignment.

Clean Up Your Resources

Your project exists as a local Excel for Microsoft 365 workbook with no cloud resources or extra purchase. Decide whether to keep it, close it for later, or delete it.

Resources you used:

Your saved Community_Care_Program_Tracker.xlsx workbook with the Clients, Care_Actions, Dashboard, Lists, and Capacity worksheets.

Keep everything running

No action is needed. Choose this option if you plan to keep refining or demonstrating the fictional workflow.

Keep Community_Care_Program_Tracker.xlsx in its saved folder.
Continue using fictional IDs if you add more assessment or care-action records.
Preserve the refreshed management views as evidence of workload oversight without exposing client information.

Pause - I'll come back to this later

Pausing closes the current Excel session while preserving the workbook at no new project cost.

Save your latest changes to Community_Care_Program_Tracker.xlsx.
Close the workbook in Excel.
Confirm that Community_Care_Program_Tracker.xlsx remains in the folder where you saved it.
Return later by opening the same workbook from that folder.

Delete - I don't want to use this again

Deleting the workbook removes every worksheet from this saved copy. A backup preserves your demonstration if you need it later.

Close Community_Care_Program_Tracker.xlsx in Excel.
Navigate to the workbook's saved folder in the Windows file browser.
Select Community_Care_Program_Tracker.xlsx.
Move the selected workbook to the Windows area for recently deleted files by using the file browser's deletion control.

The workbook is now absent from its saved folder. Windows still holds a recoverable copy.

Open the Windows area that stores recently deleted files.
Permanently remove Community_Care_Program_Tracker.xlsx from that area.
Return to the folder where the workbook was saved.
Confirm that Community_Care_Program_Tracker.xlsx no longer appears there.

Nice Work!

You built a confidentiality-safe community care coordination workbook in Microsoft Excel for Microsoft 365. Your fictional records now connect assessments to care actions while giving managers a clear view of workload and client progress.

You've learned how to:

Build a confidentiality-safe care workflow using fictional client IDs without names, addresses, diagnoses, contact details, or narrative case notes.
Control assessment, care-plan, referral, follow-up, and outcome fields with Excel tables, Data Validation, and conditional formatting.
Connect client-level coordination records to a management dashboard that separates operational outputs from client outcome checks using refreshed PivotTables.
Secret Mission: Added a caseload and community coverage view that highlights unresolved workload concentration by worker and travel area without mandating reassignment.

Ready to quiz yourself?