Blog Archive

Using VLOOKUP to extract data

Vlookup

As its Halloween this weekend we thought we would dedicate this week’s blog post to something that people find particularly scary! Something that sends chills down your spine! OK, I am being a little dramatic but it is fair to say that when it comes to Excel, Formulas are the thing that tend to strike fear into the heart of most.

Fortunately, formulas have never been easier or more human in Excel which means you don’t have to be a mathematical genius to understand them. Today we are going to take a look at ‘Using VLOOKUP to extract data’, a really common and useful formula which will help you cross-reference spreadsheets and save a lot of time.

Using VLOOKUP to extract data

VLOOKUP is a great time saving formula.

Let’s set the scene…

In this spreadsheet, I have a table (Table 1) that lists Items in column C and their associated unique Item Number or ID in column A. When a customer orders an item, I log the Item Number and the Customer Name in a separate spreadsheet (Table 2). However, the Item Name is not listed. I am going to use VLOOKUP to quickly and easily reference the Item ID and pull back the Item Name.

vlookup8

STEP ONE: Name your lookup range

This step is optional. You do not have to name your lookup range but I find that this is the best way of ensuring that your data pulls through correctly and it means that you don’t have to mess around making cell references absolute (fixed).

  • Highlight the lookup table (the table that contains the data you are pulling through, in this case Table 1)
  • Click in the Name box and type a name for your table i.e. PRODUCTS.
  • Press Enter
vlookup5

NOTE: It is worth noting that table name cannot have spaces. If I wanted to name it ‘PRODUCT KEY’ I would need to use the format ‘PRODUCTKEY’ or ‘PRODUCT_KEY’.

STEP TWO – Create the VLOOKUP Formula (the scary part)

  • Click in the cell where you want the value to appear (C12)
  • Click on the Formulas ribbon
  • Click Insert Function
vlookup7
  • Enter the criteria into the fields

Lookup_value – The lookup value is the information that you want to reference. It is normally a piece of information that is in both tables. Excel needs a reference point and in this example we are using the Item ID.

  • Click the cell that contains the first Item ID (A12)

Table_array – The table array is the table Excel is referencing. Excel needs to know which table to find the Item ID. This is where naming your table in Step 1 comes in useful.

  • Press F3

Any named tables will appear in a list. Select the table ‘PRODUCT’. If you did not name your table, you would need to highlight the cells instead.

Col_index_num – This is the column in the lookup table that contains the data you want to pull through. In our example, we need the ITEM NAME. This information is contained in Column 3.

  • Enter ‘3’

Range_lookup – This field can be left blank or will contain the value ‘TRUE’ or ‘FALSE’. Which one you use depends on how your table is sorted and the data you want to pull through. If you have your tables sorted in Ascending order, you can leave this field blank or use TRUE. TRUE also searches for an exact match.  In our example, my data is not sorted and so I need to enter FALSE. In my experience, most data that you are dealing with is not sorted nice and neatly and so FALSE is the more common value to go in here.

  • Enter FALSE
vlookup3
  • Click OK

If the formula has been added correctly, you should now see the item name in cell C12. Excel has essentially looked in the lookup table for the Item ID and then pulled through the corresponding Item Name.

Once you have used the helpful dialogue box a few times and you are comfortable with the format, you can just type the formula into the cell. E.g. =VLOOKUP(C12,PRODUCTS,3,FALSE)

vlookup6

You could repeat the above steps to complete the rest of the table but that can be quite tedious especially if you are dealing with a lot of data. To quickly copy the formula down, you can use the AutoFill handle in the bottom right-hand corner.

  • Drag the handle down OR double-click on the handle
vlookup9

TIP: If you get an unexpected result when using the auto-fill handle, such as the wrong data being pulled through or the same Item Name repeated, it is worth checking that you do not have your Auto-fill settings set to manual.

  • Click the Formulas ribbon
  • Click Calculation Options
  • Set to Automatic

So as you can see, Excel formulas do not have to be a scary prospect.

happy-halloween

Excel – Paste into Visible Cells Only

Microsoft Excel

 

