You know your way around Excel. You may already be using it to create reports for yourself or your work. But when you get past the beginner stage you also start to see something else: all of the things that you don’t know about Excel.
We’re here to fill you in on any gaps of knowledge that you might have. One lesson at a time. Welcome to the intermediate lesson on Excel.
To be honest, this isn’t just one lesson. It is a group of lessons designed to kickstart your knowledge of functions and formulas in Excel.
The lessons take about five minutes each. Lets get started, this first part is about formulas in general.
Welcome to the first lesson in a series of four preview lessons! If you find them too easy, you can always scroll ahead to the next lesson. If you find them too difficult, you can take your time and use the ‘Show answer’ button alongside every exercise to find out how an exercise could be finished. Without further ado, let’s talk about formulas.
Formulas are a very important part of advanced Excel usage. A formula is like a calculation: one or multiple instructions that can be executed by Excel to lead to an output.
In Excel, all formulas start with an equals sign = . For example, if you were to write:
into the edit input bar at the top of your Excel worksheet, the number two will be shown in the selected cell.
Try it out
In cell A1 in the spreadsheet editor below.
You can always use the ‘Show answer’ button at the bottom right of the exercise to see how you could solve the exercise.
Referencing other cells
A powerful feature of Excel formulas is the possibility to reference other cells. For example, you could have the values 10 and 5 in cells A1 and A2. To determine the sum of the two, you can write
=10+5 or you could write
=A1+A2. Both would result in the value 15. Lets take a look at how this works in the next exercise.
To make sure you don’t just read, but also learn, we use practice exercises. Here’s one where you need to calculate monthly expenses.
In this exercise, your expenses are in cells A1, A2 and A3. Calculate your monthly expenses by summing the values in the cells A1, A2 and A3 using the function discussed above. Use references to the cells instead of absolute values (write A1 instead of 1000).
More about formulas
Sometimes, you might want to just write some text in the cell that happens to start with an equals sign =. In that case, you can write an apostrophe ‘ in front of the =. That way Excel knows not to process it as a function.
=10+5 gives output:
'=10+5 gives output:
Excel function Count
Some of the most used functions in Excel are the functions that count. There are multiple variations of the count function.
The regular count function calculates how many cells of the given cell range contain numbers. The COUNT function looks like this:
So for example
=COUNT(A1:A5) would count how many of the cells A1 up and including A5 contain a number.
Try out count
Now try using the count function in cell A7 and applying it to cells A1 to A5 in the spreadsheet editor below.
The count function counts how many numbers there are. It can also be handy to see how many non-blank cells there are in general. For that purpose, there is the COUNTA function. Counta stands for count all and it works in the same way as count.
Try it out below, by using the COUNTA function in cell D2 to count all non blank cells in the range A1:A5.
Above, we have selected multiple cells by writing a start cell, a colon : and an end cell (
A1:A5). Such a group of cells is called a range of cells. It includes the start cell and the end cell and everything in between.
A range does not have to consist of cells of the same column. It can be any rectangular selection in the worksheet. For example:
A1:B2 is the range of cells consisting of A1, A2, B1 and B2.
A range can be a rectangle of any dimensions you want. We will practice this in combination with the Counta function in the exercise below.
In this exercise, you have just started your job at FinePhones, a phone store, as a cashier. You are interested in knowing how many different phone brands are sold here at FinePhones. Your manager has told you there is an Excel sheet that you could take a look at that contains all brands, but it is not structured very well at all.
Count all brands whose phones are sold at FinePhones using a Counta function in cell F1. Do not forget that you can set the cell range to be any rectangle selection you want.
More about count
There is another function in the family of count functions called countblank. This is what it looks like:
As you can see, it is very similar in its use to count and counta. You are probably also able to guess what countblank does: it counts the number of blank cells in the given range. You could for example use this function to count the number of gaps in the exercise above.
Excel Sum Function
Together with count, the
SUM function is one of the most used functions in Excel. It is handy in many situations, from personal finance to business reports.
The sum function sums up the numbers in the given cell range. It is very similar to the + function in a way. Except that the sum function works on ranges of cells whereas the + function works on individual cells.
The sum function looks like this:
Try out sum
Now try using the sum function in cell B7 and applying it to cells B2 to B5 in the spreadsheet editor below.
Summing specific values
The sum function also works with a different way of defining the cells that should be summed. For example, instead of
A1:A5, you could write
You can also leave cells out or combine the two methods of defining ranges. For example, you could write
=SUM(A1:A3, A5) to sum calculate the sum of A1, A2, A3 and A5, skipping A4.
After a couple of months of working at FinePhones, you have taken some necessary vacation days off. Since the end of the year is near, you would like to know how many days off you have taken. However, the Excel document that contains the taken days off is filled with entries for all employees at this FinePhones branch.
Find out how many days off you have taken this year by using a SUM function in cell E4 that uses a colon : and a comma , to set the range.
Combining sum and count
Knowing how and when to combine functions is a skill that is easy to learn but hard to master. The sum and count function(s) are one of the combinations that appear often. They can be used to count different types of things and then summing their totals.
Since that is quite abstract, here is an example where you could use this:
Suppose you want to make an overview of all of the movies you own. You have written each movie title and its genre on a row per movie in an Excel sheet. You could count the number of movies per genre and then sum them to see how many movies there are in total.
We will practice this in the next lesson.
Combining it all
In this final preview lesson we will combine the learned formulas to create a spreadsheet that can keep track of product sales for our preview lessons’ example, phone shop FinePhones.
Overview and revenue per category
You have worked really hard and made many things more efficient at your FinePhones branch by using your new Excel skills. This has not gone unnoticed by the higher-ups.
It is decided that you will be promoted to become a store manager. Congrats!
For your first task as a store manager, you want to keep track of your sales and revenue. You already made a small start in the spreadsheet below.
The next step is to calculate the revenue per item category. For this, you could use what you’ve learned in the About formulas lesson, but instead of using the plus + to sum, you could use the asterisk * to multiply.
Fill in the cells D4, D5 and D6 using the asterisk multiplication function to calculate the revenue per phone category.
Next, you shop overview would benefit from some totals, like total number of items sold and total revenue.
- The total number of different products, 3 in this case, can be automatically calculated by Excel using a COUNTA function in cell B9.
- The total number of items sold across all product categories (11) can be found by using a SUM function in cell B10.
- Finally, the total revenue can also be calculated with a SUM function in cell B11.
Part of the challenge is knowing what ranges to use for these functions. We have explicitly chosen not to tell you what these ranges are because we are confident that you can figure this out yourself.
That’s it for the intermediate lessons
Well done on reading this far. You’ve gotten a nice start to your understanding of the formulas and functions in Excel.
Thank you for learning with us. We sincerely hope you have had a good time. If you would like to learn even more about Excel, take a look around the website.
Here’s a little secret: these past lessons have been heavily inspired by the Excel Foundation Course. If you like the way they were structured you’d really enjoy the full course.
If you don’t get the full course, that’s fine too! If you enjoyed it though, please share these lessons with people that could benefit from it.