Save Money: Know How to Make a Medical Bill Tracker in Excel

There are many advantages and disadvantages in keeping up with a Medical Bill Tracker. The disadvantage is that it takes time and effort to keep up with each explanation of benefit statement you receive and each medical bill. The advantages of having a Medical Bill Tracker is that it helps you know if your insurance company is paying their fair share, if you have met your deductible, and you could save a lot of money due to medical mistakes.

This Hubpage will guide you step by step on how to create a professional looking spreadsheet in order to keep track of your medical bills and matching explanation of benefits. This will keep you organized and ensure that your insurance company is not cheating you. It will also ensure that doctor's offices are not trying to charge you too much. This spreadsheet is like a sigh of relief, knowing that you have everything under control.

1. Open a fresh Excel sheet.
2. Highlight columns A thru J on Row 1. With this section highlighted, merge and center.
3. In this merged area, type “Medical Bill Tracker.”
4. In this area, use 25 point font and Book Antiqua font (if you choose).
5. In this area, color the cell the fourth color over in the color box (the lightest blue color shown).
6. In this area, go to “borders” and use a thick box border.

1. For rows 7 thru 55 and for column A thru J, select it all. Go to “borders” and select “All Borders.”
2. Column A – Highlight. Hold down Control and the number one. This will bring up format cells block. Format Column A as a date. Choose the fifth date down.
3. Columns E thru H – Highlight and format these columns as “$ English United States” (There’s a button at the top to just do that).
4. Column J – Highlight and format as “$ English United States.”

1. In cell B3, right align the cell, and enter “Total Billed.”
2. In cell B4, right align the cell, and enter “Total Paid by Insurance.”
3. In cell D3, right align the cell, and enter “Total Out of Pocket Paid.”
4. In cell D4, right align the cell, and enter “Total Due.”
5. In cells C3, C4, E3, and E4, color them the seventh color over in the color box (the lightest green color shown). Select “borders,” and use “All Borders.”
6. In cell D3 and B4, enter Alt, then “O,” then “C,” and then “A.” This will automatically fit the contents to the cell.
7. (If during the building of this chart, your table does not wrap text, click the button to do so, or go to format cells [Control + 1] and wrap text).
8. Select columns A7 thru J55, and center.
9. Title cell A7 – “Date of Service”
10. Title cell B7 – “Name”
11. Title cell C7 – “Account #”
12. Title cell D7 – “Description of Services”
13. Title cell E7 – “Total Amount Billed”
14. Title cell F7 - "Insurance Amount Paid"
15. Title cell G7 – “Adjustments”
16. Title cell H7 – “Out of Pocket Expenses Paid”
17. Title cell I7 – “Check #”
18. Title cell J7 – “Amount Due”
19. Highlight columns A thru J, and make the column size 15.
20. Automatically size row 7 to fit contents (do this anywhere contents are not fully showing).

1. In cells A7 thru K55 – Highlight and wrap text.
2. In cell J8, enter “=E8-F8-G8-H8”
3. Copy cell J8 (Control-C)
4. Highlight to cell J55 and press enter. This will extend the formula.
5. Title cell D55 – “Totals”
6. Enter in cell E55, the formula “=Sum(E8:E54).”
7. Copy cell E55.
8. Highlight cells E55 thru H55.
9. Copy cell E55.
10. Highlight cells E55 to H55, and press enter to copy the formula.
11. Copy cell E55.
12. Put the cursor on cell J55, and press enter to copy the formula.
13. Fill in cell I55 with black. (You will not need an amount here).

1. In cell C3, enter “=E55”
2. In cell C4, enter “=F55+G55”
3. In cell E3, enter “=H55”
4. In cell E4, enter “=J55”

1. Highlight Column H, press Alt, “I,” and “C.” This will enter a column to the left of column H.
2. Format this new column (Column G), Format $ English United States, Center, and title cell G7, “Applied to Deductible.”
3. Highlight Row 7. Bold this row.
4. Set Row 7 as a filter so that you can put everything in date order when you have finished entering everything.
5. Select Row 7 – Automatically fit to cells (Alt, O, C, A).
6. Copy the formula in F55 (Control C) in cell H55 (enter).
7. Name the tab. (Alt O, H, R) You can do this by double clicking on the tab name. Rename it “Medical Bill Tracker.”
8. Highlight row 8, freeze panes. This way you can see the title headers as you enter information down the spreadsheet.
9. Format H59 to a percentage. Enter “=F55/H58”
10. Format H58 to a $ English United States. Enter “=E55-G55-H55”
11. Label G58 as “Total Owed.”
12. Label G59 as “Percentage.”
13. Highlight G58 to H59. Select “borders” and choose “All Borders.”
14. In cells G58-G59, format them as bold.
15. In column D and rows 3 and 4, enter Alt O, C, A (widen columns), and right align.
16. Do the same as you did in column D for column C.

1. Now you can start entering your information from your EOB’s and bills.
2. If the insurance (EOB) says that they did not cover services, these amounts should go under “Adjustments.”

Here are some Excel short-cuts that will make it easier to enter all of your medical information.

1. Holding down Control + “ ‘ “ will repeat what was written in the cell above it.
2. The “Home” button will take you to the beginning of a row.
3. The “End” button will take you to the end of a row where there is data.
4. “End” and the up arrow will take you to the top of data that is recorded.
5. “End” and the down arrow will take you to the bottom of the data that is recorded.
6. Control + c = Copy
7. Control + v = Paste
8. Control + x = Cut
9. Control + b = Bold
10. Control + I = Italics
11. Control + s = Save – SAVE YOUR DOCUMENT NOW IF YOU HAVE NOT ALREADY
12. Alt and then F and then A – Save your document “as” – Then you can choose the location you would like this document stored and title it as you wish.
13. Control + a = Select all

Enter the information from your EOB’s and your bills. If the information is the same, do not enter it twice, but make sure it is identical on the EOB and on the bill. If the EOB says the insurance company paid $50.00, but the bill says you owe that money, you need to call the doctor to straighten that out. If based on the bills you can see that you have over-paid on your deductible, you need to call your insurance company. The percentage amount should show you how much your insurance is paying besides your deductible and co-pay. If they are supposed to be paying 80%, and it says they are paying 60%, then you need to go over your bills and EOB’s with your insurance company.

Example: When I did mine, I came up with $1,179.00 as what I’ve paid toward my $1,000 deductible. So, I have over-paid $179.00. I need to go over this with my insurance company. Also, I found on a bill that they were trying to charge me a certain amount, when my insurance had already paid some. The insurance payment was not recorded on the bill. Therefore, I have to call the doctor to find out why they did not put the insurance payment on the bill.

If you keep up with all of your explanation of benefits and medical bills, you will be surprised how many little things you catch. You will also have a weight off your shoulders, because you will know you are organized and on top of things. This gives you a level of confidence as you go through your mail and pay bills.

**NOTE** I went over this one more time after writing it down the first time, so there is still a possibility of errors. If you see anything I wrote incorrectly (such as E55 versus F55), please let me know so that I can correct it for my readers. Thanks!