Bottom Line: Learn helpful tips and keyboard shortcuts to insert and format Excel tables quickly.
Skill Level: Beginner
Download the Excel File
The file I use in the video can be downloaded here:
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.
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.
Your data field has now been converted to an Excel Table, which makes it easier to sort, filter, and format the data.
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.
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.
Keyboard Shortcuts for This Method
The keyboard shortcut to open the Format as Table menu is Alt, H, T. Then you can 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. But once you've highlighted the style you want, 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: Alt, H, T, Menu, C, Enter
If you're not sure what the Menu key looks like, or want to know more about how to use it, I've created these helpful posts:
Duplicating a Style
If you always want to use the same style within a workbook, 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.
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.
This placement at the top simply helps save time hunting for the style you want each time you create a new table. The Alt, H, T, Enter keyboard shortcut will also apply this first style in the Custom section.
You can also follow that with the Menu, C 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.
If you are new to Excel Tables, you can learn more about them here: Excel Tables Tutorial Video – Beginners Guide for Windows & Mac
When naming your tables, it's best to use a prefix. This tutorial explains why: Best Practices for Naming Tables
And this video will help you when you are creating pivot tables: 5 Reasons to Use an Excel Table as the Source of a Pivot Table
Excel Tables are a great way to work with data. I hope this post is helpful as you create them in your spreadsheets. If you have questions or tips about working with tables, I'd love to hear them in the comments.