Unlinking A Pivot Table From Its Source Data

Category: General / Formatting | [Item URL]

You may have a situation in which you need to send someone a pivot table summary report, but you don't want to include the original data. In other words, you want to "unlink" the pivot table from its data source.

Here's a nicely formatted pivot table in Excel 2010:

Excel doesn't have a command to unlink a pivot table, but it does have a flexible Paste Special command. Using that command, with the Value option, should do the job:

1. Select the pivot table cells and press Ctrl+C to copy the range.
2. Display the Paste Special dialog box. Pressing Alt+ES is my favorite method, and it works for all versions.
3. In the Paste Special dialog box, choose the Values option, and click OK.

The pivot table is unlinked, but if you use Excel 2007 or Excel 2010, the fancy pivot table style formatting is gone:

To get the formatting back, you need to perform two additional steps:

1. Display the Office Clipboard. In Excel 2007 and 2010, click the dialog box launcher icon in the bottom right corner of the Home - Clipboard group.
2. With the unlinked pivot selected, click the item on the Office Clipboard that corresponds to the pivot table copy operation. It will be the last item, unless you copied something else.

Now the pivot table is unlinked from its data source, yet retains all of its original formatting.

By the way, this is actually the first time I've ever done something useful with the Office Clipboard. It's actually fairly useless in Excel because it doesn't hold formulas -- just the values returned by formulas.

Text Effects In Text Boxes

Category: Formatting | [Item URL]

If you use Excel 2007 or later, you may not have discovered the "special" Font dialog box that's available when you're working with a Text Box or text in a Shape.

Add a text box and some text, select the text, right-click, and choose Font. (or press Ctrl+1). Excel displays this Font dialog box that is unlike its other Font dialog boxes.

It has quite a few options that aren't normally available -- and even more are in the Character Spacing tab. Here's an example of some unusual text effects.

Quantifying Color Choices

Category: Formatting / VBA Functions | [Item URL]

A companion file is available: Click here to download

I got lots of Excel workbooks via email. A significant number of them have some downright ugly color choices. Beauty is in the eye of the beholder, but there's no excuse for making color choices that result in illegible text.

The World Wide Web Consortium (W3C) has created some formulas that can help you determine if your foreground and background colors are legible: Ensure that foreground and background color combinations provide sufficient contrast when viewed by someone having color deficits or when viewed on a black and white screen.

The W3C presents two formulas, each of which returns a value:

• Color Brightness Difference: returns a value between 0 and 255
• Color Difference: Returns a value between 0 and 765

I converted their formulas into VBA functions, and formulas that use these functions are shown in Columns B and C:

To be an acceptable color combination, the Color Difference score should be 500 or greater, and the Brightness Difference score should be 125 or greater. I used conditional formatting to highlight values that exceed these minimums.

Column D has a simple formula that determines if both score meet the minimum requirement.

These formulas seem to work quite well. The color combination deemed Acceptable are all very legible. Bottom line: You can't go wrong with black text on a white background. Reserve the fancy colors for column headers, or for special areas of a worksheet that you want to be noticed.

Excel 2007 Upgrade FAQ: Formatting And Printing

Category: General / Formatting / Printing | [Item URL]

Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.

Q: How do I get my old workbook to use the new fonts?

A: Press Ctrl+N to create a blank workbook. Activate your old workbook and choose the Home tab. Click the very bottom of the vertical scrollbar in Styles gallery, and choose Merge Styles. In the Merge Styles dialog box double-click the new workbook you created with Ctrl+N. But this only works with cells that have not been formatted. For example, bold cells retain their old font.

Q. How do I get a print preview?

A. Try using the Page Layout view (icon on the right side of the status bar). Or, add the Print Preview button to your QAT.

Q: When I switch to a new document template, my worksheet no longer fits on a single page.

A: That's probably because the new theme uses different fonts. After applying the theme, use the Page Layout / Themes / Fonts control to select your original fonts to use with the new theme. Or, modify the font size for the Normal style. If page fitting is critical, you should choose the theme before you do much work on the document.

Q: How do I get rid of the annoying dotted-line page break display in Normal view mode?

A: Open the Excel Options dialog box, click the Advanced tab, scroll down and look for the 'Display options for this worksheet' section, and remove the checkmark from 'Show Page Breaks'.

