I have a confession: I love Excel. Excel makes my life easier by taking complex, number-related problems and simplifies them to the components that matter. From my personal budget to website analytics, Excel can transform a chaotic data nightmare into an easy-to-understand spreadsheet.
Most people get the impression that you have to be a math or logic person to understand Excel. In short, you don’t. I am not particularly great with math nor logic per se, which is exactly the reason I use Excel — because once I create a spreadsheet with the formulas I need it’s a simple matter of just plugging in my data or values. So even if you’re not an “Excel person” at least keep an open mind since the hardest part is designing and creating a spreadsheet that works for your needs in the long time.
While I often use Excel professionally, I also rely on it for things like my personal budget. A properly designed Excel sheet can make short work of establishing (and actually sticking to) a monthly budget. Most banks offer some type of data exporting option via an online banking account (usually in an Excel or CSV format).
Excel Budget Formulas That Work
To me, there are basically two types of spreadsheets. The first is a simple sheet that just tracks your income and expenses. Now, if you’re just attempting to get a grasp on how much your spending on gas or groceries, this may be sufficient for your needs. The down side with creating a sheet the only does simple tracking it that you lose the ability to glean anything worthwhile from your data.
A better approach is to spend a little more time designing your spreadsheet to make it work for you in the long term using a series of very simple formulas that will, in the end, save you time and make money management and budgeting a breeze.
5 Great Excel Budgets Formulas
Keeping things simple is a good philosophy for practically everything in life and could be more true in the case of working with Excel sheets. I have 5 different formulas that are designed to keep things as simple as possible but are a little more advanced that most people are used too. Each one is meant to provide just enough information to be useful without being confusing.
1. Percent of Change
Formula: =(first value – second value/ABS(first value)
Why You Want It: It allows you to quickly see changes in income and expenses.
Instead of simply looking at dollar amounts from one month to the next try look at percentages, which will give you an easy to way to see changes in your budget and spending habits over time. This is a fantastic and simple formula for calculating how much change exists between two values — making it a perfect start from some month-to-month analysis on, say, how much you’re spending on gas or eating out each week.
This formula is super easy to use (essentially just subtracting one number from the other and dividing it by the first). Make sure that you have the division in an ABS bracket or otherwise your values may come out wonky. Percent of change also is an excellent one for line graphs since you can watch how your money changes over time.
I use this formula a lot when trying to view how much my income and expenses change from a month-to-month perspective. Moreover, if you get in the habit of using a budget each year you can, largely, anticipate changes to your budget depending on the time of year (e.g. expecting higher electric bills during summer months or fewer freelance jobs or commissions depending on your industry.
2. Conditional Formatting
Why You Want It: It helps you understand the highest and lowest values for income and expenses.
Conditional formatting really isn’t a formula per se, it’s just a way to highlight your expenses really quickly. As the name implies, conditional formatting will format the cells based on certain values. There area ton of options under the Conditional Formatting menu, but the one you’ll want to pay attention to the most are the “Color Scales” rule, which will quickly format all the values in a column from high to low. This is really handy if you have a lot of entries on your budget sheet and, best of all, it’s really fast to set up.
Formula: =AVERAGE(first value:last value)
Why You Want It: Averages help you adjust your monthly budget to reflect what you really end up spending and saving.
Averaging your income and expenses each month make seem like an obvious task to some folks but it surprises me how many people don’t have any perspective on what the spend and save each month. Getting average values on what you spend the most money on is, really, the first step to reduce your expenses.
4. Projected Vs. Actual
Why You Want It: Helps you determine how accurate you are at estimating expenses.
Projected Vs. Actual isn’t as much a formula as it is a hard reality check of your budging skills. Essentially, you’re taking what you thought you’d spend on something and subtract it from what you actually spent. You can set this up in multiple ways on your spreadsheet, but I prefer to do it monthly. I’ll admit that this can be a little tedious at first, but it does help you to at better at estimating your budget.
Looking at your projected budget versus your actual budget also has the effect of allowing you to pinpoint where the problems are. If you estimated only $50 for gas last month and you spent nearly double that, your projected versus actual area can highlight this quickly. One helpful tip is to combine this calculation with conditional formatting to see how your budget changed over a period of several months.
5. If Than Formulas
Why You Want It: It helps you save significant time in data entry and lets you get a quick grasp on your finances.
Of the Excel budget formulas I have listed here If Than formulas are the hardest to use. Essentially, you’re telling Excel to do something when certain conditions are met. You can use these formulas to into a simple data input sheet to something truly smart and customized for what you need. I frequently use If Than commands to organize my transactions based on categories. For example, I used to set If Than formulas to look for all the groceries stores listed over my past month of transactions and then add those value together — saving myself 20 minutes each month that I’d normally spend manually adding these values up.
If you’re not interested in worrying about your individual transactions you can still use If Than commands to give you a quick summary of what happened with your finances last month — like if you’ve met your budget or went over on a particular expense.