Excel Formulas To Help You Budget

Even though you’ve likely mastered Microsoft Word, you probably aren’t an Excel wizard yet. Up your skills (and your resume!)  by learning the following formulas, which can help create a budget.

  • Average. Did you know Excel can easily calculate the average of a set of data? Just click on an empty cell and type =AVERAGE(A1:A100) – those numbers we used were just an example. Excel will add up the numbers from cells A1 to A100, then divide them by 100 to calculate your result. It’s a pretty easy formula to learn and can, for example, help you figure out how much you spend per week on food.
  • Concatenate. This shortcut is especially useful if you need to merge 2 rows of data into 1. For example, if you’re trying to combine a year and a date into one column in order to track your purchases. Type =CONCATENATE(A1,” “,B1) into a 3rd column and then drag it down so each cell has been combined. This formula can also be modified to combine more than 2 columns of data together.
  • Count. Once you start using Excel, you’ll never have to count anything by hand again. Just type =COUNT(A1:A7). This will tell you exactly how many cells in that range have data in them. For example, if you had a filter applied to your data that meant the cells did not go in numerical order but you still needed to know how many times you’ve bought Starbucks this month, then you would need to use this function.
  • Trim. This is especially useful if you’re importing data you pulled from your credit card’s website, which often formats weirdly in Excel. So if you have a lot of extra spaces in your digital statement, use this formula. Type =TRIM(A1) – it’ll remove any spacing that’s not a single space and leave your data looking clean.
  • Pivot Table.  This is a really easy way to break down how much you spend in each category. Let’s say you’ve entered your data, like how much you’ve spent on food, bills, entertainment, etc. You then highlight your worksheet, find Insert Tab and click on Pivot Table. Once there, click “Select table or range,” and then “New Worksheet.” This will take you to the field list. You’ll be required to select a field for Row Labels and Values. Since you’re likely using this formula to keep track of your expenses – let’s say you want to see how much money you’ve spent per day this month.

So select Date for your Row Label (provided that you’ve broken down your spending by date) and then for Values select Amount, which would be where you input what you spent. Make sure that it reads “Sum of Amount.” After that you’ll have a table that shows you how much you’ve spent per day. If you want to sort the list by smallest to largest or vice versa simply highlight the labels for Date and Amount and then click Filter. Then you can sort your data how you please.

Just remember, these instructions are different for different versions of Excel – we used Excel 2003 – 2007. For example, in Excel 2011, you’ll find Pivot Table under the Data menu. So if you’re having trouble with these instructions, find the ones relevant to your particular version.

Keep in mind that a formula must always end with a parenthesis – otherwise, Excel won’t be able to execute. We hope this helped you conquer your budgeting woes!

Enjoyed this? Check out 10 Things I Learned From Techweek!

Written by Roselyn Sebastian

Even though you’ve likely mastered Microsoft Word, you probably aren’t an Excel wizard yet. Up your skills (and your resume!)  by learning the following formulas, which can help create a budget.

  • Average. Did you know Excel can easily calculate the average of a set of data? Just click on an empty cell and type =AVERAGE(A1:A100) – those numbers we used were just an example. Excel will add up the numbers from cells A1 to A100, then divide them by 100 to calculate your result. It’s a pretty easy formula to learn and can, for example, help you figure out how much you spend per week on food.
  • Concatenate. This shortcut is especially useful if you need to merge 2 rows of data into 1. For example, if you’re trying to combine a year and a date into one column in order to track your purchases. Type =CONCATENATE(A1,” “,B1) into a 3rd column and then drag it down so each cell has been combined. This formula can also be modified to combine more than 2 columns of data together.
  • Count. Once you start using Excel, you’ll never have to count anything by hand again. Just type =COUNT(A1:A7). This will tell you exactly how many cells in that range have data in them. For example, if you had a filter applied to your data that meant the cells did not go in numerical order but you still needed to know how many times you’ve bought Starbucks this month, then you would need to use this function.
  • Trim. This is especially useful if you’re importing data you pulled from your credit card’s website, which often formats weirdly in Excel. So if you have a lot of extra spaces in your digital statement, use this formula. Type =TRIM(A1) – it’ll remove any spacing that’s not a single space and leave your data looking clean.
  • Pivot Table.  This is a really easy way to break down how much you spend in each category. Let’s say you’ve entered your data, like how much you’ve spent on food, bills, entertainment, etc. You then highlight your worksheet, find Insert Tab and click on Pivot Table. Once there, click “Select table or range,” and then “New Worksheet.” This will take you to the field list. You’ll be required to select a field for Row Labels and Values. Since you’re likely using this formula to keep track of your expenses – let’s say you want to see how much money you’ve spent per day this month.

So select Date for your Row Label (provided that you’ve broken down your spending by date) and then for Values select Amount, which would be where you input what you spent. Make sure that it reads “Sum of Amount.” After that you’ll have a table that shows you how much you’ve spent per day. If you want to sort the list by smallest to largest or vice versa simply highlight the labels for Date and Amount and then click Filter. Then you can sort your data how you please.

Just remember, these instructions are different for different versions of Excel – we used Excel 2003 – 2007. For example, in Excel 2011, you’ll find Pivot Table under the Data menu. So if you’re having trouble with these instructions, find the ones relevant to your particular version.

Keep in mind that a formula must always end with a parenthesis – otherwise, Excel won’t be able to execute. We hope this helped you conquer your budgeting woes!

Enjoyed this? Check out 10 Things I Learned From Techweek!

Written by Roselyn Sebastian

  • Comments