Organize Hospital Insurance and Procedure Data with an Excel PivotTable
Question details
Create a structured report that compares hospitals, insurance companies, procedure codes, billed amounts, and contracted amounts.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to organize healthcare transaction data and summarize it across hospitals, insurers, and procedures.
- Observed behavior
- The goal is to view billed and contracted amounts in a filterable PivotTable report.
Make sure each transaction is recorded on its own row and that every column has a unique header. Store billed and contracted amounts as numeric currency values rather than text.
Create a normalized source table for hospital transaction data
Use this structure before building a PivotTable so hospitals, insurers, procedures, and amounts can be compared accurately.
A normalized table uses one row for each hospital-insurance-procedure transaction. Avoid merged cells, subtotal rows, and repeated headers within the source data.
In the first row of the worksheet, create headers such as Hospital Name, Insurance Company, Procedure Code, Billed Amount, and Contracted Amount.
Enter each record on a separate row beneath the headers. Repeat the hospital, insurer, and procedure values for every applicable transaction.
Select any cell in the data range, then choose Insert > Table. Confirm that My table has headers is selected, and click OK.
Select the Billed Amount and Contracted Amount columns, then use Home > Number Format > Currency or Accounting to format the values consistently.
Build a PivotTable to compare billed and contracted amounts
Use a PivotTable after the source table is ready to compare hospitals and procedure codes by insurance company.
Create a Hospital and Insurance Comparison PivotTable in WPS Spreadsheets
WPS Spreadsheets lets you organize hospital transaction records into a table and create a PivotTable that compares billed and contracted amounts by hospital, insurance company, and procedure code.
- 1. Prepare the transaction columns: In WPS Spreadsheets, enter Hospital Name, Insurance Company, Procedure Code, Billed Amount, and Contracted Amount as headers, with one transaction on each row.
- 2. Format the data as a table: Select the complete data range and use the table feature on the Insert tab to create a structured source range with headers.
- 3. Insert a PivotTable: Click inside the source table, then choose Insert > PivotTable and select where to place the report.
- 4. Arrange the PivotTable fields: Place Hospital Name and Procedure Code in Rows, Insurance Company in Columns, and Billed Amount plus Contracted Amount in Values.
- 5. Apply report filters: Add Hospital Name, Insurance Company, or Procedure Code to Filters and select the records you want to analyze.
Frequently Asked Questions
What columns should a hospital insurance comparison table include?
Include Hospital Name, Insurance Company, Procedure Code, Billed Amount, and Contracted Amount. You can also add service date, patient account identifier, location, or procedure description when those fields are needed for analysis.
Why should each transaction be on a separate row before creating a PivotTable?
A PivotTable summarizes records by their field values. One row per transaction allows Excel to correctly group hospitals, insurers, and procedure codes and calculate amount totals.
How do I show both billed and contracted amounts in the same PivotTable?
Drag both Billed Amount and Contracted Amount into the Values area of the PivotTable Fields pane. Use Value Field Settings to confirm each field is summarized by Sum.
How can I filter the report for one insurance company or hospital?
Drag Insurance Company or Hospital Name into the Filters area, then use the filter drop-down above the PivotTable. You can also keep the field in Rows or Columns and use its label filter.
Why are my billed amounts counted instead of added in the PivotTable?
The amount cells may be stored as text or contain nonnumeric characters. Convert the source column to numbers, then refresh the PivotTable and set the value field to Sum through Value Field Settings.
