Tips & Shortcuts for Inserting Excel Tables

Bottom Line: Learn helpful tips and keyboard shortcuts to insert and format Excel tables quickly.

Skill Level: Beginner

Video Tutorial

Watch on YouTube and give it a thumbs up.
YouTube Subscribe Logo Excel Campus

Download the Excel File

The file I use in the video can be downloaded here:

Insert Table Shortcuts.xlsx (68.7 KB)

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

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:

Excel Keyboard Shortcuts for the Menu Key

Best Keyboards for Excel Keyboard Shortcuts

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.

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 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.

Related Posts

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

Conclusion

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.

    • yes, I’m a ctrl-L man too, as the closely-related ctrl-shift-L toggles the closely-related autofilter

      another method I’ve recently developed is to add the Format as Table button to the QAT, then using this to right-click my preferred style and select Apply and Clear Formatting

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >