Excel.

It’s a program that you can fall in love. I guess the average user, just knows 5-10 commands like sum, average, a little bit or customizing the look of the spread sheets and that’s all. The work is done and the representation is more than fine.

But excel is way more than 5 commands and colored cells. It is more than diagrams and charts. It is something between your calculator and a programming language. Actually is a programming environment. Is it like C++ or R? No. Definitely no. Is it is like Matlab? Again, no. Is Matlab a program language? No. Is it a programming environment that uses its own scripting language? Of course it is. So is Excel. It is a programming environment for the masses. And it is a shame for a user to use only 5-10 basic commands while using another 10 maybe, the program can make miracles.

So here I will represent some more “advanced” commands for excel that I find myself using them almost every day and they make my workflow 1000% easier while some of them also preventing some mistakes that would be unavoidable otherwise.

1. IFS

The command that takes the place of the nested if. It is found in 2016 version and beyond (the command cannot be recognized on earlier versions of excel and it will give an error).

The syntax is this:

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

So what it does is that it contains all the possible outcomes to a clearer and more concentrated format than the if command. IFS(something then something, something else then something else etc.)

Expert tip: There is a way to use ELSE with this command. The syntax is this:

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3, TRUE, Value if something else)

For more click here https://www.excel-easy.com/examples/ifs.html

and watch the video below:

2. Index Match Match

What a formula! Not easy to remember though. But below I’ll give you a tip to remember what it the general structure. What this formula does is indexing a value that you are looking from a table, to the table that you want to fill, based on the column and the row description.

Syntax:

=MATCH (lookup_value, lookup_array, [match_type])

This will give the position of the lookup value we are looking for, in the lookup table we want to search (vertically or horizontally).

Where match type (as I use it) is False for exact match and True for not exact match.

This video explains the formula:

Syntax:

=INDEX (array, row_num, [col_num], [area_num])

array – A range of cells, or an array constant.

row_num – The row position in the reference or array.

col_num – [optional] The column position in the reference or array.

area_num – [optional] The range in reference that should be used.

The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

So actually Index is INDEX(ARRAY,Y position, X position)

Replacing the Y and X positions with MATCH formulas you have the ultimate table filler formula INDEX(ARRAY, MATCH, MATCH).

3. VLOOKUP

Another quick, easy and nice formula to use in order to fill columns.

As it is described in Microsoft’s website:

=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE).

I personally prefer this when I have to make some quick column fills and do not want or have the time for an Index Match. VLOOKUP has some disadvantages though. They are:

• It cannot lookup and return a value which is to the left of the lookup value.
• It works only with data which is arranged vertically.
• VLOOKUP would give a wrong result if you add/delete a new column in your data (as the column number value now refers to the wrong column).

4. Sumifs

Sumifs is just a Sumif on steroids! If you use Excel 2007 and beyond then you can use it. What SUMIFS does? Add cells based on criteria. I can say they are one of the most handy functions.

The syntax is this:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

sum_range – The range to be summed.

range1 – The first range to evaulate.

criteria1 – The criteria to use on range1.

range2 – [optional] The second range to evaluate.

criteria2 – [optional] The criteria to use on range2

5. Trim

Small, easy but handy formula. It vanishes the extra spaces before, after and between the strings (words) in the cell that is applied. Many times I found myself fighting with columns that something is wrong and gives N/As without any obvious reason. After TRIMming the cells its clear that they had extra spaces.  The Syntax is:

=TRIM(CELL)

6. Right/Left & Len

• First thing, Left/Right commands with the syntax

=LEFT (text, [num_chars])

Example:  a1 cell is HEDProject, LEFT(A1,3)-> HED

• Secondly, Len

LEN gives the number of the characters that a cell has.

For the above example LEN(A1)->10

Combining LEFT/RIGHT & LEN

Let’s say that you have a list of names like A1= Plot1, A2=Plot2,A3= Plot3,…, A150=Plot150, and you want to extract only the numbers. Combining LEFT and LEN is an easy way to do it and it is:

Right(a1,Len(a1)-4)->1.

What it does is that it takes the string of A1 cell, then gets only the right part of the word, with the number of characters as the total length of the A1 cell deducted by the number of characters in the word Plot (4 in this case). So it lefts only 1 character from the left side of the string which is the number 1.

 

7. Extra commands

Absolute reference

 

Conditional formatting

 

Today’s date shortcut

Easy but handy. Go to a cell you want to insert the today’s date and simply press CTRL+;

and today’s date will be there!