Excel Formula Basics
Writing a spreadsheet formula is different from writing an equation in math class.
Excel formulas look like=3+2instead of3 + 2 =.
The formula is still there, but it doesn’t appear in your spreadsheet.
Lifewire / Maddy Price
Improve Formulas with Cell References
Excel formulas can also be developed usingcell references.
Here’s a real-life example of how this approach can be useful.
Say you lead a team of salespeople and are tracking their monthly and quarterly sales.
Lifewire / Maddy Price
You want to calculate their total sales for the year.
Using Cell References
Each cell in Excel is part of a row and a column.
Rows are designated with numbers (1, 2, 3, etc.)
shown along the top.
They refer to the quarterly sales numbers for the salesperson named Jean.
Theformula adds those numberstogether to come up with the annual sales number.
Create a Formula With Cell References
Try creating a simple formula using cell references.
First, you must populate the spreadsheet with data.
Open a new Excel file and select cellC1to make it the active cell.
Type3in the cell, then pressEnteron your keyboard.
If it’s not, select cellC2.
Type2in the cell and pressEnteron your keyboard.
Now create the formula.
Select cellD1and punch in=C1+C2.
Notice that when you jot down each cell reference, that cell becomes highlighted.
PressEnterto complete the formula.
The answer5appears in cellD1.
If you select cellD1again, the complete formula=C1+C2appears in the formula bar above the worksheet.
Use your pointer to select cellC1to enter the cell reference in the formula.
PressEnterto complete the formula.
The result appears in cellE1.
Notice that the results in cellsD1andE1both change from5to8, though the formulas remain unchanged.
An easy way to remember the order of operations is to use the acronym BEDMAS.
Excel actually considers division and multiplication to be of equal importance.
It performs these operations in the order in which they occur, from left to right.
The same is true for addition andsubtraction.
Here’s a simple example of the order of operations in use.
It then performs the multiplication operation,2*5, with the result of10.
Try entering the formula into Excel to see it work.
Enter a Complex Formula
Now let’s create a more complex formula.
Select cellC2to enter the cell reference in the formula, then throw in the minus sign (-).
Select cellC4to enter this cell reference into the formula, then key in a right bracket ()).
pop in the multiplication sign (*), then select cellC1to enter this cell reference into the formula.
jot down the plus sign (+), then selectC3to enter this cell reference into the formula.
punch in the division sign (/), then selectC5to enter this cell reference into the formula.
PressEnterto complete the formula.
The answer-4appears in cellD1.
