Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Zooming with the Keyboard.

Zooming With the Keyboard

by Allen Wyatt
(last updated May 5, 2017)

10

For most purposes, Excel allows you to issue commands and perform functions by using either the mouse or the keyboard. Unfortunately, Excel does not provide "equal access" for all commands. For instance, it is relatively easy to zoom in or out using the mouse, but there is no easy way to do it using the keyboard (other than using the keyboard to traverse the menus and select a zoom setting).

If you want the ability to zoom in or out easily using the keyboard, the only way to get it is to create a macro and then assign the macro to a keyboard combination. The following VBA macro (MyZoomIn) allow you to zoom in to (enlarge) a worksheet by 10%:

Sub MyZoomIn()
   Dim ZP As Integer
   ZP = Int(ActiveWindow.Zoom * 1.1)
   If ZP > 400 Then ZP = 400
   ActiveWindow.Zoom = ZP
End Sub

Notice that the macro only allows you to zoom in up to 400%. This is because Excel allows you to only zoom that high, and any higher would generate an error. A slight variation on the same theme results in a macro I call MyZoomOut. It zooms out of (reduces) a worksheet by 10%:

Sub MyZoomOut()
   Dim ZP As Integer
   ZP = Int(ActiveWindow.Zoom * 0.9)
   If ZP < 10 Then ZP = 10
   ActiveWindow.Zoom = ZP
End Sub

This macro sets the bottom boundary at 10%, which is the smallest you can go. Any smaller, and Excel would generate an error again.

The final trick to make these macros really useful is to assign them to a keyboard combination. You can then quickly zoom in or out by 10% with a simple keystroke. The following are the steps you can use to assign a macro to a keyboard combination:

  1. Press Alt+F8. Excel displays the Macro dialog box, which includes a list of your defined macros. (MyZoomIn and MyZoomOut should be among them.)
  2. Select the MyZoomIn macro.
  3. Click on Options. Excel displays the Macro Options dialog box. (See Figure 1.)
  4. Figure 1. The Macro Options dialog box.

  5. In the Shortcut Key box, specify the shortcut you want to use. For instance, if you want to use Ctrl+I, you would enter an I in the Shortcut Key box.
  6. Click on OK.
  7. Select the MyZoomOut macro.
  8. Click on Options. Excel again displays the Macro Options dialog box.
  9. In the Shortcut Key box, specify the shortcut you want to use. For instance, if you want to use Ctrl+O, you would enter an O in the Shortcut Key box.
  10. Click on OK.
  11. Click on Cancel to close the Macro dialog box.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12582) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Zooming with the Keyboard.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Removing Conditional Formats, but Not the Effects

Conditional formatting is very powerful, but at some point you may want to make the formatting "unconditional." In other ...

Discover More

Creating a Photo Catalog from a Folder of Photos

Excel is great for collecting all sorts of information. You might even use it to create a catalog of your photos. Working ...

Discover More

Finding an Optimal Table Height

Word can adjust the height of individual rows in a table based on the information you put in each row. This may not result in ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Nifty Zooming

If you are using a mouse that has a center wheel, you can use the wheel to zoom in and out of your work. This tip shows how ...

Discover More

Displaying a Set Column Range

Do you want to display a particular range of columns within the Excel window? Here's a couple ways you can accomplish the ...

Discover More

Zooming In On Your Worksheet

If you have trouble seeing the information presented in a worksheet, you can use Excel's zooming capabilities to ease the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 4?

2017-05-05 05:10:24

gerdami

Since Excel does not allow to assign a shortcut other that Ctrl + a letter, here is my workaround:

I assign this code to my PERSONAL.XLSB:

Private Sub Workbook_Open()
MyZoomAssignKeys
End Sub

In one of the modules of my PERSONAL.XLSB I put this code:

Sub MyZoomAssignKeys()
Application.OnKey "^{107}", "MyZoomIn" 'Ctrl KeyPadPlus
Application.OnKey "^{109}", "MyZoomOut" 'Ctrl KeyPadMinus
Application.OnKey "^{096}", "MyZoom100" 'CTrl KeyPad0
End Sub

Be aware that I diverted Ctrl +, Ctrl - from their Excel original function, i.e. insert/delete rows or columns.


2013-12-18 07:56:38

Shirley Jewell

I use the control key on my keyboard along with the scroll wheel on my mouse to zoom in and out on Excel spreadsheets.


2013-08-01 16:47:48

gerdami

I wrote a similar set of macros for Word this morning. I assigned the following keyboard shortcuts: Ctrl + Numpad Plus, Ctrl + Numpad Minus and Ctrl + Numpad Zero to have a smilar behaviour to Firefox.
I am shocked that Excel 2013 only accepts letters.


2013-07-25 07:55:07

Michael J. Wolter

Thanks, Willy. I will do as you suggest.


2013-07-24 08:25:18

Willy Vanhaelen

Since your recorded/edited macro generates an error you better delete it.

Then copy these macros in that same module and assign them a shortcut as described above. That schould work.


2013-07-23 13:24:44

Michael J. Wolter

[CORRECTED:] Thanks, Willy. I don't know how to do a VBA macro, but I already substituted the text of these in the macros I recorded. Obviously, I did something wrong, because that's when I started getting the error.


2013-07-23 06:14:48

Michael J. Wolter

Thanks, Willy. Well, I don't know how to do a VBA macro, but I alteady substituted the text of these in the macros I recorded. Obviously, I did something wrong, because I am started getting the error.


2013-07-22 11:56:02

Willy Vanhaelen

You cannot record macros like these. The recorder can only record commands you execute and in the zoom level dialog you can only select a percentage, there is no command to increase/decrease by 10%.

Instead of trying to edit your existing macro, why not use one of these? They work :-)


2013-07-21 19:10:50

Michael J. Wolter

Is there a way to just record these macros, rather than using VBA? Before finding this ExcelTip, I tried to record MyZoomIn, assigned to Ctrl+Shift+L, by clicking on the "+" end of the zoom slider. However, the zoom value was recorded as an absolute, e.g., 10%, which made it useful only for setting the zoom level at 10%. After finding this ExcelTip, I tried to edit my existing macro in VBA so it would be relative to whatever the current zoom level was. However, whenever I try to run the macro, I get "Run-time error '1004': Unable to set the Zoom property of the Window class." Clearly, I made an error while editing the macro, which means I should not be fooling around with VBA.


2013-04-06 06:55:07

Willy Vanhaelen

Here a one line variant of both macros:

Sub MyZoomIn()
ActiveWindow.Zoom = Application.Min(Int(ActiveWindow.Zoom * 1.1), 400)
End Sub

Sub MyZoomOut()
ActiveWindow.Zoom = Application.Max(Int(ActiveWindow.Zoom * 0.9), 10)
End Sub


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.