Build a Malaria Incidence Dashboard

Build a Google Sheets dashboard to compare country malaria incidence trends.

Introduction

30 Second Summary

Annual malaria figures can make it hard to see which countries need closer attention. A high rate also says little about whether conditions are improving or worsening.

In this project, you will turn annual country-level malaria incidence rates into a Google Sheets surveillance dashboard. You will then use dashboard evidence to write a cautious public health briefing.

What You'll Build

You will change the selected years and watch the country ranking, priority groups, and summary metric update together.

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

An audited data table where every usable country-year record shows OK beside clear source metadata.
A year-to-year comparison showing current incidence beside absolute change, percentage change, and relative priority.
A shareable surveillance package with a bar chart, comparison-year median, priority table, and evidence-linked briefing.
Secret Mission: Add a country selector that updates an annual incidence trend chart.

Are there any prerequisites?

You need annual country-level malaria incidence data for at least two years, plus its exact definition and unit from the source metadata. You also need Google Chrome and a Google account with Google Sheets access.

Before We Start

This first checkpoint locks in the countries or time period you want to compare before you begin the analysis. It also keeps your interpretation focused on relative surveillance signals without treating incidence patterns as proof of why malaria changed.

Set Up and Audit the Incidence Data

A country ranking is only trustworthy when every row uses the same incidence rate definition. Each country-year must also appear once.

In this step, you'll prepare a workbook in Google Sheets. The Data tab will audit every country-year record before it reaches your analysis.

In this step, get ready to:

Create the workbook structure for the project.
Standardize the incidence table against its source metadata.
Resolve every missing-value or duplicate-record flag.

Create the surveillance workbook

Separate tabs give the source data a stable home. They also reserve space for the analysis that builds on it.

In Google Chrome, go to Google Sheets.
Create a new blank workbook using the blank spreadsheet option.

You'll see a new workbook with one sheet tab.

Rename the workbook to Malaria Incidence Watch using the title field in the top-left corner.

The new name appears at the top of the workbook.

Rename the first sheet tab to Data using its tab menu.
Use the plus button at the bottom of the workbook to add a second sheet tab.
Rename the second sheet tab to Analysis using its tab menu.
Use the plus button at the bottom of the workbook to add a third sheet tab.
Rename the third sheet tab to Dashboard using its tab menu.
Use the plus button at the bottom of the workbook to add a fourth sheet tab.
Rename the fourth sheet tab to Briefing using its tab menu.

You should now see Data, Analysis, Dashboard, and Briefing across the bottom of the workbook.

Copy the annual country-level incidence table from your available source dataset.
Paste the copied table into cell A1 on the Data tab.

You'll see the source headers followed by the annual country records.

Dataset did not paste cleanly?

Check that you selected A1 before pasting. A different starting cell shifts the entire table away from the workbook layout.

If every value lands in one column, import the source file through File, then Import. Use the separator option that matches the source file.

Ask for help with the source format if the columns still do not separate correctly: help me place my annual malaria incidence dataset into separate Google Sheets columns.

Structure the incidence data

Every later formula depends on the same three-column layout. The source metadata determines what the incidence values mean.

Move the source's country field into column A.
Set cell A1 to Country.
Move the source's year field into column B.
Set cell B1 to Year.
Move the source's incidence field into column C.
Set cell C1 to Incidence.

Columns A:C now contain one country-year incidence observation per row.

The column name Incidence does not define the rate by itself. Recording the source wording protects the analysis from an unsupported unit assumption.

Record the source's exact incidence definition here: your source's exact incidence definition.
Record the source's exact incidence unit here: your source's exact incidence unit.
Set cell F1 to Incidence definition.
Set cell G1 to Incidence unit.
Enter your source's exact incidence definition in cell F2.
Enter your source's exact incidence unit in cell G2.

Keep the source's unit

Malaria incidence indicators can use a specific population denominator. Copying the exact definition preserves that denominator.

Use cases per 1,000 population at risk only when the source metadata states that unit. The workbook should describe the data you actually received.

Audit every country-year record

A single audit column can expose missing required values. It can also catch repeated country-year combinations before they distort the comparison.

Set cell D1 to Record check.
Select cell D2.
Enter the audit formula in D2 by copying the formula below:

