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.

unpivot1
Excel's Query Editor provides users with the option to unpivot columns. (Click on image to enlarge).

That will create two new columns at the right of your spreadsheet, Attribute and Value, with the columns you unpivoted. You can rename those columns to something that makes more sense, such as "Product" and "Price" or "Quarter" and "Revenue."

To save your work, select File > Close & Load (to the default destination) or File > Close & Load To in order to be asked where you'd like to save your results. If you try to close without saving, you'll be asked whether you want to keep your changes; say Yes and they'll be saved on a new worksheet.

unpivot2
Unpivoting data turns a wide table into a longer one, combining multiple columns into two: attribute (category) and value. (Click on image to enlarge).

The Microsoft Office website has more information on unpivoting.

8. Make multiple pivot tables for one column of categories. If you have a pivot table and add a filter for one column that contains categories, you can generate copies of that pivot table, one for each category in your filter, by going to Analyze > Options > Show Report Filter Pages and then selecting the filter you want. This can be handier than having to click through each category in your filter manually.

(On Excel 2016 for Mac, go to the PivotTable Analyze tab on the Ribbon and choose Options > Show Report Filter Pages.)

9. Look up data with INDEX MATCH. While VLOOKUP is a popular way to find data in one Excel table and insert it into another, INDEX combined with MATCH can be more powerful and flexible. Here's how to use them.

Let's say you have a lookup table where column A has computer model names, column B has price information, and column D also the name of a computer model where you want to add price info. Create a formula using this format:

=INDEX(ColumnToSearchForValue, MATCH(CellWithLookupKey, ColumnToSearchForLookupKey, 0)

A sample might look like:

=INDEX(B2:B73, MATCH(D2, A2:A73, 0))

This is how/why INDEX MATCH works (if you don't need to know, skip to the next tip): INDEX selects a specific cell by numerical location. You first give it a range of cells, either within a single column or a single row, and then tell it the specific number of the cell you want.

For example, you could pick the 6th item in column B with:

=INDEX(B2:B19, 6).

You'd be using the following format:

=INDEX(ColumnOrRowToSearch, ItemNumberInThatColumnOrRow)

However, using INDEX alone isn't much help if you want to find a value based on some condition in another column. That is, you don't want the 6th item in your Price column B; you want the item in your Price column that matches something in column A, such as a certain computer model.

 

Previous Page  1  2  3  4  5  Next Page 

Sign up for CIO Asia eNewsletters.