© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2022
A. MurrayAdvanced Excel Formulashttps://doi.org/10.1007/978-1-4842-7125-4_1

1. Excel Formulas: A Quick Primer

Alan Murray1  
(1)
IPSWICH, UK
 

This is a short chapter to lay a solid foundation on using formulas in Excel before we get to the really exciting stuff.

Although this is an advanced Excel formulas book, I felt this chapter would be useful to plug any gaps that may exist in your knowledge. Also, it can serve as a reference point that you can revisit to help understand some of the formulas later.

This chapter will begin by explaining the anatomy of formulas and the use of the different characters, for example, !, $, >. We then explain the use of workbook and worksheet references and absolute addresses. It then concludes with the different types of errors and other alerts you experience when using Excel formulas.

Anatomy of an Excel Formula

Now, let’s begin with a question that you may be surprised to read in this book: What exactly is an Excel formula?

An Excel formula is an expression that returns a result. In Excel, this result can be a value (number, string, or Boolean), multiple values, or a range. Excel formulas always begin with =.

Formulas cannot delete, unhide, or format. They return something. However, they can be used with other Excel features to perform actions such as format or chart.

Breakdown of a Function

The terms formula and function are often used interchangeably. However, they are different. A function is a prewritten expression. It has a name and a purpose. A function can be included as part of a formula.

There are many functions in Excel. They each have their own unique characteristics. The good news – they all follow the same syntax.

The syntax of an Excel function is
=FunctionName(argument1, argument2, ...)
A function always has brackets after its name. This is true even when it has no arguments. The best example of this is probably the TODAY function:
=TODAY()

Its job is to return today’s date. It does this by using the system date and therefore requires no information from the user. However, you must still enter brackets after its name.

Most functions will prompt for arguments. An argument is information that the function needs to fulfill its purpose. Figure 1-1 shows the arguments of the SUM function.
Figure 1-1

The arguments of the SUM function

The SUM function prompts for one or multiple values. It needs values to fulfill its purpose, which is to sum.

The second argument is enclosed in square brackets: [number2]. This indicates that it is an optional argument.

Figure 1-2 shows the VLOOKUP function and its arguments. You can see that the first three arguments are mandatory, and the last argument is optional.
Figure 1-2

The arguments of the VLOOKUP function

Arguments are separated by a comma, and your position in the syntax is highlighted in bold.

Note

In regions where a comma is used as a decimal separator, a semicolon “;” is used as the function argument separator.

Table of Formula Characters

Table 1-1 is a list of the different characters used in Excel formulas. This book includes examples that demonstrate the use of all these characters.
Table 1-1

Table of formula characters

Characters

Name

Description

=

Equals

All formulas start with =.

The equal is also the logical operator for equal to.

( )

Brackets or parentheses

Used to enclose the arguments of a function.

Also, used to specify the order of calculation in a formula.

,

Separator or union operator

Primarily used to separate the arguments of a function.

Also, creates a range from multiple references. For example, =SUM(A2:A5,C2:C5).

:

Range operator

Creates a range of all cells between two references. For example, =SUM(A2:A5).

Space

Intersection operator

Creates a range from the intersection of two references. For example, =SUM(A2:A5 A3:D3) would result in =SUM(A3).

+

Addition

The operator to add values.

-

Subtract

The operator for subtracting values.

*

Multiply or asterisk

This symbol is most used as the multiply operator.

It is also a wildcard character. It is used to replace unknown characters when performing partial text matches.

/

Divide

The operator to divide values.

%

Percent

Used in a formula to represent a number as a percentage.

>

Greater than

Logical operator to test if the first value is larger than the second value.

>=

Greater than or equal to

Logical operator to test if the first value is larger than or equal to the second value.

<

Less than

Logical operator to test if the first value is less than the second value.

<=

Less than or equal to

Logical operator to test if the first value is less than or equal to the second value.

 

Not equal to

Logical operator to test if a value is not equal to another value.

