Book Image

101 Excel 2013 Tips, Tricks and Timesavers

By : John Walkenbach
Book Image

101 Excel 2013 Tips, Tricks and Timesavers

By: John Walkenbach

Overview of this book

Excel is a popular program. Millions of people throughout the world use it on a regular basis. But it’s a safe bet that the vast majority of users have yet to discover some of the amazing things this product can do. 101 Excel 2013 Tips, Tricks, & Timesavers?is packed with information that you need to know in order to confidently and seamlessly master the challenges that come with using Excel! Excel 2013 is excellent, but there's lots to learn to truly excel at Excel! In this latest addition to his popular Mr. Spreadsheet's Bookshelf series, John Walkenbach, aka "Mr. Spreadsheet," shares new and exciting ways to accomplish and master all of your spreadsheet tasks. From taming the Ribbon bar to testing and tables, creating custom functions, and overcoming "impossible" charts, mixing nesting limits, and more,?101 Excel 2013 Tips, Tricks, & Timesavers?will save you time and help you avoid common spreadsheet stumbling blocks.
Table of Contents (10 chapters)
Free Chapter
1
Cover
2
Table of Contents
3
Title Page
4
Introduction

Tip 62: Identifying Excess Spaces

A common type of spreadsheet error involves something that you can’t even see: a space character. Consider the example shown in Figure 62-1. Cell B2 contains a formula that looks up the color name in cell B1 and returns the corresponding code from a table. The formula is

=VLOOKUP(B1,D2:E9,2,FALSE)

9781118642184-fg06201.tif

Figure 62-1: A simple lookup formula returns the code for a color entered in cell B1.

In Figure 62-2, the formula in cell B2 returns an error — indicating that Red wasn’t found in the table. Hundreds of thousands of Excel users have spent far too much time trying to figure out why this sort of thing doesn’t work. In this case, the answer is simple: Cell D7 doesn’t contain the word Red. Rather, it contains the word Red followed by a space. To Excel, these text strings are completely different.

9781118642184-fg06202.tif

Figure 62-2: The lookup formula can’t find the word Red in the table.

If your worksheet...