Must-Know Legacy Excel Functions

Functions are a very important part of Excel and make many of the reports and analyses carried out in Excel possible. The ability to have reports that recompute the very instant the underlying data changes is what makes Excel a preferred choice by company executives and business managers who don’t want complex IT-driven applications. The ease with which reports can be created, edited, and shared is why Excel is widely used by all the functional departments of most companies. Functions in Excel are the biggest enablers of that ease of report creation.

Functions are what make it possible to carry out quick computations and complex analyses in Excel with a few keystrokes. There are over 460 Excel functions and Microsoft keeps adding new ones almost yearly. From Excel 2021 and above, Excel completely changed how functions work within Excel and gave rise to the classification of prior versions of Excel as Legacy Excel. Thus, Excel 2021 and above...

Math and statistical functions

The most used functions in Excel are math and statistical functions such as SUM, COUNT, AVERAGE, MIN, MAX, SUMIFS, COUNTIFS, AVERAGEIFS, and ABS. They allow people to grab quick insights from a range of numeric data. SUM is, perhaps, the most used Excel function as it allows people to quickly see the sum of a range of cells.

In this section, we will cover the most well-known math functions that a data analyst and dashboard creator must be well versed in. You may perceive them as very simple functions that do not require much explanation, but their importance warrants we cover them. Also, when combined with dynamic array functions, which we will learn about in Chapter 7, Dynamic Array Functions and Lambda Functions, they become very formidable tools.

Every data analyst using Excel should be proficient in using the following math and statistical functions:

• SUM
• SUMIFS
• COUNT
• COUNTIFS
• MIN
• MAX
• AVERAGE

We will explore...

Logical functions

In Legacy Excel, logical functions are functions that carry out logical comparisons or return a logical value (TRUE or FALSE). You can find a full list of the logical functions that are available, as well as their documentation, at https://support.microsoft.com/en-us/office/logical-functions-reference-e093c192-278b-43f6-8c3a-b6ce299931f5. They make a lot of condition-based calculations possible in Excel.

The logical functions that we will cover in this section are as follows:

• IF
• IFS
• IFERROR
• SWITCH
• OR
• AND

The first of these logical functions we will cover is IF.

IF

IF is an Excel function that carries out a specified logical check and allows us to have different outputs for when the check equals TRUE and for when the check equals FALSE. It is one of the most important functions in Excel as it allows us to wrap other functions in a condition-based implementation. Think of any computation you need to do but you must factor...

Text manipulation functions

Most datasets are a mix of text fields and numeric fields. A good data analyst should be adept at manipulating text fields. In this section, we will cover the major text manipulation functions you should be proficient at using:

• LEFT
• MID
• RIGHT
• SEARCH
• SUBSTITUTE
• TEXT
• LEN

The first of these text manipulation functions that we will cover is LEFT.

LEFT

LEFT is an Excel function that extracts the first set of characters in a provided value. Its syntax is LEFT(text,number_of_characters). It is often useful for extracting a categorizing substring from a text field. The following screenshot shows an example involving extracting a country code from an asset tag:

Figure 6.15 – LEFT function example

The formula extract is =LEFT(A3,2). It extracts the first two characters from the selected cell.

MID

MID is an Excel formula for extracting characters from a provided value starting from...

Date manipulation functions

Most business datasets have date fields because datestamps and timestamps are very important when recording activities for organizations. This makes it important for a data analyst and dashboard creator to be very proficient in manipulating dates in Excel.

The must-know date manipulation functions in Excel are as follows:

• TODAY
• DATE
• YEAR
• MONTH
• DAY
• EDATE
• EOMONTH
• WEEKNUM

The first of these date manipulation functions we will cover is TODAY.

TODAY

TODAY is an Excel function that returns the current date at all times (it uses the computer’s date). It takes no arguments, so its syntax is TODAY(). The following screenshot shows using TODAY to do a countdown of the days to a planned activity:

Figure 6.23 – TODAY function example

With TODAY, you can achieve a lot of dynamic date computations and countdowns. Next, we will cover another date manipulation function.

DATE...

Lookup and reference functions

Lookup functions in Excel are mostly for looking up values in a specified range and returning a related value. They are very useful for working between two tables or finding content related to a given value while reference functions are for manipulating cell references.

In this section, we will cover the following important lookup and reference functions in Legacy Excel:

• VLOOKUP
• HLOOKUP
• INDEX
• MATCH
• OFFSET
• INDIRECT
• CHOOSE

The first of these lookup and reference functions that we will cover is VLOOKUP.

VLOOKUP

VLOOKUP is an Excel function for looking up a value in a table and returning a related value in one of the table’s columns. Its syntax is VLOOKUP(lookup_value,table,column_value_to_return,exact_or_approximate), where the value to look up is the first input, followed by the table to search in, then the column position of the value to return and, finally, whether to do an exact match (FALSE) or...

Summary

Functions are the bedrock of reports and dashboard creation in Excel. In this chapter, we covered the must-know Excel functions in Excel versions up until Excel 2019. This has laid an important foundation that we will build on in Chapter 12, Best Practices for Real-World Dashboard Building.

You are now knowledgeable about important math functions, logical functions, text manipulation functions, and lookup functions in Legacy Excel. In the next chapter, we will cover a new class of functions that are available in Excel 365: dynamic array functions and lambda functions. They make what used to require macros and complex processes possible in Excel. You will find them very interesting and useful in creating a modern interactive dashboard.