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.

If you want to filter by more than one item in a slicer, Ctrl-click. To clear all filters, there's a clear button at the top right of the slicer.

5. Create a summary cell that changes when you filter a table. If you create a cell outside a table that summarizes data within a table -- the sum of a column, for example -- and you'd like that cell to display an updated sum if you filter the table by something, a basic SUM formula won't work.

Instead of simply using SUM in that cell, use the AGGREGATE function within your cell, and then your cell can be linked to your table filters.

aggregate 1a
Excel's AGGREGATE function requires three arguments, two of which are numbers. Excel for Windows offers lists of available options. (Click on image to enlarge).

AGGREGATE requires three arguments: A function number, a desired option number and the range of cells you want to operate on. Type =AGGREGATE( in Excel for Windows and you'll see the available functions and options; in Excel for Mac, you'll have to click on the AGGREGATE help function in order to see available function and option numbers.

SUM is function number 9; ignore hidden rows is option 5. So, a cell with the following code:

=AGGREGATE(9,5,Table1[Expenditures])

gives you the sum of all visible rows only. If a filter changes which rows are visible, your sum will change accordingly.

aggregate2
AGGREGATE offers the option of summarizing only visible rows. (Click on image to enlarge).

6. Sort data in a pivot table. Sometimes you'd like to sort data by a specific column in a pivot table -- just as with a regular table. But unlike regular tables, pivot tables don't have dropdown menus on each column offering the ability to sort. However, if you choose the lone dropdown arrow on the first column, you'll get a menu allowing you to sort by any column.

7. "Unpivot" data. Some call this reshaping data from "wide" to "long". In the database world, it's known as "fold": Taking data from individual columns and moving them into rows. Basically, it's the opposite of creating a pivot table -- in a pivot table, you pull categories within one column up into their own columns.

To unpivot columns, you need to use the Query Editor in Excel 2016. Access the Query Editor via the Data ribbon: In the Get & Transform section, choose From Table.

Once the Query Editor comes up (if your data isn't already in a table, you'll be asked to confirm a data range first), select the columns you want to unpivot, click on the Transform tab and chose Unpivot Columns.

 

Previous Page  1  2  3  4  5  Next Page 

Sign up for CIO Asia eNewsletters.