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.

Old-Style Menus In Excel 2007

Category: General / CommandBars & Menus | [Item URL]

Here's a quick way to display the old-style menu in Excel 2007 or later. Execute this VBA procedure, and you'll get a toolbar that contains the old Excel 2003 menu commands.

Sub MakeOldMenus()
   Dim cb As CommandBar
   Dim cbc As CommandBarControl
   Dim OldMenu As CommandBar

'  Delete it, if it exists
   On Error Resume Next
   Application.CommandBars("Old Menus").Delete
   On Error GoTo 0

'  Create an old-style toolbar
'  Set the last argument to False for a more compact menu
   Set OldMenu = Application.CommandBars.Add("Old Menus", , True)

'  Copy the controls from Excel's "Built-in Menus" shortcut menu
   With CommandBars("Built-in Menus")
     .Controls("&File").Copy OldMenu
     .Controls("&Edit").Copy OldMenu
     .Controls("&View").Copy OldMenu
     .Controls("&Insert").Copy OldMenu
     .Controls("F&ormat").Copy OldMenu
     .Controls("&Tools").Copy OldMenu
     .Controls("&Data").Copy OldMenu
     .Controls("&Window").Copy OldMenu
     .Controls("&Help").Copy OldMenu
   End With

'   Make it visible. It appears in the Add-Ins tab
   Application.CommandBars("Old Menus").Visible = True
End Sub

After you execute this macro, the new menu appears in the Add-Ins tab:

The menu isn't perfect. A few of the command don't work, and the list of recent files in the File menu just shows placeholders.

Getting A List Of Files Names - Another Method

Category: General | [Item URL]

In a previous tip, I described method of putting a list of file names from a directory into an Excel range. Today I discovered another method that's much simpler. It uses the File protocol in a Web browser.

• Note: This method does not work with Internet Explorer. I tested it with Firefox and Chrome, and it works fine with those browsers.

It's pretty simple:

1. Enter the directory path in your browser's address bar. For example, g:\music.
   
   The browser will convert this path into a URI (Uniform Resource Identifier). For example, g:\music is converted to file:///g:/music/. In addition, the files in that directory will be displayed as hyperlinks, and you can traverse the directory by clicking on directory names. Here's how the file listing appears in Firefox:
   
   
    
2. Press Ctrl+A to select all of the text, and then Ctrl+C to copy it.
    
3. Activate Excel, select a cell, and press Ctrl+V to paste.
   
   The filenames will be pasted as hyperlinks with wrapped text. You might prefer to use Paste Special (with the Text option) rather than Ctrl+V. You'll get a three-column range like this (copied from Firefox):
   
   

Google's Chrome browser is actually better for this task because it doesn't precede the file names with "File:" and it puts the date and time in a single cell.

This technique works with a single directory. If you need to get a list of files in multiple directories, you must repeat the procedure for each directory.

For more flexibility, use the Filename Lister utility in my PUP add-in.

Clearing The Text To Columns Parameters

Category: General / General VBA | [Item URL]

Have you ever imported a CSV file, or pasted data into a worksheet, only to find that Excel split up your data incorrectly? If so, the culprit is probably the Text To Columns feature. Here's Step 2 of the wizard that's used to split a single column of delimited data into multiple columns.

In this case, three delimiters are specified: tab, comma, and colon.

This is a very useful feature, and I use it a lot. The problem is, Excel tries to be helpful by remembering these settings for subsequent CSV imports and paste operations. Sometimes remembering these settings really is helpful, but often, it's not. To clear these delimiters, you must display this dialog box, clear the settings, and click Cancel.

If you're importing or pasting via a macro, there's no direct way for your macro to check these settings or reset them. The solution is to "fake" a text-to-columns operation. The procedure below does that, with the effect of clearing all of the settings from the Text To Columns dialog box (and making no changes to your workbook).

Sub ClearTextToColumns()
    On Error Resume Next
    If IsEmpty(Range("A1")) Then Range("A1") = "XYZZY"
    Range("A1").TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        OtherChar:=""
    If Range("A1") = "XYZZY" Then Range("A1") = ""
    If Err.Number <> 0 Then MsgBox Err.Description
End Sub

This macro assumes that a worksheet is active, and it's not protected. Note that the contents of cell A1 will not be modified because no operations are specified for the TextToColumns method.

If cell A1 is empty, the code inserts a temporary string (because the TextToColumns method will fail if the cell is empty). Before ending, the procedure deletes the temporary string.

Making An Exact Copy Of A Range Of Formulas, Take 2

Category: General / Formulas | [Item URL]

When you copy a range of formulas and paste them to a new location, Excel adjusts the cell references automatically. Most of the time, this is exactly what you want. Consider this simple formula:

=SUM(A2:A13)