{ }

Array

Curly braces around a formula indicate an array formula. They are generated automatically by Excel on pressing Ctrl + Shift + Enter. For example, {=SUM(A2:A5*B2:B5)}.

{1,2,5} or {1;2;5}

Array of values

This could be values returned by part of a formula or entered as an array of constants.

A comma is used to separate columns and a semicolon to separate rows.

“”

Double quotes

Denotes a text string.

&

Ampersand

The concatenate operator. Used to join different text strings together. For example,

="Sales total: "&SUM(A2:A5).

[ ]

Square brackets

Most used when referring to the column name of a table. For example,

=SUM(Sales[Total]).

Also used when referencing another workbook in a formula. For example,

=[Book1.xlsx]Sheet1!$C$6.

And this character is seen in the function tooltip to indicate an optional argument.

‘’

Single quotes

Used in references to surround worksheet and workbook names that contain spaces. For example,

='South Africa'!A3.

!

Exclamation mark

Follows a sheet name in a formula and separates it from the grid reference. For example, =Sheet1!A2.

$

Absolute marker

Used to make parts of a cell reference absolute. For example, $A$2 or $A2.

#

Hash or pound symbol

Used to reference a spill range. For example, =SUM(D2#).

@

Implicit intersection

Indicates that the data being referenced is on the same row.

Most used when writing formulas in tables and you reference a cell on the same row. For example,

=[@Total]*0.1.

.

Period

Used to reference information in a data type. For example,

=C3.Population.

--

Double unary

Used to convert TRUE and FALSE to a 1 and 0.

Order of Calculation

File

order-of-calculation.xlsx

With so many operators and other characters, it is important to be aware of the order that a formula calculates.

An Excel formula is evaluated in the following order:
  1. 1.

    Range operations in the order of colon, space, and then comma

     
  2. 2.

    Converting values in the order of negation (Excel needs to convert a value entered as –5 from 5 to –5) followed by converting percentages (10% to 0.1)

     
  3. 3.

    Mathematical operations (exponentiation, followed by divide and multiply, then addition and subtraction)

     
  4. 4.

    Concatenation (the joining of text strings)

     
  5. 5.

    Comparison operations (=, <, >, <=, >=, <>)

     

Brackets or parentheses can be used to change the order of calculation.

Let’s see some examples.

Order of Range Operations

We have the data shown in Figure 1-3, and we want to sum the values for hot dogs and chips in April only.
Figure 1-3

Data for the order of range operation examples

The following SUM function is used. You can see that both the union (comma) and intersection (space) operators have been deployed.
=SUM(B3:F3,B5:F5 E2:E7)

This was done to sum the values in E3 and E5 only, as those cells are at the intersection of rows 3 and 5 (hot dogs and chips) and column E (April). However, we do not get the result that we wanted.

The range operator evaluates first, followed by the intersection operation, then the union operation, as detailed in the following. Figure 1-4 illustrates the intersection operation occurring before the union.
  1. 1.

    Evaluation of the ranges: {262,297,209,135,173},{67,35,84,226,242} {166;135;203;226;160;189}

     
  2. 2.

    Intersection of the two arrays in row 5 and column E: {67,35,84,226,242} {166;135;203;226;160;189}= 226

     
  3. 3.

    Summing the union of the array in row 3 and the resulting value of the intersection: SUM({262,297,209,135,173},226) = 1302

     
Figure 1-4

Intersection operation is evaluated before the union

Brackets can be used to force the union operation to occur before the intersection, and then we get our desired result (Figure 1-5).
=SUM((B3:F3,B5:F5) E2:E7)
135 + 226 = 361
Figure 1-5

Brackets to change the calculation order

Now, it is unlikely that you will write a formula such as this. However, it does demonstrate the order of calculation in range operations. This could explain an incorrect result you get from a formula, maybe from the accidental use of spaces.

Order of Mathematical Operations

There is also an order to the mathematical operations. Division and multiplication are evaluated before addition and subtraction.

Let’s see a simple example.

The result of the following formula is 53, because multiplication (10*5) occurs before addition (3+10):
=3+10*5

So, this formula is evaluated as 10 * 5 = 50, then 50 + 3 = 53.

If we needed the addition to occur first, that operation would be surrounded in brackets to change the order of calculation. The result of the following formula is 65:
=(3+10)*5

When writing complex formulas, not only do brackets change the order of calculation, but they also make the formula easier to read and digest by everyone.

Documenting formulas better is a rarely taught and underappreciated skill. It is important, especially if you are not the only one using the spreadsheet.

The Function Wizard

You may have used the Function Wizard before, probably in your early days of using Excel formulas. I personally do not use it, but I have friends who are advanced Excel users, and they love it.

It is a tool that helps you find and, more importantly, use a function. It provides a lot more information and assistance than you get when typing a formula into a cell.

You can open the Function Wizard quickly by pressing Shift + F3 from the cell where you want to enter the formula.

It can also be opened by clicking the Insert Function button on the Formulas tab of the Ribbon or to the left of the Formula Bar (Figure 1-6). Starting a function through the menus of the function library will also trigger the Function Wizard.
Figure 1-6

How to open the Function Wizard

After starting the wizard, you can find the function you need by either searching for it or by navigating through the different function categories (Figure 1-7).
Figure 1-7

Finding the function you want to use

It really gets interesting when you get to the Function Arguments window. In Figure 1-8, the VLOOKUP function is shown with the different elements of the window detailed.
Figure 1-8

VLOOKUP function in the Function Arguments window

  1. 1.

    Description of the function you are using.

     
  2. 2.

    The function arguments. The names in bold are mandatory, and others are optional.

     
  3. 3.

    The data type expected from the argument. This part can be very useful. As you write the function, this area will show you the current value and alert you to errors.

     
  4. 4.

    Description of the active argument.

     
  5. 5.

    Formula result.

     
  6. 6.

    Link for extra help with how to use the function.

     
Figure 1-9 shows a completed VLOOKUP function. Notice the returned values next to each argument box and the formula result shown before you run the formula.
Figure 1-9

Completed formula in the Function Arguments window

Note

You can click any part of the formula in the Formula Bar to quickly switch the Function Arguments box to that area of the formula, and vice versa. This is great for navigating complex formulas in the wizard.

Cell Referencing

We have individual chapters dedicated to defined names, tables, and data type references. In this chapter, we will focus on different types of cell references in your Excel formulas.

Absolute References

File

absolute-references.xlsx

One of the biggest obstacles to learning formulas when people are just getting started is the understanding of an absolute reference.

Absolute references can be found in many places within a spreadsheet. They are often input by Excel itself when you select a range from within a feature such as Data Validation or defined names.

The use of absolute references is extremely important to understand. Let’s look at when, why, and how to use absolute references.

Relative References Explained

In Figure 1-10, the formula =B4*E2 has been used in cell C4.
Figure 1-10

Formula with relative references

This multiplies the first value by the input cell in E2. This works great. But when the formula is filled down to range C5:C8, zeroes are returned as the result for the other values (Figure 1-11).
Figure 1-11

Zero is returned as the result for the other values

The formula in cell C4 is using relative cell references. So, when the formula is filled down, both references change in conjunction to the formula.

In Figure 1-12, you can see the formula in cell C5 refers to the cells one cell below the original references.
Figure 1-12

Both cell references change

Although the original formula was =B4*E2, what it is really calculating is the cell one column to the left multiplied by the cell two rows up and two columns to the right (Figure 1-13).
Figure 1-13

Relative references explained

This is no different to how we may provide directions to someone. For example, take the second left and in 50 meters take the next right. It is all relative to the starting position or starting cell in Excel’s case.

This behavior is why they are called relative references.

The formula displays the cell address from the grid, making it easier to read and write. Although behind the scenes, this is not what is being used.

Making a Reference Absolute

In this example, we need the reference to cell E2 to be an absolute address instead of the relative reference. This will stop it from changing when the formula is filled down.

To make a reference absolute, dollar signs are added before the column letter and row number of the reference, that is, $E$2. You can do this by simply typing the dollar signs or with the F4 key on your keyboard.

Figure 1-14 shows the reference to the input cell as an absolute reference. It no longer changes when the formula is filled, therefore returning the correct results.
Figure 1-14

Absolute reference in an Excel formula

This reference is fully absolute and will not change if the formula is filled to the left, right, up, or down.

In this example, we are filling the formula down. Because of this, we only really need to make the row absolute. This cell reference is sufficient for the formula = E$2.

Note

Each time you press the F4 key, it cycles through the different references available – $E$2, E$2, $E2, and E2.

Despite that being the case, most users will make a reference fully absolute unless there is a requirement otherwise.

Mixed References

A mixed reference is the term used when you make only the column or the row of a cell reference absolute.

Let’s see an example where these mixed references come in helpful. In Figure 1-15, we want to complete the grid with the result of the times tables from 2 to 10.
Figure 1-15

Calculate times tables in Excel

In cell B2, the formula =A2*B1 is used. But this does not work when the formula is filled down to B10 and across to column J (Figure 1-16).
Figure 1-16

Relative reference goes horribly wrong

Because we are filling the formula in two directions, we need to fix (or make absolute) the column or the row of each reference in the formula.

The formula =$A2*B$1 is used in Figure 1-17 and works perfectly.
Figure 1-17

Mixed references used to ensure correct results

In the first part of the formula, column A is made absolute to keep it on that column when the formula is filled over to column J. Yet, we need it to look at the numbers in the other rows of column A.

In the second part of the formula, row 1 is made absolute to stop it changing when the formula is filled down the row 10. Yet, we need the other numbers along row 1 to be used.

Sheet References

Files

worksheet-references.xlsx

When you reference data on other sheets in a formula, Excel writes the references for you. This is brilliant!

However, it is important to be fluent in the syntax used to reference data on other sheets, especially if you want to be an advanced Excel formula user.

If you were to reference cell A1 of a sheet named [Canada], the syntax would be
Canada!A1

The exclamation mark (!) always follows the sheet name.

Single quotations are used to enclose the sheet name if it includes spaces. So, if you referenced cell A1 of a sheet named [South Africa], the syntax would be
'South Africa'!A1
Note

Cross-sheet references can be simplified by using defined names and tables. Both these topics are covered later in the book.

Let’s see two examples of the SUM function being used to sum values from different worksheets. We will be using the [worksheet-references.xlsx] workbook for these examples.

The workbook contains attendance data on four different worksheets (Figure 1-18). These are [France], [South Africa], [Canada], and [Germany]. On each sheet, the attendance values are in range B2:B7.
Figure 1-18

Attendance data on four different worksheets

The following formula is used in cell B3 of the [Report] sheet (Figure 1-19):
=SUM(France!B2:B7,'South Africa'!B2:B7,Canada!B2:B7,Germany!B2:B7)
It sums the values from range B2:B7 on each sheet. You can see the syntax described earlier, including the single quotes around the [South Africa] sheet.
Figure 1-19

Sum ranges from multiple worksheets

In this example, the comma is used to separate each range used by the SUM function.

The ranges are consistent and occur on consecutive worksheets. Because of this, the formula could be simplified.

The following formula sums the same values but uses the range operator to sum the values in B2:B7 from the [France] sheet to the [Germany] sheet (Figure 1-20). The [France] and [Germany] sheets are the first and last sheets in the range (Figure 1-18).
=SUM(France:Germany!B2:B7)
Figure 1-20

Sum values from multiple sheets with the range operator

This formula is more concise than the previous example. It also offers the advantage that any new sheet, if inserted in the range (between [France] and [Germany]), would also be included in the sum.

In this example, using sheets named [France] and [Germany] is quite arbitrary. So, a neat technique to deploy is to create “start” and “end” sheets that function as book ends for the sum formula. These sheets would be empty and have no purpose aside from containing the sheets involved in the sum range.

Protecting a workbook to prevent the accidental moving of sheets is also a good idea. The technique of summing a range of sheets is great, but it does have vulnerabilities also.

Workbook References

Files

worksheet-references.xlsx and France.xlsx

Just like with sheet references, Excel will write the reference for you when you select another workbook from a formula. However, it is important to be familiar with workbook references in Excel formulas.

If you referenced cell A1 on a sheet named [Report] of a workbook named [Sales.xlsx], the syntax would look like this:
='[Sales.xlsx]Report'!$A$1
If the [Sales.xlsx] workbook is closed, then the formula syntax would change to show the full file path:
'C:UsersadminGoogle DrivePerformance[Sales.xlsx]Report'!$A$1

Let’s see an example of a workbook reference in a formula. We will write a formula on the [worksheet-references.xlsx] workbook that references the [France] workbook.

The following formula is entered in cell D3 of the [Report] sheet (Figure 1-21). It calculates the difference between the sum of values on the [France] sheet of the current workbook and the sum of values on the [Last Year] sheet of the [France.xlsx] workbook.
=sum(France!$B$2:$B$7)-sum('[france.xlsx]Last Year'!$B$2:$B$7)
Figure 1-21

Formula that sums values from another workbook

Note

Many functions in Excel are able to retrieve data from a closed Excel workbook, such as SUM, AVERAGE, and INDEX, while others cannot, such as SUMIFS and OFFSET.

When you open a file that contains external references to other workbooks, it will probably greet you with a security message (Figure 1-22).

External links are disabled, by default, from updating automatically. Click Enable Content to update the links and see the changes in the current file.
Figure 1-22

Security warning to update external links

You can change these settings by clicking File ➤ Options ➤ Trust Center ➤ Trust Center Settings ➤ External Content (Figure 1-23).
Figure 1-23

Trust Center settings to disable or enable workbook links

The default is to prompt the user for the update, which is why you receive the security message. This can be changed to enable or disable these updates.

Figure 1-24 shows the Edit Links window, which is accessed by clicking Data ➤ Edit Links. In this window, you can view, update, and edit external links in a workbook.

The Edit Links window lists all external links in the workbook. You can see the location of the external files, and there are multiple controls on the right to update, change, and break the links.

When breaking a link to an external source, the formula is converted to the calculated value at the time the link was broken.
Figure 1-24

Edit links in a workbook

Calculating Percentages

File

percentages.xlsx

Calculating percentages is very commonplace in Excel, so I thought it would be good to include some quick examples.

Percentage of a Value

Let’s start by finding the percentage of a value. The following formula returns 3% of the value in cell B3 (Figure 1-25):
=B3*3%
Figure 1-25

Find 3% of the value in cell B3

This is probably the most straightforward method. You could also use =B3*0.03.

It may also be the case that the percentage is a variable. In this scenario, we could reference a cell that contains that value (Figure 1-26).
=B3*C3
Figure 1-26

Variable percentage of a value

Percentage of a Total

The calculation for percentage of a total is value/total. You will then need to format the result as a percentage.

The following formula has been entered into cell D3 and then formatted as a percentage to one decimal place (Figure 1-27):
=B3/C3
Figure 1-27

Calculate percentage of total and then format as required

Let’s look at another example. This example is a nice reminder of the use of absolute references covered previously.

We want to return the percentage of total for multiple values. In cell G5, the following formula has been used and filled to range G6:G9 (Figure 1-28):
=F5/$G$2
Cell G2 is absolute to prevent it from changing when the formula is filled down.
Figure 1-28

Percentage of total for multiple values

Increase a Value by a Percentage

The following formula increases the value in cell B3 by the percentage in cell C3. You can see brackets being used to ensure the second operation occurs first (Figure 1-29).
=B3*(1+C3)
Figure 1-29

Increase a value by a given percentage

Decrease a Value by a Percentage

A similar technique can be used to decrease a value by a given percentage. The following formula is used to decrease the value in cell B3 by the percentage in cell C3 (Figure 1-30):
=B3*(1-C3)
Figure 1-30

Decrease a value by a given percentage

Calculate Percentage Change

For the final examples, let’s calculate the percentage change between two values. The following formula is entered in cell E3 to return the percentage change from the value last month to this month. Cell B3 contains the old value, and cell C3 contains the new value (Figure 1-31).
=(C3-B3)/B3
Figure 1-31

Formula to calculate percentage change

The result is formatted as a percentage to one decimal place.

So, the value in cell C3 was a 13.8% increase on the value in cell B3.

Here is another example that calculates the percentage change monthly (Figure 1-32). The following formula is entered into cell C8 and filled across to cells D8:G8:
=(C7-B7)/B7

This example is a nice demonstration of the power of relative references. The formula references the sales of the current column and the sales of the previous column.

Custom formatting has been applied to range B8:G8 to format the negative values in red.
Figure 1-32

Percentage change monthly

When Formulas Go Wrong

Excel formulas can and will go wrong. This can happen for many reasons. Let’s explore the different types of errors you may encounter and some of the common reasons behind formulas that stop calculating.

Formula Errors

Formulas often return error messages when their expectations are not met. For example, multiplying a value by another cell that contains text will not work, so the #VALUE! error is returned.

Table 1-2 lists the different error messages you may come across when using Excel formulas. A description for each error is provided.
Table 1-2

Formula error messages and description

Error Message

Description

#VALUE!

The formula is using a data type in the wrong way. For example, you are performing a mathematical operation on a cell that contains text.

Check the type of data that the function argument expects. Then check that the data type in the cell you referenced, or that you typed, matches the expected data type.

#REF!

A reference used by the formula is invalid.

This is often caused when a reference that is being used by a formula is removed. For example, when a sheet that is being referenced in a formula is deleted.

Another cause can be if the reference is out of range. For example, if a formula that uses a relative reference to seven cells above is copied into cell G3. G3 does not have seven cells above it, so the #REF! error is returned.

#N/A!

The familiar error commonly associated with lookup formulas.

It is returned when the lookup formula cannot match the value you are looking for. This could be because the data types do not match, a typing mistake, or maybe the lookup value cell is empty.

#NAME!

The formula does not recognize a name used in the formula.

This could be the name of the function, a defined name, table, or some other name.

Other causes can be a missing bracket after a function name or missing double quotes around a text string.

#DIV/0!

Your formula has attempted to divide by zero or a blank cell.

#NULL!

A formula returns this error when the intersection operator (the space) is used incorrectly. If the ranges do not intersect, this error is returned.

This is often caused when a space is accidentally used in a formula. Typically, a typing mistake when a space is entered instead of a comma or a colon.

#NUM!

This error is returned when the formula tries to use invalid numeric values.

For example, entering symbols such as dollar signs when entering a numeric value, i.e., $1000. Symbols such as dollar signs have a specific role within formulas, so should not be used as part of an entered value.

#SPILL!

This error occurs when a formula cannot spill its results. This is often due to another cell value blocking the spill range.

A blue border is shown to visualize the spill range and help diagnose this formula error.

To fix this, the value that is interfering with the spill range must be moved or deleted.

#CALC!

This error is returned when there is a calculation error in the array of a formula. For example, if an empty array is returned.

This is often seen, though not uniquely found, with the FILTER function, when the conditions provided cannot be evaluated properly, typically when no results are returned by FILTER. The [if_empty] argument is provided to prevent the error in this scenario.

#FIELD!

This formula error is related to the rich data types in Excel.

If the formula cannot access the required field from a data type, this error is returned.

#BLOCKED!

The formula cannot access a required resource.

The solution depends on the resource you cannot access and why. Potential solutions include checking your Trust Center settings, privacy settings, or the connection settings to external workbooks.

#BUSY!

The formula is waiting for a required resource that’s taking a long time to access.

This is typically seen when accessing online data using linked data types, rendering images, or getting data from an external Excel workbook.

Automatic Error Checking

Excel performs specific checks in the background and will display a green triangle in the top-left corner of the cell if it has a query (Figure 1-33).
Figure 1-33

Green triangle warning

This does not mean that there is an error with your formula, but that Excel is querying something about it. Excel has a list of “error checking” rules, and the formula has failed one or more of these checks.

When you select the cell, a warning icon appears. Click the icon to see further information about the warning and a few options on dealing with it (Figure 1-34).

In this example, the formula is wrong. Cell D3 has been omitted from the SUM function by mistake.
Figure 1-34

Access information and options regarding the warning

This can be fixed by editing the formula to include cell D3 in the range or by clicking the Update Formula to Include Cells option.

The options you see in this list are dependent upon the rule that the formula failed to clear.

You can turn off this background error checking or even turn off specific error checking rules. To do so, click File ➤ Options ➤ Formulas and change the required settings at the bottom of the window (Figure 1-35).
Figure 1-35

Change error checking settings in the Excel options window

Excel Formula Is Not Calculating

There are a few reasons to explain why a formula has stopped updating. Let’s look at a few.

Formula Calculations Are Set to Manual

The most likely reason that your formulas do not calculate automatically is that the calculation mode has been set to manual.

The calculation mode is set by the first workbook that is opened in a session. So, another workbook may have set the mode of the current workbook to manual calculation. This could also have been set by a macro.

You can calculate all the formulas in the workbook, while in manual calculation mode, by clicking Formulas ➤ Calculate Now or by pressing F9. To calculate all the formulas on the current sheet only, click Formulas ➤ Calculate Sheet.

Change the formula calculation to automatic by clicking Formulas ➤ Calculation Options ➤ Automatic (Figure 1-36).
Figure 1-36

Change formula calculation to automatic

Show Formulas Is Switched On

When the Show Formulas feature is switched on, it is quite noticeable because the columns get wider to accommodate the formula text.

It is, however, another reason that stops your formulas from calculating (Figure 1-37). This feature can be switched on accidentally with the Ctrl + ‘ (back quote symbol) keyboard shortcut.

To fix this issue, switch it off by pressing Ctrl + ‘ or clicking Formulas ➤ Show Formulas.
Figure 1-37

Show Formulas feature is switched on

Formula Is Stored As Text

Excel formulas can be inadvertently stored as text by typing a character before the equals of the formula.

In Figure 1-38, a space has been entered before the SUM function. The formula therefore does not calculate and is instead stored as text. Remove the character that is causing this issue (space in this example) to fix this issue.
Figure 1-38

Accidental space before the formula

Cell Formatted As Text

If a cell is formatted as text, some formulas subsequently used in that cell will not calculate (Figure 1-39).

To fix the issue, change it to the required format, for example, general, number, accounting. Then edit the cell and press Enter.
Figure 1-39

Cell formatted as text before inputting a formula

It is interesting that if the cell is formatted as text after the formula is entered, it continues to calculate.

Summary

This chapter provided a comprehensive primer on Excel formulas and their parts. It covered the important building blocks of formulas and functions including

  • The different characters and the role that they play within formulas

  • The order that a formula evaluates

  • And different methods to reference other cells, worksheets, and workbooks, and how to work with them

We also covered the important task of identifying and handling errors and other issues that will occur with formulas.

In the next chapter, we will look at the logical functions in Excel including IF, SWITCH, AND, IFERROR, and more. These functions are incredibly useful and provide a logical (sorry! amp;#x1F609) next step in our formula journey.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.223.33.157