=IF(A2="","Missing country",IF(B2="","Missing year",IF(C2="","Missing incidence",IF(COUNTIFS($A:$A,A2,$B:$B,B2)>1,"Duplicate country-year","OK"))))

How does the audit work?

The nested IF checks stop at the first missing required value.
The COUNTIFS check counts rows with the same country and year.
A repeated country-year returns Duplicate country-year.
A complete unique row returns OK.

Press Enter to apply the formula.

Cell D2 shows OK or a specific issue found in the first data row.

Formula not calculating?

Confirm the formula starts with an equals sign. Check that every quotation mark and closing parenthesis matches the reference.

If Google Sheets reports a parsing problem, your sheet locale may use a different argument separator. Follow the separator shown in the function tooltip.

Get help checking the formula without changing its audit logic: help me troubleshoot the Record check formula in Data!D2.

The fill handle is easy to miss. It is the small square at the lower-right corner of the selected cell.

Select cell D2.
Double-click the fill handle to copy the audit formula down to the final data row.

Each populated source row now has an audit result in column D.

Why duplicate rates cannot be summed

A duplicated country-year means the table contains more than one rate for the same observation key. Adding those rates creates a value that the source metadata does not support.

Use the source documentation to identify one valid record. Keep that documented record in the analysis table.

Scan the Record check column for flagged rows.
Compare each missing-value flag with the original source.
Restore the missing value when the source provides it.
Remove the row from the analysis copy when the source does not provide a required value.
Review the source documentation for each duplicated country-year.
Keep one documented record for each duplicated country-year.
Delete the remaining duplicate rows without summing their incidence rates.
Refill the audit formula to the final data row after resolving the flags.

Before the final scan, predict whether every audit cell now shows OK.

Scan column D from D2 to the final populated row.
Confirm the exact incidence definition remains visible in F2.
Confirm the exact incidence unit remains visible in G2.

Every row used in the analysis displays OK. The source definition and unit remain recorded beside the table.

You have cleared the most important trust check. Every record feeding the next analysis now has the required fields plus a unique country-year key.

✔️ Awesome, I've got everything!

Your Data tab is structured and audited. Double-check that Google Sheets has saved the workbook.

ⓧ I'd like to double check the full code

Compare your Data tab against this cell map.

Cells A1:C1 contain Country, Year, and Incidence.
Cell D1 contains Record check.
Cells F1:G2 contain the exact source definition and unit.
Every populated audit cell from D2 downward returns OK.

=IF(A2="","Missing country",IF(B2="","Missing year",IF(C2="","Missing incidence",IF(COUNTIFS($A:$A,A2,$B:$B,B2)>1,"Duplicate country-year","OK"))))

How to compare this reference

This is the complete formula for Data!D2. Filled-down copies should adjust the row number for each country-year observation.

Your source table is now structured and audited. Next, you'll turn it into a comparison-year ranking.

Build the Comparison-Year Ranking

Your audited Data tab in Google Sheets now gives you one clean row for each country-year observation. That groundwork makes a country ranking defensible.

A comparison-year snapshot answers a practical question about which countries have the highest incidence in a selected year. In this step, you will build that snapshot from your audited data.

In this step, get ready to:

Create an available-years list with two year controls.
Build a country-level incidence comparison.
Rank comparison-year incidence from highest to lowest.

Create the year controls

The year controls need values that already exist in your source data. A formula-driven list gives you a reliable set of years to choose from.

Switch back to the Analysis tab at the bottom of the workbook.
Enter Available years in K3.
Select K4.
Generate the available-years list by entering the formula below:

=SORT(UNIQUE(FILTER(Data!B2:B,Data!B2:B<>"")),1,TRUE)

What does this formula do?

The FILTER function removes blank cells from the source year column.
The UNIQUE function keeps one copy of each available year.
The SORT function arranges those years in ascending order.

Press Enter to commit the formula.

You will see the available dataset years spill down from K4 in ascending order.

Enter an earlier year from K4:K in B1 to use as the Baseline year.
Enter a later year from K4:K in C1 to use as the Comparison year.

Cell B1 now controls the baseline year. Cell C1 controls the comparison year.

No years in the list?

Check that the source years remain in Data!B2:B. Blank output usually means the year values are in a different column.

