Excel Scroll Wheel Shortcuts

I started on a Mac, where the mouse had one big button. Later, I moved to a PC, with a two button mouse. For the past few years I’ve used a mouse with two buttons and a scroll wheel.

Scroll Through Excel

At first, I completely ignored the scroll wheel, and found it faster to drag the scroll bar, than to move the scroll wheel. Now I use the wheel occasionally, but more often in Firefox or Word, than in Excel.

Use the Scroll Wheel With Outlines

AlexJ, who shared his technique to hide Excel rows with outlining a few days ago, also uses the scroll wheel to show and hide outline levels.

  1. Point to a cell within the outlined range
  2. Press the Shift key, then roll the scroll wheel down, to hide the outline levels for that cell.
  3. Or, press the Shift key, then roll the scroll wheel up, to show the outline levels for that cell.

Use the Scroll Wheel to Zoom

He also reminded me that you can use the scroll wheel to zoom in or out on the worksheet.

  1. Point to a cell within the outlined range
  2. Press the Ctrl key, then roll the scroll wheel down, to hide the outline levels for that cell.
  3. Or, press the Shift key, then roll the scroll wheel up, to show the outline levels for that cell.

Use the Scroll Wheel to Scroll

And yes, you can even use the scroll wheel to scroll. Really fast!

  1. Click the scroll wheel, and a four-headed arrow appears
  2. Move the mouse up, down, left or right, and a single arrow appears, pointing in the direction that the mouse is moving.
  3. The Excel sheet scrolls very quickly, increasing in speed as you move the single arrow toward the edge of the worksheet.
  4. To stop the scrolling, tap the scroll wheel, or click a mouse button.

Other Scroll Wheel Shortcuts?

Do you use the scroll wheel? Do you have any other shortcuts for it?

My Logitech mouse with its scroll wheel
My Logitech mouse with its scroll wheel

_________________________

Paste Values Shortcut For Excel

After I create formulas in Excel, sometimes I want to lock in the results of those formulas, so I use the Paste Values command.

For example, I might use the RAND function to create a set of random numbers. After creating them, I don’t want them to change every time the sheet calculates.

Continue reading “Paste Values Shortcut For Excel”

Do You Subscribe To Your Own Blog?

Ever since I started my blog, I’ve subscribed to the RSS feed. That lets me know if everything is working as expected with the feed, and I can investigate any problems as early as possible.

It’s also a good check when I upload an article with a delayed posting time. Sometimes I forget to set the posting time as AM, instead of PM, so the delay is much longer than I intended.

If I notice that the feed hasn’t been updated, I can go to the blog and change the publishing time.

Subscribe By Email Too

However, I also have an email subscription option on my blog, and hadn’t tested that.

Subscribe

I finally subscribed last week, which gave me the chance to see how the subscription process works (seemed pretty easy).

Now I get an email every morning, with the latest Contextures blog entry, and can follow up if there’s any problem with that delivery method.
___________

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.

Word2007Mag

In Excel 2003, click the Magnifier button on the toolbar, to turn it off.

Word2003Mag

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,

mark the rows and columns with an X
mark the rows and columns with an X

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

GoToSpecial

  • 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.

GoToSpecialConst

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.

HideColumns

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:

Format2007_01

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
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.

  1. On the Ribbon’s Page Layout tab, click the drop down arrow for Themes or for Fonts, then point to one of the options.
  2. The live preview affects all the cells that use the Calibri font, instead of just the selected cells.
  3. Click on one of the options and all the cells with Calibri font are changed.

Format2007_03

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.

Format2007_04

To make this worksheet fit the current theme, I’ll change the fonts to Calibri and Cambria:

  1. Click the Select all button at the top left of the worksheet, to select all the cells on this worksheet.
  2. Then, on the Ribbon’s Home tab, select the Calibri font.
  3. 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.

Format2007_05

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.

Macro dialog box has a Collapse Dialog button
Macro dialog box has a Collapse Dialog button

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.

Old Excel Macro Sheets
Old Excel 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.

Starting Cell on Macro Sheet
Starting Cell on Macro Sheet

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.

___________________