Ever find yourself in the middle of a long spreadsheet, having forgotten which column is which and needing to scroll all the way back to the table headings to make sense of what you’re looking at? Not exactly a fun way to spend you time. Nor is it the most efficient. That’s why you’ll be glad to know there is a way to keep all of the identifying information in view as you scroll down rows and rows of data.
This is the freeze panes option, and it is one of many time saving tools you’ll learn in a complete online course on Excel.
This brief article will show you how you can use the freeze panes feature to set up your document for a more effective view, leading to increased efficiency. We’ll go through several options for configuring the feature and take a look at how to remove it when you no longer need to have it turned on. Finally, we’ll take a look at how you can essentially achieve the same view on printout versions of your spreadsheets. Note that this tutorial uses Excel 2010. The methods in earlier versions of Excel may differ slightly. And editions prior to Excel 2007 will take a different approach. You may want to take a look at the official Microsoft documentation to complete this for your software.
Freeze the first row
The most common need that you will experience with Excel is, no doubt, to keep your column headings in place. So that is where we’ll start. It’s a simple operation and really just involves knowing where to look. Assuming you’ve got your workbook open to the spreadsheet you need, follow these steps.
1. Click on the View tab from the main menu.
2. From the View Ribbon, select Freeze Panes, which will bring up a dropdown menu.
3. Select the Freeze top row option from the dropdown menu.
Remember that, even though it is the column headings you want to stay in place, it is the top row you want frozen, because this is where that information will be placed. You’ll know that the process has been successful if a black border appears under the top row. Then to confirm, simply scroll down the spreadsheet, and your column headings should remain in place.
Freeze the first column
In some cases, you will be looking at a lot of records that, for whatever reason, require you to scroll horizontally across the spreadsheet, rather than vertically. If you find yourself with in this situation, you can use the freeze pain function on your inital column, rather than the top row, so that you can keep the row labels in view.
Here is the procedure to follow:
1. Click on the View tab to bring up the View ribbon.
2. Select Freeze Panes, which will bring you the dropdown menu for the feature.
3. Select Freeze First Column, since this column will contain your row labels.
You should see a black border along the right side of the first column. You can then scroll horizontally to confirm that the procedure was successful.
As an aside, many times a spreadsheet that extends out horizontally is less than ideal, and an unintended consequence of less-than-stellar spreadsheet planning. If what you really want to do is flip flop the rows and columns, you can do that using the transpose function. An advanced Excel course will guide you through this and other helpful array functions for rearranging data.
Freeze columns and rows
Once in a while, you come across that spreadsheet that is just chock full of data. Not only does it have a multitude of record entries for its columns, but it also has a seemingly endless list of data entries filling up the rows. In this case, you will want to freeze panes for both the rows and the columns to make it easier on yourself as you navigate the sheet. Here’s what you’ll do:
1. Select the first cell inside the table that is not a row or column label. For instance, if your columns are listed across row 1 and your rows are labeled down column A, you will want to select column B2.
2. Cick on the View tab from the main menu.
3. Click on Freeze Panes to bring up the drop-down menu.
4. Select the option labeled Freeze Panes.
This time, you should see a black border both under your column headings and to the right of your row labels. You can test out whether you were successful by scrolling both horizontally and vertically. Your first row and your first column should both remain in place as you do.
Sometimes, you may not want to keep the Freeze Panes feature turned on after you have finished with the spreadsheet. And, as you would hope, Microsoft has planned for that. Also, as you might have guessed, you will be using roughly the same procedure, with a different option from the dropdown menu. Here are the steps for unfreezing the panes:
1. Click on the View tab
2. Select the Freeze panes option
3. From the drop-down menu, select unfreeze panes.
The black borders will have dissappeared and column/row labels will no longer follow along with you as you scroll within the spreadsheet.
Bonus tip: print your headings
Technically, this feature is not related to freeze panes, but it works the same way when you are working with a printed document. You don’t want to be flipping back to the first page constantly to recall which row or column you are looking at. So you can make sure that Excel prints the headings for you on each page. Oddly, it does not do this by default.
Here is the process:
1. With your workbook open to the appropriate spreadsheet, click on the Page Layout tab.
2. In the Sheet Options group, check the box next to Print, under headings.
Keep in mind that you will need to repeat this for each spreadsheet that you want printed in this way. If you have experience printing Excel spreadsheets, you know that the result can often be messy and leave you with some unreadable material. Take a course in Excel essentials if you would like to learn to avoid these common problems and pick up a lot of information on making your experience as simple as possible.
Hopefully this brief tutorial gives you what you need to stop spending time scrolling up and down to locate where you are in a spreadsheet. If you would like to learn additional time-saving Excel features, you can start today with a comprehensive course in Microsoft Excel.