What Every Experienced Excel User Should Know

by Emily Cartman, Guest Blogger

 

They say Excel users fall into two groups: Those who draw tables in Excel, and those who surprise their colleagues with amazing charts of data analysis functions. If you are the latter type, here are some tips that you absolutely must know.


VLOOKUP

Got data scattered across different workbooks and sheets? VLOOKUP will help you gather all that data to create a report or summary. Make sure you have a unique reference to connect all the related data. Open the VLOOKUP formula and enter the cell containing the reference number. Now enter the range of cells from where you need to pull your data. If you haven’t used VLOOKUP yet, you have missed out on a lot of things.

Here’s the syntax: VLOOKUP(lookup_value, table_array, col_index_num)

The example given here looks up for D2 (Bruno) and checks in the given range (A2:B5) to find out what kind of animal Bruno is. Pretty simple, huh?


IFERROR

As an experienced Excel user, you must know about IF and IFERROR. With the IF formula, you can put conditions, and if the condition is true, a certain action will take place, else something else will be done. IFERROR is a conditional structure that will let you calculate a formula. If it returns an error, a specific action will take place. You can use it with VLOOKUP to use the formula on another workbook.

Syntax: IFERROR(value,value_if_error)

In the example above, the cell B8 displays the value of A3/B3. Let’s see what will happen when we change B3 to 0 (a mathematical error).

It displayed error now!

What do you think about these tips?

Excel is certainly one of the most useful programs, but it’s highly underrated. If you’re an experienced Excel user, make sure you include these tricks in your little Excel blackbook.