Contextures Blog - Page 121 of 139 - Excel tips and tutorials

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.

WordCover2007

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.

WordCover2007b

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.

WordCover2007d

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!)

WordCover2007c

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.

Excel Formatting Live Preview Font Selection

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: