Category Archives: education

Excel Named Range

Excel Named Range

Defining names in Excel involves giving a range of cells a unique name, which means that you can easily refer to a specific defined range, e.g. while creating a function. This is especially applicable in the following functions:
as the first argument
MATCH as the second argument
VLOOKUP and HLOOKUP as the second argument.
Formulas containing names instead of ranges are more understandable and easier to use, provided that the names we give will be legible. These names are also used in VBA programming.
In the Formulas card you will find the Defined names section:

How to define name for the range?

• Select the relevant range.
• In the Name Box, enter the unique name of the selected range. Do not use any illegal special characters, e.g. spaces, $, @, #,! or arithmetic operators (+, -, /, *, ^), comparison (<> =) and the text operator (&).
• Accept by pressing the Enter key.

Instead of the Name box you can click Formulas card then Define Name, set up: the name, scope, comment and refers to. In addition, the defined name cannot only refer to a cell or range of cells, but it also contains a formula or a specific value or text.

Create multiple names from the selection

• Select a range of data which contains multiple columns or rows with the labels at the beginning or the end.
• Click Formulas card then Create form Selection or shortcuts[Ctr] + [Shift] + [F3].
• Select options based on what you want to name.

Name Manager

• Click Formulas card then Name Manager [Ctrl] + [F3].
• Create another new name, edit existing name, delete it, filter it and sort it

Insert the defined name into the formula

Simple click [F3] to obtain a list of created names and select the relevant one or start typing existing name and select the relevant one.

You can learn similar tips and tricks in Excel at a comprehensive Training in London.

IFS vs nested IF

IFS vs. nested IF

Excel IF Statement

Syntax of Excel IF Statement:​

= IF ( Logical Test , Value if True , Value if False )​​

Logical test Examples:

• Equal to: A1=A2.
• Higher than: A1>A2.
• Higher or equal than: A1>=A2.
• Less than: A1<A2.
• Less or equal than: A1<=A2.
• Not equal to: A1<>A2.

If we do not declare a third argument, because it is not mandatory, it will return FALSE by default. If we want to return the text as a result in the second or third argument, it must be enclosed in quotation marks.

=IF(A1>5,"Pass","Fail")

If cell A1 value is higher then five, formula return Pass otherwise Fail.

In the case when we want to apply one logical condition, creating an IF function is simple and obvious. However, if we want to introduce further conditions, everything gets slightly more complicating. In this case, the function is nested, the next function (function in function or multiple) as an argument of the function.

Nesting of the IF function

I think each of us, at least once, has found it difficult to master the IF function when it is nested. That is, when the IF function was an argument of the IF function at least once. In addition, the nested function is a burden on the work of the system during conversion, of course, when we often use this function in the worksheet.
I will now give you a simple example of an IF function nested once. In place of the third argument, a new IF function containing another condition is introduced.

= IF (( Logical Test , Value if True , IF ( Next Logical Test , Value if True , Value if False ))
=IF(A1>5,"Excellent",IF(A1>3,"Good","Poor"))

If cell A1 value is higher then five, formula return Excellent, if not but higher than three return Good,  otherwise Poor.

IFS function alternative

In 2016, an alternative to the IF function appeared in Excel, i.e. the IFS function, which gives the possibility to introduce more logical conditions than one, which simplifies the creation of the formula and significantly speeds up its work.

= IFS ( First logical Test , Value if True , [ Next Logical Test , Value if True ] , ...)
=IFS(A1>5,"Excellent",A1>3,"Good")

If cell A1 value is higher then five, formula return Excellent, if not but higher than three return three,  otherwise return error #NA.

It can happen that more than once the condition is met in the arguments of the IFS function. In this case, the one that is recorded first is decisive.

=IFS(A1>3,"Good",A1>5,"Excellent")

In this case, if the content of cell A1 is higher than three, the function will always return Good (never return Excellent). Therefore, the order in which the conditions are saved is important. If none of the conditions are met, the formula will return error #NA. 

IFS vs nested IF

Benefits of IFS over nested IF:

• The IFS function allows you to easily enter further logical conditions without nesting.
• Due to the lack of nesting, the performance of IFS is much more efficient
• Allows you to apply twice as many, or 127 conditions, while the nested formula 64 conditions only.
• If none of the conditions are met, IFS will return error #NA. In this case, it is worth using IFEROOR. In this case, in the second argument we give what should be if an error occurs, if we need it.

= IFERROR ( IFS ( Logical Test , Value if True , Next Logical Test , Value if True ) , Value if False )
=IFERROR(IFS(A1>5,"Excellent",A1>3,"Good"),"Poor")

If the value in cell A1 is higher than five, the formula return Excellent, if not, but higher than three return Good, otherwise it return Poor.

You can learn similar tips and tricks in Excel at a comprehensive Training in London.

XLOOKUP vs VLOOKUP

XLOOKUP vs VLOOKUP

xlookup vs vlookup

Forget about the VLOOKUP function. The new XLOOKUP function has arrived, being a genuine alternative for VLOOKUP, HLOOKUP, INDEX and MATCH. If you know how to use the XLOOKUP function, you will never bother to use VLOOKUP anymore. The new feature, which has been recently introduced, has more options and is characterised by its flexibility, efficiency and user-friendliness.

XLOOKUP has six arguments:

= XLOOKUP ( Lookup_Value , Lookup_Array , Return_Array , If_not_Found , Match_Mode , Search_Mode )​​

1. Lookup_Value
2. Lookup_Array
3. Return_Array
4. If_not_Found
5. [Match_Mode] – default FALSE – Exact match
6. [Search_Mode] – default TRUE – Fist to last

Benefits of XLOOKUP over VLOOKUP:
✅ the searched result in the column may be placed to the left of the column with the searched value;

✅ change of the structure of the table with data by inserting or removing columns will not affect the result;

✅ we select only relevant ranges and not necessarily the entire table;

✅ it can return a range, not just a value;

✅ it can return a value, if not found;

✅ the exact search is set to 0 by default in argument 4, the most commonly used option;

✅ it can return the nearest higher value if we declare (1) in the 4th argument;

✅ should we look for the closest value in the 4th argument we do not need to have any data sorted;

✅ wildcard support only if you declare (2) in the 4th argument;

✅ it can search for the last value (-1) in argument 5;

✅ it carries out binary search;

✅ it performs searches not only vertically, but also horizontally if we mark the ranges accordingly;

✅ faster functionality.

You can learn similar issues and tricks at my comprehensive Excel Training in London.