How to Create Excel Table with Row and Column Headers

In this tutorial, I am going to show you 3 effective ways to create excel table with row and column headers. Headers help to make tables more readable. They are also the means to apply any sorting and filtering to the dataset. Although there are quite a few methods to add headers to table, they all have slightly distinct functions.


Download Practice Workbook

You can download the practice workbook from here.


3 Effective Ways to Create Excel Table with Row and Column Headers

1. Create Table in Excel with Column Headers

For this method, we will use a dataset of the marks of four students. Using the dataset we will create an excel table with row and column headers. Note that the data that you want to set as the table header, should be on row 4. Let us go through the steps to create an excel table with row and column headers in this dataset.

Create Table in Excel with Column Headers

Steps:

  • First, drag and select the whole dataset.
  • Likewise, you can click on cell B4 and press Ctrl+Shift+Down Arrow then Ctrl+Shift+Up Arrow as a shortcut.
  • After selecting the dataset, you should see the Quick Analysis option on the lower right.

Create Table in Excel with Column Headers

  • Here, click on this option and go to the Tables tab.
  • Next, click on the option Table.

  • Finally, excel will create a table from the dataset and more importantly add a header as well.

Read More: How to Change Column Header Name in Excel VBA (3 Examples)


2. Insert Excel Table with Column Headers Using Power Query Editor

The Power Query Editor in excel is really helpful to create an Excel table with row and column headers without making any changes to the source dataset. Follow the steps below to use this tool.

Steps:

  • First of all, select the dataset as I showed previously.
  • Then go to the Data tab.
  • From Get & Transform Data section click on From Table/Range.

Insert Excel Table with Column Headers Using Power Query Editor

  • After that, a new window with the name Power Query Editor will open.
  • Now, under the Home tab, click on Close & Load.

Insert Excel Table with Column Headers Using Power Query Editor

  • Finally, the Power Query Editor will create a table of our dataset within a new worksheet and will insert column headers.
  • Additionally, this will add filter options to allow the sort and filter of your data table.

Read More: Dealing with Tables with Changing Headers in Power Query


Similar Readings


3. Add Row Headers in Excel Table by Freezing Panes

For this method, we will use the previous dataset. But this time the data is listed horizontally instead of vertically. Also, we want to make cells B4 and B5 our row headers.

Steps:

  • To begin with, set the window in such a way that column B becomes the first visible column from the left.

Add Row Headers in Excel Table by Freezing Panes

  • Second, navigate to the View tab.
  • Then go to Window > Freeze Panes > Freeze First Column.

Add Row Headers in Excel Table by Freezing Panes

  • As a result, the cells B4 and B5 will now become the row headers and will not move from the screen.

  • Finally, to make the headers more visible, you can make them bold and change their fill color.

Read More: How to Make First Row as Header in Excel (4 Simple Methods)


How to Hide or Show Row and Column Headers in Excel Table

In some cases, you might want to hide row and column headers in your excel table to focus more on the dataset itself. You can do this very quickly by following the steps below.

Steps:

  • To start with, go to the Table Design tab on top of the screen.
  • Then, click on the Table Style Options dropdown.
  • Here, simply uncheck the Header Row options.

How to Hide or Show Headers in Excel Table

  • As a result, excel will hide the column headers.
  • Besides, if you want to bring the headers back, just check the option Header Row.


Conclusion

I hope that you found beneficial the 3 ways I showed in this tutorial. To better organize data and make the document easier to read, you can use these methods to create an excel table with row and column headers. Also, if you have headers in your data tables, you can easily scan them for a header of your interest. And lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles 

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo