10 tips for summarizing Excel data

Number crunching is Excel’s forte, so when you’re ready to move beyond the basic summarizing aggregates, such as SUM() and AVERAGE(), you’ll find a lot of power and flexibility at your disposal. These summarizing tips will help you put Excel’s summarizing features and functions to work for you.

1: Sort

The easiest way to summarize data is to start with a simple sort if all you need is a quick glance at ordered data. More important, many summarizing tasks require sorted data. If users don’t know that, the resulting analysis will be incorrect. When creating workbook applications for others, automate any required sort process if possible. In lieu of automation, be sure users are well trained and aware of this detail. It’ll mean the difference between valid data and a mess.

2: Selection

Sometimes, all you need is a little selection power; after sorting the data, select a subset. Figure A shows the summarized values for November 12 in the Status bar. This is a one-time deal — something you might rely on in a meeting. You can’t actually use the results in further calculations or summaries.

Figure A

The Status bar displays summary values for the selected subset.

3: AutoSum

AutoSum is one of the quickest ways to summarize data. Select a cell to the right or below a range of values and click AutoSum. Excel will enter a SUM() function that references the data above or to the left, as shown in Figure B. You can press [Enter] or change the reference. In addition, you can enter other aggregate functions, such as Average(), Count(), Maximum(), Minimum(), and so on. AutoSum also works with a multiple range of columns (or rows).

Figure B

Use AutoSum to get quick summary values.

4: Filter

Filtering might not be on your summarizing radar, but don’t dismiss it. Filtering allows you to selectively review specific data. It won’t summarize the data mathematically, but it will provide a unique view. Then, you can use AutoSum to summarize the visible data, as follows:

Apply a filter by selecting the data range ([Shift]+[Ctrl]+8).
Click the Data tab and click Filter in the Sort & Filter group. In Excel 2003, choose Filter from the Data menu and then select AutoFilter.

