Search Contextures Sites

Contextures Excel news by email Email: Name (optional): We respect your email privacy	Excel Pivot Table Tutorial -- Grouping Data Grouping Data Video: Grouping Dates Grouping Dates Grouping Dates by Week Video: Grouping Numbers Grouping Numbers Grouping Text Items Problems when Grouping Data Count Unique Items Grouping in One Pivot Table Affects Another Download the Sample File Excel Pivot Table Tutorial List Grouping Data In an Excel pivot table, you can group dates, number and text fields. For example, group order dates by year and month, or group test scores in bands of 10. You can manually select text items in a pivot table field, and group the selected items. This lets you quickly see subtotals for a specific set of items in your pivot table. Video: Grouping Dates To see the steps for grouping dates, please watch this short video tutorial. There are written instructions below the video. Grouping Dates To group the items in a Date field Right-click on one of the dates in the pivot table. In the popup menu, click Group In the Grouping dialog box, select one or more options from the 'By' list. To limit the dates that are grouped, you can set a Start and End date, by typing the dates in the 'Starting at' and 'Ending at' boxes Click OK to close the dialog box. Grouping Dates by Week To group the items in a Date field by week Right-click on one of the dates in the pivot table. In the popup menu, click Group In the Grouping dialog box, select Days from the 'By' list. For 'Number of days', select 7 The week range is determined by the date in the 'Starting at' box, so adjust this if necessary. In the example below, January 1, 2012, was entered as the starting date. Click OK Video: Grouping Numbers Use grouping to create a concise summary, instead of showing a long list of individual numbers. This video shows you the steps, and the written instructions are below the video. Grouping Numbers Instead of listing numbers individually in a Row field, you can group them, to create a concise summary of the data. In this example, the original pivot table shows the units sold, per product. In column C, it shows the number of orders where that quantity was sold. To summarize the orders, group the units by tens. So, instead of 3 and 9 appearing in separate rows, they'll be summarized in the grouping 1-10. To group the numbers Right-click on one of the unit numbers in the pivot table. In the popup menu, click Group In the Grouping dialog box, enter 1 in the Starting At box In this example, the highest number of units is 50, and you can type a higher number, if necessary. Click OK, to apply the grouping In the pivot table, the Units now appear in groups -- 1 - 10, 11 - 20, etc. To make a more concise layout, you could move the grouped units field to the Columns area, as headings.   Grouping Text Items The Grouping dialog box isn't available if you try to group items in a text field. Instead, you can manually group the items. Select the items that you want to group On the Ribbon's Options tab, click Group, then click Group Selection To rename the group: Click on the heading cells with the default name, e.g. Group1 Type a new name. Problems when Grouping Data If you try to group a date or number field, you may see an error message that says, "Cannot group that selection." This problem usually occurs when the field contains records with a blank date/number field, or text in a date/number field. To fix the problem For blank cells, fill in the date/number (use a dummy date/number if necessary). If there is text in the date/number field, remove it. If numbers are being recognized as text, use one of the techniques to change text to real numbers. Then return to this pivot table tutorial, and try the grouping steps. If you don't have blank cells or text in the date column, there may be a grouped field left over from the previous time that you grouped the data. Check the field list, to see if there's a second copy of the date field, e.g. Date2. If there is, add it to the row area, and ungroup it. Then, you should be able to group the date field again. Count Unique Items In an Excel pivot table, you may want to know how many unique customers placed an order for an item, instead of how many orders were placed. A pivot table won't calculate a unique count. However, you could use one of the following workarounds: Use PowerPivot to create the pivot table, and use its functions to create a unique count. See the details here Add a column to the database, then add that field to the pivottable. Follow the instructions below Add a Column to Calculate Unique Counts For example, to count the unique occurences of a Customer/Item order: add a column to your database, with the heading 'CustItem' In the first data row, enter a formula that refers to the customer and item columns. For example:   =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1) Copy the formula down to all rows in the database. Then, add the field to the data area of the Excel pivot table. In this example, you can see that 7 unique customers placed an order for binders, and there were 13 orders for binders.  Grouping in One Pivot Table Affects Another If you create two pivot tables based on the same Excel Table in Excel 2007, when you change the grouping in one pivot table, the same grouping appears in the other pivot table. Because you created the two pivot tables from the same source data, by default they use the same pivot cache, the grouped items are the same in both tables. To use different grouping in each pivot table, you'll need to create a separate pivot cache for each pivot table. Use the following method, suggested in the Excel newsgroups by Dave Peterson. To create a separate pivot cache for the second pivot table: Cut the second pivot table, and paste it into a new workbook. Change the grouping of the second pivot table. Cut the second pivot table from the new workbook, and paste it back into the original workbook. Download the Sample File Download the zipped sample file for this Excel tutorial

Pivot Table Tutorials Pivot Table Introduction  Clear Old Items in Pivot Table Create a Pivot Table in Excel 2007  Custom Calculations  Data Field Layout Dynamic Data Source FAQs - Pivot Tables Field Settings Filter Source Data   Filters, Top 10  Filters, Report Filters GetPivotData Function Grand Totals Grouping Data Layout, Excel 2007 Multiple Consolidation Ranges Pivot Cache    PivotTable Style Printing a Pivot Table    Protection   Running Totals   Show and Hide Items  Sorting Subtotals  Summary Functions Unique Item Count

Pivot Table Books Beginning Pivot Tables (Excel 2007)  Pivot Tables Recipe Book (Excel 2003)  Pivot Tables Recipe Book (Excel 2007)  Pivot Table Add-Ins Pivot Power  Pivot Play PLUS  Pivot Table Videos Clear Old Items Copy a Custom PivotTable Style Create Pivot Table in Excel 2007 Create Pivot Table from Multiple Sheets Data Field Layout Date Filters, Add GetPivotData Group Data Layout, Excel 2007 Report Filters, Add Running Totals Select Sections Subtotals, Create Multiple Top 10 Filters

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2013 All rights reserved.	Debra Dalgleish