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: Freezing Top Rows and Bottom Rows.

Freezing Top Rows and Bottom Rows

by Allen Wyatt
(last updated May 2, 2015)

5

Kevin has a long (vertical) worksheet that has the first few rows frozen so the column headings are always visible. He would like to also freeze the bottom row, so the column totals are always visible.

Unfortunately there is no way to do this in Excel. At first thought you may believe that you can freeze rows and also split the worksheet window so that you can put the totals below the split. Excel won't let you do this, however—when you try, then the freeze is removed and replaced with the split, and trying to reapply the freeze removes the split.

What most experienced Excel users do is to put the column totals at the top of the columns instead of at the bottom. This may seem awkward, but it has the added benefit of allowing you to easily add new rows to your data table. The top-of-column totals could be added either using SUM formulas (as you would with the totals at the bottom), or you can leave the totals at the bottom of the columns and simply add a referential formula (like =B327) in a row at the top of columns.

There is another approach you can use, however. Start by opening the workbook that contains the worksheet you want to work on. (This should be the only workbook open.) Then follow these steps:

  1. Display the View tab of the ribbon.
  2. Click the New Window tool in the Window group. Excel creates a new window on the data in the worksheet you are working on.
  3. Click the Arrange All tool in the Window group. You should see the Arrange Windows dialog box. (See Figure 1.)
  4. Figure 1. The Arrange Windows dialog box.

  5. Make sure the Horizontal radio button is selected.
  6. Click OK.

At this point you should see your two windows—one in the top half of the screen and the other beneath it. Use the mouse to adjust the vertical height of both windows. (The bottom window should be large enough to hold your totals and the top window can occupy the rest of the available space.)

Now you can display the totals row (or rows) in the bottom window, and freeze the top rows in the top window. This allows you to see everything you want to see, although it is a bit expensive when it comes to screen real estate since both windows have column letters visible.

The biggest drawback to this approach is that the windows are not horizontally linked. This means that if you scroll one of the windows left or right, the other window doesn't scroll at the same time. You could write some VBA code to handle the horizontal scrolling, but that simply adds complexity to the situation.

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

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

Spell Check Misses Misspelled Words

If you do a spelling check and notice that Word doesn't catch a word that you know is misspelled, it is easy to get ...

Discover More

Locking Lines in a TOC

Want to "lock down" the lines in a TOC so that you cannot add new paragraph marks in the middle of one? You may not be able ...

Discover More

Turning Off Printing of Document Properties

Getting to a printed document is often the entire purpose of using Word. The program, however, allows you to print out ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Creating a Copy without Formulas

Excel makes copying worksheets (duplicating them) rather easy. However, you may want a worksheet copy that differs from the ...

Discover More

Detecting Types of Sheets in VBA

When processing workbook information in a macro, you may need to step through each worksheet to make some sort of changes. It ...

Discover More

Changing the Height of Worksheet Tabs

Do you need your worksheet tabs to be taller than what they are? You can't make the adjustment in Excel, but you can make it ...

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 6Mpixels. 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 four less than 9?

2015-05-08 17:08:40

Ken Maltese

If all you care about is tracking the values in the columns (and do not need to print in that format) another method is to use Excel's "Watch Window" I work with a bunch of large spread sheets and like to visually verify the effect of my entries. Unless you have a lot of columns, this may be a "quick fix" to tracking data input.


2015-05-04 21:36:24

fred

The Camera function can also be used
Take a 'snap shot' of the 'Total row' and display it any where near the top of the sheet in the 'frozen' section.
These shots refresh as the contents of the worksheet is altered
Simply click delete on image once complete / want to print (or move off print area)


2015-05-04 13:08:33

Glenn Case

A further note to my previous one below: If you only have a few rows you need visible at the bottom, then you can use Freeze Panes to keep those rows in view, even if you scroll vertically. The rows above the Freeze Panes freeze line will remain in view.


2015-05-04 13:05:21

Glenn Case

Actually, if you use View Side by Side and Synchronous Scrolling, you can scroll both windows at once. The down side of this is that both also scroll vertically as well as side-to-side, which may not work for what you're after. However, if you need not scroll vertically, this may the workaround you need.


2015-05-04 10:55:23

Ron Wroczynski

Of course, the REALLY simple solution would just be to add the column totals to the top above the headings. Then the totals and the headings can be frozen together. The advantage of this is that additional rows can be easily added. Also, the totals row can be hidden if it hinders other uses (printing, special display, etc)


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.