ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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

Updated on August 23, 2013


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.


Medical Tracking

Do you have your EOB's and medical bills organized?

See results

How to Create a Spreadsheet in Excel

How to Build a Medical Bill Tracker


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.


Excel 2013 Software

Excel 2013 Power Programming with VBA
Excel 2013 Power Programming with VBA

This is the Excel software that is needed to create an Excel spreadsheet. This even comes with a tutorial on how to build a spreadsheet to help beginners to advanced users.

 

The Title


  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.


Formulas and Functions

Excel Formulas and Functions For Dummies
Excel Formulas and Functions For Dummies

This book is an excellent resource for covering all functions of Excel and all formulas that Excel has to offer. Teach yourself this valuable skill using this book.

 

The Setup of the Table


  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.”


Labeling the Table


  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).



Expert Skills

Learn Excel 2010 Expert Skills with The Smart Method: Courseware Tutorial teaching Advanced  Techniques
Learn Excel 2010 Expert Skills with The Smart Method: Courseware Tutorial teaching Advanced Techniques

Learn expert techniques, functions, formulas, macros, and methods with this handy book. It will allow you to teach yourself how to use "The Smart Method."

 

The Table’s Formulas


  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).


Excel 2010 Formulas

Excel 2010 Formulas
Excel 2010 Formulas

Many different versions of this book are sold on Amazon. This book covers all formulas that Excel has to offer. You can teach yourself how to become a pro!

 

The Summary of the Table


  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”


General Layout on the Table


  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.


Excel 2013 for Dummies

Excel 2013 For Dummies
Excel 2013 For Dummies

This Excel 2013 book contains everything you need to know in a very simple-to-read format. It covers all short-cuts, functions of Excel, and formulas. Learn how to create a spreadsheet too.

 

Analyzing Your Explanation of Benefits and Bills


  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.”


Secret Short Cuts


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


Example

 
 
Total Amount Billed
$1000
Insurance Amount Paid
$800
Adjustments
$150
Amount Due
$50

Solving Any Problems


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!


Comments

Submit a Comment

  • misslong123 profile imageAUTHOR

    Michele Kelsey 

    5 years ago from Edmond, Oklahoma

    I hope it helps :)

  • profile image

    SRoss 

    5 years ago

    This is helpful because it allows you to keep track of adjustments and what's been applied to the deductible. But I'm not completely sure everything is in the right place quite yet.

working

This website uses cookies

As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

Show Details
Necessary
HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
LoginThis is necessary to sign in to the HubPages Service.
Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
AkismetThis is used to detect comment spam. (Privacy Policy)
HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
Features
Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
MavenThis supports the Maven widget and search functionality. (Privacy Policy)
Marketing
Google AdSenseThis is an ad network. (Privacy Policy)
Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
Index ExchangeThis is an ad network. (Privacy Policy)
SovrnThis is an ad network. (Privacy Policy)
Facebook AdsThis is an ad network. (Privacy Policy)
Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
AppNexusThis is an ad network. (Privacy Policy)
OpenxThis is an ad network. (Privacy Policy)
Rubicon ProjectThis is an ad network. (Privacy Policy)
TripleLiftThis is an ad network. (Privacy Policy)
Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
Statistics
Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)