Last week I described how I use X entries in hidden columns, so I can easily hide specific rows in an Excel worksheet. In the comments for that article, AlexJ mentioned that he uses outlining in his workbooks, to show and hide the rows and columns.
Edit a Word Document in Print Preview
While viewing a Word document in the Print Preview window, you might notice a typo, or something else that you’d like to fix quickly, before printing.
However, the pointer is a magnifying glass, and when you click on the page you just zoom in or out.
To edit the document, you can turn off the magnifier:
In Excel 2007, remove the check mark from Magnifier in the Ribbon, to turn it off.
In Excel 2003, click the Magnifier button on the toolbar, to turn it off.
After you turn off the Magnifier, the pointer will change to an I-beam, and you can click on the document and edit it.
Hide Marked Rows and Columns in Excel
In some of my Excel workbooks there are calculation rows or columns that are required for producing the end result, but users don’t need to see them.
I can manually hide these rows and columns, then unhide them if I need to check a calculation, or adjust a formula.
Mark Row and Columns
To make it easy to hide things, I mark the rows and columns with an X. In the screen shot below,
- the only entries in Row 1 are the X marks on the columns that should be hidden
- the only entries in Column A are the X marks on the rows that should be hidden.
Select the Marked Columns
First, follow these steps to select the marked columns.
- Click the row button for row 1, to select the entire row.
- On the Ribbon, click the Home button
- In the Editing group, click Find & Select, then click Go To Special
- In the Go To Special dialog box, click Constants
- Uncheck all the boxes except Text, then click OK
Now, only the cells that contain constant values (the “X” cells) are selected.
Hide the Marked Columns
Next, with the marked columns selected, follow these cells to hide the columns:
- Click the Format command on the Ribbon’s Home tab
- Click Hide & Unhide, then click Hide Columns.
Hide the Marked Rows
Use similar steps to hide the marked rows. Select column A and go to the Constants that are text, then use the Hide Rows command.
Automate the Steps
If you frequently hide and unhide the columns and rows, record a macro as your perform the steps.
Then, run that macro to automatically hide all the marked rows and columns.
Add a Cover to a Word 2007 Document
I’ve been using Microsoft Word since 1985, starting with Word for Mac and eventually moving to the Windows version for Word 6.0. As with other Office programs, some great new features have been added, and other features, that were working just fine, have been removed or changed. Don’t get me started on Word’s numbering.
Most of my Word documents are currently in Word 2003, but I’m experimenting with Word 2007, in case some of my clients decide to switch. One of the new features that I’ve discovered is the automatic Cover Sheet.
Insert a Cover Page
To automatically add a cover page to the front of the Word document:
- On Word’s Ribbon, click the Inert tab.
- Click the Cover Page command, to open the list of cover page options.
- Click on one of the options to select it.
Add Details to the Cover Page
On the cover page that’s inserted, there are fields that you can customize with your name, document title, and other information.
Click on a field (indicated with square brackets), and the name of the field will appear, such as Subtitle.
Type the text you want in that field.
Change the Cover Page
Many of the cover pages use fonts or colours from the documents Theme. You can:
- select an element and change its font, colour or other properties.
- select a different Theme, and those fonts and colours will replace the existing Theme’s fonts and colours.
- select a different cover page from the drop down list (maybe not this one!)
Remove the Cover Page
If you change your mind, and no longer want a cover page, click the Cover Page command on the Ribbon.
Then click Remove Current Cover Page.
The cover page is deleted immediately, with no confirmation message. However, you could click Undo to restore it.
Or, insert a cover page later, and the data that you entered previously (Title, Subtitle, etc.) should appear in the new cover.
___________________
Excel Formatting Live Preview
One of the new features in Excel 2007 is live preview when you select a different font or theme on the Ribbon.
For example, here’s how my worksheet looks now:
The default font for the cells is Calibri, and I’ve added bold in the first row.
I’m not sure if the Calibri font is the best choice, so I’d like to see how the data would look in a different font.
Select a Different Font
I select column H, then click the drop down arrow for Fonts on the Ribbon’s Home tab. The live preview shows the selected cells in each font as I point to it in the list.
If I find a font that I like better, I can click on it to change the font for the selected cells. None of the other cells are affected.
Select a Different Theme or Theme Font
In most cases, I wouldn’t just change the font in one column or a few cells. To keep the workbook from looking like a ransom note, I’d usually change the font for the entire workbook.
In Excel 2007 I can do this by choosing a new Theme or by selecting a different Theme Font.
- On the Ribbon’s Page Layout tab, click the drop down arrow for Themes or for Fonts, then point to one of the options.
- The live preview affects all the cells that use the Calibri font, instead of just the selected cells.
- Click on one of the options and all the cells with Calibri font are changed.
Formatting Converted Workbooks
Slowly, I’m converting some of my old Excel files, like the price list shown below, to Excel 2007 format.
When I use the Theme or Theme Fonts drop down list, the live preview doesn’t work. The data is still shown in Arial Narrow, which was the default font in Excel 2003.
To make this worksheet fit the current theme, I’ll change the fonts to Calibri and Cambria:
- Click the Select all button at the top left of the worksheet, to select all the cells on this worksheet.
- Then, on the Ribbon’s Home tab, select the Calibri font.
- To format the first row as Headings, select Row 1 and apply the Cambria font.
Now, any cells that are formatted with one of the Theme fonts will show a live preview when selecting a different Theme or Theme Font.
Merge Styles Tip
John Walkenbach added this formatting tip in the comments below:
“Here’s a way to quickly make an XLS workbook look like an 2007 workbook:
Open your XLS workbook, then press Ctrl+N to open a new workbook. Go back to your XLS window and activate the Merge Styles dialog (click the bottom of the scroll bar in the Styles group, then choose Merge Styles). Select the new workbook you opened, click OK, and confirm your choice.
All cells that have the “Normal” style will now be formatted in the default style for Excel 2007. In addition, the cells will respond to theme changes.”
_________________________
Compare Annual Data in Excel Clustered Stacked Chart
How can you create a chart from annual or monthly data, and make the results easy to understand? See how to compare annual data in Excel Clustered Stacked Chart — like a clustered column chart, but with stacked columns, instead of normal ones.
Continue reading “Compare Annual Data in Excel Clustered Stacked Chart”
Run an Excel Macro From a Cell Reference
Last week I wrote an article about hiding Excel macros, so they don’t appear in the macro list.
Collapse Dialog Button
While working on that article I noticed that the Macro dialog box has a Collapse Dialog button.
While the Macro name box is active, you can go to the workbook, select a sheet, and click on a cell.
Confusing Button
I couldn’t figure out why that button was there.
Even if I typed a macro name in a cell, then clicked on that cell, that macro didn’t run.
Refer to Macro Sheets
Dave Peterson suggested that the Collapse Dialog button might be related to Excel’s old style XLM macros.
And he was right – I finally found the confirmation in my old Excel 3.0 manual.
- It says “You can also enter in the Reference box the reference of the first cell of the command macro you want to run.”
The old style macros were written on Macro sheets.
Starting Cell on Macro Sheet
From the Macro dialog box, you could click on the starting cell of a macro, then click the Run button.
Compatibility Reasons?
I guess the button is still there for compatibility with those old XLM macros. Maybe the button will eventually disappear from the Macro dialog box.
___________________
Quickly Create Named Ranges in Excel
In an Excel workbook you might have a worksheet that contains several lists that you use as the source for data validation drop-down lists.
Worksheet List
For example, this worksheet has a list of countries, and lists of regions within those countries.
If each list has a heading, you can quickly create named ranges from the lists.
Create the First Named Range
To quickly create a named range, follow these steps:
- Select the heading and the items in the first list that you want to name.
- On the Excel Ribbon, click the Formulas tab
- In the Defined Names group, click Create from Selection
- (Note: In Excel 2003 and earlier versions, click Insert > Name > Create)
Create Names dialog box
- In the Create Names dialog box, add a check mark to Top row
- Next, remove any other check marks, then click OK.
Name the Remaining Ranges
To name the next range, follow these steps:
- First, select the range’s heading and items
- Next, on the keyboard, press the F4 key
- This is the Repeat Last Action keyboard shortcut
- Repeat for all the remaining ranges.
_______________
Remove Old Addresses From Outlook Drop Down List
In Outlook, as you start typing a name in the To box, a drop down list may appear, showing names of people to whom you’ve previously sent an email.
Here’s how you can remove old addresses from Outlook drop down list, in Outlook for Windows or Outlook.com.
Continue reading “Remove Old Addresses From Outlook Drop Down List”
Filter Pivot Table Source Data in Excel
When you’re analyzing data in an Excel pivot table, you might want to see the detail behind one of the numbers.
To extract the data, you can double-click a data cell and a new worksheet is created, with the related records.
This is a nice feature, but you’ll end up with extra sheets in your workbook, and will need to clean things up occasionally.
Filter the Source Data
If the pivot table source data is in the same workbook, you can use the following macro, written by Héctor Miguel Orozco Díaz. It filters the source data, based on the pivot items connected to the double-clicked cell.
For example, if you double-click the cell circled in screenshot below:
the source data is filtered for Class_A, Month_3, Store_1, Code_A cost.
This lets you focus on the detail records, without creating new worksheets.
Download the Sample File
Héctor’s code is shown on my Contextures site, and you can download the sample file to filter a pivot table’s source data.
There is also a sample file with a shorter version of the code.
________________