Excel table is useful as you can organize and analyze data quicker. You can convert a data range into a table with features like filtering and sorting. It is straightforward to create a simple table in Excel by using a shortcut. This tutorial shows you how to create an Excel table with data and headers.
Table of contents
Prepare your data
Before you start, you need to have data entered into your Excel sheet. Even though your data looks like a table, it is not the same as an Excel table. It has more features, where you can sort, filter, and format data easier. Look at the guidelines below about preparing your data.
- Make sure that your data is organized in rows and columns. It should contain information about a single piece of data.
- Write a different title for each column.
- The data list should not contain any blank rows or columns.
- Keep your data separated from other tables or data in your Excel sheet. Don’t combine multiple data lists next to each other. There should be at least one row or column in between.
Creating an Excel table with data and headers
If you have a list of data written in your spreadsheet, you can convert it into a table with or without headers. Follow the steps below.
- First, you have to open your Excel spreadsheet with data.
- Select a cell in your data list. Or select all the data that you want to include in your table with your mouse cursor.
- Click on Insert at the top > Then click on Table in the ribbon.
- Now a Create Table box opens up. Make sure that all the data is selected. If you don’t have any headers, you don’t have to do anything.
- If you want to include headers, you should put a check in front of My table has headers.
- Then click OK to confirm.
Creating an Excel table using a shortcut
You can also create an excel table with data by using a shortcut. This way, you avoid pressing the Insert button and clicking on the Table in the ribbon.
- First, click on a cell inside your data.
- Then use the keyboard shortcut: CTRL + T.
Press the CTRL button and the letter T on your keyboard. This will open the Create Table box in your spreadsheet, and it automatically selects the data. It is much faster to use a keyboard shortcut.
Table with data filters
If you followed the steps above, you know how to create an Excel table with headers. You may have noticed a few changes once you have converted the data range into this table.
By looking at the pictures below, you can see a difference in its styles. The rows in the table have two different colors, where one row is white, and the other is light blue. The headers in the right picture are bold and colored blue. Each header has a drop-down arrow, which you can use to sort and filter.
Data filters in a table mean that you can uncheck some of the data you don’t want to use. This way, you can emphasize the data that is most important to you. Click on one of the drop-down arrows next to a header, such as Color. Then uncheck the color blue > Click OK.
Now, the table looks different as the products (rows) with the color blue are removed. After you used the filter, the table has fewer rows than before. You can also see a filter icon next to the header Color to let you know it is used in this column.
Change the table name
When you first create a table, it will automatically be named. You can find this if by clicking on a cell inside your table. Then go to the Table Tools > Click on the Design tab. On the left in the ribbon, you can see the Table Name. In our example, it is named as Table1.
You can rename the table to make it easy to recognize it and use it in formulas. Click on Table1 once to select it. It becomes blue, and you can write the name you want. Then press ENTER on your keyboard or click anywhere else in your sheet. Please make sure to write one word without spaces.
Now you have learned how to format a table with data in Excel. Once you have your data ready in your spreadsheet, you can convert it into a table. This table will include headers and many features that make it easy for you to analyze the data.