How to Make First Row Header in Excel

puneet-gogia-excel-champs

- Written by Puneet

Headers are a very important part of data structure, making the data easier to read and understand. It helps users to understand what kind of data the cells will have under different columns based on the header’s names.

So, everyone who works in Excel knows the importance of having the first row of your data set as a header row.

Let’s take an example: Let’s say you have sales data with names, dates, and amounts. With headers, it’s easier to tell which column is which. To fix this, select the first row, then click “View” and “Freeze Panes” to keep the top row visible.

This way, you see the column titles even when you scroll down. This makes your data clear and easy to understand.

This tutorial will explain how to make the first row of your data a header row not with the one I have mentioned above but a three different ways.

  • Method 1 : You can use the Freeze Option Option. Go to the View > Freeze Panes > Freeze Top Row.
  • Method 2: Convert data into an Excel Table. Go to the Home > Format as Table or use the keyboard shortcut Control + T.
  • Method 3: To use power query, go to the Data Tab > From Table/Range > Open > Transform > Use First Row as Headers.
  • Method 4: You can use print titles to make the first rows a header in the printouts, and for this, Page Layout > Print Titles > “Rows to repeat at top”.

Steps to Make First Row a Header Row using Freeze Option

By applying Freeze to the first row of the data set, the first row will get frozen as the header row, and when you scroll downwards in the spreadsheet, the first header row will get visible all the time.

freeze-top-row
  1. First, open the data in the spreadsheet, where you want to freeze the first row as the headers row.
  2. After that, go to the “View” tab and click on the “Freeze Panes” icon and then click on the “Freeze Top Row” option from the drop-down menu.
  3. Or use the keyboard short “Alt → W → F → R” in sequence to directly choose the “Freeze Top Row” option.
  4. At this moment, your first row got frozen as the headers row, and when you scroll down the Excel sheet, the headers will get visible all the time.

Even with the freeze pane option, you can make more than one row as a header. For this, you need to use the select the A2 or A3 cell in the worksheet and then click on the “Freeze Pane” option. This option will freeze first two or first three rows and you will get them at the top while scrolling.

Convert Data into Excel Table to Make the First Row as Header Row

You can convert your data set into table format and make the first row a headers row by following the below steps:

  1. First, go to any cell within the data and then go to the “Home” tab.
  2. After that click on the “Format as Table” icon and choose the table format design.
    table-format-drop-down
  3. Once you choose the table format or press the keyboard short “Ctrl + T” together, you will get the “Create Table” dialog box opened.
  4. Now, you will see your data range is automatically got selected and you just select the “My table has headers” option and click OK.
    create-table-dialog-box
  5. At this moment, data will get converted into the table format with the first row as the headers row with filter icons.
    first-row-as-header-row

Select First Row as Header Row Using Power Query Editor

Now, let’s think about a unique problem: you have data in a workbook and want to get the data from that workbook and make the first row as a header.

With Power Query, you can use the first row of values as a header. And let me tell you loading data into Power Query and making the first row a header is easy and fun.

  1. First, go to any cell within the data set and go to the “Data” tab and there click on the “From Table/Range” option.
    get-table-range
  2. After that, see the selected range if it covered your whole data set, then un-select the “My table has headers” option and click OK.
    un-select-my-table-has-headers-option
  3. Now, under the “Home” tab, click on the “Use First Row as Header” drop-down icon and then select the first option “Use First Row as Headers”.
    use-first-row-as-headers
  4. Once you click on “Use First Row as Headers”, it will convert the first row of your data as headers.
    convert-first-row-as-headers
  5. In the end, click on the “Close & Load” icon and then click on the “Close & Load” option from the drop-down menu.
    choose-close-&-load-icon
  6. Once you click on the “Close & Load” option, your data will get transferred to a new sheet within the same workbook with the first row as headers.
    data-with-first-row-as-headers

Select First Row as Header Row using Page Layout

Many times, you need to make the first row of your data set the headers row and you want to print that headers row on each page while printing the data. Follow the below steps for the same:

  1. First, go to the “Page Layout” tab and then click on the “Print Titles” option and it will open the “Page Setup” dialog box.
    page-layout-print-titles
  2. After that, go to the “Rows to repeat at top” field and select the first header row, and then click on the “Print Preview” button to view if the headers are properly visible on the pages.
    page-setup-dialog-box
  3. In the end, go to the “File” tab and click on the “Print” option or use the keyboard shortcut “Ctrl +P” to go to the “Print” dialog box to print the pages.
Last Updated: May 25, 2024

Leave a Comment