Creating Tables in Excel

There are two quick and easy ways to insert an Excel Table using existing data in a spreadsheet.

1. Using the Insert Tab

The first way is to go to the Insert tab in the Ribbon and select the Table icon. (First make sure your selected cell is anywhere in the data set that you want to convert into a table). The keyboard shortcut for this procedure is Ctrl + T.

Insert Excel Table Using Insert Tab on Ribbon

This will bring up the Create Table box, which is prepopulated with the existing boundaries of the data set you’re in. Assuming you want all of that data in your table, just select OK.

Create Table Dialog Box

Your data field has now been converted to an Excel Table, which makes it easier to sort, filter, and format the data.

Example of Excel Table

Changing the Style

If you had applied formatting to your data before making it a table, the Insert Table feature does not override that formatting. If you want to change the style, you can go to the Table Design tab on the Ribbon and right-click on the selected style. This gives you an option to Apply and Clear Formatting. When you select that option, the previous formatting is replaced with the style you’ve selected.

Apply and Clear Formatting

2. Using the Home Tab

Another way to insert a table, which might be a little bit faster if you have formatting that you want to clear, is using the Home tab.

From the Home tab on the Ribbon, select the Format as Table menu. This will bring up a gallery of different styles to choose from. If, as above, you right-click on the style you want, and select Clear and Apply Formatting, you’ve combined the creating of the table with the formatting at the same time.

Format as Table from Home Tab

Keyboard Shortcuts for This Method

Once you improve your knowledge of Excel, you will find keyboard navigation to be very fast and intuitive. To get you started on your way to keyboard kingdom, let me give you a few pointers.

The keyboard shortcut to open the Format as Table menu is AltHT. You can then use the arrow keys to select the style you want and hit Enter.

This does not clear the existing formatting, but if you want to do that, you can use the keyboard shortcut above. Once you’ve highlighted the style you want, proceed to click on the Menu key to bring up the right-click menu that we’ve already seen, then press C for the Apply and Clear Formatting option. The underlined letter is the shortcut key for each option.

So the full keyboard shortcut to create a Table and Clear and Apply Formatting is: AltHTMenuCEnter

Duplicating a Style

If you want to keep your styling consistent, here’s a tip to quickly access the style you like. From the gallery of styles, right-click on the one you like and choose Duplicate.

Duplicate Style

This will open a box allowing you to customize the style or leave it as is. Once you kit OK, this style will appear at the top of your gallery as a Custom style.

Custom style

This placement at the top simply helps save time hunting for the style you want each time you create a new table. The AltHTEnter keyboard shortcut will also apply this first style in the Custom section.

You can also follow that with the MenuC shortcut to Clear and Apply Formatting.

Keep in mind that this custom styling is only for the workbook you are in and does not carry over to other workbooks.

You can expand your knowledge of Excel through our Microsoft Excel Courses.