Excel Zoom

Say you have a large contact list in Excel that is formatted, for example like: Column A – Names, Column B – Addresses, Column C – Cities, Column D – States, Column E – Zip Codes. It’s great that everything is formatted in neat columns, but it would be a nightmare if you wanted to use this list to print off envelope address lables.

Thankfully, there’s a macro can speed up this process. The macro below assumes that you’re starting in the left most column in your list (in my example, column A), and will insert three rows below (one for the address, one for the city, state and zip, and one blank one between records). It then takes the address from the second column (column B) and puts it below the name (row 2). It then proceeds to take the city, state and zip and puts them in the appropriate columns below the address (in row 3). Finally, it will move down to row 5, where the next record starts and repeats the process over again. It will continue to repeat (or loop) until it runs into a blank cell in column A. The result is a list of all your contacts formatted to be used on an envelope.

You’ll also notice that I added “Application.ScreenUpdating = False” to keep the screen from updating everytime the macro did something. If you have a list with a few hundred or even thousands of people it would get pretty obnoxious to see the screen flicker everytime something was cut and pasted. At the end “Application.ScreenUpdating = True” is added to allow Excel to reveal the final masterpiece.

I only wish I thought of this macro when I was formatting my wedding invitation list!

Copy all the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub FormatList()
'
'MACROS BY EXCELZOOM.COM
'
Application.ScreenUpdating = False
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Range("A1:A3").Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 1).Range("A1").Select
Selection.Cut
ActiveCell.Offset(1, -1).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 2).Range("A1").Select
Selection.Cut
ActiveCell.Offset(2, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-2, 3).Range("A1").Select
Selection.Cut
ActiveCell.Offset(2, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-2, 3).Range("A1").Select
Selection.Cut
ActiveCell.Offset(2, -2).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, -2).Range("A1").Select
Loop
Application.ScreenUpdating = True
End Sub

Using the Application.OnTime Method will allow you to schedule a macro to be run at a specified time. The specified time can be either after certain amount of time has passed, or at a certain time of day. This is especially useful if you would like a daily or weekly report to be printed, without having to be around to print it. If the report is long, it might take a while to print, so it might be useful for the macro to run after business hours or on the weekend.

The only drawback to this macro is that the file must be open in order for it to run. You can have this automated for you by using the Windows Scheduler found in the Control Panel to open the file at a certain time, and have the code below run automatically when opened.

Copy all the code below. Paste it into your workbook’s Visual Basic editor, in a Module. Replace “my_macro” with whatever name you have given your macro.

Need help? Use our nifty guide to help figure out how to install and use your macros.

The code below will allow you to run a macro called “my_macro” 30 seconds from now. You can change the time to whatever time interval you would like.

Sub Run_After_30_Sec()
'
'MACROS BY EXCELZOOM.COM
'
Application.OnTime Now + TimeValue("00:00:30"), "my_macro"
End Sub

Use the following if you wish to run the macro at a certain time of day. The code below will automatically run my_macro at 5pm (written in 24 hour time as 17:00:00).

Sub Run_At_5PM()
'
'MACROS BY EXCELZOOM.COM
'
Application.OnTime TimeValue("17:00:00"), "my_macro"
End Sub

For a person who has used previous versions of Excel, Excel 2007 can be a little intimidating at first. For starters, there are none of the familiar menu options, and some of the keyboard shortcuts that have become second nature have changed. Don’t let this discourage you though. With a little guidance and familiarization, you’ll find that Excel 2007 is more powerful and easier to use than before.