Check that the formula was entered in K4. The cells below it must be empty so the results have room to appear.

Help me troubleshoot the available-years formula in Google Sheets.

Build the country comparison

Each country needs one baseline value beside one comparison value. Blank results preserve missing observations without turning them into zero incidence.

Fill A3:C3 with these headers in order: Country, Baseline incidence, Comparison incidence.
Select A4.
Generate the alphabetical country list by entering the formula below:

=SORT(UNIQUE(FILTER(Data!A2:A,Data!A2:A<>"")),1,TRUE)

How is the country list built?

The formula removes blank country cells from Data!A2:A.
It keeps one copy of each country name.
It sorts the final list in ascending order.

You will see one country per row starting at A4.

Select B4.
Return the first country's baseline incidence by entering the formula below:

=IF(COUNTIFS(Data!$A:$A,$A4,Data!$B:$B,B$1)=0,"",SUMIFS(Data!$C:$C,Data!$A:$A,$A4,Data!$B:$B,B$1))

How is the baseline value found?

The COUNTIFS function checks whether the country has a record for the selected baseline year.
The SUMIFS function returns the matching incidence value from the audited source table.
The outer IF function leaves the result blank when the country-year observation is missing.

Cell B4 now shows the first country's baseline incidence when that observation exists.

Select C4.
Return the first country's comparison incidence by entering the formula below:

=IF(COUNTIFS(Data!$A:$A,$A4,Data!$B:$B,C$1)=0,"",SUMIFS(Data!$C:$C,Data!$A:$A,$A4,Data!$B:$B,C$1))

How does the comparison formula differ?

This formula uses the comparison year stored in C1. The country still comes from the current row in column A.

Missing comparison-year observations remain blank. Only recorded incidence values enter the comparison.

Cell C4 now shows the first country's comparison-year incidence when that observation exists.

Select the formula cells in B4:C4.
Drag the small square at the bottom-right corner of the selection down to the final country in column A.

The baseline and comparison columns now line up beside the country list. Countries without an observation for one selected year display a blank in that year column.

Seeing unexpected blanks?

Confirm that the values in B1 and C1 exactly match years listed in K4:K.

Confirm that country names in Data!A:A use consistent spelling. Extra spaces can prevent a country-year match.

Help me troubleshoot blank incidence values in the Analysis table.

Rank the comparison year

The comparison table is alphabetical. A separate ranking surfaces the highest current incidence values first.

Fill H3:I3 with these headers in order: Ranked country, Comparison incidence.

Pause for a prediction about which country will appear at the top of the comparison-year ranking.

Select H4.
Generate the ranked country list by entering the formula below:

=SORT(FILTER(A4:A,C4:C<>""),FILTER(C4:C,C4:C<>""),FALSE)

How are the countries ranked?

The first FILTER function keeps countries with a comparison-year incidence value.
The second FILTER function supplies the matching incidence values as the sort key.
The FALSE setting places the highest values first.

You will see the countries spill down from H4 in comparison-year rank order.

Select I4.
Generate the descending incidence list by entering the formula below:

=SORT(FILTER(C4:C,C4:C<>""),1,FALSE)

How are the values aligned?

This formula filters out blank comparison values. It sorts the remaining incidence values in descending order.

Both ranking formulas use the same comparison values. Each incidence value therefore stays aligned with its ranked country.

The top row in H4:I now identifies the country with the highest incidence in the selected comparison year.

Pause for a prediction about whether the same country will stay at the top after you change the comparison year.

Replace the value in C1 with a different year from K4:K.

The country and incidence lists update for the new year. The values remain sorted from highest to lowest incidence.

Inspect the top rows in H:I for any field that shows whether incidence rose or fell.

The ranking shows current incidence only. The snapshot contains no change field, so it cannot reveal direction over time.

Strong work. Your live comparison-year ranking now makes the highest current rates visible.

Ranking not descending?

Check that H4 contains the country-ranking formula. Check that I4 contains the incidence-ranking formula.

Confirm that the cells below H4:I4 are empty. Existing content can block the formula results from expanding.

Help me troubleshoot a comparison-year ranking that is not sorted correctly.

✔️ Awesome, I've got everything!