With the filter in place, display a subset. For instance, the filter in Figure C is the date November 12, which displays a subset of five records. At this point, you have a detailed summary of activity for November 12, but you can go a step further by using AutoSum (#3), which is smart enough to recognize the active filter and substitute the SUBTOTAL() function.

Figure C

Apply a simple filter to summarize data.

5: Subtotals

Sorting and filtering are easy to implement, but some tasks are more complex. Excel’s Subtotal feature summarizes values based on a corresponding value that changes. For this reason, Subtotal relies heavily on sorting (#1). To illustrate, we’ll use Subtotal to return the sum of all units sold on November 12 in our example sheet, as follows:

Sort the data range by the column that categorizes or groups your values in some way. Our date values are already sorted. Do not skip this step unless the data is already in the appropriate order!
Click inside the data range and press [Ctrl]+[Shift]+8 to select the sorted data range.
Click the Data tab.
Click Subtotal in the Outline group. In Excel 2003, choose Subtotals from the Data menu.

In Figure D, you can see that Excel does a good job of anticipating your task, but you can change the settings. In this case, Subtotal sums the sold values, inserting a subtotal for each date. The At Each Change In column should be the sort column in step 1.

Click OK to display the results shown in Figure E.

Figure D

Select the appropriate settings.

Figure E

Excel Subtotal inserts subtotaling rows.

6: Pivot table

Pivot tables are more about structure than summary, but they support some flexible summarizing options. To illustrate, let’s use a pivot table to summarize our example data by dates, as follows:

Select the data range.
Click the Insert tab. Then, click PivotTable in the Tables group. In Excel 2003, choose PivotTable and PivotChart Report from the Data menu to launch the wizard, click Next twice, and then click Finish. In this example, you won’t need to change any of the default settings.
Click OK to insert a pivot table in a new sheet.
From the task pane, drag the Date field to the Column Labels list, the Personnel field to the Row Labels list, and the Sold field to the Values list. Figure F shows the resulting pivot table, complete with summary values.

Figure F

The pivot table structure offers several ways to summarize data.

Once the table is in place, you can analyze the data in different ways. For instance, you could regroup the table to display values for the month instead of the individual days. Simply right-click the column header area and choose Group from the resulting submenu. Then, choose Months and click OK. The resulting table, shown in Figure G, would be more effective if we had dates in multiple months.

Figure G

You can quickly change the summarizing effect by regrouping the data.

7: Conditional aggregates

Using SUM() or AutoSum (#3), you can discern that 420 units sold during November. When questions are more complex, try Excel’s conditional aggregate functions, SUMIF(), AVERAGEIF(), and COUNTIF(). These functions act upon values that meet a specific condition. To illustrate, let’s use SUMIF()to determine how many units each person sold, as follows:

Enter a list of conditional values in E5:H5. In this case, that’s the personnel: Alexis, Susan, Kate, and Bill.
Enter the formula =SUMIF($B$6:$B$13,E$5,$C$6:$C$13) in E6 and copy it to F6:H6, as shown in Figure H.

Figure H

SUM() adds all the values in a range; SUMIF()adds only those values that meet a condition.

The first argument, $B$6:$B$13, identifies the conditional values (the names of the personnel). The second argument, E$5, refers to the individual condition. The final argument, $C$6:$C$13, identifies the values to sum. In other words, the function in E6 will sum only those values in column C where the corresponding value in column B equals “Alexis.”

8: Multiple conditional aggregates

The conditional aggregates reviewed in #7 evaluate one condition. When you have multiple conditions, use SUMIFS(), AVERAGEIFS(), and COUNTIFS(). Continuing with the example in #7, we can add a second condition, as follows:

Create a series of dates in D7:D10 to create the row labels for a simple matrix (refer toFigure I).
Enter the formula =SUMIFS($C$6:$C$13,$B$6:$B$13,E$5,$A$6:$A$13,$D7) in E7. Copy it to F7:H7.
Copy the row of formulas to E8:H10.

The result, shown in Figure I, is a simple matrix, similar to a pivot table (#6). The SUMIF() function returns totals for each person. SUMIFS() further reduces the sold values by considering dates.

Figure I

Use SUMIFS() to specify multiple conditions.

9: Dynamic multiple conditional aggregates

In #8, the criteria assume the equality operator (=). But what if users need more flexibility? For instance, Figure J shows a function that uses a bit of concatenation magic to count records that fall within a specific period. The equality operator can’t handle that.

Figure J

Concatenating the operators creates a dynamic formula.

The expression in B3, =COUNTIFS(A5:A12,”>=”&B1,A5:A12,”<=”&B2) relies on the COUNTIFS() function (#8) to evaluate multiple conditions. By concatenating the operators, the expression can evaluate a range of dates instead of a single date. The first range, A5:A12, refers to the dates the function evaluates. The first criteria argument evaluates to >=11/9/2011; the second evaluates to <=11/10/2011. As a result, the function counts any record where the date is later than or equal to 11/9/11 and prior to or equal to 11/10/11. This trick isn’t new, but it certainly simplifies many summarizing tasks.

10: Consolidate

The Consolidate feature’s traditional use is to merge and summarize data from multiple workbooks, but you can use it to summarize data in the same file — a use many people overlook. First, the feature requires a bit of setup:

The column(s) you’re summarizing must have a heading.
You must assign a range name to the column(s) you’re summarizing.
The values you’re summarizing by must be to the left of the values you’re summarizing.

With the above conditions met, you can execute this feature as follows:

Select the top-left anchor cell where you want to display the summary. (I chose E5.)
Click the Data tab and then click Consolidate in the Data Tools group. In Excel 2003, choose Consolidate from the Tools menu.
In the resulting dialog, click the Function drop-down to see what’s available and choose the appropriate function. (I chose Sum.)
In the Reference control, enter the range name (DataRange refers to A5:C13) that refers to the data you’re summarizing, as shown in Figure K. If any references are in the All References list, delete them.
Click the appropriate options in the Use Labels In section — usually, that includes both Top Row and Left Column.
Click OK, and Excel will display a summarized version of your data, as shown in Figure L. (You might have to format the date serial values in column E.)

Tuesday, 27 November 2012