If you copy this formula and paste it to the next column, the references are adjusted and the pasted formula is:

=SUM(B2:B13)

Making an exact copy of a single formula is easy: Press F2, highlight the formula, and press Ctrl+C to copy it as text. Then paste it to another cell. In some situations, however, you might need to make an exact copy of a range of formulas. In an older tip, I described a rather complicated way to do this. See Making An Exact Copy Of A Range Of Formulas.

Matthew D. Healy saw that tip and shared another method, which uses Notepad. Here's how it works:

1. Put Excel in formula view mode. The easiest way to do this is to press Ctrl+` (that character is a "backwards apostrophe," and is usually on the same key that has the ~ (tilde).
2. Select the range to copy.
3. Press Ctrl+C
4. Start Windows Notepad
5. Press Ctrl+V to past the copied data into Notepad
6. In Notepad, press Ctrl+A followed by Ctrl+C to copy the text
7. Activate Excel and activate the upper left cell where you want to paste the formulas. And, make sure that the sheet you are copying to is in formula view mode.
8. Press Ctrl+V to paste.
9. Press Ctrl+` to toggle out of formula view mode.

Note: If the paste operation back to Excel doesn't work correctly, chances are that you've used Excel's Text-to-Columns feature recently, and Excel is trying to be helpful by remembering how you last parsed your data. You need to fire up the Convert Text to Columns Wizard. Choose the Delimited option and click Next. Clear all of the Delimiter option checkmarks except Tab.

Create A Drop-Down List Of Possible Input Values

Category: General | [Item URL]

If you're creating a worksheet that will require user input and you want to minimize data entry errors, use Excel's data validation feature to add a drop-down list. The best part about it is that you don't have to write any macros.

Data validation is an excellent way to ensure that a cell entry is of the proper data type (text, number, or date) and within the proper numeric range. The drop-down list produced with the feature appears when a user clicks the cell.

Here's how to create a drop-down list:

1. Type the list of valid entries in a single column. If you like, you can hide this column (select Format, Column, Hide).
2. Select the cell or cells that will display the list of entries.
3. Choose Data, Validation, and select the Settings tab.
4. From the Allow drop-down list, select List.
5. In the Source box, enter a range address or a reference to the items that you entered in step 1.
6. Make sure the 'In-cell dropdown' box is selected.
7. Click OK.

If your list is short, you can skip step 1 and type the list entries directly in the Source box in step 5, separating items with a comma.

The Data Validation dialog box has two other tabs. Click Input Message to add a prompt that will appear when a user selects a cell. Click Error Alert to specify a custom error message if the user's entry is invalid.

The handy data validation feature suffers from one serious flaw. If you paste an entry into a cell that uses data validation, the validation isn't performed. And if you select that cell again, the drop-down list no longer appears. Fortunately, you can circumvent this problem by protecting the worksheet: Select Tools, Protection, Protect Sheet.

Excel 2007 Upgrade FAQ: Charts And Graphics