Sometimes Microsoft Excel is just too helpful.  Like American shop assistants to an English shopper (I’m not being xenophobic, I’m just not used to lots of people asking if I want help finding things (try shopping in Reading on a Saturday afternoon).

Anyone who has tried to paste data in a filtered Excel spreadsheet knows this.  Excel will also paste the data into the hidden (filtered out) cells.  It obviously thinks it’s being helpful but it’s really not.

There have been many suggested workarounds but it comes down to using Paste into Excel Visible Fields or a Paste into Excel Visible fields only with code.

There are two solutions that we use.  If you’re running Excel 2013 or above, you can utilise Flashfill.  For earlier versions, you might be able to use the Fill function..

Let’s look at Fill first.  Here is our example sheet:

Excel Paste into Visible cells only

 

A nice simple table with numbers in column B, whether they are odd or even in column A and the square of the number in column C.  What I would like to do is filter on odd numbers (because I am a little odd), copy the square and paste those into the new column D.

Let’s try to do that in the most obvious way and see what happens.

Filter the table to show only odd numbers.  Select all the squares in column C and copy.

Excel Paste into Visible cells only

 

Click in cell D2, right-click and select Paste Values.  But wait!  Only half of the values are shown.  That’s because Excel is being over-helpful and pasting into the hidden, filtered-out rows as well as the visible rows.  It would be lovely if there was a ‘Paste Values into Visible Cells’ option but you’ve already spent an hour searching the internet to discover there just isn’t.

 

Excel Paste into Visible cells only

 

If we clear the filter, we can see exactly that behaviour.  Our five selected cells have been pasted into the interim rows.

Excel Paste into Visible cells only

 

 

 

 

 

 

Using Fill to Successfully Paste into Visible Cells Only

Let’s go back to our filtered table.  However this time select the cells and the column next to it.

Excel Paste into Visible cells only

Now go up to the ribbon (Home tab) and click Fill and Fill Right.  Obviously if your destination column is to the left then feel free to hit Fill Left instead.

Excel Paste into Visible cells only

 

And voila, unlike the previous attempt, we are seeing all five desired values.

 

Excel Paste into Visible cells only

 

And just to be sure, let’s clear the filter condition to make sure nothing has been copied into the hidden rows.

 

Excel Paste into Visible cells only

 

Bingo.  We have our desired outcome.  Obviously this only works in the same sheet and if your columns are adjacent left or right to the cells you wish to copy.  If there are columns in between, you can hide those columns and this method will still work; Excel doesn’t paste into hidden columns in the same way it pastes into hidden rows.  In the screenshot below, I moved column A between the source column and the destination.  I filtered on Odd numbers in the same way, then hid column C.  Select Columns B and D and use the Fill Right method and as the screenshot works, once I unhide column C and clear the filter, everything still works out ok.

 

Excel Paste into Visible cells only

 

Flashfill Will Only Update Visible Cells

In Excel 2013, we have the lovely Flashfill feature which we blogged about previously.  Flashfill will also help but it’s not relevant for Excel versions earlier than 2013 (or Office 365 ProPlus if you ‘re in the cloud).

You can filter on odd numbers, type 1 in the first cell of the destination column, type 9 in the next cell down, hit Enter and then CTRL + E to force Flashfill to take over.  All the desired cells will be copied and if you clear the filter condition, you’ll see that the hidden rows haven’t been touched.  This is why we love Flashfill!

If you’d like more hints and tips, subscribe to our newsletter or better still, sign-up for one of our courses.

 


Office Demos to Make Customers Cry

User jumping for joy

We don’t mean make them cry in a bad way.  Let me set the scene.  Microsoft have between 1-1.5 billion users of Office applications worldwide.  The Office developers work really hard to make the applications intuitive so users can just get on and use them without needing to take a day’s training course in Outlook or Excel.  However there’s a downside.  Users tend to use new versions of Office in the same way they used the previous version so they don’t really see any immediate improvement and that’s part of the reason we see customer inertia; no-one takes the time to point out some of the fantastic things they can now do.

With new Office versions, there’s really two buckets of goodness – firstly, a bunch of new features and secondly, improvements.  The improvements might include a single button which does what used to take the user 5 minutes and twenty clicks in the previous version.  Or perhaps an improvement will stop users swearing so much!  New features are great too but I find they’re best learnt organically; take a couple of minutes each day to look at a new feature and if it will help, then practice it but don’t try to learn every new trick in Office from day 1.

So we’re starting a series of very quick demos that you can easily emulate either to learn from or to repeat if you have customers that use Office.  Welcome to number 1.

Excel Flashfill

1. Download and open the sample Excel file.  Once you’ve downloaded that, pat yourself on the back for ensuring you had up-to-date anti-malware installed and you can confidently download files from the great unwashed Internet.

2. You’ll see a simple table with five columns.  Column 2 (Data) holds concatenated strings which we need to split out into the correct columns, so the Manager column will hold names such as John, Jenny and Bill and the Category column will hold the type of expense such as Advertising, Events and Digital Marketing.  Some Excel users will look in the help for a string function that will work.  Others will look in the ribbon and perhaps try out Text to Columns.  Most users will see there’s only twenty table rows and just type or copy and paste.  That’s how errors occur; one Bill might be typed with three l’s in it; we won’t notice and the reports will be wrong.

Excel flashfill demo step 1

 

Excel flashfill3. Click in cell D4 and type John.  Hit ENTER to go to cell D5 and type Jenny.  As soon as you’ve typed Jen you should see Excel volunteer the rest of the rows.  It’s as if a little Excel intern has been watching and is now stepping into take over your work.  How lovely.  Hit enter to accept the Flashfill.

4. Now click in cell E5, type Events and hit ENTER.  Hit CTRL+E to force Flashfill to evaluate the pattern at this point and you should see the suggestions.  Press ENTER to accept.

5. Flashfill can do smarter things too.  Click in cell F5 and type John heads up events.  Then hit enter to move down into cell F6 and start typing Jenny heads up advertising.  Flashfill will complete the rows for you, including respect to your capitalisation of the category name (keeping it lowercase).

Customers have asked at what point Flashfill is checking.  You can be reassured that this is nothing scary.  We’re all happy with autofill; type in 1 in a cell and 2 underneath and you can have Excel continue the numbering pattern t0 3, 4, 5, etc.  Flashfill is just an extension of autofill, that’s all.

Flashfill step 3Sometimes Flashfill will not guess correctly first time in which case you ignore the greyed out suggestions and keep typing rows.  Your little Excel intern will keep watching and at some point will guess the correct pattern at which point you just hit ENTER to save lots of typing!  Sheet 2 includes an example of this.  Click in cell C2 and type Adriana from Germany.  Go to cell C3 and type Billy from United Kingdom.  Flashfill is suggesting incorrect matches at this point but you just ignore it and keep typing in the rows.  You will need to get down to row 5 (Damien from Germany) before the suggested pattern is correct and at this point you can accept it by hitting ENTER.

Flashfill is available as a Ribbon command too (on the Data tab) and is a feature of Microsoft Excel 2013 and Office 365 ProPlus.

Subscribe to our Twitter feed for more hints and tips, take one of our Office courses or let us run an Office Buzz Day for your users.


Excel Chart Conditional Formatting

Craig from Lloyds Banking Group asked if Excel’s conditional formatting can be applied to charts.  I’d like to provide the answer in the form of a news sandwich (good news, bad news, good news).

It’s a nice sunny day as I’m writing this article.

No, you can’t I’m afraid, Craig.

You can create a pseudo conditionally formatted chart.

Excel Conditional Formatting – a Recap

You have a list of customer accounts and due dates in Excel.  You want to highlight customers who have due dates within the next week in orange and you want to highlight customers who have due dates within 2 days in red.  That makes your list more readable and Excel more human.  Back in the old days of Excel 2003 you’d need to do some functions to achieve this and you were limited to 3 conditions per cell.  Conditional Formatting was introduced in Excel 2007 and has improved ever since.  It allows the user to create some nifty formatting based on cell values with just a few clicks.

Excel Conditional Formatting Date Example

 

You can also choose conditions based on averages, top %, text occurring, duplicate values and many more.  You can also use icons such as red, amber and green or perhaps smiley faces.  And also you can choose data bars which allow you to visualise the value according to magnitude.

Excel 2013 Data Bars screenshot

 

So Craig’s question asked if one can apply this Conditional Formatting feature to bars or segments within Excel charts?

And if you recall, the answer is no but you can create a similar experience by categorising the data into distinct, banded columns and charting those.  Let’s take a table (below) with data series and values which we want to chart.

Excel table ready for conditional formatting

 

A default chart will represent each column in the same colour (below).

Excel chart without conditional formatting

We want each bar coloured according to the band of values it falls into.  To do this we can create the bands we want to chart as additional columns in the table (below).  In rows 1 and 2 we list the boundaries of each band (e.g. 0 and 500).   Each value cell (G4-M16) is then calculated using a formula =IF(AND(G$1<$F4,$F4<=G$2),$F4,””).  For each cell, the Amount (F4-F16) is compared to the boundaries.  If the value falls within a boundary then value is written into that cell, otherwise it’s left blank.  The result is our mapped table below.

Chart with additional columns for conditional formatting

 

Now if we click on the default chart we created earlier, Excel highlights the table data used for the chart (below).

Excel data used for the default chart

 

Drag the red and blue boxes to select our new data (below).

New data selection for chart

 

And voila, we have our chart with conditionally coloured bands and a key to those bands (below).  Note, in true Blue Peter fashion I’ve adjusted the colours for each band and also adjusted the gap and spacing of the bars (simply double-click on any of the bars and you’ll have access to those settings).  This is a rough and ready but effective solution.  It won’t be the most efficient for large volumes of data because we are running the calculations for every cell repeatedly.  We could use VBA for a more efficient method but that’s another blog entry for the future…

Excel chart with conditional formatting