Thanks to the CMS Hospital Price Transparency Rule, U.S. hospitals are required to publish an online price estimator tool for common services and as well as a comprehensive machine readable file that lists the prices of all items and services they provide. These hospital standard charges files are typically very large CSV files that contain millions of rows of data - one for each combination of item, insurer, and health plan. Historically, these hospital chargemaster files were too big to open in Excel or Google Sheets. Row Zero is a next-gen spreadsheet built for big data that makes it easy to open and analyze any hospital standard charges file, making these large datasets truly accessible to the general public.
In this guide, we'll show how to access and open hospital standard charges files in a spreadsheet, analyze the data, and share some interesting findings. Click here to see an example spreadsheet for three hospitals which includes some basic analysis. Read on for the full guide to CMS hospital price transparency data or skip to a specific section.
- 5 Takeaways from Hospital Price Transparency Data
- CMS Hospital Price Transparency Rule and Datasets
- How to Open a Hospital Standard Charges File
- Analyzing Hospital Standard Charges
- Prices for Common Medications
- Prices for Common Procedures
- Look Up and Chart Any Item
- Compare Hospitals Costs and Price Lists
- Using a Hospital Price Estimator Tool
- Conclusion
5 Takeaways from Hospital Price Transparency Data
To start, we'll share some interesting findings from our basic analysis across three hospitals: Mayo Clinic, Cleveland Clinic, and UCLA Health. To learn how to open hospital standard charges files and analyze the data, skip to the next sections.
1. Significant Upcharge at Hospitals
Hospitals significantly markup drug costs and medical supplies versus retail options like Cost Plus Drugs, Amazon Pharmacy, Walmart, or your local pharmacy. Here's a comparison of prices charged for common medications at three hospitals versus retail prices. 
It's fairly common to see more than a 1000% upcharge over retail prices, let alone wholesale prices. The cost of Tylenol in hospitals is a stark and relatable example. When hospitals overcharge for basic items like Tylenol and IV bags, hospital price gouging can have a widespread impact and affect many patients. While it's unfortunate to see these price ranges at nonprofit hospitals, they likely reflect the pricing power of hospitals in a given region and the lack of options many Americans face when choosing a hospital.
2. Wide Price Disparity Across Health Plans
Health insurance companies negotiate a wide range of prices for the same items and procedures at a given hospital. For example, let's look at price disparity for the cost of Tylenol in hospitals across plans and compared to retail options. 
Price disparity also exists within a health plan. The exact same item on the same health plan can cost different at different hospitals and may even cost different at the same hospital depending on whether you are inpatient vs outpatient.
3. Your total bill may be higher than prices listed in chargemaster files
You'll likely pay more for procedures than what is listed in the standard charges file. The prices listed in the hospital chargemaster files appear to represent the specific cost of a procedure but don't necessarily cover the total cost of your visit. For example, they do not include facilities charges like the cost of an overnight stay and may not include all of the medical supplies and medications you receive as part of your stay. To estimate your total hospital bill, you should use the hospital's price estimator tool, which each hospital is required to publish on their website. Here is an example for the Mayo Clinic.
4. Cleveland Clinic is consistently more affordable
While each hospital analyzed appeared to levy a significant markup on medications and medical supplies, we consistently saw Cleveland Clinic as the most affordable hospital in the data analyzed, which supports its reputation has a provider of quality care at an affordable price.
5. The CMS price transparency data is very messy
These hospital chargemaster files have millions of rows of data that were likely manually entered into a system, so it's susceptible to human error. The files are self-reported by the hospitals and there doesn't appear to be any central data clearinghouse that approves a hospital's file or checks for accuracy before publishing. There are often several line items and potential codes for a given item so comparing across hospitals and plans isn't always easy or necessarily accurate. As a result, these files should be used as a starting point for analysis and exploration, and you should contact the hospital directly before making any definitive conclusions about the data.
CMS Hospital Price Transparency Rule and Datasets
The Centers for Medicare & Medicaid Services (CMS) Hospital Price Transparency Rule went into effect on January 1, 2021 and mandates that all U.S. hospitals provide clear and accessible pricing information online for the items and services they offer. The rule aims to provide hospital price transparency, foster competition, and empower patients to make informed healthcare decisions by understanding potential medical costs in advance. Under the CMS rule, hospitals are required to disclose pricing information in two specific formats:
1. Comprehensive Machine-Readable File:
Hospitals must publish a digital file containing standard charges for all items and services they provide. This file should include:
- Gross charges: the full, undiscounted list prices
- Discounted cash prices: rates for individuals paying cash without insurance
- Payer-specific negotiated charges: prices negotiated for each health plan.
- Min and max negotiated charges: lowest and highest rates negotiated with insurers
If you want to analyze hospital standard charges data, view a hospital charge master list, or compare prices across hospitals and health plans, this is the data for you. To find a file, simply Google "[HOSPITAL NAME] machine readable standard charges file" and you should find it. Here's an example hospital chargemaster raw file for the Mayo Clinic. Here is the same file opened in a Row Zero spreadsheet along with a few other hospitals. Here is a blog post walking through how to open and analyze the Mayo Clinic standard charge file.
2. Consumer-Friendly Display of Shoppable Services:
Hospitals must present pricing information for at least 300 common services that can be scheduled in advance in a format that's easy for consumers to understand. This is typically done with an online price estimator tool that provides personalized cost estimates based on an individual's insurance information.
If you want to look up the cost for a particular procedure and shop around, you're probably best off using the hospital's pricing calculator online. Simply Google "[HOSPITAL NAME] pricing calculator" and you should find it. For an example, here is a link to the Mayo Clinic Price Estimator Tool.
How to Open a Hospital Standard Charges File
The CMS Price Transparency machine readable files for a given hospital are too large to open in Excel or Google Sheets, so Row Zero is your best option to open and analyze the file. Here's how we opened the file in Row Zero:
Access the latest file: A simple Google search for HOSPITAL NAME standard charges machine readable file should take you to a page where you can download the file. Here is an example CMS price transparency page for Mayo Clinic. The file will download as a CSV onto your computer.
Open up a Row Zero workbook: Login or sign up for free and open a workbook.
Import the CSV file: Click Data in the top navigation to import a file and then select the CSV you downloaded and click Import.
The first two rows of the file are descriptors of the file. After viewing them, you can delete them to clean up the file.
Take a moment to familiarize yourself with the data. The two key columns are "description" which is the descriptive name of each item or procedure and "standard_charge|negotiated_dollar" which is the price negotiated by a specific payer and plan (see a definition of key fields below).
Analyzing Hospital Standard Charges Files
Now that we have our hospital standard charges data in our spreadsheet and ready to filter, we can start exploring the data. The data contains one row for every combination of 1) item/procedure 2) payer and 3) plan and lists the Negotiated Price that each payer/plan has negotiated along with some additional info.
Definition of key fields:
- description - The descriptive name for each item or procedure
- code|1 - While there are 3 code columns, the first is consistently filled and typically has a standard code for that item that is comparable across hospitals. Note, the same "description" can have multiple codes.
- code|1|type - Code type refers to the category the code represents. For example NDC is National Drug Code and is used for medications. Here's a breakdown of code types
- setting - This can be inpatient, outpatient, or both. Note that prices for anything can vary by setting, even something as simple as Acetaminophen (Tylenol).
- standard_charge|gross - The list price before any discounts
- standard_charge|discounted_cash - The discounted price for paying out-of-pocket without insurance
- payer_name - Typically the insurance company that has negotiated the price with the hospital (e.g. Aetna)
- plan_name - The insurance plan, which can be commercial insurance or government programs like Medicare and Medicaid
- Negotiated Price - The price negotiated by a specific payer and plan. Note, there are a number of variables that can impact this including the charge methodology, payment models, and prices negotiated for other services. In the original file, this field was named "standard_charge|negotiated_dollar"
- standard_charge|min - Lowest charge negotiated for the item
- standard_charge|max - Maximum charge for the item across plans
- count_of_compared_rates - Count of different negotiated rates for the item
- standard_charge|methodology - The pricing methodology for the item. View a breakdown of these methodologies. Note that the majority of the
Filtering to a specific item or procedure
Filter the "description" column to narrow down to a specific item or procedure. For example, filter to Acetaminophen 325 MG Oral Tablet (Tylenol) to see the range of prices charged by health plan at the Mayo Clinic. 
For this Mayo Clinic example, the prices charged range from $0.20 to $0.50 per pill with a median price of $0.47. This simple example demonstrates the wide disparity in prices and the significant markup that hospitals charge for medications. For example, you can buy Acetaminophen 325 MG tablets at any pharmacy, grocery store, Amazon, etc. and the price is often less than $0.10 per pill and is $0.02 per pill at Walmart. At $0.47 per pill the Mayo Clinic upcharge appears to be 2250% vs the $0.02 per pill at Walmart. Even the lowest price charged of $0.20 per pill is a 1000% upcharge. And this isn't an outlier. Many hospitals markup drug prices and medical supplies significantly above retail and wholesale prices.
Compare Prices for Common Medications at a Hospital
In our template Analysis tab, filtering down to some common medications reveals a wide range of drug prices across plans and significant markup from what you'd pay at your local pharmacy. 
Compare Prices for Common Procedures at a Hospital
Similarly, filtering down to a few common medical procedures reveals a wide range of prices charged across health plans. 
It's important to note that this is just the price for the specific procedure. You may also be charged for supplies used, medication provided, and will incur significant costs for overnight stays.
For example, the standard charges file lists a Vaginal Delivery (childbirth) as $2.8K to $6.7K across insurance plans and uninsured patients. However, according to the Mayo Clinic's Cost Estimator Tool, the estimated of the total cost of a vaginal delivery is nearly $29K without insurance. 
It appears that the standard charge range of $2.8k to $6.7k likely just corresponds to the "Professional Charges" line item after discounts. "Professional Charges" is the service offered by the doctors whereas the majority of the total cost is made up of "Facility Charges" which includes everything else - the room, additional supplies, additional medication, etc. This is where price overcharging for basic items like Tylenol and IV bags can have a widespread impact.
Lookup and chart any item
Using our template analysis tool, you can look up the negotiated price distribution of any item at the Mayo Clinic. Here's an example for Ondansetron 8 MG Tablets, also known as Zofran, which is commonly used for nausea in hospitals. 
As we saw in the examples above, there is a wide range of prices paid for Ondansetron and a massive upcharge over what you would pay at your local pharmacy or from a discount prescription drug company like Cost Plus Drug Company, which lists 8 MG Ondansetron tablets at $14.90 for 90 pills or $0.17 per pill. Using our simple Upcharge Calculator shows a 3329% upcharge for Ondansetron at the median price charged of $5.83. 
This represents an even larger upcharge than the 2250% upcharge for Tylenol.
Compare Hospitals Costs and Price Lists
As mentioned in our takeaways above, there is a wide range of prices charged at hospitals for the same items and hospital price gouging for basic items appears to be widespread. Since each hospital is required to publish a hospital standard charges file we can easily use Row Zero to open and compare standard charges files at any hospital. For this example, we'll compare Mayo Clinic vs Cleveland Clinic vs UCLA Health across the same common medications and procedures. Keep in mind, there can be multiple line items and multiple codes for the same item so comparing across hospitals can be challenging and may not always be accurate.
Compare Standard Charges for Common Medications
When we compare drug prices at hospitals we see a range of prices charged and in most cases, a significant upcharge versus retail options like Cost Plus Drugs, Amazon Pharmacy, or Walmart.
Compare Standard Charges for Common Procedures
When we compare hospital prices for common medical procedures, we see a similar dynamic. 
However, keep in mind that these price may be just a fraction of the total cost of your visit. For something like a childbirth that requires multiple nights stay in a hospital, and significant supplies and medication, the total costs will be much higher and the least expensive option for the procedure could be a more expensive option for total cost.
Disclaimer: Discrepancies and Errors in Hospital Standard Charge Files
While hospital standard charges files give significant transparency into healthcare pricing, it's important to note some caveats. First of all, these files are complicated, have millions of rows, and the data was likely manually input into a system at some point, so it's susceptible to human error. There are also often several line items for very similar items with different price ranges and it's not clear which particular item is the one most frequently used at a given hospital. While the standard charge files use standard codes for items, there can be several potential codes for a given item so comparing items across hospitals isn't always easy or necessarily an exact, accurate comparison. As a result, these files should be used as a starting point for analysis and exploration and you should contact the hospital directly before making any definitive conclusions about the data or to inquire about noteworthy findings.
Using a Hospital Price Estimator Tool
In addition to publishing hospital chargemaster files, each hospital is required to publish transparent pricing for at least 300 common services, which is typically done with a hospital price estimator tool on their website. These can typically be found with a simple Google search like "[HOSPITAL NAME] pricing calculator". As an example, here is a link to the Mayo Clinic Price Estimator Tool. Typically, you'll select the procedure you're interested in, choose your insurance plan and enter plan details (or select continue without insurance), and then see an estimate for the total costs of the procedure including discounts and facilities charges. Here's an example for a knee replacement: 
Conclusion
Hospital standard charges files provide detailed price transparency at hospitals across the U.S. It's typically not possible to open standard charges files in Excel or Google Sheets because of those spreadsheets' data limits. Row Zero is a powerful spreadsheet built for big data that lets you open and analyze hospital standard charge files and compare prices across hospitals (with the caveats mentioned above). To provide an example to explore, we've already opened the standard charges file at 3 hospitals in Row Zero and set up a template with pivot tables and charts to analyze the standard charges data in a spreadsheet. The data reveals a wide range of prices charged across health plans and payers and a significant upcharge for common medications and medical supplies - in some cases more than 1000% markup. You can try Row Zero for free to open and analyze hospital standard charges for any hospital in the U.S.