Using Excel to Manage Personal Finances: Creating Templates & More

Using Excel to Manage Personal Finances:  Creating Templates & More
Page content

Personal Finances are easy to manage once you are looking at all the information in print. One of the earliest methods of managing personal finances in the computer age was using Excel. While there are many software programs available to manage personal finances, Excel is still the only one that can be completely customized by the user. In order to customize Excel so that it fits your needs, you need to understand how to use Excel to best manage your personal finances.

Creating the Template

Excel can be set up to have a generalized template that can be reused. To do this you will need a blank spreadsheet. From this blank page you can create a simple or complex management system for using Excel to manage personal finances. You can use the tabs found at the bottom left to create multiple categories similar to having folders in a filing cabinet. Set up five tabs: cash flow, living expenses, bills, incidentals, and the last one for totals. The cash flow sheet can be further divided into three more tables within the sheet: a summary; 3-year forecast; current cash flow; and, finally the budget itself. Set up the living expenses tab using Excel to manage personal finances such as rent/mortgage, utilities (heat, electric, water, sewer), and food to cover the essential needs. Use the bills tab to track the remaining bills including credit cards, loan payments, TV, gas, insurance, and auto. Incidentals is where items such as laundry, cell phones, Internet service and other items that need to be paid but not required to provide shelter will go. The totals tab will be where everything gets added up in the end.

Adding Formulas

Using Excel to manage personal finances is made much easier with formulas. You can use predetermined or self-made formulas. Formulas are used to carry out mathematical calculations such as addition, subtraction, division and multiplication. Formulas are written in reverse of normal math formulas; an example is where Excel formulas use the equal sign “=” before the calculation instead of after. To write “3+6=” as an Excel formula you would write “=3+9” instead. There is a good tutorial site that shows how to set up simple formulas as well as how to calculate tax and bank interest. You can find that here. Take the time to fully understand how formulas work and you can create any variation of financial calculations for using Excel as your personal finance tool.

Adding Information

Once you have the tabs and the formulas set up for each sheet, you can start adding categories, labels, and other items including colors. Using the menu bars on top of the spreadsheets you can customize each sheet. You will notice that the menu bars are divided into six sections: font, alignment, number, styles, cells, and editing. Font is good for highlighting, bold lettering, various letter colors, and sizing as well as various text fonts such as Calibri or Times New Roman. Alignment is good for text orientation where you need to have text at a diagonal angle to the rest of the sheet; the alignment tab is also where you find the “merge cells” function and the “wrap text” function. You can also align the numbers in the cells to a left, right or center orientation. The Numbers tab in the menu is used to add currency symbols and various number formats from the drop down menu in this section. You can also create percentages, fractions, and decimal placement from the Numbers tab. The Styles tab contains the ability for conditional formatting, cell styles, and table formatting; conditional formatting allows you to highlight certain cells, create data bars, color scales, and input various icons for reference; table formatting allows you to create multiple mini-sheets within a sheet so you can have many tables, each with their own sets of formulas and values in one tab; the Cells tab allows you to insert or delete columns, rows, individual cells or groups of cells. Editing is where all of excels shortcut functions are located.

Customizing and Shortcuts

When using Excel to manage personal finances you may have lots of information to put in. Above the menu tabs is a group of seven words: Home, Insert, Page Layout, Formulas, Data, Review, and View. Each word when clicked, brings up a new menu bar below. Of use for personal finances is the Home and Formulas bars. The Home bar was described in “Adding Information”, but the Formulas bar has shortcuts to nearly every formula you may need; you can also find recently used formulas, a reference guide for common formulas, a name manager, and a tab for checking formulas to ensure they are functioning properly. The “Data” tab provides options on importing data from outside sources, creates groups and sub-groups, and provides a “what-if” tool for future analysis.