Discover Excel Formulas

Basic Mathematical Operations

In this lesson. we’re going to look at some basic mathematical operations of adding, subtracting,
multiplying and dividing in Excel.

CALCULATIONS IN EXCEL

Let’s take a look at this simple spreadsheet (download available in Course Files.)

In cell E3, we simply want to add the values from C3 and D3 together.

ADDING NUMBERS

Every formula in Excel begins with an equals sign (=). This indicates to Excel that a formula is
coming.

We will begin by clicking on the first value, which is the number 50.

Excel automatically writes the reference to cell C3, while also indicating both the cell and the

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Basic Mathematical Operations

reference with blue colour.

We can then put in the mathematical operator, which is a plus sign (+) in this case.

We can then press Enter to run this calculation. Now we can see the result in Cell E3.

Tip: It’s always a good habit to press Enter or Tab on completion of formulas instead of clicking with
your mouse because that will get Excel referencing somewhere else.

If we click on the cell E3, we can see the formula in the Formula bar.

If the value in D3 was going to change from 20 to 40, that recalculates in cell E3 to 90.

SUBTRACTING VALUES

Again, we would start with an equals sign,

…and we would click on the cell that contains the number we want,

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Basic Mathematical Operations

Then we would add the mathematical symbol, which in this case is the minus sign (-),

and then we can click on the other number.

Or, you could just type the numbers into the formula. You don’t always have to reference a cell.

MULTIPLYING VALUES

We would be repeating the same process, but this time, the symbol is the asterisk (*) as we are
multiplying two values.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

Powered by TCPDF ([Link])

 Discover Excel Formulas
Multiple Operations - BODMAS

In this lesson, we’re going to perform multiple operations in our Excel formulas.

Performing Multiple Operations

First, let’s say we want to run a formula using the values in columns B, C, and D.

Let’s type ‘= (equals)’ to begin in Cell F4 (Green), and add the values from columns B and C.

Next, we’re going to multiply the result by the value in cell D4.

Note that Excel always performs multiplication first before addition, unless the addition is
enclosed with brackets.

So when we type in =B4+C4*D4, it will multiply first, so 3 * 2 = 6, and then 6 + 5 = 11.

To get the adding to happen first, we can enclose it with brackets or parenthesis: (B4+C4)*D4 = 7 *
3 = 21. This is referred to as the rule of BODMAS.

What is BODMAS?

BODMAS is a useful acronym that tells you the order in which you solve mathematical problems. It
stands for:

Bracket (brackets always come first)

Order of operations (numbers involving powers or square roots)
Division
Multiplication
Addition

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Multiple Operations - BODMAS

Subtraction

Tip: If you’re not sure, just put the brackets in to tell Excel what order it goes in.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

Powered by TCPDF ([Link])

 Discover Excel Formulas
Calculating Percentages

In this lesson, we’re going to be looking at calculating percentages, which is useful when calculating
discount, tax, commission, or percentage difference.

Calculating Percentages

Let’s begin calculating 15% of the value in cell B4. To do this, you can either type in 15% directly
into the formula:

Or you can write it in a decimal form (0.15):

The result for both methods is equal to 30.

Increasing/Decreasing By Percentage

In the next example, we want to increase the value by 15%. To do this, we need to first find out what
15% of the value is (= 200 * 15%), and then add it to the original value (200).

We know that multiply will happen first, because of the rule of BODMAS. Hence, the calculation goes
like this:

= B7 + (B7 * 15%)

= 200 + (200 * 15%)

= 200 + 30 = 230

Alternatively, we can add 1 to the percentage value and then multiplying the result (1 + 0.15 =
1.15) with the original value (1.15 * 200).

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Calculating Percentages

Note that by using brackets, we’re adding 1 to 15% first, before multiplying it with B7.

For decreasing by percentage, we can follow the same rule but subtract instead of adding.

or

Converting Value to Percentage

On the other hand, we might want to know what % is a value of another value. Let’s say, we want to
know what 70 is of 200, as a percentage.

To do this, we can divide 70 by 200, and it gives us 35%.

If the result doesn’t appear as a percentage immediately (it may come out as 0.35), select the cell
and click on the percentage button up on the Home tab.

Calculating Percentage Increase/Decrease

Let’s say we have two values: 680 and 600.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Calculating Percentages

Obviously, 680 is bigger than 600, but exactly how much % is it an increase of 600?

To calculate this, we’re going to divide 680 by 600 first, and then subtract 1 from the result.

The result is 13%. In other words, 680 is 13% improvement on 600.

If we change 680 to 400, it tells me that it’s 33% decrease.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

Powered by TCPDF ([Link])

 Discover Excel Formulas
Using Absolute References

Calculating Commission

In this example, we have some salespeople and their sales, and we need to calculate their
commission.

The commission rate is in cell F3, so we want to know what is 3.5% of these values. We’ll simply
multiply the sales value by the commission rate.

The result is 51.

Now, we can copy this formula by grabbing the little fill handle and dragging it to the bottom.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Using Absolute References

Note that the values are empty right now because the sales values are multiplied by empty cells.
Since we’re copying the formula down in a vertical manner, the reference cells also follow us.

