Category Archives: Computer Skills

Excel hints and tips – data and VLOOKUP

itworkerWorking in an office, we have to use Microsoft Excel a lot to assist us in doing our jobs. Some people in our office have developed skills to be able to use Excel well, while others struggle and waste a lot of time using Excel inefficiently. I have made a name for myself in my office as one of the Excel gurus and people are always surprised at how quickly things can be done with Excel that have previously taken them some time. I am going to explain some of the things that I have learnt in Excel so that maybe you can also save yourself some time.

Firstly let’s look at what the people do that struggle with Excel and then look at ways to save time. In my office, people who struggle with Excel usually only use it so that they can layout their information in a grid format. I have even seen people using a calculator to get their results and then manually inputting this information into Excel. Then they place a pretty border around it and they are done. Using Excel to display your information in a grid format is fine if that is all you want to do with it but there is always a requirement to change your information and this can be time consuming if you have nothing to back it up. If you are not using formulas to update your information then one small change can take much longer to update when it affects the whole spreadsheet.

A common mistake I have seen when placing data in a spreadsheet table is that the data is not laid out in the correct format, again making it hard to update any information. For example if you have a table of information, in my experience it is best to lay the information out in a database format. To lay the information out in a database format you have to pick headings for the columns that are common to all entries. If your heading is common to only a few entries then it is incorrect and needs to be rethought. The reason for laying the information out in this way is that all the information is present and complete and can be referenced in any way required.

vllokup example

From your database style table of information you can then use what is called a pivot table to group the data and do analysis. A pivot table will read your database style table and then you can choose different ways to represent it. It is a very powerful tool and I use it all the time. Simply highlight all the data in your database style table of information ensuring that all the columns have a unique heading. Then select pivot table from the menu. There are a few options available but the default options are fine so just select ok. You are then given options to be able to place your different headings in different parts of the pivot table.

For example you may have country in the top level, then state on the vertical axis, sales person on the horizontal axis and sales figures in the data field. From this you will quickly be able to see sales figures for country, state and sales person. Now you can change your mind and place the date on the horizontal axis and quickly be able to see when sales took place in the certain states. When you know how to use this it is very powerful and will save you a lot of time. If you had to recalculate all these figures over and over again then this would be quite time consuming. So give the pivot table a try and save yourself some time now.

You may have a requirement to do a calculation and this is where formulas can be very handy. Simple formulas within excel are easy to use and can save a lot of time, please do not use your calculator. Excel has a formula wizard that assists you in creating all of the formulas so there is no excuse. A formula I use a lot in particular is one called VLOOKUP. This formula is used to draw relevant information from one database style table of information to use in another.

For example you have spreadsheet one which is a database that lists employee staff numbers and the sales that they made and you have spreadsheet two that lists the employee staff numbers and their names. You can use the VLOOKUP formula to join this information to display the employee name from spreadsheet two in spreadsheet one. To do this the formula is placed into spreadsheet one into a new blank column of the data where you want the name to appear. This new column however must be to the right of the reference, ie to the right of the staff number, in order for the formula to function properly. In this example we are using the staff number as the link between the two spreadsheets. The VLOOKUP formula looks like this when complete “=VLOOKUP(A1,A1:B50,2,FALSE)”.

This doesn’t make much sense on its own so let’s break it down. Again don’t forget that the Excel formula wizard will help you with this. So first we start with an “=” equals sign. This tells Excel that we are going to be writing a formula and will change the small navigation window to the upper left of the screen to display the last type of formula to be used. If you click the drop down here you are presented with more options and also the option to search. Type VLOOKUP into your search and when you find it, click on it to open the formula wizard. There will be four small data entry areas in the wizard for this formula and these represent the 4 parts in the brackets of the above example. Firstly between the brackets is the information you want to search for. In our example this will be the staff number and it is stored in cell A1 on the same spreadsheet as you are writing the formula.

Then there is the part “A1:B50”. This is the data range of the information that you will be searching. As per our example this will be the database that shows staff number and name. This is a 2 column database style spreadsheet with the staff number in column one and the name in column two. Then there is the value “2”. This tells the formula to return the value from column 2 of the external spreadsheet. So in our example the Name would be returned. Lastly there is the word “FALSE”. This tells Excel to look for an exact match when searching. If the word TRUE were placed here then Excel will return a value that is just close to the value you are searching for and is not accurate enough for this example.

So if all that was as clear as mud then let’s look at it in another way.

Place VLOOKUP formula in Spreadsheet 1 cell B2. You will search for the ID from Spreadsheet 1 cell A2 in Spreadsheet 2 range A1:B6. You want to return the name so this will be value 2 meaning the second column of spreadsheet 2 and you will then enter false as you want an accurate search. So your formula will be “=VLOOKUP(A2,spreadsheet2!A2:B6,2,FALSE)”. This is a simple example however but imagine if spreadsheet 1 was a thousand rows long with multiple values for each staff member and this is when this formula is very handy.

Now that you have your formula complete you should see the name in spreadsheet 1. Now to save writing the formula over and over again lets use a little trick feature of Excel. Select the field with your formula in it and you will see the bottom right corner of the cell will have a small black square. Click and hold your mouse cursor onto this black square and drag down to select all the cells that you want the correct name to appear. Now release the mouse button and the formula will now be in the new cells. What is so great about doing this is that Excel actually changes the formula to suit the new cells and everything will work fine.

Give these methods a try today and see if they save you some time. I will cover other methods and techniques in future articles.