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

Try writing

=10+5

In cell A1 in the spreadsheet editor below.

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.

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).

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

Now try using the count function in cell A7 and applying it to cells A1 to A5 in the spreadsheet editor below.

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)

Try it out below, by using the COUNTA function in cell D2 to count all non blank cells in the range A1:A5.

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.

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.

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

Now try using the sum function in cell B7 and applying it to cells B2 to B5 in the spreadsheet editor below.

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.

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.

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

Fill in the cells D4, D5 and D6 using the asterisk multiplication function to calculate the revenue per phone category.

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.

  1. The total number of different products, 3 in this case, can be automatically calculated by Excel using a COUNTA function in cell B9.
  2. The total number of items sold across all product categories (11) can be found by using a SUM function in cell B10.
  3. 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.

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.


You may also like

Is there a ‘minus sum’ for spreadsheets?

7 Excel Tricks for Marketers

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"36c04":{"name":"Main Accent","parent":-1},"3a8fd":{"name":"Accent Light","parent":"36c04","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]},"original":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]}}]}__CONFIG_colors_palette__

    ​Get Good at Excel on Autopilot

    Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

    __CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"2dd0d":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"2dd0d":{"val":"rgb(45, 164, 92)","hsl":{"h":143,"s":0.57,"l":0.41}}},"gradients":[]},"original":{"colors":{"2dd0d":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
    Join List
    >