Your Analysis tab now contains two selected years, a country comparison table, and a descending comparison-year ranking.

ⓧ I'd like to double check the full code

Cell K3 contains Available years. Cell K4 contains:

=SORT(UNIQUE(FILTER(Data!B2:B,Data!B2:B<>"")),1,TRUE)

Cell B1 contains your selected baseline year. Cell C1 contains your selected comparison year.

Cells A3:C3 contain Country, Baseline incidence, and Comparison incidence.

Cell A4 contains:

=SORT(UNIQUE(FILTER(Data!A2:A,Data!A2:A<>"")),1,TRUE)

Cell B4 contains:

=IF(COUNTIFS(Data!$A:$A,$A4,Data!$B:$B,B$1)=0,"",SUMIFS(Data!$C:$C,Data!$A:$A,$A4,Data!$B:$B,B$1))

Cell C4 contains:

=IF(COUNTIFS(Data!$A:$A,$A4,Data!$B:$B,C$1)=0,"",SUMIFS(Data!$C:$C,Data!$A:$A,$A4,Data!$B:$B,C$1))

The formulas from B4:C4 are filled down beside every country in column A.

Cells H3:I3 contain Ranked country and Comparison incidence.

Cell H4 contains:

=SORT(FILTER(A4:A,C4:C<>""),FILTER(C4:C,C4:C<>""),FALSE)

Cell I4 contains:

=SORT(FILTER(C4:C,C4:C<>""),1,FALSE)

Your comparison-year ranking is live. Next, you will add change measures that reveal which countries are improving or worsening.

Add Trend and Priority Signals

Your Google Sheets ranking now shows which countries have the highest comparison-year incidence. However, that snapshot cannot show whether those rates rose or fell from the baseline.

This step adds absolute change. It also adds percentage change.

A median-based label then combines current incidence with direction of change.

In this step, get ready to:

Calculate baseline-to-comparison absolute change and percentage change.
Assign each eligible country to a relative priority group.
Verify that every signal responds to the selected years.

Calculate absolute and percentage change

Absolute change subtracts the baseline incidence from the comparison incidence. Its sign shows the direction of change.

Switch back to the Analysis tab from earlier.
Enter Absolute change in cell D3.
Select cell D4.
Calculate the first absolute change by entering this formula:

=IF(B4="","",IF(C4="","",C4-B4))

How Does This Formula Work?

The two IF checks leave the result blank when either selected year lacks an incidence value.
The expression C4-B4 preserves the direction of change from baseline to comparison.

Press Enter to calculate the first result.
Compare the value in D4 with the difference between C4 and B4.

Cell D4 now shows the signed difference between the comparison incidence and the baseline incidence.

No Result in D4?

Check whether B4 or C4 is blank. The formula intentionally leaves D4 blank when either year has no value.

Confirm that the formula was entered in D4 with every quotation mark and closing parenthesis intact.

Help me troubleshoot the blank absolute-change result.

Percentage change scales the difference against the baseline. The formula leaves missing values or a zero baseline blank.

Enter Percentage change in cell E3.
Select cell E4.
Calculate the first percentage change by entering this formula:

=IF(B4="","",IF(C4="","",IF(B4=0,"",(C4-B4)/B4)))

How Is Percentage Change Calculated?

The condition B4=0 leaves the result blank when the baseline is zero.
The expression (C4-B4)/B4 divides the absolute change by the baseline incidence.

The fill handle is easy to miss. Look for the small square at the bottom-right corner of the selected cells.

Press Enter to calculate the first percentage change.
Select the range D4:E4.
Drag the fill handle down to the final country row.
Click the column E header.
Click the toolbar button with the percent symbol to apply percentage formatting.

Columns D and E now show change for every country with data in both selected years. Incomplete country-year pairs remain blank.

Changes Not Filling Down?

Reselect D4:E4 before dragging the fill handle. Stop at the final populated country in column A.

If column E shows decimals such as 0.25, apply percentage formatting to the whole column.

Help me fix the filled-down change formulas.

Classify relative priority

The comparison-year median splits countries into High and Lower groups. The sign of absolute change separates rising rows from falling or stable rows.

Enter Relative priority in cell F3.
Select cell F4.
Assign the first relative priority label by entering this formula:

