How to use 4 basic spreadsheet functions to create a budget - Cents and Family (2024)

The use of a spreadsheet is a very effective tool for organizing your finances. Use basic formulas and functions in a spreadsheet to create your own budget. While there are many online programs and templates available to create a budget, making one with a spreadsheet is a great option. You can customize your own needs.

Affiliate Disclosure:This post contains “affiliate links”. This means if you click on an affiliate link and purchase a product/ service, I may receive a small commission at no extra cost to you. However, I only recommend products I personally use, love, and/or believe will add value to my readers. For more information see mydisclosures.

Microsoft Excel and Google Sheets are two popular programs with spreadsheets. Open up a spreadsheet and follow along with the examples given below. These exercises will take you a quick 10 minutes to do. By the end, you will be able to apply simple functions in a spreadsheet. Let’s do it!

Function 1: How to use formulas to add, subtract, divide and multiply

A spreadsheet is like a big calculator. Start a formula off with an equal sign “=”

There are 2 basic ways to do each math function. Let’s use adding as an example:

= then the numbers [=20+10]

= name of the cells that hold the numbers you want to add up [=A1+A2]

Exercise 1 – how to add single cells:

In cell A1 enter the number 10

In cell A2 enter the number 20 and so on until you get to cell A5 for 50

In cell A6 enter: =A1+A2+A3+A4+A5 to add up all those numbers in those 5 cells

The answer in A6 will come up to 150

Examples of how this function can be used in a budget:

Adding: Adding up the expenses

Subtracting: Determining if income=expenses (income – expenses = 0)

Multiplying: Determining the 10% portion of the monthly income that will go towards savings (=$3000*10%)

Dividing: Determining how much to save each month for an annual lump expense (i.e. =1300/12)

FUNCTION 2: How to use a formula to add many numbers across a row or column

There is a formula that makes it super easy to add many numbers across a row or column. Instead of adding each number individually, use this simple “sum formula” to do it one step.

Exercise 2 – How to add multiple cells together

In cell A1 enter the number 10

In cell A2 enter the number 20 and so on until you get to cell A5 for 50

In A6 enter the sum formula: =sum(A1:A5)

The answer is the same as exercise 1 which is 150

This formula tells the program to add all the numbers between A1 to A5 inclusive. This is a lot easier than having to add each number or cell individually like in exercise 1.

How to apply this to a budget:

This formula is super useful to get a total of the various incomes and a total of the expenses.

FUNCTION 3: How to organize numbers and categories by sorting

There is a simple function that allows you to sort numbers and categories.

Exercise 3.1: Sorting categories by alphabetizing

In a column enter each word in a new cell: utilities, rent, personal spending, vacation fund, gift fund, retirement, car fund

Highlight the cells containing these expenses

Right click and find the “Sort” function

Voila – the expenses are now alphabetized

Exercise 3.2 – Sorting numbers lowest to highest (or vice versa)

Enter 5 different numbers down in a column.

Highlight the cells containing the numbers

Right click and find the “sort” function.

Depending on what you clicked on – the numbers are now arranged from smallest to largest or largest to smallest.

How to apply this to a budget:

You may want to list your expenses from the smallest to the largest expenses. Perhaps you may want to alphabetize your expenses to make it easier to read and track.

FUNCTION 4: How to use the drag function to extend a series

This function saves you time and effort in having to type out a series of words or formulas. Enter it once in a cell and then use the drag function across to adjacent cells to finish the series

Exercise 4.1: Use the drag function to list out the months in a year

Enter January in a cell

Click on the January cell. There is a small square dot on the bottom right-hand corner.

Click on the square and drag it down another 11 cells. This will populate the cells with the month names from January all the way to December. Easy and it’s done in a few seconds!

You can also use the drag function to repeat the same formula across different cells. For example, you have three columns of numbers and would like to get the total of each column. Use the sum formula to get the total of the first column and then use the drag function and to extend the sum formula towards the other 2 columns.

How to apply to a budget:

Use the drag function to list the months in your budget.

Apply the sum formula across the adjacent columns to add up expenses.

Here is an example of how all 4 functions in a spreadsheet are used in a budget:

Take a look at how formulas and functions in a spreadsheet can be used in creating a Christmas gift list, how to organize your money and how it can be used to calculate your net worth

These 4 basic functions in a spreadsheet (simple math, sum formula, sorting and drag functions) will help you to create your own budget. Once you know how to navigate a spreadsheet using these basic functions, then it will be certainly easy to apply it to other uses in a spreadsheet.

PIN FOR LATER

Highly recommended reading:

You may also like:

Other FINANCIAL BASIC posts