Tips & Shortcuts for Inserting Excel Tables - KING OF EXCEL

Friday, May 1, 2020

Tips & Shortcuts for Inserting Excel Tables

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

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.
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 AltHT. 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: AltHTMenuCEnter
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.
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.
Excel Tables are a great way to work with data. I hope this post is helpful as you create them in your spreadsheets.
#evba #etipfree #eama #kingexcel
📤How to Download ebooks:

Popular Posts