Using Formulas In Excel
If you are rusty with Excel, I will go over basic formula and function usage in this guide.
Using Formulas In Excel
In this section, I will go over how to create formulas, enter in operators, and use functions. A formula is a cell entry that performs a calculation. It does one specific task, usually. At the most basic level, typing a formula is selecting a cell, typing the formula you want to use, and hitting enter.
Typing
Formulas begin with an equals sign. If you leave out the = sign, Excel treats it like text. Enter in any numbers that you need and then a range if needed. You will probably want an operator and possibly a function. When you finish and hit enter, the result appears instead of the finished formula. I find the easiest way to edit formulas is using the formula bar. This prevents mistakes because you have a dedicated place for the formula and do not have other cells next to it. A single misclick can change a lot in your spreadsheet.
Cells and Ranges
In cells, you can click inside and do basic math. Such as:
=10 + 3
You can also enter values to add.
=a1+a2+a3
Values can be multiplied.
=a1*a2
You can do division the same way.
=a3/a2
If there are a lot, you can enter in a range to sum.
=sum(a1:a10)
This saves a lot of typing and probably errors. Much of function usage is learning the functions originally and then using on a regular basis.
A good trick you can do is naming a range of values. To do this:
- Highlight the range of values you want
- Right click to bring up the menu
- Name the range something meaningful
Now, if I named the range a1:a10 “range1”, I can do this:
=sum(range1)
This will sum everything in that range. Of course, you can easily see how doing any math operation is made quicker by this method.
Operators
Order of precedence is important for any software that does calculations. When writing formulas, the order has to be correct for you to get the result that you want. Operators are symbols that tell Excel what type of calculation to perform. It has all the usual types that you can think of. These include arithmetic, comparison, text, and reference operators.
Excel looks at cell contents from left to right, generally. Of course, higher precedence items get evaluated first. Then, anything in parentheses is looked at first.
Functions
Functions simplify your formula writing a lot. While you can create and write your own formulas, there is probably a function that does it. So, using the function just saves you time and effort. A function contains an equal sign, parentheses and arguments. Arguments consist of cell references, ranges, constants, text, and range names. Btw, any text arguments must be enclosed with quotation marks.
Functions can be nested as in most programming languages. Nested functions get evaluated first. Some of the more common functions are included in the “AutoSum” tab. Click it and choose which you want. You can choose sum, average, count, max, and min.
When typing a function in a cell, you might not remember what arguments it needs. Make sure and look in the formula bar. It will give you hints as to what you need. When getting the #Value error, don’t panic. It is one of the most common errors. It usually happens when one of the cells it is looking at contains text instead of a numerical value.
You can find all the formulas available to Excel on the functions tab, on the ribbon. You can start there if you do not remember the details of a function you want. Sometimes, it is fun to just explore the different functions!
The function library organizes everything by categories. These categories can include finance, date, time, and text.