Category: Excel Tips and Tricks

Switching to Excel 2010? Start here…

Anneliese Wirth

 

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

 

 

There are lots of great reasons to step up to Excel 2010 from an earlier version. Some of the new features—sparklines, slicers, PowerPivot—were enough for me to take the plunge personally. If you’ve recently upgraded to Excel 2010, or are just about to do so, I want you to know that we have resources available that can help you through the transition.

If you’re upgrading from Excel 2007, it won’t take you long to figure out Excel 2010. For the highlights, I’d recommend scanning the What’s New in Excel 2010 article or watching the videos in the Make the Switch to Excel 2010 training course.

If you’re upgrading from Excel 2003, you’ll have a steeper learning curve because of the ribbon—the supersized toolbar that replaces the old menus and toolbars. I went through the menu-to-ribbon transition myself after I upgraded to Excel 2007 (when the ribbon was first introduced), so I well understand the challenges here.

5 resources for getting started

Use the following resources to figure out where to find things on the ribbon and get started with Excel 2010.

1. Excel 2010 Menu-to-Ribbon Guide An interactive guide to finding commands on the Excel 2010 ribbon. Click any item on a simulated version of an Excel 2003 menu or toolbar, and the guide reveals the item’s new location on the ribbon. This guide is particularly useful during the first few days after you upgrade.

Menu-to-ribbon guide

If you want to share this guide with others in your organization or use it when you’re working offline, feel free to grab the downloadable version here.

2. Excel 2010 Menu-to-Ribbon Mapping Workbook If the interactive guide feels like overkill, we have a simple workbook that maps each command in Excel 2003 to its corresponding location on the ribbon.

Menu to ribbon workbook

3. Excel 2010 Migration Guide This eight-page, printable guide is aimed specifically at Excel 2003 users who are moving to Excel 2010. We’ve reviewed feedback and questions from people who have already upgraded and turned that into a lightweight guide. Their pain, your gain!

Migration guide

4. Make the Switch to Excel 2010 An online training course consisting of 8 instructional videos, a self-assessment module, and a Quick Reference Card.

Make the switch to Excel 2010 training course

If you need to train other people to use Excel 2010, you can also download the Make the Switch to Excel 2010 course. This course is available in PowerPoint 2010 (.potx) format. Once you download it, you can add or remove slides, add your own branding elements, or otherwise make it your own.

5. Rev Up to Excel 2010: Upgraders Guide to Excel 2010 A fantastic, FREE 250-page book by Bill Jelen, Excel MVP and author of 32 books about Excel. (Yes, Bill is prolific, and I can’t think of a better person to learn from.) If you want a comprehensive walkthrough from a true Excel expert, this is the resource for you. If 250 pages is too much, just take a look at “Where to find commands in Excel 2010” on page 2 of this book, and you’ll be well on your way.

Rev Up to Excel 2010 book

For more information about Bill’s other books, visit The MrExcel Store.

If you have your own tips to share, or comments about any of these resources, I’d love to hear from you.

This post is originally from IANet Excel Feed
http://blogs.msdn.com/b/excel/archive/2010/11/16/switching-to-excel-2010-start-here.aspx

 

Create World Peace With an Excel Drop Down List

imageCan the sales staff and accounting staff ever work in peace? One group wants to see product descriptions, when entering orders. The other group thinks the descriptions clutter up the worksheet -- they just want the product codes. Try this data validation trick, and you might be nominated for next year's Nobel Peace Prize. (Results not guaranteed.)

Create Drop Down Lists

To make it easier for users to enter data in an Excel workbook, you can create drop down lists in the cells, by using Excel data validation.

DataValProdCode02

In this example the product list is in an Excel Table, and the ProductShow column is a named range -- ProdList. The ProdList range is used as the source for the drop down lists on the order entry sheet.

DataValProdCode01

Add the Magic

After the product is selected from the drop down list, the full description is automatically replaced by the product code. How does it happen? It's the magic of Excel VBA -- event code that runs when the worksheet is changed.

DataValProdCode03

The Excel VBA code uses the Match worksheet function to find the row number in the lookup list. It replaces the selected product description with the matching Product Code from that row in the lookup list.

DataValProdCode04 

Peace at last! Your co-workers will be happy that they don't have to memorize the product codes, and the accounting department will be grateful that they get the data in the format they need. 

Download the Sample File

To see the Excel VBA code that changes the product name to a product code, go to the Contextures website, and download the sample file: DV004: Data Validation Change. The example used here is the Excel 2007 version, and there is also an Excel 2003 version of the sample file.

Watch the Data Validation Video

Watch this video to see the steps for creating an Excel Table, naming a column in that table, then using that name when creating the data validation drop down list.

___________

This post is originally from IANet Excel Feed
http://blog.contextures.com/archives/2010/11/03/change-product-name-to-code-in-excel-drop-down-list/

 

Q & A: Things you really want to know about Excel IF function

image

 

Today’s post is brought to you by Frederique Klitgaard. Frederique is a writer on Office.com

 

To follow up on my previous post about the IF function, in which I showed you a very simple application of this formula, I want to address some questions about the use of this function that come up regularly.

As pointed out to me, the grading example isn’t the best one for the IF function, but I think it still helps illustrate the answers to the following questions.

You asked: Can the formula results be displayed in different colors?

Yes, you can do this by using conditional formatting. To distinguish between the different formula results in my worksheet, I created a conditional formatting rule that applies a different color to cells containing specific text.

image

For more information about using conditional formatting, see Add, change, find, or clear conditional formats.

You asked: Can the IF function be combined with other functions such as AND and OR?

Yes, to match multiple conditions, you can include the AND and/or OR functions so that you can specify additional criteria that result in values, whether true, or false. For example, if I include test scores for tests throughout the quarter, I can include the AND function to determine who has completed the quarter with flying colors, using the following formula:

IF(AND(B2+E2>=178), “Excellent!”, “”)

image

You asked: Can the IF function be used with text entries instead of values?

Yes, you can use text in all arguments of this function, as long as you include quotes around it. In the examples above, the formulas have returned text-based results, but you can also use text in the logical_test argument. For example, to use a star system in addition to grades, I can use the following formula:

IF(D2=”A”, “***”, IF(D2=”B”, “**”, IF(D2=”C”, “*”, “”)))

image

Note that two of the cells were left blank. By adding two quotes without any text in between (in the last argument of this formula), I specified that cells should be blank when the values don’t meet the criteria. And yes, I centered the stars after entering the formula, just for looks!

You asked: Can the IF function calculate percentages?

Yes, you can include calculations in the formula that result in percentages. For example, to use a point system, I could use the following formula:

=IF(B2>79, B2*1%, IF(B2>89%, B2*5%, “”))

image

Because commas are used as argument separators in a formula, be careful not to use them as decimal separators in the values you want to calculate.

For additional information about this function, please see our function reference article, IF function.

This post is originally from IANet Excel Feed
http://blogs.msdn.com/b/excel/archive/2010/10/26/q-amp-a-things-you-really-want-to-know-about-excel-if-function.aspx

 

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