Category: General / Charts & Graphics | [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: Double-clicking on a chart element doesn't display the Format dialog box.

A: Yes, that handy mouse action no longer works. Right-click a chart element, and choose Format xxxxx from the shortcut menu. Or, press Ctrl+1.

Q. I find that it's very difficult to select some of the elements on a chart by clicking. Is there any easier way to select a particular chart element?

A. Use the arrow keys to cycle among the elements on a chart. Or, use the Chart Elements drop-down control in the Chart Tools / Layout / Current Selection group. Better yet, add the Chart Elements control to your QAT so it's always visible.

Q: How do I prevent a chart from changing its size when I resize the underlying rows or columns?

A: Select the chart, then click the dialog box launcher in the Chart Tools / Format / Size group to display the Size And Properties dialog box. Use the controls in the Properties tab to change the move and size properties.

Q: What's a dialog box launcher?

A: It's the tiny icon in some of the ribbon groups. The icon is displayed on the right side of the group name.

Q: I'm working with a chart, using the modeless Format dialog box. If I click in a cell, the Format dialog box inexplicably displays the title 'Format Shape,' and it has the focus. So the arrow keys move within the dialog box, not the worksheet.

A: Annoying, isn't it? When you're finished working with the Format dialog box, press Escape to close it and return the focus to your worksheet. Maybe this will be fixed in a future service pack.

Q: In a chart, how do I control plotting empty cells and plotting hidden cells?

A: Select the chart, then choose Chart Tools / Design / Data / Select Data. In the dialog box, click the button labeled Hidden and Empty Cells. 

Q. What happened to the chart fill patterns?

A. The fill patterns are no longer available in the user interface. However, they can be applied using a VBA macro.

Q: In previous versions, I could use the Increase and Decrease Decimal buttons to change the number of decimal places displayed in a chart trendline equation. Those buttons don't work in Excel 2007.

A: Click the trendline equation box and press Ctrl+1 to display the Format Trendline Label dialog box. Click the Number tab, select the Number category, and set the number of decimal places.

Q. How do I change the shape of a cell comment? In Excel 2003 I used Change Autoshape on the Drawing toolbar.

A. Right-click your QAT and choose Customize. Choose 'All Commands' and then select 'Change Shape'. Click Add to add the command to your QAT. Then you can use it to change the shape of a comment.

Q. A cell comment is a shape. Why can't I use the ribbon commands to format it?

A. Comment formatting is done via the Format Comment dialog box. Right-click the comment's border and choose Format Comment. Oddly, the color options available are not from the document theme.

Q: I can change the Height and Width of an object by entering values in the controls in the Format / Size group. How do I enter values for the Top and Left properties?

A: You don't. You can, however specify the Top and Left properties by using VBA.

Q: When I right-click a Shape, I see an option to "Set as Default Shape." This command seems to have no effect.

A: When you choose that command, Excel uses the *formatting* that you've applied to the shape as the default (fill, outline, effects). The command should probably read "Set as Default Shape Formatting."

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.

Excel 2007 Upgrade FAQ: General

Category: General | [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.

General

Q: I opened a workbook and my worksheets have only 65,536 rows.

A: Save it in an Excel 2007 format, close it, then re-open it.

Q: Where is the list with open workbooks?

A: Use View / Window / Switch Windows. Better yet, add this command it to your QAT. Right click on Switch Windows to add it so it is always one click to access it.

Q: Ctrl+A doesn't select all of the cells in my worksheet.

That's probably because the cell pointer is inside of a table. Press Ctrl+A three times to select all worksheet cells.

Q. The Custom Views command is grayed out.

A. That's probably because your workbook contains a table. Convert the table to a range, and then you can use Custom Views.

Q: What happened to the ability to create a pivot table using the Multiple Consolidation Ranges option?

A: That option still exists, but you need to add the 'PivotTable and PivotChart Wizard' command to the Quick Access toolbar (Found in 'Commands not in the Ribbon'), and use that command to start a new pivot table.

Q: I can't find the command to apply names to cell references in a formula. In Excel 2003, the command was Insert / Name / Apply.

A: The Define Name control in the Formulas / Defined Names groups is a drop-down. Click the down-arrow, and you'll see the Apply Names command.

Q: Why doesn't the F4 function key repeat all of my operations? 

A: I don't know. The very useful F4 is much less useful in Excel 2007.

Q. What happened to the ability to "speak" the cell contents?

A. To use those commands, you must customize your QAT. They are listed under 'Commands Not in the Ribbon'.

Q: Where is the Mail Recipient (body) option in Excel 2007?

A: You must customize your QAT. They are listed under 'Commands Not in the Ribbon'

Excel 2007 Upgrade FAQ: User Interface

Category: General | [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 open a file?

A: That round logo in the upper left corner is not just for cosmetics. It's called the Office Menu button, and it's used for a variety of purposes, including file operations. Click it.

Q. How do I get to the Excel Options dialog box?

A. Click the round Office Menu button, then click Excel Options.

Q. I clicked the round Office Menu button, but I don't see Excel Options.

A. Look at the very bottom. It's a button, not a menu item. And make sure you don't click Exit Excel by mistake.

Q: how do I hide/show the Ribbon?

A: Use Ctrl-F1 to toggle the display of the ribbon.

Q: Where did the xxxxxx command go?

A: There's a pretty good chance that it's on the ribbon somewhere. But there's also a chance that it's not on the ribbon. In the latter case, you can add the command to your QAT. Also, try using the old Excel 2003 hot keys (for example, Alt+T, I to display the Add-Ins dialog box).

Q. What's a QAT?

A. QAT is Quick Access Toolbar. This is the only user interface element that can be customized by the end user.

Q. Where are my old custom toolbars?

A. Click the Add-Ins tab and you'll see them.

Q. I can't make my old custom toolbars float.

A. No, you can't.

Q. How do I "tear off" the Fill Color icon so I can float it?

A. You can't.

Q. How do I get Help? The 'ask a question' box is gone.

A. Press F1, or click the little question mark icon in the title bar.

Q: How can I hide the status bar in Excel 2007?

A: You must use VBA to hide the status bar. Either of these statements does the job:

Application.DisplayStatusBar = False

Application.CommandBars("Status Bar").Visible = true

Q: How do I get back to the classic PivotTable wizard?

A: Press Alt+D+P

Q: Why can’t I drag fields in my PivotTable any more?

A: You can. Right click on the pivot table in question, and select PivotTable Options. Next, select the Display tab, and then place a check next to "Classic PivotTable Layout"

Page 1 of 3 pages
[Next page]