I've been working on my monthly budget and finally things look the way that I want them
to.
Making formatting decisions such as what columns to include, what text to include, and the
text style and size can be difficult.
I've already listed all my bills, but now I need to include my income information so
I can do a couple of calculations.
Calculating and analyzing numerical information Calculating and analyzing numerical information
is really what Excel is intended to be used for.
You can use it at home to create budgets like this one, or in the workplace to create complex
spreadsheets that keep track of company finances, inventory, and more.
To be able to use Excel to accomplish these tasks, it's important that you understand
formulas.
In school, you used formulas to calculate math problems like 2+2 =4 or 20% off of $100.
Microsoft Excel uses these same formulas to perform spreadsheet calculations.
A formula is an equation that performs a calculation using values in the worksheet.
You're probably used to seeing equations like 2 + 2 with an equal sign after it, but in
Excel, every formula starts with the equal sign.
This may be a little confusing, but I promise it will make sense.
In C3 you can see I have my net pay of $1500 listed and in C4 the money I made from my
extra job that month.
Let's say I want to add those two numbers together so the total income for the month
appears in C5.
Let's select cell C5 since that is where I want the total income for January to appear.
Since Excel formulas all begin with an equal sign, use your keyboard to insert an equal
sign in the cell.
As you insert the equal sign, notice how the formula bar changes.
You see, entering the equal sign makes the formula bar active.
You can now see the Cancel icon, which removes information from the formula bar, the Enter
icon, which will accept what you type, and the Function icon that provides you with some
other mathematical options.
Let's go back to C5 and finish our formula.
We want to add my 1500 and 200, so we'll just type the first number and then use the keyboard
to insert the addition sign and now type the second number.
The addition sign lets Excel know that an add operation is to be performed.
Ok, now that I have my formula entered in the cell, I can press the Enter key on my
keyboard or click the Enter command on the formula bar and now I can see that Excel added
$1500 and $200 and placed the answer in cell C5 where I typed the formula.
Another way you can create formulas is by using cell references.
Instead of typing =$1500+$200 in cell C5 to calculate my total income that month, I can
use a cell reference in the formula.
A cell reference is when you use the cell address in a formula and refer to that specific
cell by name.
For example, the cell address of this cell is C3.
This cell is where column C and row 3 intersect.
Now, let's write a formula using cell references.
Select the cell where you want the answer to appear.
Type the equal sign to let Excel know we're writing a formula.
Type the cell number that contains the first number to be added, and then type the addition
sign.
Now type the cell number that contains the second number to be added.
Press Enter on your keyboard or click the Enter button on the formula bar.
So, you're probably wondering why we might want to use cell references, it doesn't seem
any easier to type the cell address rather than the numbers, does it?
Let's say that I realized I entered my Net Pay incorrectly.
All I have to do is change the numbers in cell C3 and as I change it, my total income
in cell D5 automatically changes.
If I changed the amount in cell C3, I would not see a change in C5 because I didn't use
a cell reference, but instead typed the numbers in the formula.
Cell references are especially important to know about when working with columns that
contain many numbers that change regularly.
As you've probably noticed in most Microsoft Office applications, there is usually more
than one way to accomplish a task.
In this formula, I typed in the cell reference as I wrote the formula, but you can also use
the point and click method to select cells so that you don't have to type the cell reference
each time.
Let's see how the point and click method works while creating a simple subtraction formula.
I've entered the total amount of my January expenses in C29.
Now to calculate how much money I have after paying all my January bills I just need to
write a formula in C30 that subtracts my January expenses from my January income.
So, we'll select the cell where we want the answer to appear and then insert the equal
sign.
Now, all I need to do is left-click the first cell that is included in the formula.
The border of the cell changes to the marching ants and the cell reference C5 appears in
the formula here.
You can see the cursor is in C30, so now I can enter the subtraction sign to let Excel
know that a subtraction operation needs to be performed.
Now, just left- click the next cell in the formula.
Since we want to subtract our January expenses, we'll select C29.
Now it appears in my formula.
If we have more cells to subtract, we would just repeat these steps until the formula
is complete.
I'm ready to click Enter.And now the formula shows me how much money I have remaining after
paying all my bills.
You'll find the point and click method of selecting cells is really helpful because
often it's just easier and faster to use the mouse instead of the keyboard.
We've created formulas using the addition and subtraction signs, but you can also use
the multiplication and division signs in your formulas.
All four of these mathematical signs or symbols are called operators because they tell Excel
what type of mathematical operation needs to be performed.
You need to know this term because if you have to use the Excel help tool, that is how
it refers to these mathematical signs.