Looking for an online microsoft Excel training? 5miles offers trainings in Excel through engaging 5 minute bite-sized learning.

A Microsoft Excel course for beginners to advanced learners

Are you ready to learn all about Excel? Check out our carefully carved out level and topic tracks, or let our algorithm decide which course you should follow.

Excel learnings provided in our library

  1. VLOOKUP and XLOOKUP
  2. Index matching
  3. VBA for Excel
  4. Pivot Tables
  5. Conditional formatting
  6. Formulas

Learn more about online excel training

VLOOKUP and XLOOKUP

Frequent users of Excel cannot do without the lookup function, particularly VLOOKUP which has been coined by many as one of the top 10 most important things to know in Excel. But VLOOKUP isn’t perfect, and Microsoft has recently created XLOOKUP, a new, more powerful look-up feature that is simpler and without limitations of VLOOKUP.

XLOOKUP

XLOOKUP has five possible arguments:three required arguments and two optional arguments based on the following syntax.

= XLOOKUP ( lookup_value, lookup_array, results_array, [match_mode], [search_mode] )

Microsoft Excel Xlookup syntax

Below you will find an example of the XLOOKUP function

The lookup_value is A1, the look_up array is A:A, and the return_array is E:E. Our return value was 0 which left a blank cell.

GIF: Microsoft Excel Xlookup process

Using the keyboard shortcut Ctrl + D, the same XLOOKUP formula is applied to D1:D28 to see the return_arrays when applied to other lookup_values.

VLOOKUP

The Vlookup function lets you find things (text or numbers) in a range (table or list) by row. This sounds complicated, but becomes easier when you know that this is the exact same approach you use when searching in a traditional telephone directory. Do you remember the steps you would take?

  1. You go to the pages that correspond to the city where the person is living.
  2. You start searching for the family name.
  3. You find the information you need on the same row, such as the home address and the phone number corresponding to that particular family name.
  4. You make sure that the family name is spelled correctly, to prevent making a mistake and calling the wrong person.

The syntax of the Vlookup function is the following:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Let’s see if you got it. What is the result of the Vlookup formula in cell F3?

Discovery_02

The formula uses the value in B6 as the lookup value. It looks for it in the in the range B3:D7. It then returns the value in the third column from the lookup value, using an exact match. The return value is 33.

Index Matching

When looking something up, everyone has their own method. For example, when searching for information, some use Google and others prefer going to Wikipedia directly. The same goes for searching values in Excel. A lot of Excel users will apply a Vlookup function, but did you know that the Index function combined with a Match function is a good and sometimes better alternative?

To illustrate, we first present some sample data and a short description of the Index and Match functions. Then we will explain the Index Match combination, and compare this combination with Vloopup.

The situation

Imagine you miss a call from a potential investor. All you have is their number, no other information. So you go to your Excel file with all the potential investors’ names. You use the Index and Match combination to get their name. The number that called is +3102003366 and the name you’re looking for is Cole, Jermaine. The image below graphically explains what we are looking for.

Alt text

Index function

The INDEX function returns a value in an array based on the row and column numbers you specify. The syntax for the INDEX function is the following:

Syntax
=Index(array, row_num, column_num)

Suppose you want to return the name Cole, Jermaine from the example above (blue cell (A3)). The formula to return this value would be:

=INDEX(A1:C3,3,1)

The formula is quite straightforward and easy to use when you know the array, the exact row number and column number. It gets harder when the exact row or column number are unknown. This is when the MATCH function becomes crucial.

Match function

The Excel MATCH function returns the relative position of the lookup value in a range of cells. The syntax for the MATCH function is the following:

Syntax
=MATCH(lookup_value,lookup_array,match_type)

Suppose you want to return the row in which you can find Jermaine Cole’s phone number (the orange cell (B3) in the example). The Match formula would look like this:

=MATCH(B3,B1:B3,0)

Index Match

So how do you combine these two brilliant functions in the above example?

As you may have notices you:

  • Use the Index function to return the Name.
  • Use the Match function to identify the row of the Telephone number.

The syntax and formula for the combination function becomes:

Syntax
=INDEX(array,MATCH(lookup_value,lookup_array,match_type))

Applying this to the example, you would have to enter:

=INDEX(A1:A3,MATCH(B3,B1:B3,0))
  • Note that we don't use the optional column_num argument from the Index function