Q: Can I add that 'Show Page Breaks' option to my QAT?

A: No. For some reason, this very useful command isn't available as a QAT icon.

Q: I changed the text in a cell to use Angle Clockwise orientation (in the Home / Alignment group). I can't find a way to get the orientation back to normal. There's no Horizontal Alignment option.

A: To change the cell back to normal, click the option that corresponds to the current orientation (that option is highlighted). Or, choose the Format Cell Alignment option and make the change in the Format Cells dialog box.

Q. I'm trying to apply a table style to a table, but it has no effect.

That's probably because the table cells were formatted manually. Remove the old cell background colors, and applying a style should work.

Q: I thought Office 2007 was supposed to support PDF output. I can't find the command.

A: You need to download a free add-in from Microsoft. Blame the Adobe attorneys. After you download and install the add-in, click the Office Menu button and then select Save As / PDF or XPS.

Comparing Two Lists With Conditional Formatting

Category: Formatting / Formulas | [Item URL]

Excel's Conditional Formatting feature has many uses. Suppose you need to compare two lists, and identify the items that are different. The figure below shows an example. These lists happen to contain text, but this technique also works with numeric data.

The first list is in A2:B19, and this range is named OldList. The second list is in D2:E19, and the range is named NewList. The ranges were named using the Insert - Name - Define command. Naming the ranges is not necessary, but it makes them easier to work with.

As you can see, items in OldList that do not appear in NewList are highlighted with a yellow background. Items in NewList that do not appear in OldList are highlighted with a green background. These colors are the result of Conditional Formatting.

How to do it

1. Start by selecting the OldList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:

   =COUNTIF(NewList,A2)=0

5. Click the Format button and specify the formatting to apply when the condition is true (a yellow background in this example).
6. Click OK

The cells in the NewList range will use a similar conditional formatting formula.

1. Select the NewList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:

   =COUNTIF(OldList,D2)=0

5. Click the Format button and specify the formatting to apply when the condition is true (a green background in this example).
6. Click OK

Both of these conditional formatting formulas use the COUNTIF function. This function counts the number of times a particular value appears in a range. If the formula returns 0, it means that the item does not appear in the range. Therefore, the conditional formatting kicks in and the cell's background color is changed.

The cell reference in the COUNTIF function should always be the upper left cell of the selected range.

Alternate Row Shading Using Conditional Formatting

Category: Formatting | [Item URL]

One way to make your data legible is to apply cell shading to every other row in a range. Excel's Conditional Formatting feature (available in Excel or later) makes this a simple task.

1. Select the range that you want to format
2. Choose Format, Conditional Formatting
3. In the Conditional Formatting dialog box, select Formula Is from the drop-down list, and enter this formula:

   =MOD(ROW(),2)=0

4. Click the Format button, select the Patterns tab, and specify a color for the shaded rows.
5. Click OK twice to return to your worksheet.

The best part is that the row shading is dynamic. You'll find that the row shading persists even if you insert or delete rows within the original range.

Duplicate Repeated Entries In A List

Category: Formatting | [Item URL]

You've probably seen an Excel worksheet (like the sheet on the left, below) in which one entry in column A applies to several rows of data. Sort such a list and you get a real mess, because rows with empty cells in the sort column move to the top or bottom (depending on the sort order).

When a list is small, you can enter the missing cell values manually. But if your database is huge, you need a better way of filling in those cell values. Here's how:

1. Select the range (A3:A14 in the example above)
2. Press Ctrl-G to get the Go To dialog box.
3. In the Go To dialog box, click Special.
4. Select the Blanks option.
5. Type = followed by the address of the first cell with an entry in the column (=A3 in the example above), and press Ctrl-Enter.
6. Reselect the range and choose Edit, Copy.
7. Then select Edit, Paste Special, choose the Values option, and click OK.

The missing entries will be filled in, as in the sheet on the right in the above figure.

Removing Or Avoiding Automatic Hyperlinks

Category: Formatting / VBA Functions | [Item URL]

You may have discovered that Excel 2000 (and later versions) supports automatic cell hyperlinks. Whenever you type something that resembles a URL or an e-mail address into a cell, this feature automatically converts the text into a clickable hyperlink. But what if you don't want to create a hyperlink?

