Tables in Microsoft Excel are used frequently. They help with formatting, filtering, sorting, creating ranges, and are even required to light-up or correctly use other features in the application. In this quick post, let me share two shortcuts that I use basically every time I work with tables in Microsoft Excel.

I’ve decided to add other short cuts that aren’t related to tables to this post as well. This is based on the types of traffic that this article was receiving (and the fact that I want it to be helpful). So, take a look through the entire post and you may find some other non-table related shortcuts that you can use in your daily work with Microsoft Excel.

I’ll keep adding to this post so check back or subscribe to the blog.

Ctrl + t – Create Table

The first shortcut is Ctrl + t – the shortcut for creating tables. To start, create a set of tabular data that you haven’t yet converted to a table. For example:

Now, to convert this data to a table (i.e. to create the table), highlight any cell in the range and press Ctrl + t. You’ll get the familiar “Where is the data for your table?” dialog with options for specifying headers. Press OK, and you’re done. This will likely work for many of the tables you want to create in Microsoft Excel.

Ctrl + Shift + <Arrow> – Select Continuous Cells

The second shortcut is Ctrl + Shift + <Arrow> shortcut for selecting continuous sets of cells . It’s important to note that this shortcut actually works for any set of continuous cells, you don’t need to have your data converted to a table. In this example though, I’ll work with the following table:

What I want to do is quickly select all of the “Shares” values, but I don’t want to include the “Shares Total” value of 2800 in cell D10. Of course, in my sample table I could just use the mouse because it’s only 6 rows, but in reality you’ll probably have more than 6 rows (and I also find this shortcut so useful that I’d still use it over the mouse).

To select all of the values from D2:D7, just highlight cell D2, and press Ctrl + Shift + Down-Arrow. That will highlight all cells below D2 and automatically stop as soon as there is a break in continuous data. Now I can quickly refer to the Status Bar values without accidentally skewing my data such as count and average.

Again, you don’t need to have data formatted as a table to use this second shortcut; I just find I’m most often selecting data this way when I happen to be working with tables. Also, this works horizontally too (e.g. Ctrl+Shift+<Left/Right Arrow>).

Alt + = to Auto Sum

Another favorite is Alt + = sign used to insert the SUM formula into a cell which will add up those cells appearing above in a continuous numeric series. Given the following spreadsheet for example:

If I press Alt + = in B33, it will sum B19:B32. Knowing to stop when it hits a non-numeric cell in the series at B18. The same would be true if I pressed Alt + = in B16, it would automatically choose the series B2:B15.

Conclusion

In summary, useful shortcuts when working with tables in Microsoft Excel and, recently added, more general useful shortcuts:

1. Create a table with Ctrl + t
2. Select continuous column or row cells by using Ctrl + Shift +

Thanks for reading.

Categories: Microsoft Excel

Tags: Microsoft Excel