In order to have Excel recognize your data as a table, you should put labels at the top of what

By Shawn McClain Updated February 12, 2019

Table of Contents Show

Apply formatting
Format as table with header row
Freeze header row
Add print title
Instant Connection to an Excel Expert

For many small business owners, Microsoft Excel is not only a powerful tool for internal tracking and bookkeeping, but it can also be used to prepare documents for distribution to partners or customers. When creating a spreadsheet for distribution, controlling the spreadsheet's appearance ensures it appears professional to colleagues and outside contacts. Excel offers two types of column headings; the letters the Excel assigns to each column, which you can toggle in both view and print modes, or the headings that you create yourself and place in the spreadsheet's first row, which you can then freeze in place.

Excel refers to rows by number and columns by letter, starting the first row at one and the first column with "A". For some purposes, this is fine, but you often want to add your own column labels in Excel specifying for yourself and other people using the spreadsheet what each column contains.

For instance, if each row is an employee record, you might label columns with headers such as "first name", "last name", "email address" and the like.

Open the Excel spreadsheet where you want to define your column headings.
Click the "Page Layout" tab at the top of the ribbon, then find the Sheet Options area of the ribbon, which includes two small checkboxes under the Headings category.
Add or remove a check mark next to "View" to reveal or hide, respectively, the Excel headings on the spreadsheet. The headings for the columns and rows are linked, so you can only either see them both, or hide them both. The column headings will be letters and the row headings will be numbers.
Place a check mark in the box next to "Print" to have Excel include the column and row headings on anything that you print out. These headings will appear on every page that you print, not just the first.

Open the spreadsheet where you want to have Excel make the top row a header row.
Enter the column headings for your data across the top row of the spreadsheet, if necessary. If your data is already present in the top row, right-click on the number "1" on the top of the left side of the spreadsheet and choose "Insert" from the pop-up menu to create a new top row, then enter your headings by typing in the appropriate cell.
Click on the number "2" on the left side of the spreadsheet to select the second row, which is the now the first row under the headings and the first containing actual data.
Click the "View" tab in the ribbon menu, and then click the "Freeze Panes" button in the Window area of the ribbon. Your column headers now stay visible as you scroll down the spreadsheet, letting you see which column is which as you edit the document.
Click the "Page Layout" tab if you want your headers to print on every page of the spreadsheet. Click the arrow next to "Sheet Options" in the ribbon to open a small window. Check the box next to "Rows to repeat at top," which shrinks the window and takes you back to the spreadsheet. Click the number one on the left side of the spreadsheet, and then click the small box again to return the window to its normal size. Click "OK" to save your changes.

A table without a header row can cause confusion and reduce efficiency in tracking and managing data because we always have to wonder what each of the values are referring to. Fortunately, Excel offers several ways to make a header row that will help us become more efficient and effective in presenting and handling data on a spreadsheet.

Figure 1. Final result: How to make a header row

Suppose we have below data.

Figure 2. Sample table with no header row

How to create a header row

Apply formatting
Format as table
Freeze rows
Add print title

Apply formatting

We can make the first row as header by changing the format of the first row of data in order to make its appearance distinct from the other cells. We can apply the following format:

Bold text
Fill the cell with color: Blue, Accent 5, Lighter 60%
Bottom double border

Figure 3. Output: New format reflected

Now we can easily identify the first row as the header row.

Format as table with header row

In order to format our data as a table, we follow these steps:

Select the cells we want to format
Click Home tab > Format as Table > Table Style Light 9

Figure 4. Format As Table in menu

Click OK. The Format As Table dialog box will appear. Verify the range B2:F7 for our data set and ensure that the checkbox is ticked for My table has headers.

Figure 5. Format As Table dialog box

We have successfully formatted our data as a table with a header row, and Excel automatically adds filter arrows that can be used to sort or filter the values per column.

Figure 6. Output: Format as table

Freeze header row

When navigating through a list or data that consists of many rows, it is wise to freeze the first row of data such that we can always see the labels even when we have scrolled so far down the worksheet.

In order to freeze the first row so that it is always visible on screen, we follow these steps:

Click the second row of data, or the row just below our header row
Click View tab > Freeze Panes > Freeze Panes

Figure 7. Freeze Panes in menu

After freezing, the header row will always be present on-screen even when we scroll down to the last row in the worksheet.

Figure 8. Output: Freeze header row

Add print title

For long lists or large data sets that are more than one page, it is very helpful to have a header row so that our data is properly labelled in every page that we print or preview. In order to add a print title, we follow these steps:

Click Page Layout tab > Print Titles
In the Page Setup dialog box, Sheet tab, enter row 2 as $2:$2 in Rows to repeat at top or click the icon and click row 2.

Figure 9. Add print title option in Page Setup

This method ensures that the selected header row (row 2) is repeatedly displayed on every page as we print or preview the worksheet.

Figure 10. Preview of page 1 with header row

Figure 11. Preview of page 2 with header row

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.