If you use Excel 2000, you're out of luck. There is no way to turn this potentially annoying feature off. But you can, however, override it. If Excel creates a hyperlink from your cell entry, click the Undo button (or press Ctrl-Z) to restore the cell's contents to normal text. Or, you can precede the cell entry with an apostrophe.

Note: If you're using Excel 2002 or later, you can turn automatic hyperlinks on or off in the AutoCorrect dialog box

Surprisingly, Excel doesn't provide a direct way to remove all hyperlinks on a worksheet. In fact, the only way to accomplish this is one cell at a time: Activate the cell that contains the hyperlink, right-click, and then select Hyperlink, Remove Hyperlink. Unfortunately, this command is available only when you have selected a single cell. To quickly deactivate all hyperlinks, you need to use a macro.

To create the macro, press Alt-F11 to activate the Visual Basic Editor, select Insert, Module to insert a new VBA module into your project, and then enter the following code:

Sub ZapHyperlinks() 
    Cells.Hyperlinks.Delete
End Sub

When you execute the ZapHyperlinks macro, all hyperlinks on the active sheet are deleted and replaced with normal text.

Working With Fractions

Category: Formatting | [Item URL]

Some types of data, such as stock market quotes, normally display as fractions, not decimals. To enter a fraction in Excel, type the whole number (or integer) followed by a space, and then type the fraction, using a slash (for example, 5/8). If you type only a fraction, Excel may interpret it as a date (so it might read 5/8 as May 8). To avoid this mistranslation, enter 0, a space, and then the fraction.

When you enter a fractional value, Excel automatically applies a fraction number format that reduces it to the smallest possible denominator. For example, if you enter 16 2/8, Excel displays the number as 16 1/4. In some cases, however, you'll want the fractions to use a common denominator. For example, you might want the value 16 2/8 to be shown as 16 4/16. To obtain this result, select your cells and choose Format, Cells. Then select the Number tab and choose Fraction from the Category list. Finally, select the desired number format from the Type list.

The worksheet below shows some examples of numbers expressed as fractions. Column B shows the numbers produced using Excel's default formatting. Column C has the same values formatted as 16ths.

You can also express fractional data using a decimal point. For instance, the number 9 4/16 could appear as 9.04. Here, the digits to the right of the decimal represent 16ths. To display values in this format, use Excel's DOLLARFR() function. It's available only when the Analysis ToolPak is installed (select Tools, Add-ins to install it). The DOLLARFR() function takes two arguments: the number and an integer for the denominator. The formula =DOLLARFR(9.25,16), for example, returns 9.04.

This function is also useful for nondollar data. So, if you work with feet and inches, you can represent 11.5 feet as 11.06 (11 feet, 6 inches) by using this formula:

 =DOLLARFR(11.5,12)

The value will then appear as "11 [feet] 6 [inches]."

The DOLLARFR() function is for display only. You can't use the value it returns in other calculations or in charts. To perform calculations on such values, reconvert them into decimal values by using the DOLLARDE() function (also part of the Analysis ToolPak).

Using Conditional Formatting

Category: Formatting | [Item URL]

Excel's conditional formatting feature (available in Excel 97 or later) offers an easy way to apply special formatting to cells if a particular condition is met. This feature is even more useful when you understand how to use a formula in your conditional formatting specification.

The worksheet below shows student grades on two tests. Conditional formatting highlights students who scored higher on the second test. This formatting is dynamic; if you change the test scores, the formatting adjusts automatically.

To apply conditional formatting, select range A2:C15 and choose Format, Conditional Formatting. The Conditional Formatting dialog box will appear with two input boxes. In the first box, choose Formula Is, pressTab, and enter the following formula:

=$C2>$B2

Click Format and choose a format to distinguish the cells (the example uses background shading). Click OK, and the formatting will be applied.

The conditional formatting formula is evaluated for each cell in the range. The trick here is to use mixed cell references (the column references are absolute, but the row references are relative). To see how this works, activate any cell within the range and choose Format, Conditional Formatting so you can examine the conditional formatting formula for that cell. You'll find that cell A7, for example, uses this formula:

=$C7>$B7

Page 1 of 2 pages
[Next page]