Category: Excel Tips and Tricks

VLOOKUP: No more #N/A

image

 

Today’s blog post is brought to you by Anneliese Wirth, who writes about Excel for Office.com.

 

To grow your skills with Excel, it’s helpful to familiarize yourself with a core set of functions, or predefined formulas that are built into Excel. One of my all-time favorites is VLOOKUP. If you’re new to VLOOKUP, we have a free, entry-level training course available on Office.com that can get you started.

If you’re familiar with VLOOKUP, you’ve no doubt seen this before:

image

#N/A errors really irritate me. First, it looks like something’s broken on my worksheet, and that’s just bad form. Second, #N/A can complicate life if you’re trying to use your VLOOKUP results in other formulas.

In Excel’s defense, these errors appear for a reason. Simply put, #N/A is Excel’s way of telling you that the thing you’re looking for doesn’t exist in your lookup table. Yes, it may look like there’s a perfectly wonderful match in your lookup table, but believe me, if you’re seeing #N/A, the match doesn’t exist (as far as the function is concerned, anyway).

With exact-match VLOOKUPs, #N/A errors often occur when:

· The thing I’m looking for is in my lookup table, but Excel doesn’t “see” the match. It’s tempting to fixate on my formula when troubleshooting, but the problem often stems from bad data in the lookup table. VLOOKUP always looks in the first column of the lookup table for a match to the lookup value you specified in your formula. When you’re troubleshooting #N/A errors, always focus your sights on that column. Scrub it carefully for misspellings, extra leading or trailing spaces, invisible characters and line breaks, numbers or dates that aren’t formatted correctly, and so on. This is especially important if you’re importing or copying data from another source, like a database or web site, because formatting oddities are common and can be hard to spot. You may have to dig for them by using TRIM, CLEAN, and other helper functions. (Don’t panic about that last part; it’s not hard, as Mike Girvin demonstrates in this informative video.)

· The thing I’m looking up really isn’t in my lookup table—for example, a particular employee name is missing. In this case, #N/A is doing me a favor by drawing my attention to what doesn’t actually exist. Here’s a trick: use VLOOKUP together with the IFERROR function—then, if VLOOKUP can’t find something, I can tell it to show me a message such as “Employee not found” instead of the confounding #N/A error.

You can learn a few simple troubleshooting techniques in this troubleshooting tips card. While the card doesn’t list every scenario that may result in an error, it covers some of the main ones.

image

If you have your own troubleshooting tips to share, or if you have a more lucid way to explain the intricacies of the #N/A error as it relates to VLOOKUP, I would love to hear from you—and so would everyone else. It takes a village to eradicate #N/A!

This post is originally from IANet Excel Feed
http://blogs.msdn.com/b/excel/archive/2010/10/06/vlookup-no-more-n-a.aspx

 

Excel 2007 Hi-Lo-Close Chart

This post is originally from About.com Spreadsheets
http://spreadsheets.about.com/b/2010/04/02/excel-2007-hi-lo-close-chart-2.htm

 

How to Create an Excel Pivot Table from Multiple Sheets

If Excel data is on different sheets, you can create a pivot table using multiple consolidation ranges. It’s better if the data is all on one sheet, but if you don’t have that option, multiple consolidation ranges will pull all the data into one pivot table.

In Excel 2003 you can open the PivotTable and PivotChart wizard by choosing Data | PivotTable and PivotChart Report. There’s no equivalent command on the Ribbon in Excel 2007, but you can press Alt+D, then type P to open the wizard.

The pivot table from multiple sheets isn’t as flexible as a regular pivot table — all the data fields use the same summary function, and there’s only one row field. However, while you’re setting up the pivot table you can create one or more page fields, and create labels for the data ranges.

Read the Instructions

There are written instructions, and details on the pivot table limitations, on the Contextures website: Excel Pivot Table Tutorial — Multiple Consolidation Ranges

Watch the Video

This video shows you the steps in Excel 2007, to create the pivot table from multiple sheets, and set up a page field.

____________

This post is originally from IANet Excel Feed
http://blog.contextures.com/archives/2010/04/16/how-to-create-an-excel-pivot-table-from-multiple-sheets/

 

Excel 2007 Pivot Table Tutorial

This post is originally from About.com Spreadsheets
http://spreadsheets.about.com/b/2010/04/09/excel-2007-pivot-table-tutorial-2.htm

 

Quickly Turn off Gridlines in Excel 2003 using Forms Toolbar [Excel Tips]

These days I rarely use Excel 2003. But when I do open the trusty old software, I always look for opportunities to improve my productivity with it. And I am pleasantly surprised to find a shorter and faster way to turn off grid lines on spreadsheets in Excel 2003. (I like my spreadsheets without any grid lines, like a clean slate).

I usually go to Tools > Options > View Tab and then uncheck the “gridlines” checkbox.

This takes about 5 clicks and a lot of mouse commute. So much that I often feel like taking a detour for a sip of coffee. But the new and simpler method I found yesterday is to use the forms tool bar (which is turned on by default in my comp). In forms toolbar you find the “toggle gridlines” button which can quickly turn on / off the grid lines. See below:

Quickly Turn off Gridlines in Excel 2003 using Forms Toolbar [Excel Tips]

Earlier on grid lines: Hide Gridlines in Excel
More Quick Excel Tips and Tricks

This post is originally from Pointy Haired Dilbert: Learn Excel Online - Chandoo.org
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/47T9HltwoUo/