1. Office Button: This button contains most of the functions found in the File Menu from previous versions of Excel. Commands, such as New, Open, Save, Print and others can be found here.
2. Quick Access Toolbar: The Quick Access Toolbar is fully customizable, and allows you to display commonly used commands. In the image above, there is Save, Undo, Redo, Print Preview and New Workbook.
3. Ribbon: The Ribbon replaces the traditional menus found in previous versions of Excel. The standard ribbon tabs are Home, Insert, Page Layout, Formulas, Data, Review, and View. Excel will automatically add menus as necessary to allow you to work with the content of your worksheet. You’ll notice that there is also a Developer and an Add-Ins tab in the image above. This is because there are macros stored in the workbook, which are shown in the Developer tab, as well as several Add-Ins that are running.
4. Help: The Help button provides one click access to be able to browse Excel’s help topics.
5. Name Box: The Name Box isn’t different from previous versions of Excel, however it is one of the lesser known features found in Excel. This displays the cell reference when selecting a cell (the column and row cross section). You can also type a named range in here. To learn more about naming ranges, take a look at our article Tips – Naming Ranges.
6. Insert Function: By clicking the Insert Function button, Excel will open the Insert Function dialogue box. Here you can search for functions by typing keywords in the search box, or by selecting a category from the drop down menu. The Insert Functions interface is the same as in previous versions of Excel.
7. Formula Bar and Expand Formula Bar Button: The Formula Bar should look familiar to you if you have used Excel before. What’s different in Excel 2007 is the Expand Formula Bar Button on the right. When working with long formulas, this button increases the height of the formula bar, without covering over the data in the first few rows of your spreadsheet. It also enables you to scroll vertically through the formula with the scrolling buttons it displays after clicking the Expand Formula Bar Button.
8. Right Click MiniBar: When right clicking a cell in Excel 2007, you’ll see a new MiniBar in addition to the usual shortcut menu, which displays commonly used editing features. This is makes editing easier, so you don’t have to keep clicking back to the Home tab on the Ribbon in order to format your worksheet.
9. Right Click Shortcut Menu: The shortcut menu in Excel 2007 hasn’t changed much, but also displays with the MiniBar when right clicking.
10. Vertical/Horizontal Split Box: The split boxes in Excel 2007 are not a new feature, but are not very well known. By clicking and dragging either the vertical or horizontal bars, you can split your worksheet into several scrollable panes, allowing you to view different areas of your worksheet at once. To learn more about the split box, take a look at our article Tips – Split a Worksheet Into Multiple Panes.
11. Status Bar: The status bar shows information about Excel. In the image above it says “Ready”, however if you’re saving a file, it will display the status there. The AutoSave feature will also display a status in this location when it is saving your work. The status bar also displays the selected cell statistics, quick views bar and zoom level. The status bar is also customizable. Simply right click somewhere in the status bar to display the customization menu.
12. Worksheet Tabs and Insert New Worksheet Button: The worksheet tabs should be a familiar sight from previous versions of Excel, but the Insert New Worksheet button is a new addition. You can now insert new worksheets with one click of the Insert New Worksheet button.
13. Selected Cell Statistics: In previous versions of Excel, when highlighting several cells with numbers Excel would, by default, show the sum of those numbers here. In Excel 2007, it also displays the average and count of those cells in addition to the sum. For purposes of the illustration above, we’ve edited the image to display the selected cell statistics as if we were to highlight two cells, one with a 5 and the other with a 1. We did this so we could also illustrate the right click menu, that would have otherwise obscured the data in the selected cells.
14. Quick Views: The Quick Views bar allows you to quickly switch between Normal, Page Layout and Page Break Preview views. While these page views aren’t new to Excel 2007, the easy access quick views bar is.
15. Zoom Level: The Zoom Level bar replaces the zoom level drop down menu from previous versions of Excel. You can change the zoom by dragging the arrow left or right to a minimum zoom of 10% or a maximum zoom of 400%. Alternatively, you can click the + or – buttons to increase/decrease the zoom level by 10% with each click.

You can split a worksheet area into vertical and/or horizontal panes so that you can click inside any one pane and scroll to locate information in that pane while the other panes remain in place.

Drag a split box (the small box at the top of the vertical scroll bar, or at the right end of the horizontal scroll bar) in the direction you want the split to appear. To remove the split, move the pointer over the split until the pointer changes to a double-headed arrow, then double-click the split.

If you need to fill cells with sequential text such as months of the year, days of the week, or text plus a number such as Quarter 1, Quarter 2,…, use the fill handle to quickly create labels for the cells simply by typing the first item in the series.

For example, you would type January in your first cell, then hover the mouse over the bottom right hand corner until it turns into a black cross, then click and drag the fill handle from the cell containing January until you have all the monthly labels you need.

Do you want to have a list of information in all CAPITAL LETTERS, all lower case, or All In A Title Format? This would be an extremely tedious task if, say you have 100+ lines that need to be updated. With these macros, you can select the cells you wish to update and change their case in a flash!

Note: These macros won’t change the output of a cell with a formula in it.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, either under a Microsoft Excel Object or Module.
Need help? Use our nifty guide to help figure out how to install and use your macros.

Lower Case:

Sub TextLowerCase()
'
'MACROS BY EXCELZOOM.COM
'
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = LCase(cell)
End If
Next
End Sub


Upper Case:

Sub TextUpperCase()
'
'MACROS BY EXCELZOOM.COM
'
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = UCase(cell)
End If
Next
End Sub


Title (Proper) Case:

Sub TextTitleCase()
'
'MACROS BY EXCELZOOM.COM
'
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = Application.Proper(cell)
End If
Next
End Sub


OK, so your co-worker or boss has one of those enormous monitors, and you’re stuck working on this little tiny laptop screen. Every time they open a file, they change the zoom setting to 50%, because heaven forbid they look at things any other way. Then, they go ahead and save the file at a 50% zoom. The next time you open the file, you’re squinting to try to see the little ants marching across your spreadsheet. Of course, you could just change the zoom level in the toolbar, but that gets annoying after a while doesn’t it?

