Budgeting spreadsheets are powerful tools for managing your finances, but with all the data and calculations involved, they can quickly become overwhelming. Fortunately, Google Sheets offers a range of formulas that can streamline your budgeting process. These formulas help you track expenses, manage income, and stay on top of your financial goals.

1AVERAGEIF

The AVERAGEIF function helps youcalculate the average of a group of numbersin a range of cells that meet specific criteria. The syntax for this function is:

Whererangeis the range of cells you want to evaluate,criterionis the condition that must be met, and[average_range]is the range of cells to average.

Sample data for a budgeting spreadsheet

AVERAGEIF Example Calculation

Let’s assume you’ve decided tocreate a budgeting spreadsheetthat tracks various expenses along with their corresponding dates, like in the example below.

To find the average amount you spend on groceries, where “Groceries” is listed in columnAand the amount in columnB, use the formula:

Using the AVERAGEIF function on a budgeting spreadsheet

Based on the sample data, the average amount spent on groceries is $150.

SUMIF allows you to sum the values in a range that meets specific criteria. The syntax for the SUMIF function is:

Using the SUMIF function on a budgeting spreadsheet

Whererangeis the range of cells to evaluate,criterionis the condition to meet, and[sum_range]is the range of cells to sum.

SUMIF Example Calculation

If you want to total your grocery expenses, use the formula:

In this case, the total amount spent on groceries is $450, based on the sample data.

Using the COUNTIF function on a budgeting spreadsheet

With COUNTIF, you can count the number of cells in a range that meets specific criteria. This makes it easier to track the frequency of certain expenses.

The syntax for the COUNTIF function is:

Whererangeis the range of cells to count, andcriterionis the condition that must be met.

COUNTIF Example Calculation

To count how many times you’ve shopped for groceries, use the formula:

Based on the sample data, the formula returns 3, meaning I made grocery purchases three times. If you want to input multiple criteria, you can usethe COUNTIFS function.

Using the IFS function on a budgeting spreadsheet

IFS is a more advanced function that allows you to test multiple conditions. It’s useful for categorizing expenses based on different criteria.

The syntax for the IFS function is:

Condition1is the first condition to evaluate, andvalue_if_true1is the result if that condition is true. You can add more conditions and corresponding results.

IFS Example Calculation

If you want to categorize your spending based on amounts, use the formula:

This formula will label each expense as Low, Medium, or High based on its amount. For example, a $150 grocery purchase would be categorized as High, as shown in the screenshot below.

TheTEXT function formats numbersas text, which is useful for displaying numbers in a readable way (like showing currency or percentages).

The syntax for the TEXT function is:

Wherevalueis the number you want to format, andformat_textis the desired format (such as currency or percentage).

TEXT Example Calculation

To display a number as currency, use the formula:

Since cellB2contains 150, the TEXT formula displays it as$150.00to clarify that the cell’s value represents money.

If you prefer not to use formulas to format numbers, there are alternative methods toadjust decimal places in Excel.

The INDIRECT function allows you to reference cells dynamically by converting a text string into a cell reference. This allows you to update your formulas based on varying inputs, such as different sheets or ranges within your spreadsheet.

The syntax for the INDIRECT function is:

Whereref_textis a reference supplied as text, and[a1]is an optional argument that specifies whether the reference should use A1 style (TRUE) or R1C1 style (FALSE). By default,[a1]is set to A1 style (TRUE).

INDIRECT Example Calculation

Suppose you have a reference to a cell in text format (e.g., “B2”), and you want to convert it into an actual cell reference. To do that, use the formula:

This formula returns the value in cellB2, which is $100.

Now, let’s say you have multiple sheets in your budget workbook, each representing a different month (e.g., “January,” “February,” “March”). For instance, below is a screenshot showing the sample data for the monthly expenses in March:

The total amount is indicated in cellB7, and it’s $1,775.

Let’s say you want to create a summary sheet that displays the months in columnAand dynamically pulls in the total expenses from any given month and writes them in columnB. Below is an example of what the sheet would look like.

Now, assume you want to dynamically pull the total expense from cellB7in the March sheet and display it in your summary sheet. Here’s how you’re able to use the INDIRECT function to do that:

In this example,A4refers to the cell in your summary sheet containing the name of the sheet you want to pull your data from (e.g., “March”), and the formula dynamically references cellB7from the March sheet, which contains the total expenses for the month. The ampersand (&) is used toconcatenate or join text strings together.

In this case, it combines the single quote (') for sheet names with spaces, the sheet name fromA4, and ‘!B7’, where the exclamation mark (!) separates the sheet name from the cell reference.

The screenshot shows that the total monthly expenses for March resulted in $1,775 when using the INDIRECT formula. This corresponds to the total monthly expenses in the March sheet itself. Interestingly, changing the value inA4to “February” will automatically update the reference to the February sheet.

The FILTER function allows you to filter a range of data based on specific conditions—making it easy for you to isolate particular expenses or categories.

The syntax for the FILTER function is:

Whererangeis the data you want to filter, andcondition1andcondition2are the conditions that the data must meet.

FILTER Example Calculation

Here’show to use the FILTER functionto filter out only grocery expenses using our sample data:

Enter the formula in a new column. In my example, I entered it into cellE2in columnE. The formula will return only the rows where columnAcontains “Groceries,” which would allow you to focus on your grocery spending without distraction.

XLOOKUP is a versatile function that searches a specified range for a match and returns a corresponding value. It’s perfect for looking up expenses or categories.

The syntax for the XLOOKUP function is:

Wherelookup_valueis the value to search for,lookup_arrayis the range to search,return_arrayis the range to return the result from. The optional parameters help specify what to do if no match is found and how to handle matching criteria.

XLOOKUP Example Calculation

Suppose you have a budget spreadsheet that tracks various expenses by category in columnA, the amount spent in columnB, and item names in columnC. Now, you want to determine how much you spent on a specific grocery item, such as Vegetables.

Here’s the XLOOKUP function that can help you with this:

In this example, the formula returned $150, the amount spent specifically on Vegetables

Now, all the formulas we’ve covered can transform your budgeting process and make it easier to manage your finances effectively. Try them out today and discover how they can streamline your budgeting tasks.