Excel Zoom

Adding a drop down list to your worksheet allows the users of the worksheet to enter information in a neat, consistent format.

For example, say your worksheet asks a user to input information that later will get summarized in a PivotTable. If the user doesn’t enter the information consistently (i.e. spells out words some of the time, and abbreviates the rest), the information won’t show correctly in the PivotTable output. Adding the drop down list will limit the number of values a user can enter into a cell, and provide consistency to your worksheet.

To do this, follow the steps below.

• Select the cell where you want your custom list to appear (if you want the same list to appear in multiple cells, you can copy the list later).
• Click Data | Validation.
• On the Data Validation window’s Settings tab, select Allow: List, and make sure the In-cell dropdown box is checked.
• In the Source box, type the items you want in the drop down list separated by a comma. Alternatively, you can refer to a range of cells in the same worksheet that contains your list, or refer to a named range elsewhere in your workbook.
• Click OK.

When you select the cell that now contains the data validation, you should see an arrow to the right of the cell. Click the arrow, to display the drop down list.

Say you have a spreadsheet that keeps track of weekly sales information, which you want displayed in a new sheet for each month. You don’t want to copy an “original” worksheet each week, so a macro can help to automate this process.

The macro code below will copy the worksheet named “Original”, and place it after the last worksheet in your file. It will then prompt you to enter in the date that corresponds with that week. It will continue to do so until you enter a valid worksheet name (i.e. valid format and a name that isn’t already used somewhere else in the workbook. Make sure your workbook already has a file called “Original”, and it is formatted the way you want all your weekly reports to be formatted. (Note if your “Original” worksheet isn’t called “Original”, you can change the name in the macro code below to whatever you want).

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 CopyNewSheet()
'
'MACROS BY EXCELZOOM.COM
'
Dim shname As String
Dim wrksh As Worksheet
Worksheets("Original").Copy after:=Sheets(Worksheets.Count)
Set wrksh = ActiveSheet
Do While shname <> wrksh.Name
shname = Application.InputBox _
(Prompt:="Enter This Week's Date")
On Error Resume Next
wrksh.Name = shname
On Error GoTo 0
Loop
Set wrksh = Nothing
End Sub

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.