How to Manage Your Personal Finances Using Excel

by Bill on January 18, 2012

One of many opinions I have is that your money is better managed when you have the information printed out in front of you. There’s a whole slew of ways to do this with computers today. Among the earliest ways of personal finance management was the Excel spreadsheet that came with MS Office. Since then many software programs have emerged to manage personal finances, but still among the easiest is using Excel. It’s still the only spreadsheet program, other than the Open Office version, that is completely customizable. To do this you must understand the functioning parts of Excel. Don’t worry, this isn’t that hard to do. If you’re still out there looking for personal finance software, stop, go find the latest edition of either Excel or its Open Office equivalent on your computer and follow along.


Making Yourself a Template
Create a general template you can set up for each period you want to record. You can make a template for weeks, months, quarters and so on.

Open a blank spreadsheet. From this page you can customize the template to as minimal or complex as you need. Start with the tabs found at the bottom left corner of the page. Start using Excel to manage personal finances by creating categories as you would in a filing system. The tabs are the same you would use on the file folders. Each tab becomes a sheet with one category.

For a simple example, set up five tabs: bills, cash flow, incidentals, living expenses and totals. Each tab is a separate sheet. You can divide each sheet into more categories by using labels at the top upper left corner of the sheet. Along the far left column number down each block corresponding to the number of days in the period (7 days for a week or 30/31 days for a month).

Label the cash flow sheet first. Use titles such as Summary, 3-year forecast, Current Cash Flow and your Cash Budget. The Living Expenses sheet uses labels like Rent or Mortgage, Utilities and Food. You can further separate the utilities into individual utilities if you want. Use the Bills tab for Credit cards, Installment payments, Television, Insurance and Auto. Again you can break down the Auto label further into Gas, Insurance and such. You can have a tab for incidentals covering items such as Laundry, Cell phones, Internet and other things you really don’t need to eat and keep a roof over your head. Don’t forget the Totals tab. This is where all the totals from the other tabs all end up; that’s how you track the overall outcome of your personal finances using Excel.

Automating the Formulas
Use formulas to help you automate the process. There are both predetermined and self-made formulas which are basic mathematical calculations (addition, subtraction, multiplication and division).

One thing to remember, formulas in Excel are written the reverse of normal mathematical equations; in other words to write “3+9=” in an Excel formula, you would write it as “=3+9” instead. To get a good tutorial on Excel formulas you want to visit this site for Excel Basics, Calculations and Special Features. This site is is a lot better than I would be at describing this.

Customizing
Once you have your tabs, sheets and formulas you start customizing the spreadsheet. From here you can add categories, labels, colors, columns, rows and a whole bunch of things. Each sheet can be customized individually.

Move your courser up to the Menu bars up top on the upper left corner of Excel. The menu bars are split up into six sections: Font, Alignment, Number, Styles, Cells and Editing.

Font is where you find the highlighting feature, bold lettering, letter colors, sizing and font texts like Calibri or Tahoma.

Alignment allows you to place the information in each cell along the right or left edge, or centered in the cell. You can also “merge cells” here and “wrap text” under this tab; these are good when your information is bigger than the cell can hold visibly.

The Numbers tab adds currency symbols or formats to create percentages, fractions and decimal placements.

Use the Styles tab for conditional formatting, cell styles, table formatting and customized features for each cell. With this tab you can highlight certain cells, create data bars and colored scales for reference. This is where the graphs and charts come in if you want those. This is also the tab used when you want to really customize your financial view. Create mini-sheets within a sheet, each with its own features and formulas if you want under one tab.

The Cells tab is the easiest of the group. This one allows you to add or take out columns, rows or individual cells. You can also take or add groups of cells.

Shortcuts
You may be faced with having a lot of information to gather as you use Excel to manage personal finances. Look above the Menu tabs and you find another toolbar: Home, Insert, Page Layout, Formulas, Data Review and View. Clicking on each of these brings up yet another drop down menu with different functions. To use Excel you want both the Home and Formulas menus.

The Formulas menu has shortcuts to almost every formula you want; along with most recently used formulas, common formulas reference guide, a name manager and a tab that allows you to check formulas to make sure they are working right.

The Data tab gives you options for importing data from elsewhere on your computer such as word documents or emails. This eliminates the need for typing in a whole slew of stuff. You can also create groups or sub-groups. You can also use the “what-if” tool to analyze your situation.

Yes, I know this post was way long. But seriously, why go through the hassle of spending money you probably don’t have, for software you probably don’t really need. Use Excel to manage your personal finances like everyone did way back in the good old days before somebody decided to make a buck off of it.

Be Sociable, Share!

Comments on this entry are closed.