The following image illustrates the setup:

Alt text

Don’t whine, combine

The Vlookup function looks much simpler than combining the INDEX and MATCH function, but there are two major benefits to use the latter:

  1. The Index and Match combination can search from left to right and right to left, whereas the Vlookup can only search from left to right. There is no more need to create a helper column to repeat values for the Vlookup formula.
  2. The Index and Match combination correctly adjusts when columns are inserted or deleted. A normal Vlookup function uses static column reference and returns the wrong value when rows are inserted or deleted.

Other authors suggest two other benefits that we deem less important at this moment. With the Vlookup function, you can look up at most 255 characters, whereas the combination function has no limits. The Index and Match function is also believed to have a higher processing speed.

VBA for Excel

If you perform repetitive actions in Excel on a regular basis, it can be useful to translate these actions into Macros. Macros? … you might say.

A macro is a very small script containing code that works within Microsoft Office. Macros allow you to automate processes. The software language in which the code is written is called Visual Basic for Applications (VBA).

Macros are somewhat of a hidden gem in Excel. In the default settings, the tab for working with macros is actually hidden! The basic features are accessible through the ‘Macros’ button on the View tab but to get the full offering, and to get yourself in developer mode, you need to enable the Developer tab. In this Discovery we show you how to do this so that you can start to discover the magic of VBA.

Want to learn more about VBA for Excel?

Pivot Tables

The PivotTable’s power is its ability to summarize large volumes of data. If you are working with an exhaustive data set with many columns and row, a quick summary is just a few clicks away. Even better, you don’t need to enter a single formula.

A PivotTable does all the calculations while granting you the flexibility to arrange it dynamically. If you want to adjust your original PivotTable report, you don’t have to start from scratch. You can always choose different layouts or perform a new set of calculations.

The PivotTable stands true to its name. It enables you to pivot your data and look at it from different angles.

Know when you’re dealing with a PivotTable

There are three parts that give away the presence of a PivotTable in a workbook. Let’s review them with an example:

Discovery_01
  1. Source data – the original data set from which the PivotTable report is created. In the image, the source data is an overview of the orders for two product categories.
  2. The PivotTable report – a customizable summary of the original data. In this case, the PivotTable report shows the total sales values for each product category.

PivotTable Fields List – the pane to the right of the worksheet. It appears when clicking any cell in the PivotTable report and has two sections:

a. The Fields section contains the variables in the source data.

b. The Areas section defines the location and display of the fields in the PivotTable report.

PivotTable Tools available on the Ribbon

When clicking a PivotTable report, the Ribbon displays a new section – PivotTable Tools – with the tabs Analyze (Options in Excel 2010) and Design. The tabs contain several commands for going more in depth with your Pivot analysis. Both tabs offer specific options for working with PivotTables.

Want to learn more about Pivot Tables?

Conditional formatting

Highlights’ first appeared in art. Painters would make a part of their canvas stand out by applying colours to lighten it. However, the technique was not used solely for aesthetics. Its purpose was to direct the viewer to discover the main subject of the work first. Some examples are these works by Dutch masters.

Conditional formatting in Excel works in a similar way. It not only adds colour to a worksheet, it also guides and speeds up the understanding of the data. In this Challenge we will show you how to use the built-in rules to easily apply conditional formatting to cells.

Conditional formatting at work

The data set below contains rainfall data for the city of San Francisco over a period of 12 years. In Table 1, which has no conditional formatting, you may notice some variation in the number of rainy days. Still, unless you are a very numbers-driven person, you will not be able to immediately compare the values across all the years.

Table 2 uses conditional formatting with data bars. The principle of the yellow highlight is as follows:

  • The cell with the highest value - cell E16 - has a data bar that fills the entire cell
  • The cells for the other values are filled proportionally, according to their value

Note how the formatting makes it easier to spot the distribution of the values over the years.

Discovery_01

The strength of conditional formatting lies in its dynamic nature. Custom cell formatting is static - a fill colour in a cell stays the same regardless of the value entered in that cell. With conditional formatting, the formatting changes if the data changes . Thus, should the values above change, the bars will adjust to reflect this.

Access the conditional formatting built-in rules

The conditional formatting rules are available in the Styles group on the Home tab. We distinguish between two main purposes for using conditional formatting on your data. Within these, there are several rules available.

