Freeze Panes for view and Repeat row/col headings for printing

Location:   http://www.mvps.org/dmcritchie/excel/freeze.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

 

 ABCDEFGH
1 DescriptionDateHd-1 Hd-2Hd-3Hd-4Hd-5Hd-6
106A1062000-03-03 C106D106E106F106G106 H106
107A1072000-03-04 C107D107E107F107G107 H107
108A1082000-03-05 C108D108E108F108G108 H108
109A1092000-03-06 C109D109E109F109G109 H109
110 A110 2000-03-07C110 D110E110F110G110H110
111 A1112000-03-08C111D111E111 F111G111H111
112 A1122000-03-09C112D112E112 F112G112H112
113 A1132000-03-10C113D113E113 F113G113H113
When viewing a sheet we usually want to see the descriptive column and row headings anywhere on the sheet, and when printing we would also want to see the same descriptive information repeated on each page.  Freeze panes is used for viewing, and Rows/Columns to repeat is used for printing.

Freeze panes to display heading columns/rows (#panes)

In the above example a single row at top is used for column headings, and a single column at left for row descriptions, and these need to be seen as you scroll anywhere on the sheet.

To see both the column descriptions and the row descriptions, choose the column and cell beyond those you want to freeze.
   Select cell B2 --> Window --> Freeze panes
  (in Excel 2007 use View, Window, then Freeze panes)

To freeze only the first row descriptions, you would select Row 2 as one beyond the first row the Freeze panes.

To freeze the first two column descriptions, you would select Column C as one beyond the second column and then Freeze panes.

Split and Freeze panes are mutually exclusive you can only have one or the other.  Split would allow you to view and scroll independently a vertical and or horizontal split.  An alternative to split is to create a new window within Excel to view the same data.

Split view is similar to Freeze Frame (#split)

You can Split the view of a worksheet vertically and/or horizontally each with it own scroll bar, so you could have 4 scroll bars.  This allows you to view different parts of a sheet at the same time.  The method of choosing the cell to indicate the split using the Window menu is the same as for Freeze panes.  Split and Freeze Frame are mutually exclusive (one or the other but not both).   Another method to split is to grab the split indicator just above or to left of the scrollbar and drag it to where you want the split.     
  Window(menu), Split.
  (in Excel 2007 use View, Window, then Split)

Also see Cell Watch on Excel 2002 (Tips), add a cell to the Watch Window. Simply right click a cell and select Add Watch. You will see the Watch Window appear and it will remain on top as you work on your spreadsheet.


You can move the split to adjust the split.

New Window   (#newwindow)

Use of  New Window from the Window menu provides the ability to get an additional view of the workbook.  The views are independent view of the same workbook as you can scroll them or switch worksheets etc.   Any change to one view would be immediately reflected in the other views since the are just views and not separate files.  You can identify a view by it's  suffix of  a colon and a digit (:1)  to the worksheet name.  To create a view use  Window (menu), New Window.  To eliminate the extra view(s), simply close them one at a time using the [x] to close the worksheet view, below the [X] above it that would close the Excel application.

important note:  Each Window has a different view, so each worksheet in one view may have a different selection than it has in a different view.  Running macros from one view or the other may work with different selections and therefore create different selections and results even though working on same workbook, something to consider when writing macros and observing results.

Viewing Multiple Windows of Excel   (#multiview)

Creating another view(/window) within your Excel Window.
  Window menu, New Window you will see a number after the workbookname
  Microsoft Excel - Asher_InStock.xls:1
  Microsoft Excel - Asher_InStock.xls:2 Providing you with two (or more) views of the same workbook. If you have the workbook maximized within the Excel window you will not see both at once.  You would probably want the Excel window itself to be maximized.

To eliminate the extra view, simply click on the [x] in the upper right corner for the workbook which is below the [X] for Excel.

Windows in Taskbar   (#taskbar)

If you want to see multiple instances on your taskbar and in separate windows on the taskbar: 

Excel 2000 and Excel 2002 use
  Tools, Options, View,   Show: [x] Windows in Taskbar
or you can select the window you want from Excel's Window menu and keep only the one window on the task bar.

In Excel 2007 what was tools is split between the Developer ribbon (not visible by default), and the Office button under options.  In the case of Toolbars in Windows it is in the options of the Office button.

Excel 2007, Office Button, Options (at bottom), Advanced (button on left), under Display heading, (check) Show all windows in Taskbar
or you can continue to limit the taskbar to one Excel window, and choose the Excel window from the View (Ribbon), Switch Windows, and choose the desired window

Windows Vista:  Vista gathers like windows into a single taskbar tab (by default), you can change this behavior using WinStart key, Control Panel, Taskbar and Start Menu Properties, (uncheck) Group similar taskbar buttons (ref. http://www.mvps.org/dmcritchie/vista/vista.htm, ref. Windows-7)

Display Totals while viewing   (#totals)

Keep row visible.  Keeping the total row visible. [Have to work posting into this document, pictures may be necessary].

Repeat Rows/column headings when printing (#printing)

Now to do the equivalent when you are printing
   File --> Page Setup --> Sheet Rows to Repeat at TOP $1:$1 Columns to Repeat at LEFT: $A:$A easiest way to fill in those boxes is to place cursor in the box, then make selections on the spreadsheet (can see this in [?] help).

There is a little more flexibility with rows to repeat than you had with freeze rows.  Say you did not have column headings on first page but they began at some specific row later on.  You can choose say Rows 50-51 to be repeated subsequently to their first usage.  So once rows 50-51 has been printed or you started later than that, those rows would be repeated at the top of each sheet.
   File --> Page Setup --> Sheet Rows to Repeat at TOP $50:$51

Also on sheet you can tell whether you want to go down (default) or across sheet when printing sheets. If you have a duplex printer you probably don't want the default.

You can also print in landscape, or use Fit to Page (. The options fit to width and fit to height are two independent options -- you do not need to specify both.

Repeating Top Row(s) in HTML (when printed)   (#HTML)

For those who prefer to do their own HTML (see XL2HTML) instead of generating all of the garbage to make everything in HTML look the same as Excel, you would change the <TD>...</TD> on the first line(s) to <TH>...</TH>

For most purposes HTML does a much better job left to its own devices than would be produced by Excel.  Excel pages converted to HTML from Excel will look like Excel and be much larger than simple HTML tables.

Read the HTML / XML standard at (look for the example)
  http://www.w3.org/TR/html4/struct/tables.html#edef-TH

Note:  You can achieve viewing with Save as Web Page dialog box and then enable "Add interactivity with" to achieve direct viewing in the HTML as on Excel, but be aware that you can only do this on an IntraNet when and where you can assure everyone has a legal copy of the Web components.  Placing your HTML on the Internet with interactivity is illegal and can result in a letter from Microsoft reminding you of your EULA (End User License Agreement).  [comparison to GNU].  Excel 2007 on the other hand changes that as it produces XML, which can be put on the internet.


This page was introduced on November 10, 1999. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments


Copyright © 1997 - 2006,  F. David McRitchie,  All Rights Reserved