Intermediate Excel lesson

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.

About formulas

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:

=1+1

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

Spreadsheet editor
Nicely done! You now know how to use a formula in Excel! You can also subtract (using – ), multiply (using * ) and divide (using / ) in the same way.

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.

In practice

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.

Spreadsheet editor
Good job, you can be confident in your spending, because you know exactly how much you need. Using arithmetic is fun and all, but the formulas in Excel become even more powerful if you know how to integrate them with the data in the cells. We will get to that in the next lesson.

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.

Input: =10+5 gives output: 15.

Input: '=10+5 gives output: =10+5.

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:

=COUNT(STARTCELL:ENDCELL)

So for example =COUNT(A1:A5) would count how many of the cells A1 up and including A5 contain a number.

Try out count

Spreadsheet editor
Well done! You completed the first count exercise. Continue with more counting below!

Count all

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.

=COUNTA(STARTCELL:ENDCELL)
Spreadsheet editor
Nice! You can now count both numbers and general cells.

Ranges

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 practice

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.

Spreadsheet editor
Alright! Lets hope the rest of the administration is in better order 😉 The count functions are often used in combination with the sum function. We get to see more of the Sum function in the next lesson.

More about count

There is another function in the family of count functions called countblank. This is what it looks like:

=COUNTBLANK(STARTCELL:ENDCELL)

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:

=SUM(STARTCELL:ENDCELL)

Try out sum

Spreadsheet editor
Well done! Continue below for more spectacular summing.

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 A1,A2,A3,A4,A5.

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.

In practice

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.

Spreadsheet editor
Great job! Next up, you’ll combine what you’ve learned so far and put it all into practice in the final preview exercise!

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.

=A1*B1
Spreadsheet editor
Good job. You are really mastering the arithmetic functions!

Totals

Next, you shop overview would benefit from some totals, like total number of items sold and total revenue.

Spreadsheet editor
Congratulations! We hope you see how powerful combining these formulas can be.

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.

1 thought on “Intermediate Excel lesson”

Leave a Comment