=IF(B4="","",IF(C4="","",IF(C4>=MEDIAN($C$4:$C),IF(D4>0,"High and rising","High and falling or stable"),IF(D4>0,"Lower and rising","Lower and falling or stable"))))

How Is Priority Assigned?

The expression C4>=MEDIAN($C$4:$C) compares the current row with the comparison-year median.
The test D4>0 identifies a rising incidence value.
Zero or negative absolute change receives a falling or stable label.
The opening blank checks prevent classification unless both selected years contain data.

Press Enter to display the first priority label.
Reselect cell F4.
Drag the fill handle down to the final country row.

Column F now shows one of four relative priority labels for each complete country comparison.

Priority Labels Look Wrong?

Confirm that the comparison incidence remains in column C. Confirm that absolute change remains in column D.

Check that $C$4:$C retains its dollar signs when the formula is filled down.

Help me debug the relative-priority formula.

A priority label becomes useful when its source values support it. Check the available groups against the baseline and comparison columns.

Scan F4:F to identify every priority group present in your data.
Choose one country row from each group that appears.
Compare the baseline value in column B with the comparison value in column C for each chosen row.
Check the sign of the absolute change in column D for each chosen row.

A rising label pairs with a positive absolute change. A falling or stable label pairs with zero or a negative absolute change.

How Should You Read High and Lower?

High and Lower describe positions around the selected countries' comparison-year median. This workbook does not apply WHO policy thresholds.

Use these groups to organize surveillance attention within the selected comparison.

✔️ Awesome, I've got everything!

Your formulas now fill columns D:F. Column E uses percentage formatting.

ⓧ I'd like to double check the full code

The formulas below appear in this order: A4 through F4, followed by H4, I4, and K4.

=SORT(UNIQUE(FILTER(Data!A2:A,Data!A2:A<>"")),1,TRUE)
=IF(COUNTIFS(Data!$A:$A,$A4,Data!$B:$B,B$1)=0,"",SUMIFS(Data!$C:$C,Data!$A:$A,$A4,Data!$B:$B,B$1))
=IF(COUNTIFS(Data!$A:$A,$A4,Data!$B:$B,C$1)=0,"",SUMIFS(Data!$C:$C,Data!$A:$A,$A4,Data!$B:$B,C$1))
=IF(B4="","",IF(C4="","",C4-B4))
=IF(B4="","",IF(C4="","",IF(B4=0,"",(C4-B4)/B4)))
=IF(B4="","",IF(C4="","",IF(C4>=MEDIAN($C$4:$C),IF(D4>0,"High and rising","High and falling or stable"),IF(D4>0,"Lower and rising","Lower and falling or stable"))))

=SORT(FILTER(A4:A,C4:C<>""),FILTER(C4:C,C4:C<>""),FALSE)
=SORT(FILTER(C4:C,C4:C<>""),1,FALSE)

=SORT(UNIQUE(FILTER(Data!B2:B,Data!B2:B<>"")),1,TRUE)

The first six lines define the country analysis. The final three preserve the existing supporting formulas.

Verify the signals update

The new columns should respond whenever either selected year changes. This confirms that the analysis remains linked to the year controls.

Before changing a selected year, consider whether all three new columns will recalculate.

Read an available year in K4:K that differs from either selected year.
Enter that year in B1 or C1.

You should see absolute change, percentage change, and relative priority recalculate for every country with data in both selected years. Rows missing either selected year remain blank.

Columns Not Updating?

Confirm that the new year exactly matches one of the values in K4:K. A year stored in a different format may fail to match the source data.

Check that the formulas reach the same final row as the country list in column A.

Help me restore the year-linked calculations.

That closes the snapshot gap: every country with both years now has direction plus relative priority.

Your trend and priority signals are ready. Next, you'll turn them into a compact surveillance dashboard.

Assemble the Surveillance Dashboard

Your Analysis tab now separates current incidence from direction of change. Decision-makers still have to scan several columns to find the strongest signals.

In this step, you will use Google Sheets to assemble a compact view of the highest current rates. A bar chart makes the ranking visible.

A median metric provides a comparison point. A linked priority table exposes the trends that need attention.

In this step, get ready to:

Link the selected years and rate metadata to the dashboard.
Build a readable comparison-year bar chart.
Add the median metric and sorted priority table.

Link the dashboard context

Linked year labels keep the comparison period visible. Linked metadata preserves the source's exact rate definition.

Switch back to the Dashboard tab from earlier.
Enter Malaria Incidence Watch in cell A1.
Select cell A3.
Add the linked year labels by pasting this two-row grid:

Baseline year	=Analysis!B1
Comparison year	=Analysis!C1

How do these links work?

The reference to Analysis!B1 displays the selected baseline year.
The reference to Analysis!C1 displays the selected comparison year.
Each dashboard value changes when its source cell changes.

Confirm cell B3 shows the baseline year from Analysis.
Confirm cell B4 shows the comparison year from Analysis.
Enter Incidence definition and unit in cell A6.
Select cell A7.
Link the documented rate metadata by entering this formula:

=Data!F1:G2

What does this reference do?

This reference mirrors the four cells in Data!F1:G2. Any correction to the source metadata flows into the dashboard.

Confirm the linked dashboard text matches the exact definition and unit recorded in Data!F1:G2.
Record the comparison year shown in B4 here: your comparison year.
Record the exact incidence unit from the linked metadata here: your exact incidence unit.

Seeing an error in a linked cell?

Confirm the tab names are spelled exactly as Analysis and Data. Check that each formula begins with an equals sign.

Make sure the cells beside and below A7 are empty. Existing content can block the linked range from expanding.

help me troubleshoot the linked year or metadata references on my Google Sheets dashboard.

Build the top-country bar chart

A bar chart compares separate countries along one numeric scale. Using the first ten ranked rows keeps the country labels readable.

Switch back to the Analysis tab.
Select the range H3:I13.
Click Insert in the top menu.
Click Chart.
Set the chart type to Bar chart under Setup in the chart editor.

You should see a chart with country names as labels. The bars run from the highest comparison-year incidence to the lowest.

Why use a bar chart?

Each country is a separate item in this comparison. Horizontal bars leave more room for country names while preserving the descending ranking.

Click the chart once to select it.
Cut the chart by pressing Cmd+X on macOS or Ctrl+X on Windows.
Switch back to the Dashboard tab.
Paste the chart by pressing Cmd+V on macOS or Ctrl+V on Windows.
Drag the chart near cell H1.

The ranking now sits beside the linked years and source metadata. Its bars remain connected to Analysis!H3:I13.

Double-click the chart to reopen the chart editor.
Click Customize.
Open Chart & axis title.
Choose Chart title under Type.
Enter Malaria incidence in your comparison year (your exact incidence unit) in the Title text field.
Resize the chart by dragging its lower-right handle until every country name is readable.

That is the visual core complete. You can now identify the highest comparison-year incidence without scanning the full analysis table.

Chart labels missing or out of order?

Confirm the chart uses Analysis!H3:I13 as its data range. The first column should supply country labels.

Confirm the chart type is Bar chart. Check that the ranking in Analysis!H:I is already descending.

help me troubleshoot my Google Sheets bar chart based on Analysis H3 to I13.

Add the median and priority table

The median provides the middle comparison-year incidence across the selected countries. The sorted table groups countries by relative priority.

Enter Median comparison-year incidence in cell A10.
Select cell B10.
Calculate the comparison-year median by entering this formula:

=MEDIAN(Analysis!C4:C)

What does this formula do?

The formula reads all comparison-year incidence values from Analysis!C4:C.
Blank cells do not become additional countries in the calculation.
The result provides the reference point used by the relative priority labels.

Confirm cell B10 displays one numeric median value.

Median formula not calculating?

Confirm the formula points to Analysis!C4:C. Check that comparison-year incidence values are stored as numbers.

Your spreadsheet locale may use a different argument separator in longer formulas. Follow the separator shown by the function tooltip in your sheet.

help me troubleshoot the comparison-year median formula in Google Sheets.

Switch back to the Analysis tab.
Select the header range A3:F3.
Copy the headers by pressing Cmd+C on macOS or Ctrl+C on Windows.
Return to the Dashboard tab.
Select cell A13.
Paste the headers by pressing Cmd+V on macOS or Ctrl+V on Windows.
Select cell A14.
Build the linked priority table by entering this formula:

