Excel Template

I'm a recovering scientist now working with SharePoint.  I would suggest making a SharePoint list for this data, since that is a real database. But we all know Excel is the world's most popular "database", I'll show you how. Let's start simple.  You can improve the spreadsheet as you go along (use Google for formulas, Business Copilot (included in your M365 subscription) or check out chandoo.org (my fav Excel place)

1. Make a new Spreadsheet.
2. Rename the first sheet "Data" (right click on the tab and select "rename")
3. In the 1st 4 columns, label them "Sample name", "Date Received", "Result" and "District"
4. Select the entire Date received column and reformat to date (select your desired format)

    

5. Make a new sheet by clicking on the "+" next to the "Data" tab. Rename it Validation

6. Open the Validation sheet.  Make 2 columns:  Result and District. Uder Result list the possible choices (Positive and Negative). Uder District , list your possible districts (I'll use District A and B)
7. Click on the Data Tab.
8. In the ribbon, select the "Data" tab. Select "Data Validation.
9. Left click in the first cell under "Result".
10. Select Data Validation 

     

11. Select "List"

    

     

12.  Click on "Source". 

13. The dialog box will tighten. Click in that box. 

14. Open the Validation tab and select the 2 entries under result. 

    

     

15. Reopen the dialog box by clicking on the symbol in the lower right portion of the box (see red arrow above). Select OK.

16.  Click on the arrow in the first cell:  and you will see the choices (don't select a choice yet)

    

     

17. Click in the cell, grab the dot in the lower right-hand corner and drag the cell contents down the column.

18.  

    Repeat this step for the District.  (tip:  hide the validation sheet so no one accidently changes the choices)

    Now to analyze the data,
19. The easiest way to analyze the data is to filter by the column.  
20. Select entire column (click on the column letter). Go to the Filter option in the ribbon and click "Filter". This enables filtering for that column. Tip: you can save time by selecting all your columns and enabling filtering on all of them.

21. You filter by selecting the arrow in the heading of the column you wish to filter. 

22. You can use the "contains" option to select month or year.  You can select a range.

23. To select the # of positives, you can use the formula =Countifs(C2:C1000, "Positive") 

24. To count the # of non-blank cells (all test samples) =COUNTA(C2:C1048576) 

25. To enable filtering by month, I changed the number format to words.  Deselect the months you are not interested in (or deselect all, and select the month you want)

Note: because I put my results in the December results, when I filtered for November, I lost the cells.  You might want to put these in the header

2nd Note:  use Freeze Panes to keep the header at the top 

26. To filter by year, add some data that allows you to see different years (and months). Deselect the years you don't want to see. 

Here's a tip: you can save this filter as a view: Click on the "View" tab at the top. On the top part of teh page, you will see box with "default". 

1. Filter content to 2024 only.
2. Select the Eyeball with the word "New". (The spreadsheet will now have black trim.)
3.  Click the save symbol "Keep" and rename the view "2024".
4. Click "Exit"
5. Repeat for 2023. 
6. Now whenever you want to look at that year's data, you can go to view and select the year. 

    

7. Change the view to default to return to your unfiltered data (you may have to clear the filter)

Good luck.