Excelling with Microsoft Excel 4: Excel 2010 (Vlookup and formatting)

This post is evident that we are making progress with the self-learning series on Microsoft Excel. I appreciate the positive feedback that I have been receiving so far. Currently I have completed 41% of the course so I presume we still have more new tips to learn together.  I hope that at least the previous posts have been beneficial to you as they have been for me.

The previous post was on utilising the F4 as a shortcut function and other Excel worksheet tips. If you are interested in taking the online e-learning course administered by Alison independently, you can check out the link here >>>Microsoft Excel 2010 course. These posts are essential for those who do not have the time to go through the entire course on their own.

Key learning points for today

  • Vlookup (Vertical lookup)

Vlookup helps look up the value in one column (value must always be in the first left column of the data) to find its corresponding value on the same row in another column. Personally this excel function has really saved me time and has assisted me vastly with the preparation of financial statements in excel, ie after I have successfully classified the account codes. The key thing is for both sets of information to have something in common, in my example it would be the account codes.

Function: =VLOOKUP (lookup_value, table_array, col_index_num,range_lookup)

Lookup value: This is usually the point of reference or the column with information unique to the data sets.

Table array: Table or data from which the retrieval is made.

Column index number: The column number from the table array from which retrieval is made. Always start the count from the lookup value column.

Range lookup: Choose ‘FALSE’ if an exact match is required and ‘TRUE’ when an approximation is needed but data must be sorted in ascending order (this is usually used for discount and data ranges)…….Would illustrate further in another post.

Don’t forget to use the insert function discussed in the previous post should you face any challenges. There is no need to memorise the function!

vlookup function
Vlookup function

Would be working on developing a vlookup illustration to make it easier to follow and it would be more practical than reading all this plenty grammar I have written.

  • Conditional formatting

When faced with presenting reports, it’s important to highlight relevant information. This does not mean we should go about colouring and shading our excel worksheets. The essence of conditional formatting is to make relevant data standout and not for beautification purposes.

How to access the conditional formatting tool bar

  • Go to Home
  • Select conditional formatting

conditional formatting bar

  • The following options would be displayed: highlight cell rules, top/bottom rules, data bars, colour scales, icon sets, and others.

Would illustrate a few and as I always encourage you to do, explore the other options on your own. Don’t forget to highlight the data range before selecting the conditional formatting style you are comfortable working with.

Data bars (shows the value of a cell relative to all other cells in the range)

Data bars

Colour scales 

The data bars is much easier to understand at a glance. It is all a matter of personal opinion.

Colour scales

Icon sets

Icon sets

You can always ‘clear rules’ from the conditional formatting menu whenever you prefer to make changes to a previous format. There are also optional rules to highlight key data such as top 10, values greater or less than a set value, etc. These can be accessed from the conditional formatting menu under ‘more rules’, ‘new rules’ or ‘manage rules’. Just play around the various options if you have the time.

All the best!

The next post would be on sparklines and excel tables and hopefully the vlookup illustration indicated in the previous paragraphs would have been completed by then.

Comment directly with your questions and corrections. Alternatively you can send them to omtsdigest@gmail.com. I will be happy to research and provide answers.

Disclaimer: I am not an expert. I choose to document, detail and share tips from my personal Excel 2010 self-learning journey with Alison with others. Knowledge is of better use when it is shared with others.