Microsoft Excel – Useful Shortcuts

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:

tickertable

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.

tableconverted

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:

tickerformatted

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.

tickerwithstatus

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:

AltEquals

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.

Advertisements


Categories: Microsoft Excel

Tags:

1 reply

Trackbacks

  1. Office 365 SharePoint Usage Report and Excel Weekday Function – Westmorr Consulting

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: