Beginner to pro: use Excel like a management consultant

An overview of the section content

Shortcuts to effectively navigate your database

Shortcuts to effectively navigate your workbook

Select, add, delete, move, hide, group workbook columns and rows

Use “Center across selection” instead of “Merge Cells”

Use VLOOKUP to link two databases and tips to make VLOOKUP more flexible

Combine INDEX and MATCH to link two databases with 2-dimensional flexibility

Use IFERROR to define the outcome of a formula returning an error

Apply what you learned on VLOOKUP and INDEX/MATCH

Double check your exercise results

Quickly sum all values in a column

Keep top rows/columns always visible while scrolling through the worksheet

Most useful shortcuts accessible with the Function keys

Four ways to count or list unique values

Quickly count or list unique values with dynamic arrays (Excel 365 only)

Test what you learned in this section

Double check your test results

An overview of the section content

Introductory lesson on the basics of Pivot Tables

Select the Pivot Table form that works best for you: compact, outline, tabular

Sort, expand/collapse, and filter Pivot Table fields

Choose the Pivot Table output using “Summarize data by” and “Show values as” options

Use the Group option to create new Pivot Table fields without editing the source database

Use the Calculated Field option to create new Pivot Table fields without editing the source database

Learn the pitfalls of dealing with % changes in Pivot Tables and how to address them

Short digression on problem solving and how to ensure you calculate the right result

Use data from Pivot Tables in your analyses: how to and what to be careful with

Use GETPIVOTDATA to lock your Pivot Table references

Group numeric values in the Rows or Columns areas into buckets

Retrieve a deleted source database from a Pivot Table

Edit the source database and update the Pivot Table results

Double check your exercise results

Test what you learned in this section

Double check your test results

An overview of the section content

Introductory lesson on the structure of the business plan used in the section

Select the desired line in a table using multiple IF functions

Select the desired line in a table using IFS (Excel 2019, 2021 and 365 only)

Select the desired line in a table using VLOOKUP with approximate match

Round values to a specific number of digits

Paste the link to the source cells instead of the copied cell contents with Paste Link

Give cells a specific name to refer to them more quickly

Use dynamic arrays or Ctrl + Enter to populate multiple cells at once

Use the double-click to follow formula references instead of entering the Formula bar

Check and visualize references between cells

Setup hyperlinks to quickly move across your workbook

An overview of the coming lessons

Use DATA VALIDATION to add scenarios to your business plan

Set one cell to a target value by changing another cell used as input

Run a sensitivity on one or more output cells by changing one input cell

Run a sensitivity on one output cell by changing two input cells

Set one cell to a target value by changing multiple input cells and setting multiple constraints

Run a sensitivity on three output variables

Double check your exercise results

Record a Macro to perform repetitive tasks

Add buttons to the workbook to quickly run Macros

Prepare your worksheet for printing: set print area and define scale

Test what you learned in this section

Double check your test results

An overview of the section content

Split poorly formatted text into multiple columns

Use Excel text functions to select the first / middle characters of a text string

Combine multiple text functions to extract the last characters of a text string in a flexible way

Apply what you learned in the previous lesson

Double check your exercise results

Use FLASH FILL to quickly extract a portion of a text string

Use CONCAT or & to combine the content of multiple cells into a single one

Combine CELL and other text functions to retrieve and use the worksheet name

Select multiple worksheets to perform actions on all of them simultaneously

Reduce the number of rows and columns displayed in your worksheet by hiding them

Protect your worksheets to allow only selected edits

Save selected worksheets and move worksheets across workbooks

Make your formulas more flexible by using other cells as part of the formula reference

Combine VLOOKUP and INDIRECT to link databases on multiple worksheets with 2-dimensional flexibility

Use this compact formula to sum a cell from multiple sheets with the same layout

Learn multiple ways to correctly format numbers stored as text

Manage and remove cell references across multiple workbooks

Use Excel pre-set formatting rules to visually extract insights from your data

Master conditional formatting and correctly manage multiple rules in the same cell

Create custom conditional formatting rules to best match your visualization needs

Test what you learned in this section

Double check your test results

Deep dive on how to solve the test using text formulas

Final advices to continue your learning journey

An overview of the section content

Link to download all section support files

The IF function, or statement, is a cornerstone of every programming language, as it allows you to make a comparison between a value and an expected outcome.

The IF function is very versatile: it can be used on its own or combined with other logical functions like AND or OR. In addition, you can also combine multiple IF functions to create the so-called nested IF, or use the more recent IFS equivalent.

In this video, we’ll see some practical examples for each of the main use cases of IF, including:

• The different ways you can input the arguments in the IF function

• How AND and OR work and how to combine them with IF

• What is a nested IF and how to use it to choose a range from a list

• How IFS differs from the nested IF

Note: nested IF and IFS are also explained in Section 4, but I included examples here as well to present a comprehensive view of IF.

In this video, we’ll see two very popular Excel functions: SUMIF and its close friend SUMIFS.

As the names say, these functions are useful when you need to sum a range of values, but you need to take into account only a subset of those values, based on one or more specific conditions.

In particular, you can use SUMIF when your data has to satisfy just one condition. If you have more conditions to be satisfied, then you need to use SUMIFS. The SUMIFS criteria are additional, meaning that your data will have to satisfy all of them for SUMIFS to take the values into consideration.

SUMIF and SUMIFS work with the same logic, but their syntax differs a bit. In this video, we’ll see how they both work by going through some practical examples.

In this video, we’ll see another couple of very popular Excel functions: COUNTIF and its close friend COUNTIFS.

While SUMIF and SUMIFS sum a specific subset of a range of values, COUNTIF and COUNTIFS, as the names say, will count the elements of a specific subset of a range of values, based on one or more specific conditions.

You can use COUNTIF when your data has to satisfy just one condition. If you have more conditions to be satisfied, then you need to use COUNTIFS. The COUNTIFS criteria are additional, meaning that your data will have to satisfy all of them for COUNTIFS to take the values into consideration.

Unlike SUMIF and SUMIFS, COUNTIF and COUNTIFS share the same formula syntax, so they’re easier to remember and to use. In this video, we’ll see how they both work by going through some practical examples.

Ok, you know how to use IF, SUMIF, COUNTIF, etc., but do you know all the possible ways you can input the arguments in these functions and in general in several other Excel functions?

In this video, we’ll see how to use complex logical operators to correctly manage values, like greater than or equal to, and how to use wildcards to deal with text conditions, for example to select both the singular and plural of a name.

In this video, we’ll see another function that is extremely useful when dealing with databases. SUMPRODUCT allows you to calculate in one single formula the sum of the products of multiple cells.

With SUMPRODUCT, you can select two or more cell arrays of the same size and you

• first multiply all the cells in the same array positions (i.e., all the first cells of the arrays, then the second ones, etc)

• and then you sum all the resulting products.

If you’re still wondering what SUMPRODUCT does and how it works, watch the video to see it in action, first with a very basic example and then in a real-life database.

In this video, we’ll see another function you should know when dealing with databases. SUBTOTAL is a formula often neglected, but that can speed up your work compared to other functions.

SUBTOTAL is a peculiar function, as it can perform multiple operations, you need to tell the formula what to do. A sum, an average, max, min, etc.

In my experience, I find SUBTOTAL particularly useful for two main functionalities:

• SUBTOTAL automatically updates its value by considering only the cells included in the result of the filter, so you can quickly check the result of the formula once you apply a filter to your data

• You can nest multiple SUBTOTAL functions to easily calculate the subtotals and the grand total of a set of values, since SUBTOTAL ignores other SUBTOTAL functions included in its range of values

Watch the video to see all the operations SUBTOTAL can perform and the power of its functionalities.

How many times have you struggled to write a number starting with zero or some text starting with plus or minus? A phone number, a zip code… If you’ve been there before, you know Excel will delete all the zeros to the left of a number. Also, if you start the cell content by typing a plus or a minus, Excel will consider what comes next as a formula.

If you want to correctly manage numbers starting with zero or text starting with plus or minus, you need to first type an apostrophe in the cell before anything else.

Watch the video to know more and see some examples.

In this video, we’ll deep dive on number formatting.

Every time we work with Excel we’re dealing with numbers. Most of the time we need just a handful of formatting options, depending on what we normally work on, like comma style or percentage style. However, it is not unusual to deal with dates, phone numbers, and other special kinds of numbers that need a specific formatting to be applied. Knowing how to properly manage these kinds of numbers will save you quite some time and headaches.

In the first part of this video, we’ll see all the standard options Excel offers you to format numbers.

In the second part, we’ll see how to create our own custom formatting styles. I’ll explain how to read special number formatting rules and how to create basically any formatting style we want.

Finally, a couple of links to deep dive the topics mentioned in the video:

• Number scientific notation: https://bit.ly/3O0Q0Ev

• Full guidelines to customize number formats by Microsoft: https://bit.ly/3UtIaWk

In this video, we’ll see all the different ways you can sort a database. Sorting values largest to smallest, sorting entries A to Z, or vice versa. We’ve all been there, we all like to put some order in our databases.

But do you know that you can sort a database based on two different conditions? And do you know you can also sort a horizontal database?

Watch the video and I’ll show you how to do it.

In the last video, we saw how to master Excel sorting.

As I mentioned, you have to be careful if there are formulas in your database, as changing the order of your rows could result in the formulas pointing to the wrong cells.

So if you want to sort a database with formulas you have two options:

• Either you transform all the formulas into hard coded values

• Or you carefully check that your formulas are sort-resistant.

In this video, we’ll see how to make your formulas sort-resistant. There are going to be multiple solutions, depending on the specific formulas you’re using, and on which cells the formulas are referring to. I’ll show you three possible solutions, keep them in your pocket and try them out when you encounter this problem.

Do you know how to write something in a cell and then stay in the same cell for further editing? Do you know how to move across columns in a database and then go back quickly to the next row in the first column you edited?

In this short video, we’ll see all the options you have to move from one cell to the next. Some options may be common knowledge, but there may be some that you don’t know and that are worth keeping in your pocket.

In this short video I’ll show you one option that you may need to customize on your Excel. I’ll show you how to create a new Excel workbook with just one sheet in it, or any number of sheets you want.

When creating a new workbook, by default Excel creates it with three worksheets. However, most of the time we don’t really need all those sheets and we have to delete them before sharing the file with somebody.

But if we don’t need them, and we need to spend time manually deleting them, why don’t we avoid creating them in the first place?

The good news is that there’s an easy option for this in Excel preferences. Watch the video to see what to do.

An overview of the section content

Link to download all section support files

As we’ve already discussed, VLOOKUP is a wonderful formula, allowing you to merge two different databases in a quick and flexible way. However, as we’ve seen, VLOOKUP, and its horizontal counterpart HLOOKUP, have some limitations. We may want to use the INDEX/MATCH combination to have even more flexibility, but the formula is going to be a bit more complicated.

To overcome these issues, Excel introduced in its latest releases a more powerful version of VLOOKUP, called as we said XLOOKUP.

In this video, we’ll see the basics of how XLOOKUP works and how it solves VLOOKUP main drawbacks.

In this video, I’ll show you how to use XLOOKUP to replace the soon-to-be old-fashioned INDEX/MATCH combination.

As you know, the INDEX/MATCH combination is used when you need a higher flexibility compared to VLOOKUP to merge two different databases. As a recap, we normally use INDEX/MATCH in three cases:

1. When the column containing the so-called lookup value is to the right of the value we’re interested in

2. When we need to add one column to our source database, also called table array, and we know that VLOOKUP will not work anymore as it will return a wrong value after we add a column

3. When we need to add multiple columns to output database from the source database, and we know that with INDEX/MATCH we can do that with one single formula

We’ve already seen in our previous video how XLOOKUP can be used to replace INDEX/MATCH in its first and second application.

In this video, we’ll see how to use XLOOKUP to add multiple columns to our database with one single formula.

In this video, we’ll deep dive on XLOOKUP optional fields, and how to use them to make XLOOKUP even more flexible and powerful. In particular, in this video we’ll see how XLOOKUP integrates IFERROR within its options.

In this video, we’ll keep deep diving XLOOKUP optional fields. We’ll see how to handle approximate matches with XLOOKUP, and how XLOOKUP can replace the nested IF or IFS when you need to define in which interval a specific value falls.

We’ll be using XLOOKUP “match_mode” optional field. As we will see in the video, this field corresponds to the VLOOKUP “range_lookup field”. However, as for several other VLOOKUP functionalities, this field has been significantly upgraded as well.

When dealing with approximate matches, VLOOKUP has two main limitations:

1. The table array has to be sorted from smallest to largest, or A to Z if it is text.

2. It returns the value corresponding to the exact match or to the next smallest number, stopping once it finds a bigger number in the table array, which could create some problems with boundaries.

Once again, Microsoft took care of both when designing the new XLOOKUP function.

In this video, we’ll keep deep diving XLOOKUP optional fields. We’ll see how to handle wildcards with XLOOKUP, and how that differs from the soon-to-be outdated VLOOKUP.

For XLOOKUP to correctly process the wildcard, we need to specify in the “match_mode” optional field that we’re using a wildcard.

In this video, we’ll see another useful XLOOKUP option you can choose, focusing on the very last optional field you find in the formula bar. I’m talking about Search Mode.

XLOOKUP, by default, searches top to bottom like VLOOKUP, or left to right like HLOOKUP. This is what happens when we do not type anything in “search_mode”. However, with XLOOKUP we can now also search bottom to top or right to left.

Change your decimal, thousand, and formula argument separators

An overview of Microsoft Windows (not Excel) shortcuts to further improve your productivity