To fix the reference cell for the commission rate, we need to make an absolute reference.

Absolute Reference

We can do so by putting in a dollar sign ($) just before the 3 to fix the row. (You can put another
dollar sign before the F if you were to fix the column as well.)

Alternatively, you can press F4 to make it an absolute reference.

Now if we click on the fill handle and copy it to the bottom, we can see that it is displaying the
commission values correctly.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Using Absolute References

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

Powered by TCPDF ([Link])

 Discover Excel Formulas
Using Functions in Excel

In this lesson, we’re going to be looking at the five most commonly used functions in Excel.

Sum Function

In this example, we have some expenses down column B.

How can we calculate the sum of our expenses? First, we need to select the values, including the
empty cell underneath where the answer will be placed.

And click on the AutoSum button on the Home tab.

This automatically adds everything up, and the total value will appear after the selected cells.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Using Functions in Excel

In the formula bar, you can see the sum function, adding up the values from B4 to B12.

Note that every function in Excel has brackets after the function name. Within those brackets are
where we specify the values or the range of our reference cells.

You can also change the order and click on the cell where the answer will go first, then click on the
AutoSum button.

In that case, Excel asks us first if the range is correct, and we can confirm that by pressing Enter.

Max, Min, and Average

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Using Functions in Excel

For the next example, we’re going to look at the scores that we have on Columns H and I.

To calculate the average value, we can click on the drop-down arrow next to AutoSum, and click on
Average.

We can then select the values that we’re after, and press Enter.

As you can see, the average value has 4 decimal places:

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Using Functions in Excel

We can click on the Decrease decimal button to remove all the decimals.

Now the average score is rounded up to 58.

The Max and Min functions can also be found in the AutoSum drop-down list.

Counting Texts

In this example, we have some Yes/No responses to some kind of request.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Using Functions in Excel

We’re going to find out how many people responded to this request, by clicking on More Functions
feature in the AutoSum dropdown.

In the Insert Function window, we can select the Statistical category,

or search for a function called “count text”.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Using Functions in Excel

We then want to select the COUNTA function, which counts the number of cells in a range that are
not empty.

If we click OK, it opens up the Function Arguments window (The information we are putting within
the brackets are called arguments).

(We can easily access the same window through the fx button next to the Formula bar.)

We’re going to highlight the range just as we’ve been doing, and click OK. Now we have the result as

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Using Functions in Excel

6.

Tip: Instead of inserting functions using the method above, you can just type in function names and
read the descriptions of suggested functions, by hovering your mouse cursor over them.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

Powered by TCPDF ([Link])

 Discover Excel Formulas
Reference Other Sheets within Formulas

In this lesson, we’re going to begin to use our formulas with data that is on other worksheets, or
even other workbooks.

Referencing Values (1): within formulas

On our Overview page, we have three sales for London. Southampton and Cambridge.

At the bottom, we have different sheets that are in the same structure but have different values.

We would like to sum the values from B3 to B9 on the London sheet and bring it into cell C2 of our
overview sheet.

First, we’re going to type in “=SUM()” in cell C2…

… and then we’re going to click on the London sheet.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Reference Other Sheets within Formulas

In the formula bar above, you’ll see it writes London and an exclamation mark (!). This means it is
referencing the sheet called “London”.

Now we can select the cell range to use as a reference.

Let’s press Enter to confirm it. (Tip: Do not click back on the other sheet before pressing Enter,
because it will change the formula.)

Now we can see the total value in our overview page.

We can then repeat the same process and change “London” to “Southampton”, or “Cambridge”.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Reference Other Sheets within Formulas

Referencing Values (2): from different worksheets

Now we’re going to sum them all from the three sheets. Let’s begin by typing in the SUM function…

and we’ll click on the other sheet and select the range,

and then we’re going to hold down the shift key, and then click on the Cambridge sheet.

As we press Enter, we can see the result in cell C5.

Within the formula bar, there is a colon (:) between London and Cambridge.

The colon is indicating that the reference range is including whatever is in the middle (in this case,

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Reference Other Sheets within Formulas

Southampton).

Another way you could do this is to type a comma (,) after selecting a range from one sheet and
moving onto another sheet.

Referencing Values (3): from a different workbook

Lastly, we would like to compare the values from another workbook, which contains last month’s
data.

To do that, we’re going to reference the current month’s expenses total, and subtract that in the
other workbook.

By clicking on the Switch Windows button on the View tab, we can see other workbooks that are
open right now.

When we click on the last month’s expenses total, [the name of the [Link]] … appears in
our formula bar. The path may get lengthier depending on where the workbook is located.

When we hit Enter, it brings us back to our overview sheet. The current month’s expense total is 690
lesser than the last month.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

Powered by TCPDF ([Link])

 Discover Excel Formulas
Date Formulas

In this lesson, we’re going to look at using formulas with date values.

Calculating Remaining Dates

In our example sheet, we have some dates in column A, and we’d like to find out how many days we
have until that date.

Let’s begin by referencing the date in cell A2.

And let’s subtract today’s date from the value, using a function called TODAY(). This function will
return the current date from your computer’s clock.