=SORT(FILTER(Analysis!A4:F,Analysis!A4:A<>""),6,TRUE,3,FALSE)

How is the priority table sorted?

The FILTER section keeps rows with a country name.
The first sort uses column 6 in ascending order to cluster the relative priority groups.
The second sort uses column 3 in descending order to place higher comparison-year incidence first within each group.

Confirm countries with the same relative priority appear together.
Confirm comparison-year incidence runs from highest to lowest within each group.

Priority table not expanding?

Clear any content from the cells below or beside A14. The formula needs room to expand across six columns.

Confirm the source range ends at column F. Check that the country filter points to Analysis!A4:A.

help me troubleshoot the linked priority table formula in Google Sheets.

✔️ Awesome, I've got everything!

Your linked context, chart, median metric, and priority table are all in place.

ⓧ I'd like to double check the full code

Compare these dashboard references and formulas with the cells in your workbook.

Dashboard!A3:B4
Baseline year	=Analysis!B1
Comparison year	=Analysis!C1

Dashboard!A7
=Data!F1:G2

Dashboard!B10
=MEDIAN(Analysis!C4:C)

Dashboard!A14
=SORT(FILTER(Analysis!A4:F,Analysis!A4:A<>""),6,TRUE,3,FALSE)

Before the final check, which dashboard elements do you expect to update when the comparison year changes?

Switch back to the Analysis tab.
Choose a different year from the available list below K3.
Enter the different year in cell C1.
Return to the Dashboard tab.

You will see the linked comparison-year label change. The chart bars will update to the new ranking.

The median will recalculate. The priority table will update to match Analysis.

Restore the original comparison year by entering your comparison year in Analysis!C1.
Return to the Dashboard tab.
Confirm the selected years match Analysis!B1:C1.
Confirm the chart matches the ranking in Analysis!H3:I13.
Confirm the median and priority table match the values in Analysis.

You have proved that the dashboard responds to the selected years. Its ranking, summary metric, and priority signals now stay aligned with the analysis.

Your dashboard now surfaces current burden alongside direction of change. Next, you will turn those signals into a cautious public health briefing.

Write the Public Health Briefing

Your Google Sheets dashboard now places current incidence beside direction of change. Decision-makers still need those signals translated into a compact briefing.

In this step, you will write three evidence-linked findings. You will add a prioritized public health surveillance follow-up plus clear interpretation limits.

In this step, get ready to:

Write three evidence-linked findings from the workbook.
Prioritize one surveillance follow-up action.
Document the limits of the interpretation.

Write the highest-incidence finding

An evidence-linked finding connects an incidence rate to its country plus time frame. It also reports the displayed change so a reader can check the statement against the workbook.

What makes a finding evidence-linked?

The subject is the displayed country.
The time frame names both selected years.
The result gives the comparison-year incidence rate using the documented unit.
The trend gives the absolute change plus percentage change.

Switch back to the Dashboard tab.
Read the selected-year labels at the top of the dashboard.
Read the exact incidence unit linked from the source metadata.
Switch to the Analysis tab.
Inspect H4:I4 to identify the country with the highest comparison-year incidence.
Locate that country in A:F to read its complete evidence row.

You now have the country plus every value needed to support the first finding.

Switch to the Briefing tab.
Type Evidence-linked findings in A1.
Write the highest-incidence finding in A2 using the evidence trail above.

Your first finding is now auditable. A reader can trace it back to the selected years plus the matching row in Analysis.

Add two trend-direction findings

The highest current rate shows present burden. The next two findings add direction by highlighting deterioration plus a high-incidence country that is stable or falling.

Switch back to the Analysis tab.
Scan Absolute change in column D to identify the largest positive value among rows with both selected years.
Locate that country's complete evidence row in A:F.
Switch to the Briefing tab.
Write the strongest-increase finding in A3 using the same evidence trail.

The second finding reveals which country has the strongest deterioration between the selected years.

Switch back to the Analysis tab.
Find one row labeled High and falling or stable in column F.
Read that country's complete evidence row in A:F.
Switch to the Briefing tab.
Write the high-incidence stable-or-falling finding in A4 using the same evidence trail.

Good work. Each finding now traces back to the workbook. Together they show present burden plus direction of change.

