7 tricks you may not know about Excel
Share on Facebook
Share on Twitter
Share on Google+
Share on Reddit
Share on Email

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:

Photo Credit: Screenshot

Photo Credit: Screenshot

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

Photo Credit: Screenshot

Photo Credit: Screenshot

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.

Photo Credit: Screenshot

Photo Credit: Screenshot

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:Share
Share on Facebook
Share on Twitter
Share on Google+
Share on Reddit
Share on Email
Oscar Toledano

About Oscar Toledano


I am passionate about Excel ever since I first used it. With over 15 years’ experience as a heavy user, I decided to set up The Wizard of Excel for users to have more information, techniques, tips and more about this incredible tool.

More Goodies From Big Data


How Cognitive Search Eliminates Common Struggles Website Users Face

How did Big Data transform the manufacturing industry?

10 ways to save money with AWS Redshift