However, the result may not be correct if it is formatted as a date. To fix this problem, you can
change its format to General using the drop-down on the Home Tab.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Date Formulas

Calculating Remaining Working Days

Now in column C, we’re going to find out remaining working days, that is excluding weekends and
public holidays.

To do this, we’re going to be using a function called NETWORKDAYS().

We can see it prompts us for a start date, an end date, and then a list of holidays.

We’re going to set Today() as the start date, and refer to our cell A2 as the end date.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Date Formulas

Then we’re going to click on the sheet at the bottom called Non-Working Days, and select the
dates that were already written.

Note that we need to put dollar signs ($) to fix our range as absolute references, otherwise the
range will move as we duplicate the formula. The shortcut is pressing F4 on your keyboard.

Calculating International Working Days

Similarly, we can calculate international working days using a function called

[Link]().

Notice that here is an extra input required called [weekend]. This lets us specify the weekend range
to exclude.

Here in our formula, we’re going to type in two commas (,,) to get this list of suggestion from Excel.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Date Formulas

As we click on Sunday only, we will get the number 11 in our formula. And then as we press Enter,
we can see that it affects our results.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

Powered by TCPDF ([Link])

 Discover Excel Formulas
Named Ranges in Formulas

In this lesson, we’re going to look at the ability to create named ranges in Excel.

Let’s take a look at this date formulas workbook from the previous lesson.

Creating Named Ranges

We have this reference to the sheet “Non Working Days”, and then we have the absolute reference
to the range from “A2 to A4”.

An alternative approach could be to name the range of cells, instead of referencing it as “‘Non
Working Days’!A2:A4”.

First, we’ll select the range of cells to name:

Then in the name box, we’re going to type in some name, e.g. “holidays”:

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Named Ranges in Formulas

Now, we can replace the ‘Non Working Days’!A2:A4 part of the formula with holidays.

Accessing Named Ranges

To access named ranges, we could click on the Formulas tab…

and click on Use in Formula:

Then the drop-down will give us access to all of our named ranges.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Named Ranges in Formulas

Navigating To Named Ranges

When navigating to a named range, we can click on the little arrow in the
name box.

This will move our screen to where the range is located as we click on it.

Editing or Removing Named Ranges

When we need to edit or remove a named range, we can click on a button called Name Manager in
the Formulas tab.

This will open a Name Manager window, where we can create, edit, or delete named ranges.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

 Discover Excel Formulas
Named Ranges in Formulas

If we click on the Edit button, we can change the name of the range and the reference cells.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

Powered by TCPDF ([Link])

 Discover Excel Formulas
Open Challenge

Great job! You’ve reached the end of the video part of this course ��

Let’s now do some hands-on exercises to practice what we have learned!

1. Create a new Excel workbook and save it as “Excel formulas”

2. Rename “Sheet 1” to operations
3. Name cell A1 “Addition”
4. In column C, cell C2 write the following formula =sum(a+B). Press enter.
5. Fill this formula down to row 10
6. In columns A and B fill with whole numbers
7. In Column C, cell 11 write the following formula =sum( C1:C10). Press enter.
8. Name cell A12 “Subtraction”
9. In cell C13, write a formula that will subtract column B from column C
10. Fill this formula for 5 rows
11. Fill these rows with numbers
12. Format column C answers to two decimal places
13. Name cell E1 “Division”
14. In column G cell G2, write a formula that will divide column F by column E
15. Fill 10 rows
16. Fill these rows with numbers
17. Format column G answers to one decimal place
18. Name cell E12 “Multiplication”
19. In Cell G13, write a formula that multiplies column E by column F
20. Fill the formula for 5 cells
21. In column I, write a formula that averages the answers in column C
22. Write a formula to find the mode of the answers in column G
23. Write a formula to find the maximum number of answers in column G
24. Write a formula to find the minimum number in column C
25. Write a formula that uses all mathematical operations (don’t forget about BOMDAS)
26. Create another sheet and rename it “Percentages”
27. Name cell A1 “Percentages”
28. In column A2, write a formula that finds the percentage of another number
29. Fill this for 5 rows
30. In cell E1 name the cell “Increase Percent”
31. Write a formula that increases a number by 20%
32. Fill for 5 rows
33. In cell I1, name the cell “Decrease Percent”
34. In I2, write a formula that decreases a number by 15%
35. In row 7, practice writing formulas that find the percentage of a number, increases a number
by a percentage, and decreases a number by percentage.
36. Create another sheet and rename it “Other Formulas”
37. Name cell A1 “Counting”
38. Write a formula in cell A10 that counts text in cells A2 to A9
39. Fill cells A2 to A9 with text for the formula to count.
40. Rename cell C2 “Referencing Values”
41. Write a formula that references the operations sheet cells in cells C2 to C5
42. Rename cell E1 “Dates”
43. Fill cells E2 to E5 with dates into the future
44. In cell F2, write a formula that calculates how many days until that day.
45. In cell G2, write a formula that calculates how many school days until the date in the future.

© 2024 Zenva Pty Ltd. All rights reserved. [Link]

Powered by TCPDF ([Link])