Subscribe / Unsubscribe Enewsletters | Login | Register

Pencil Banner

11 Excel tips for power users

Sharon Machlis | May 2, 2016
Learn how to make tables more effective, reshape data, easily look up data and more.

3. Easily select columns and rows. If your data is in a table and you need to refer to an entire column in a new formula, click on the column name. That will give a reference to the full column by name -- useful if you later add more rows to the table, because you won't have to readjust a more specific reference such as B2:B194.

Note: It's important to make sure your cursor looks like a down arrow before you click on the column name. If your cursor looks like a cross when you do so, you'll get a reference to just that lone cell, not to the whole column.

Whether or not your data is in a table, there are a couple of handy selection shortcuts you can use: Shift+spacebar selects an entire row and Ctrl+spacebar (or control+spacebar for a Mac) selects an entire column. Note that if your data isn't in a table, these selections go beyond available data and include any empty cells beyond. For table data, the selections stop at the table's borders.

If you want to select an entire column that's not in a table with just the cells that have data in them, put your cursor in a column next to it, hit Ctrl-down arrow, use the right or left arrow key to move to your desired column, and then hit Ctrl-Shift-up (use command instead of Ctrl on a Mac). This can be handy if your data column is quite long.

4. Filter table data with slicers. Excel tables offer drop-down arrows next to each column header for easy sorting, searching and filtering. However, trying to filter data with that small drop-down when you've got a large number of items can be somewhat cumbersome. Several of the presenters at the Data Insights Summit suggest using slicers instead.

"Anybody who sends you a pivot table without slicers, you should teach them slicers in 30 seconds. People love slicers," said Indiana University professor Wayne Winston, who also advises Dallas Mavericks owner Mark Cuban on basketball stats.

But while slicers were originally developed for pivot tables, they now work on "regular" tables as well (and have since Excel 2013 on Windows). "This is actually more useful," Winston argued. (Slicers are available for pivot tables but not regular tables in Excel for Mac 2016.)

To add a slicer to a table, with your cursor already somewhere in the table, head to the Design ribbon, select Insert Slicer and then choose which column(s) you'd like to filter.

The slicer will show up on your worksheet, appearing one column wide with just a few items showing. But if you have a long, narrow spreadsheet with lots of space to the right of your data, you can resize a slicer to be considerably wider than the default. You can add columns to the slicer layout within the slicer options on the Ribbon.

 

Previous Page  1  2  3  4  5  Next Page 

Sign up for CIO Asia eNewsletters.