Complete the surveillance interpretation

A prioritized follow-up turns a signal into a concrete surveillance check. A limitations paragraph keeps the pattern from being mistaken for causal proof.

Keep the action surveillance-focused

The action should name the country. It should name the workbook signal that makes the country a priority.

Choose one first check. Reporting completeness or diagnostic testing can provide a concrete starting point.

Choose one country from your findings for immediate surveillance follow-up.
Type Priority surveillance follow-up in A6.
Write one prioritized follow-up action in A7 using the structure above.

What belongs in the limitations paragraph?

Apparent changes can come from several sources.

Reporting completeness can affect the displayed pattern.
Access to care can affect whether cases reach health facilities.
Diagnostic testing can affect which infections are detected.
Case confirmation can affect which suspected cases enter the data.
Treatment seeking can affect which infections appear in surveillance records.
Estimation methods can affect comparisons across time.
Genuine transmission change can affect incidence.

These patterns are signals requiring investigation. They do not prove why incidence changed or establish intervention impact.

Type Limitations in A9.
Write the limitations paragraph in A10 using the interpretation above.

Before you review the finished tab, do you expect every claim to trace back to a displayed workbook value?

Check the three findings in A2:A4 against the values in Analysis.
Check the follow-up action in A7 against one displayed priority signal.
Check the limitations paragraph in A10 for every factor listed above.
Check every sentence for unsupported causal language.

You should see three findings that name the displayed countries plus selected years. Each finding includes a comparison-year rate plus change from the workbook.

You should also see one prioritized follow-up action plus a limitations paragraph that frames the patterns as signals requiring investigation.

You have closed the interpretation gap. Your workbook now turns dashboard signals into a cautious public health briefing.

Secret mission

Add an Interactive Country Trend

Two selected years can make a short-lived jump look like a lasting trend. Add a selector that drives a chronological incidence table. Use that table to update a line chart.

Clean Up Your Resources

Your finished surveillance workbook lives in Google Drive with no new billable resources. Decide whether to keep it, pause your work, or delete it.

Resources you used:

The Malaria Incidence Watch workbook in Google Sheets with the Data, Analysis, Dashboard, and Briefing tabs.
Any source-file copies that you uploaded to Google Drive while importing the incidence data.

Keep everything running

No cleanup is needed. Choose this option if you want to keep updating the dashboard or share a view-only copy.

Review the Data tab for sensitive or restricted data before sharing.
Remove any rows or fields that you do not have permission to share.
Set the workbook access to view-only before sending its link.
Keep permitted source-file copies in Google Drive if they support your audit trail.

Pause - I'll come back to this later

Leaving the workbook private keeps its formulas and charts intact. There are no running processes to stop.

Keep the Malaria Incidence Watch workbook in its current Google Drive location.
Confirm that workbook access remains limited to your account.
Leave any retained source-file copies private until you return.

Delete - I don't want to use this again

Move the workbook and unneeded source-file copies to trash to clear this project from Google Drive. Permanent deletion is final, so the trash gives you one recovery point.

Locate the Malaria Incidence Watch workbook in Google Drive.
Move the Malaria Incidence Watch workbook to Google Drive trash.
Locate any source-file copies that you uploaded for this workbook.
Move each unneeded source-file copy to Google Drive trash.
Open Google Drive trash to confirm that the workbook appears there.
Permanently delete the workbook from trash only when you no longer need a backup.
Permanently delete the matching source-file copies from trash only when you no longer need them.

Nice Work!

That’s a wrap! You built a shareable Google Sheets surveillance dashboard that turns annual malaria incidence data into evidence-linked priorities.

You can now compare countries across selected years. You can also communicate the results without presenting surveillance signals as causal proof.

You learned how to:

Created an audited country-year table with missing-value checks. Added duplicate checks that protect each country-year record.
Calculated absolute change across the selected years. Calculated percentage change for countries with usable baseline values. Classified relative priorities using the comparison-year median.
Built a linked surveillance dashboard with a ranked bar chart. Added a median summary metric plus a grouped priority table. Wrote a public health briefing that connects each finding to workbook evidence.
Secret Mission: Added an interactive country selector that updates an annual incidence table. Connected the selected country to a chronological line chart.

Ready to quiz yourself?