1. To highlight specific cell values

These rules are useful for emphasizing certain values. For example, if you want to know which products in an inventory are almost sold out, you can highlight all products with a quantity below a certain value.

There are two sets of rules:

  • Highlight cell rules which are used to format cells that meet a specified criteria
  • Top/bottom rules which are used to format cells with values that are in the top or bottom of the data set, based on a specified cut-off value
Discovery_02

2. To show how values compare to one another or to a benchmark value

These rules are useful for creating a visual of the data set as a whole.

There are two sets of rules:

  • Data bars
  • Colour scales

Both options distribute colours amongst cells according to the values they hold. The difference lies in the presentation, as you can see in the screenshot below:

Discovery_03

There is a fifth option, icon sets, which provide discrete indicators of how values compare. We will treat this topic in another Challenge. We will also address the three options at the bottom of the menu.

How to use data bars

Going back to the example used above with rainfall data, follow these steps to apply conditional formatting data bars and obtain the results in the screenshot:

  1. Select the cells you wish to apply formatting to.
  2. Go to the Styles group on the Home tab and open the Conditional Formatting drop-down menu.
  3. Hover over Data Bars and click to select a set of colours. You can choose either gradient fill - in which the colour in the bar fades as the value increases - or solid fill - in which the colour of the bar is consistent. Note that in the 2010 Excel version, the difference between these two options is not related to the cell value.

The animation below shows the steps for applying data bars to the cells with values in column B. Notice how the bars automatically adjust when a new highest value is added in cell B4.

Discovery_04_gif

Format with moderation

Generally speaking, excessive conditional formatting defeats the very purpose of using it. Too many rules and colours make the data confusing. Our advice is to use conditional formatting to accentuate the most important data in your table.

Want to learn more about conditional formatting?

Formulas

A colleague once introduced me to "the rule of thumb" when I constructed a very basic formula. My formula looked like the one below and took up almost the entire formula bar:

=(A10+A12+A11+A14+A15+A13+B10+B12+B11+B14+B15+B13)/12

My colleague advised that in order to maintain clarity and simplicity a formula should be simplified if it is longer than your thumb. No, you don’t have to search the Internet for thumb stretching exercises because Excel can solve this problem for you.

The formula above could simply be rewritten as:

=Average(A10:B15)

In this Discovery we will explain the basics of the Sum, Count and Average functions. They are used very frequently and knowing how to construct them will save you time.

Sum, Count and Average

The Sum, Count and Average functions are the most frequently used basic functions in Excel. They help you to do standard calculations quickly. They are, of course, related since Sum/Count=Average.

SUM

The Sum function adds numbers, be those a list of numbers, a list of cell references or a range of cells (or a mix of the three).

Syntax
SUM = (number1,number2,…)
  • number1 is required. The first number, cell reference, or range you want to add.
  • number2 is optional. The second number, cell reference, or range you want to add.

Number1 and number2 are called the arguments of the function. If you enter text as an argument, the Sum function returns an error. However, if you enter cell references or a range as the argument, and any cells referenced in the argument hold text, the function does not return an error. It simply ignores the text values when performing the calculation.

Disc_01

COUNT

The Count function counts the number of cells in the argument that contain a number, or counts the number of numbers within the list of arguments.

Syntax
COUNT = (value1,value2,…)
  • value1 is required. The first item, cell reference or range within which you want to count numbers.
  • value2 is optional. The second item, cell reference or range within which you want to count numbers.

The arguments of the function can contain a variety of types of data, but only the numbers are counted.

Disc_02

AVERAGE

The Average function returns the average of the arguments. The Average function is like the Sum function in that it will not work if you enter text as an argument. However, like the Sum function, it will work if you enter the arguments as cell references or ranges, and any of the cells referenced contain text. The Average function simply ignores any cells containing text; it does not return an error.

Syntax
AVERAGE = (number1,number2,…)
  • number1 is required. The first number, cell reference or range for which you want the average.
  • number2 is optional. The second number, cell reference or range for which you want the average.
Disc_03

Online Excel training

Discover the fundamental components of Excel. Learn to understand and modify workbooks and worksheets, make calculations and charts, and print your sheets on paper. Next, expand your knowledge by building complex, error-free and user-friendly models.

Learn more about 5miles