Excel 2007 Upgrade FAQ: Formatting And Printing
Note: I originally posted this information at Daily Dose of Excel. I've updated it slightly, and augmented it with information from commenters.
Q: How do I get my old workbook to use the new fonts?
A: Press Ctrl+N to create a blank workbook. Activate your old workbook and choose the Home tab. Click the very bottom of the vertical scrollbar in Styles gallery, and choose Merge Styles. In the Merge Styles dialog box double-click the new workbook you created with Ctrl+N. But this only works with cells that have not been formatted. For example, bold cells retain their old font.
Q. How do I get a print preview?
A. Try using the Page Layout view (icon on the right side of the status bar). Or, add the Print Preview button to your QAT.
Q: When I switch to a new document template, my worksheet no longer fits on a single page.
A: That's probably because the new theme uses different fonts. After applying the theme, use the Page Layout / Themes / Fonts control to select your original fonts to use with the new theme. Or, modify the font size for the Normal style. If page fitting is critical, you should choose the theme before you do much work on the document.
Q: How do I get rid of the annoying dotted-line page break display in Normal view mode?
A: Open the Excel Options dialog box, click the Advanced tab, scroll down and look for the 'Display options for this worksheet' section, and remove the checkmark from 'Show Page Breaks'.
Q: Can I add that 'Show Page Breaks' option to my QAT?
A: No. For some reason, this very useful command isn't available as a QAT icon.
Q: I changed the text in a cell to use Angle Clockwise orientation (in the Home / Alignment group). I can't find a way to get the orientation back to normal. There's no Horizontal Alignment option.
A: To change the cell back to normal, click the option that corresponds to the current orientation (that option is highlighted). Or, choose the Format Cell Alignment option and make the change in the Format Cells dialog box.
Q. I'm trying to apply a table style to a table, but it has no effect.
That's probably because the table cells were formatted manually. Remove the old cell background colors, and applying a style should work.
Q: I thought Office 2007 was supposed to support PDF output. I can't find the command.
A: You need to download a free add-in from Microsoft. Blame the Adobe attorneys. After you download and install the add-in, click the Office Menu button and then select Save As / PDF or XPS.
Determining The Number Of Printed Pages
If you need to determine the number of printed pages for a worksheet printout, you can use Excel's print preview feature, and view the page count displayed at the bottom of the screen.
This tip provides two ways to determine the number of printed pages -- one using the the Excel 4 (XLM) Get.Document macro function, the other using VBA.
Using an XLM macro in VBA
You can execute an XLM macro function from VBA, as follows:
PgCnt = ExecuteExcel4Macro("Get.Document(50)")
In the statement above, the number of printed pages in the active sheet is assigned to the PgCnt variable.
The VBA subroutine below loops through all worksheets in the active workbook and displays the total number of printed pages. Note that this may return incorrect results when using a user-specified print range.
Sub ShowPageCount() PageCount = 0 For Each sht In Worksheets sht.Activate Pages = ExecuteExcel4Macro("Get.Document(50)") PageCount = PageCount + Pages Next sht MsgBox "Total Pages = " & PageCount End Sub
Stew Scott provided the VBA procedure below, which does not use XLM.
Sub NumberOfPrintedPages() Worksheets(1).DisplayAutomaticPageBreaks = True HorizBreaks = Worksheets(1).HPageBreaks.Count HPages = HorizBreaks + 1 VertBreaks = Worksheets(1).VPageBreaks.Count VPages = VertBreaks + 1 NumPages = HPages * VPages Worksheets(1).DisplayAutomaticPageBreaks = False MsgBox NumPages End Sub
Mail Merge - Without Word
A companion file is available: Click here to download
Mail merge is the process of merging information from a database into a text document, and then printing the document. The result is a series of personalized documents.
Normally, Microsoft Word is used for this type of operation. Word can perform a mail merge with data stored in an Excel workbook. The process works well, but in some cases it may be preferable to eliminate Word and do all of the work with Excel.
Using Excel to perform a mail merge requires:
- A range that contains the data to be merged. In the example, this is on the sheet named Data.
- A range that contains the text. In the example, this is on the sheet named Form.
- A cell that contains the row number of the "current record." In this example, that cell is C3.
- Formulas that use Excel's INDIRECT function to retrieve data from the current record. These formulas are in column L.
- Formulas within the text that refer to the cells in column L. The example uses several such formulas to personalize the letter.
- A simple macro that loops through the data, incrementing the current record number and printing the sheet.
This example, of course, can be adapted to many other purposes.
This example has a few additional features:
- The ability to specify the first and last records from the database
- Buttons to assist in navigating through the workbook
- An option to preview instead of print
- A Help button that displays instructions
Displaying A Menu Of Worksheets To Print
This tip contains a VBA subroutine that displays a dialog box that contains the names of all non-empty worksheets in the active workbook. The names are displayed as checkboxes. The user can select which sheets to print, and click OK. The macro then prints the selected worksheets.
This subroutine is rather unusual since it creates the dialog box on the fly, and then deletes it after it is dismissed.
The code below prints the selected worksheets, but this general technique can be adapted to other situations in which you need the user to specify one or more sheets in a workbook.
Although this procedure uses an Excel 5/95 Dialog Sheet, it will also work with later versions of Excel. By the way, this is a rare example of how using a Dialog Sheet is easier than using a UserForm. Creating a UserForm on the fly is much more difficult, but it is possible.
Using the code
To try out the example, copy the code below and paste it to a VBA module (there is no need to add a dialog sheet). Then execute the SelectSheets subroutine.
The SelectSheets subroutine
Option Explicit Sub SelectSheets() Dim i As Integer Dim TopPos As Integer Dim SheetCount As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False ' Check for protected workbook If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If ' Add a temporary dialog sheet Set CurrentSheet = ActiveSheet Set PrintDlg = ActiveWorkbook.DialogSheets.Add SheetCount = 0 ' Add the checkboxes TopPos = 40 For i = 1 To ActiveWorkbook.Worksheets.Count Set CurrentSheet = ActiveWorkbook.Worksheets(i) ' Skip empty sheets and hidden sheets If Application.CountA(CurrentSheet.Cells) <> 0 And _ CurrentSheet.Visible Then SheetCount = SheetCount + 1 PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(SheetCount).Text = _ CurrentSheet.Name TopPos = TopPos + 13 End If Next i ' Move the OK and Cancel buttons PrintDlg.Buttons.Left = 240 ' Set dialog height, width, and caption With PrintDlg.DialogFrame .Height = Application.Max _ (68, PrintDlg.DialogFrame.Top + TopPos - 34) .Width = 230 .Caption = "Select sheets to print" End With ' Change tab order of OK and Cancel buttons ' so the 1st option button will have the focus PrintDlg.Buttons("Button 2").BringToFront PrintDlg.Buttons("Button 3").BringToFront ' Display the dialog box CurrentSheet.Activate Application.ScreenUpdating = True If SheetCount <> 0 Then If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb.Value = xlOn Then Worksheets(cb.Caption).Activate ActiveSheet.PrintOut ' ActiveSheet.PrintPreview 'for debugging End If Next cb End If Else MsgBox "All worksheets are empty." End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete ' Reactivate original sheet CurrentSheet.Activate End Sub
NOTE: Aaron Blood suggested the following modification, which prints the selected sheets as a single print job. This allows the sheet to be printed with continuous page numbers.
If PrintDlg.Show Then For Each cb In PrintDlg.CheckBoxes If cb.Value = xlOn Then Worksheets(cb.Caption).Select Replace:=False End If Next cb ActiveWindow.SelectedSheets.PrintOut copies:=1 ActiveSheet.Select End If
Copy Page Setup Settings To Other Sheets
Each Excel sheet has its own print setup options (orientation, margins, headers and footers, and so on). These options are specified in the Page Setup dialog box, which you access using the File, Page Setup command.
When you add a new sheet to a workbook, it contains the default page setup setting. Here's an easy way to transfer the settings from one worksheet to additional worksheets:
- Activate the sheet that contains the desired setup info. This is the "source" sheet.
- Select the "target" sheets. Press Ctrl and click the sheet tabs of the sheets you want to update with the settings from the source sheet.
- Select File, Page Setup and click OK.
The Page Setup settings of the source sheet will be transferred to all of the target sheets.
Printing Just A Portion Of Your Worksheet
If you need to print just a small portion of your worksheet:
- Select the cells that you want to print
- Choose File - Print
- In the Print dialog box, choose the Selection option
- Click OK
If you've specified a print area, this will not change it.
Note: If your selection consists of a non-contiguous range (a multiple selection), each area is printed on a separate sheet of paper.
Avoid Printing Specific Rows
In some cases, you may wish to avoid printing certain rows in your worksheet. For example, the rows may contain confidential information, or intermediate results that need not be printed.
To avoid printing specific rows, you can hide the rows before you print, and then unhide the rows after printing. If your sheet has many rows that should not be printed, hiding and unhiding the rows may be a tedious process. This tip presents a way to quickly toggle the hidden status of any number of rows. It takes advantage of Excel's Group and Outline feature.
The figure below shows a simple example. In this case, rows 5, 10, 15, and 20 should not be printed.
To set up a simple outline, follow these steps:
- Select Row 5
- Choose Data - Group and Outline - Group (or, use Alt+Shift+RightArrow)
- Select Row 10
- Press F4 (this key repeats the last command)
- Select Row 15
- Press F4
- Select Row 20
- Press F4
The preceding steps created a simple outline on the worksheet, and the outline symbols are displayed along the left side of the sheet. You can hide all of the "grouped" rows by clicking the small "1" button at the top of the outline symbol area (see the figure below).
After you've printed the sheet, click the "2" button to redisplay all of the rows.
Note: To hide the outline symbols, press Ctrl+8. The outline remains, but the symbols are hidden. To re-display the outline symbols, press Ctrl+8 again.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks