# 7 tricks you may not know about Excel      Photo Credit: Excel screenshot

### Feel like upping your Excel game? Then check out these tips from the “Wizard of Excel,” Oscar Toledano

Excel is the most used tool worldwide for dealing with data, with over 750 million users every day. But one of the greatest things about this amazing tool is that it is a sea of knowledge onto itself. Here I present you with a list of 7 tricks that may prove to be very useful in your daily interactions with Excel.

### 1. Extract characters from text

– LEFT & MID & RIGHT functions

Often times we need to extract a few characters from a text, such as if the text says the full name when all we need is the surname. To achieve such extraction, we have three basic functions.

A. To extract characters from the left of a text, we use the LEFT function

=LEFT(“The wizard of Excel”, 6)= “The wi”

=LEFT(“I love Excel”, 5)= “I lov”

The number reflects the number of characters to extract.

B. To extract characters from the right, we use the RIGHT function

=RIGHT(“The wizard of Excel”, 6)= “ Excel”

=RIGHT(“I love Excel”, 5)= “Excel”

As before, the number reflects the number of characters to extract.

C. To extract characters from any other point, we use the MID function

=MID(“The wizard of Excel”, 3,6)= “e wiza”

=MID(“I love Excel”, 3,6)= “love E”

Now the numbers reflect different things. The first number reflects the position of the first character to be extracted, and the second number reflects the number of characters to extract.

Please note that for the 3 functions, spaces are also included in the characters to extract.

Bonus: We could also combine these functions with the TRIM function.

=RIGHT(TRIM(“The     wizard     of     Excel      “),5)= “Excel”

### 2. Finding out the k-th highest/lowest value in a list

Most users know that we can calculate the Maximum and Minimum value in a list by using the MAX and MIN functions. But what if we needed to calculate the second lowest value, or the fourth highest? In Excel we can calculate that easily by using the SMALL & LARGE functions.

The SMALL function finds the k-th smallest value in a data set. For example, the 20th smallest number.

The LARGE function finds the k-th largest value in a set of data. For example, the tenth highest number.

Examples:

=SMALL({1,2,5,3,6}, 4)= 5 indicates that the fourth smallest value in the list is 5

=LARGE({1,2,5,3,6}, 4)= 2 indicates that the fourth largest value of that list is 2

### 3. Lookup values to the left

You may already know that VLOOKUP function will search the first column of a range and then return a value from a selected column on the same row of the range. However, that means VLOOKUP cannot go left!

Image that we have the following data:

Using VLOOKUP, we could easily calculate the population of France, or the GDP of India, but what if we wanted to know which country has a population of 59.8 million?

How do we solve this? By using the INDEX and MATCH functions.

=INDEX(column that has the data that we want, MATCH(value we are searching, column containing that data, ())

In our example we could do:

=INDEX(B1:B11,MATCH(C10,C1:C11,0),) = “Italy”

### 4. Counting the number of unique values in a range

On many occasions we have a list or range with lots of data, and we wonder how many of those values are unique, needing to develop a complex solution by adding helper columns and doing multiple operations. We could know the answer with just one formula.

Imagine we have a list of names in the range A1:A14 which we call Name_list

The following formula will count the number of unique names.

=SUMPRODUCT(1/COUNTIF(Name_list,Name_list))= 11

### 5. Selecting a range from many using the CHOOSE function

Sometimes, we have a number of ranges but we only want to use one (for example, to sum it) depending on a condition or a selection by the user.

Let’s say that we have four ranges, {A1:A10}, {B1:B10}, {C1:C10} and {D1:D10}. With the CHOOSE function, we could just use the relevant one. How?

The syntax for the CHOOSE function is CHOOSE(index_num,value1,value2,…) where Index_num specifies which value argument is selected. Value1, value2, …are arguments that will be used and can be numbers, cell references, defined names, formulas, functions, or text.

=CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10)

This formula would return the range B1:B10 as the index_num is 2, which indicates that the formula is returning the value2, which in this case is a range.

Once we know this, we can them sum it up, or calculate the average, or the minimum/maximum value.

Now we use this to perform calculations such as the following:

=SUM(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

=AVERAGE(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

=MIN(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

=MAX(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

### 6. Determine the week number in the year

As the year goes, by we all forget what week number we are in. But sometimes it is very important to know exactly that. To assist us with the task, Excel has a very cool function: WEEKNUM function.

There are two systems to understand the week number:

• System 1: The first week of the year (week 1) is the week containing January 1st.
• System 2: The first week of the year (week 1) is the week containing the first Thursday of the year. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.

Excel accommodates both systems in the WEEKNUM function. To do so, the second part of the function indicates on which day the week begins.

Example:

=WEEKNUM(“01/01/2012”, 1)=1

=WEEKNUM(“01/01/2012”, 2)=1

….

=WEEKNUM(“01/01/2012”, 21)=52

### 7. Convert text to uppercase, lowercase or proper name

Excel has three very useful functions to alter strings, either to convert them to uppercase, lowercase or proper name (the first letter of each word in upper case text). Let’s see them more in detail.

A. To convert text to upper case, use the UPPER function.

=UPPER(“The wizard of Excel”) = “THE WIZARD OF EXCEL”

B. To convert text to lower case, use the LOWER function.

=LOWER(“The wizard of Excel”) = “the wizard of excel”

C. To convert text to proper name, use the PROPER function.

=PROPER(“THE WIZARD OF EXCEL”) = ”The Wizard of Excel”

The views expressed are of the author.

Geektime invites global tech and startup professionals to share their opinions and expertise with our readers. If you would like to share your point of view, please contact us at [email protected]

Share on:     