Now that you understand how Excel files work, let’s delve into the details of analyzing data.
Excel is well known for its accessible and powerful functions. Let’s have a look at how to use them.
Find Functions in Excel
If you want to perform a calculation on some data but you don't know what the function is called, it more than likely already exists—you just need to find it!
You’ll need to do some detective work and search for the function and/or how it works. You have two options:
Use context-sensitive help in Excel, which can be accessed using the F1 key and then typing what you're looking for. This also displays how the function works if you know which one you’re looking for, but aren’t sure how to use it.
Go to the Excel function list and search through it to find what you think you're looking for. To access the function list, you need to go to Formulas > Insert Function (you can also click directly on the “fx” icon). The functions are grouped into broad categories (Financial, Date and Time, Math and Trig, Statistical, etc.).
Use a Simple Function
The Sum function is probably the most used function in Excel.
Let’s look at a simple example. You have the following data:
In E10, you want to calculate the total sales figures, so you’re going to insert the Sum function.
To do this:
Select cell E10.
Click on the Home tab.
In the Editing group, click on the AutoSum icon.
Excel will select the range E4:E9 for you.
Confirm with the Enter key on your keyboard.
You should get the result 671,400.
If you want to calculate the average sales figure, do exactly the same thing using the Average function.
Try it! What do you get?
Use a More Advanced Function
There is a huge list of functions in Excel and it’s impossible to cover all of them in this course. But let’s look at one more function that is used a lot: the IF function.
The IF Function
Let’s take an example from your file that shows sales figures by salesperson. You want to pay a $500 bonus to each salesperson who gets over $100,000 of sales. The others will get 0.
To do this:
Select cell F4.
In the Formula Bar, click on the
icon.In the Insert Function dialog box, select the Logical category.
Select the IF function.
Click OK to confirm.
The IF function consists of three arguments, or three parts:
Logical_test is the condition you want to check.
Value_if_true is what you want Excel to do when the condition is met.
Value_if_false is what you want Excel to do when the condition is not met.
In our example:
The Logical_test will be: the sales figure exceeds 100,000.
In Value_if_true: type 500.
In Value_if_false: type 0.
And you’ll see this:
The more you use the IF function, the more you’ll discover what you can do with it!
Over to You!
Practice is the key to getting the hang of Excel functions. I'd recommend creating some simple files to begin with, and practice using formulas.
Can you find a formula that allows you to calculate the duration of time between two dates?
Have a look at the “date” functions and you should find it.
Can you also find a formula that allows you to count the number of blank cells? Or one to count cells that contain a particular value?
Go ahead and explore!
Watch the Video Tutorial
Watch a step-by-step recap of everything described in this chapter in the tutorial below:
Let’s Recap!
To perform calculations on your data quickly and easily, you can use functions such as Sum or Average, and many others!
The IF function allows you to show a result based on whether or not a particular condition is met.
You now know how to perform calculations using functions. In the next chapter, you’ll find out how to allow—or restrict—certain types of data to be entered in Excel.
