Excel Spreadsheet with multiple sets of information

Copper Contributor

I need to be able to show comparison between the different hospitals, insurance companies, the specific procedure code, and what each insurance company contracted amount is.

Hospital Name

Procedure

Billed amount over multiple insurance companies 

 

I appreciate any help.  

Thank you! :smile:

3 Replies

@DebiElderaz 

To compare information across different hospitals, insurance companies, specific procedure codes, and contracted amounts, you can organize your data in a structured way.

Here is a suggestion on how you might structure your Excel spreadsheet:

1. Data Structure:

    • Have a column for "Hospital Name."
    • Another column for "Insurance Company."
    • One more column for the "Procedure Code."
    • A column for the "Billed Amount."
    • Finally, a column for the "Contracted Amount."

This will help organize your data in a tabular format.

2. Data Entry:

    • Enter the relevant information for each transaction, making sure that each row represents a unique combination of hospital, insurance company, procedure code, billed amount, and contracted amount.

3. Use Pivot Tables for Comparison:

  • Drag "Hospital Name" to the Rows area.
  • Drag "Insurance Company" to the Columns area.
  • Drag "Billed Amount" to the Values area and choose the appropriate aggregation (e.g., sum or average).
  • Drag "Contracted Amount" to the Values area and choose the appropriate aggregation.
    • Highlight your data range.
    • Go to the "Insert" tab and click on "PivotTable."
    • In the PivotTable Field List:

This will give you a dynamic table where you can compare billed and contracted amounts for each hospital and insurance company.

4. Filter and Sort:

    • Use filters to drill down into specific hospitals, insurance companies, or procedure codes.
    • Sort the data based on your criteria for better comparison.

The text and steps were edited with the help of AI.

This structure allows you to organize your information and easily compare billed and contracted amounts across different parameters. If you need more, here a link with Templates:

Find customizable design templates for all your healthcare-themed projects.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

@NikolinoDE  You're amazing!  Thank you!!!:smile: