Mr. Spreadsheet’s Favorite Excel 2010 Tips & Tricks

When you type something into a cell in a worksheet, Excel goes to work and makes decisions regarding the type of data you entered. If you understand how Excel interprets the data you enter into a cell, you can save yourself a bit of frustration when Excel’s decision about what you entered doesn’t correspond to what you had in mind.

A common type of worksheet contains a table of data with descriptive headings in the first row. But, as you scroll down the worksheet, the first row scrolls off the screen so you can no longer see the column descriptions. A feature introduced in Excel 2007 eliminated this age-old spreadsheet problem — but it only works if your data is in the form of a table.

Excel is capable of speaking to you. You can have this feature read back a specific range of cells, or you can set it up so that it reads the data as you enter it, but it’s not the easiest tool to find. Fortunately, the feature is still available — you just need to spend a few minutes to make it available. 

Normally, Excel watches you type, and if it looks at all like you’re typing an e-mail address or a Web URL, the entry is converted into a hyperlink. Sometimes that capability is helpful, but sometimes it’s not. Learn how to control what you type so that it doesn't automatically turn into a hyperlink with this video.

Most Excel users probably believe that a VBA macro is required in order to display a drop-down list in a cell. But it’s not. You can easily display a drop-down list in a cell — no macros required.

When you enter a date into a cell, Excel formats the date by using the system short-date format. You can change this format by opening the Windows Control Panel and selecting Regional Settings to help you make your data look the way you want.

Conditional formatting enables you to apply cell formatting selectively and automatically, based on the contents of the cells. Microsoft significantly enhanced conditional formatting to include several visualization features: data bars, color scales, and icon sets.

Merging cells is a simple concept: Join two or more cells to create a larger single cell. See how using this feature can enhance the appearance of your worksheet.

Excel cell formatting isn't an all-or-none proposition. In some cases, you might find it helpful to be able to format individual characters within a cell.

If you need to enter a lot of text into a cell, you have two choices – allow the text to spill over into the adjacent cell or allow the text to wrap. This video will show you how you can use the Wrap Text feature to keep your spreadsheet neat.

One of the most underused features in Excel has been named styles. Named styles make it very easy to apply a set of predefined formatting options to a cell or range. In addition to saving time, using named styles helps to ensure a consistent look across your worksheets.

If you have a range of cells that contain formulas, you can quickly convert these cells to values only (that is, the result of each formula), and this common operation is easier than you may think.

Mixed cell references aren’t used often, but as you see in this video, in some situations, using mixed references makes your job much easier.

If you have a range of cells that contain formulas, you can quickly convert these cells to values only, which will be demonstrated in this video.

Often, you have a range of cells containing data that must be transformed in some way. This video describes how to perform addition, subtraction, multiplication, and division on a range of values without using any formulas.

This video illustrates the versatile method of transforming data by using temporary formulas.

A common type of spreadsheet model contains input cells (which are changed by the user) and formula cells that work with those input cells. If you want to delete all the values in the input cells but keep the formulas intact, here’s a simple way to do it.

Sometimes a formula returns an error, such as #REF! or #DIV/0!. Usually, you want to know when a formula error occurs, but in some cases you might prefer to simply avoid displaying the error messages.

After learning how to provide a meaningful name to a cell or range, you can use those defined names in your formulas, but there’s a trick to understanding the right way to use names in Excel.

This video describes a useful technique that can remove some clutter from your worksheets: named constants.

According to Excel 2010, the world began on January 1, 1900. If you work with historical information, you may have noticed that Excel doesn’t recognize pre-1900 dates. To be able to sort by dates that precede 1900, follow the instructions in this video. 

Discover the DATEDIF function, which calculates the difference between two dates and expresses the result in terms of months, days, or years.

The Excel pivot table feature is incredibly powerful, and you can often create pivot tables in lieu of creating formulas. This video describes a simple problem and its various solutions.

Suppose that you update a worksheet frequently by adding new data to its columns. You might need a way to reference the last value in a particular column, and this video shows you how. 

The VLOOKUP and HLOOKUP functions are useful if you need to return a value from a table (in a range) by looking up another value.

A two-way lookup identifies the value at the intersection of a column and a row. This tip describes two methods to perform a two-way lookup.

You know the distance from New York to London in miles, but your European office needs the numbers in kilometers. What’s the conversion factor? The Excel CONVERT function can help.

An Excel chart consists of a number of different elements. For example, all charts contain a plot area and at least one data series. A chart can also contain elements such as a chart title, axes, data labels, and so on. To work with a particular chart element, you need to select it — something that is often easier said than done.

Learn to easily incorporate a pattern, texture, or graphical file for elements in your chart with this video.

Oddly, Excel doesn't provide a direct way to convert a chart into a stand-alone graphics file, such as a GIF or PNG file. In this video, you’ll learn different methods for saving an Excel chart as a graphics file.

If you need to save a range as a graphic image, the best approach is to use one of several screen capture programs that are available. This type of software makes it easy to capture complete windows, or just a portion of a window. 

Normally, an Excel chart uses data stored in a range. Change the data, and the chart is updated automatically. Usually, that’s a good thing. But sometimes you want to “unlink” the chart from its data range to produce a static chart — a snapshot of a chart that never changes.

When you copy a chart as a picture, it opens the door to some creative effects that are possible with pictures, but not with charts, which you’ll discover in this video.

One of the new features in Excel 2010 is Sparklines graphics. A Sparkline is a small chart displayed in a single cell. A Sparkline lets you quickly spot time-based trends or variations in data. Because they

are so compact, Sparklines are often used in a group.

Excel lets you place quite a few different types of objects on a worksheet: charts, shapes, clip art, and SmartArt, for example. To work with an object, you must select it. The easiest way to select a single object is to click it. What if you want to select multiple objects? Watch this video to find out.

This video describes how to use tables to eliminate common errors and make common tasks much easier to do while making the results much better looking. 

If you need to insert a text file into a specific range in a worksheet, you might think that your only choice is to import the text into a new workbook and then to copy the data and paste it to the range where you want it to appear. However, you can do it in a more direct way.

This video describes three ways to capture data contained on a Web page – pasting a static copy of the information, creating a refreshable link, or opening the page directly within Excel.

If you want your printout to display one or more fixed rows at the top and/or one or more fixed columns along the left, learn how to do it in this video.