Row and Column Labels

This is a tip for absolute beginners to Excel. Most people who are familiar with the program should be well aware of this feature.

When working with spreadsheets, you will usually want to know what it is that you are looking at. For that reason, it is usually best to put labels for your columns and maybe your rows. My list of reported sightings is an example of a spreadsheet that uses both.
http://cid-2e6efb80915ed5bd.office.live.com/view.aspx/Public%20Notes/Sightings.xlsx

It can be difficult at times to deal with larger spreadsheets. Heading rows and columns are frequently pushed out of view. If you want to know what a number means, you will either have to scroll to the headers or find a way to keep the headers in view.

To keep the headers in place, split the panes. There is a rectangular box above the vertical scroll bar and another rectangular box to the right of the horizontal scroll bar. Simply click and drag these boxes to create two horizontal panes, two vertical panes, or four panes in a 2x2 layout.

Google Spreadsheet allows the same thing. At the top-left corner, there are two thick blue lines that can be dragged to split the panes.

There is a difference between these two spreadsheet applications. Google Spreadsheet locks the panes, while locking the panes is optional in Excel. What do I mean by locking panes? Rows above a split and columns left of a split can’t be scrolled if the panes are locked. If the panes are unlocked (default in Excel), you can scroll two or four windows independently of each other. This allows you to work with multiple locations within a single spreadsheet at the same time.

Since the most useful purpose of splitting panes is to keep track of the labels, you will usually want to lock panes. To do this, simply tell Excel to “Freeze Panes” in the View tab.

Also notice that this option allows a faster approach to freezing just the top raw and just the first column. Since most panes are frozen for their labels, this can be a nice shortcut.

What about printing? You don’t scroll through data on a printout the same way that you would on a computer monitor, but similar problems can be encountered. In big spreadsheets you will not be able to keep all of the data on a single page. Trying to figure out what a specific value means is not going to be easy if you have to keep returning to the first page to find the labels. What you have to do is tell the program to print the labels.

I’m going to start with Google Spreadsheets this time. Google tries to keep things simple and defaults at printing row headers. Unfortunately, this isn’t very flexible. From what I can tell (I could be wrong since I use Excel far more than Google Spreadsheets), it prints the rows that are frozen at the top of each page. It does not allow the columns that are frozen to be printed on each page, and it does not allow the printing area to differ from the frozen panes.

In Excel, you can select the rows and columns to print at the beginning of each page. You start by clicking on Print Titles in the Page Layout tab. This will open a dialog box.



To select the rows and columns to print at the beginning of each page, simply enter which rows and columns in their appropriate text boxes. To make things easier, you can select the rows or columns with the dialog box open. Just make sure that you have selected the appropriate text box first. Clicking the icon to the right of the text box will shrink the dialog box to display only the text box that you are working with. As soon as you hit OK, the new settings will be saved. Any printout of the worksheet will include the rows and columns that you had selected.