How To Freeze The Top and Bottom Rows In Excel
In an earlier article on freezing panes in Excel, I explained how to freeze the top row(s) and left column(s) however there are times when it is beneficial to always see the totals of a working spreadsheet. Normally they are displayed at the bottom of the spreadsheet making it difficult to scroll down particularly if you have a very long list of data.
There are two different methods for doing this and both are easy. Just pick the method that works best for your needs. The first method is by using the “New Window” feature in Excel. Using this feature will create a duplicate copy of your worksheet. Adjusting the height and position of the copied worksheet means you can display the data you are actually working on in the large window at the bottom of your screen. (shown in the blue area below) Then modify the second copy to display only the totals or descriptions and totals and place it at the top. (shown in the green area below)
It helps to collapse the ribbon bar so you gain a little more room. Now, whenever you make changes to the main worksheet which is in the bottom window, the top windows will display the totals or percentages giving you a real-time result of the changes without having to scroll down to see them. Use the following process to do it.
- Open the workbook that contains the spreadsheet you would like to work on.
- Click on the View tab in the menu ribbon and select “New Window”
- Click on Arrange All and select Horizontal. This will produce two windows
- Resize the copied window so that only the data you want to show is visible.
- Collapse the ribbon on the copied window and place it at the top of the screen.
- Open the original worksheet and arrange it below the copied version (as shown on the first image on this page)
Now you will have two worksheets open but you only have to continue working on the main worksheet to complete your data filling and watch the totals change to the changes made
Another method has the advantage of not requiring additional windows. To achieve this, create a new row just below your header. (shown in the image below as red with white text )
Then use the equal function by typing = plus selecting the cell that contains your totals at the bottom of the sheet. Repeat this for any cell you want to see the running totals on. This will make the red cell always equal to the value in the total cell. I also use the merge function to merge A2 through AD2 to include the “Running Tolals=” title.
All you need to finish this in the sample shown is to select cell A-3 and use the “Freeze Panes” function. Now whenever you scroll down the top two rows will stay frozen and you will always see the totals as they change.
Summary
Either method will work. Adding an additional row and using the equal function combined with the freeze feature is the fastest method and works best if you share the worksheet with coworkers. The only exception is if your main worksheet already has frozen panes you will want to choose the split window feature.