Now, it’s time for a little payback. There’s an easy way to always have Excel change the default zoom level to whatever you want (i.e. 100%, 85% etc.) Copy all text in the Auto Set Zoom Level box below. Paste it into your workbook’s Visual Basic editor, in the “ThisWorkbook” object. This will automatically set the zoom level to 100% whenever the workbook is opened.

On the other hand, if you want to be nice to your boss or co-worker, and let them get the benefits of this macro, you can set a toggle to go between their zoom level (50% in this example) and yours (100% in this example). Copy all text in the Toggle Zoom Level box below. Paste it into your workbook’s Visual Basic editor, in the “ThisWorkbook” object. Note: If you manually change the zoom level in the toolbar to anything other than 100% or 50%, the next time the workbook is opened, it will open up as 50%.

Finally, you can set all worksheets in your workbook to be the same zoom level. Copy all the code under Set Zoom Level on All Worksheets below. Paste it into your workbook’s Visual Basic editor, in the “ThisWorkbook” object, or a module if you wish. The way this macro differs from the other two is it won’t run as soon as you open the workbook. If you want it to do so, change the line that says Sub Set_All_Zoom() to Private Sub Workbook_Open(), and make sure you have it in the “ThisWorkbook” object in Visual Basic.

Need help? Use our nifty guide to help figure out how to install and use your macros.

Auto Set Zoom Level:
Note: You can change the “100″ to whatever zoom level you wish to default to.

Private Sub Workbook_Open()
'
'MACROS BY EXCELZOOM.COM
ActiveWindow.Zoom = 100
End Sub

Toggle Zoom Level:
Note: You can change the “100″ and “50″ to whatever zoom level you wish to toggle between.

Private Sub Workbook_Open()
'
'MACROS BY EXCELZOOM.COM
If ActiveWindow.Zoom = 50 Then
ActiveWindow.Zoom = 100
Else
ActiveWindow.Zoom = 50
End If
End Sub

Set Zoom Level on All Worksheets:
Note: You can change the “75″ to whatever zoom level you wish to set all worksheets in your file to.
This macro differs from the other two as it won’t run as soon as you open the workbook. If you want it to do so, change the line that says Sub Set_All_Zoom() to Private Sub Workbook_Open(), and make sure you have it in the “ThisWorkbook” object in Visual Basic.

Sub Set_All_Zoom()
'
'MACROS BY EXCELZOOM.COM
Sheets.Select
ActiveWindow.Zoom = 75
ActiveSheet.Select

End Sub

When you enter a cell reference into the formula bar, you can press the F4 button to make the reference either absolute, mixed, or relative. This helps your formula work as expected when you copy the formula to another location

• Absolute references look like this: $A$1, where the dollar signs ($) indicate that when copying a formula to another cell, Excel should not change the reference to cell A1.
• Mixed references look either like $A1 or A$1. The dollar sign ($) in $A1 means that if you copy the formula, it will always reference column A, however if you copy the formula down, it will change the row number relative to its new location. The dollar sign ($) in A$1 does just the opposite. Copying a formula will always reference row number 1, however it will change the column letter relative to its new location.
• Relative references look like this: A1, where there are no dollar signs ($). This indicates that both the column letter and the row number will change when copying a formula relative to the new location.

Always check to see if your formula is using absolute, mixed or relative cell references prior to copying them to another location.

Adding comments to your worksheet allows you to share your thoughts with other users, or to serve as a reminder for yourself.

Right click the cell where you want to insert the comment, and begin typing in the box that appears. Click off the cell and you’ll see a little red triangle in the upper right hand corner of the cell where you just inserted a comment. This is a visual cue to let a user know that there is a comment in that cell.

To view that comment, simply hover over the cell, or right click the cell and select Show/Hide Comments. Hide the comment by right clicking the cell again and selecting Hide Comment.

To edit a comment, right click and select Edit Comment.

To delete a comment, right click and select Delete Comment.

Excel 2007 allows you to do all the previously mentioned actions on the Review tab under the Comments group.

There are several different types of charts found in Excel. All of which are used to display information in a way that makes it easier to interpret data.

• Column charts are best suited to compare distinct object levels in a vertical format.
• Line charts compare trends over even time periods, but are not necessarily used to show totals.
• Pie charts show the relative size of a particular data point as a part of the whole.
• Bar charts are best for comparing distinct object levels ina horizontal format. It is similar to a column chart, just flipped on its side.
• Area charts show how individual volume changes over time in relation to a toal volume.
• Scatter charts compare trends over uneven time or measurement intervals. This type of chart is useful to spot trends in a set of data.