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 my disclosures.
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]
Subtracting (-) | [=20-10] or [=A1-A2] |
Multiplying (*) | [=20*10] or [=A1*A2] |
Dividing (/) | [=20/10] or [=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
[…] How to use 4 basic spreadsheet functions to create a budget – Cents and Family […]
[…] How to use 4 basic spreadsheet functions to create a budget […]
This is honestly sooooo useful. I can’t wait to create my own budget spreadsheet!!! Thank you ❤️
I tried to explain spreadsheets in my book but your explanation here is just amazing. Well done.
Thank you Anna!