EXCEL FORMULAS

Lesson 15 - The Basics of Excel Formulas

EXCEL FORMULAS

Download the associated Workbook for this lesson

In this lesson we will Start to take a look at what Excel does best and that is; work with formulas.

There aren’t too many spreadsheets about that do not contain formulas of some kind. Excel is regarded by many (including myself) as the number one spreadsheet package in the world today. In fact it has held this title for many years now and not without good reason. Once you become proficient in using Excel and become familiar with its capabilities, you too will no doubt wonder how you ever got by without it!

In this lesson we will look only at what I consider the bare minimum you should know about Excel and formulas. Once we have covered this we can move on to slightly more complex formulas and functions. You may or may not have heard the terms; formulas and functions used in reference to Excel before and wondered what’s the difference? The truth is they are more often than not used out of context. This is no doubt because the difference is quite subtle, though simple. Functions

Excel has over 300 built-in Functions installed by default (there are more, but 300 will do for now) which are divided into ten separate categories:

Financial
Date & Time
Math & Trig
Statistical
Lookup & Reference
Database
Text
Logical
Information
Engineering

Possibly two of the most popular Functions (and easiest to use) are the SUM , which is categorized under Math & Trig and the COUNT. The SUM function simply adds together numbers and returns the total sum of the numbers. The COUNT simply counts numbers in any given range of cells. So a Function is a single predefined formula that is built into Excel. Formulas

Once we have used one of Excels functions on a Worksheet we have created a formula. As we become more confident in using Excel we can Start to join functions together to create different formulas. For example we might use the SUM and the COUNT function together in the same cell to create what would then become a formula.

So in a nutshell we can use a Function or Functions to create what then becomes a Formula.

Formula and Function Rules

There are only two rules when using functions to create formulas in Excel and these are:

All Formulas And Functions Must Begin With A = (Equal Sign).
For Every Open Parenthesis (Brackets) There Must Be A Closing Parenthesis.

Don’t confuse point 2 as meaning all formulas must have open and closing parenthesis, as this not always true. What is does mean is, all of Excels built-in Functions use at least one set of open and closing parenthesis. These are the two rules that we must adhere to.

There is also a valuable tip that is good practice to get into also and that is that whenever you type in a function name, always type it in lower case. The reason for this is that if you have spelt the Function name correctly, Excel will automatically convert it to uppercase. Therefore you can use it as an error checking tool.

You can use AutoSum to quickly sum a row or column. Select the cell directly next to the numbers you want to sum, click Autosum on the Home tab and press enter. Alt+= will also insert the sum function.

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

Go back to:

Lesson 1 - Excel Fundamentals

Lesson 2 - Starting Excel and Excel Workbooks

Lesson 3 - Excel Toolbars and Task Panes

Lesson 4 - Excel Worksheets

Lesson 5 - Excel Cells and Navigating a Worksheet

Lesson 6 - Excel Cut/Copying and Pasting Data

Lesson 7 - Excel Copying with the Fill Handle

Lesson 8 - Excel Paste Special

Lesson 9 - Excel Insert Command

Lesson 10 - Excel's default options