Count Patient Records by Diagnosis Code with an Excel PivotTable
Question details
Create a PivotTable that groups diagnosis codes and counts the patient records associated with each code.
- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- The user wants to summarize patient data by diagnosis code and see how many Patient ID records belong to each code.
- Observed behavior
- The desired result is a table with one diagnosis code per row and a count of patient records beside it.
Make sure the source data has column headers, including Diagnosis Code and Patient ID, and that each patient record is on its own row. Check that the Patient ID field contains values, because blank entries are not counted.
Create a PivotTable to count Patient IDs for each diagnosis code
Use this method when each row in the source data represents a patient record and you need the number of records for every diagnosis code.
A PivotTable groups identical diagnosis codes automatically. Adding Patient ID to the Values area and changing its calculation to Count returns the number of nonblank patient records for each code.
Click any cell in the data range that contains the Diagnosis Code and Patient ID columns, or select the full range including its headers.
On the Excel ribbon, select Insert > PivotTable, confirm the table or range, choose where to place the PivotTable, and select OK.
In the PivotTable Fields pane, drag Diagnosis Code to the Rows area.
Drag Patient ID to the Values area. If Excel displays Sum of Patient ID, select the field drop-down in Values and choose Value Field Settings.
In Value Field Settings, select Count, then select OK. The PivotTable now shows the number of Patient ID records for each diagnosis code.
Use Distinct Count when each patient must be counted only once per diagnosis code
Use this option when the same Patient ID may appear multiple times for the same diagnosis code and repeated records should not increase the total.
Count Patient Records by Diagnosis Code in WPS Spreadsheets
WPS Spreadsheets lets you build a PivotTable from patient data, place diagnosis codes in rows, and count Patient ID records in values. Its familiar spreadsheet interface makes it easy to work with Excel-compatible tables and reports.
- 1. Select the patient data range: In WPS Spreadsheets, click a cell in the data table containing the Diagnosis Code and Patient ID headers.
- 2. Create a PivotTable: Select Insert > PivotTable and confirm the selected data range and destination.
- 3. Add the diagnosis field: In the PivotTable field list, drag Diagnosis Code to the Rows area.
- 4. Add and count Patient IDs: Drag Patient ID to the Values area, open its value settings, and select Count if it is not already set to Count.
Frequently Asked Questions
Why does my PivotTable show Sum of Patient ID instead of Count?
Excel treats numeric Patient IDs as numbers and may apply Sum automatically. In the Values area, open the Patient ID field settings, choose Value Field Settings, select Count, and select OK.
Will Count include blank Patient ID cells?
No. The Count calculation counts only nonblank entries in the Patient ID field. Fill missing IDs or use another consistently populated field if you need to count every source row.
How do I count unique patients instead of all patient records?
Create the PivotTable with Add this data to the Data Model enabled, then open Value Field Settings for Patient ID and select Distinct Count. This prevents repeated Patient IDs under the same diagnosis code from being counted more than once.
How can I refresh the diagnosis-code counts after editing the source data?
Click anywhere in the PivotTable, then select PivotTable Analyze > Refresh in Excel. If new rows were added outside the original source range, use PivotTable Analyze > Change Data Source first.
