Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
programming4us
Windows 7

Microsoft Excel 2010 : Sorting Subtotals, Grouping and Outlining

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
12/3/2013 3:17:51 AM

1. Sorting Subtotals

If you try to sort a subtotaled dataset while viewing all the data, Excel informs you that to do so would remove all the subtotals. Although the data itself cannot be sorted, the subtotal rows can be, and the data within will remain intact. To do this, collapse the data so that only the subtotals are being viewed, and then apply the desired sort.

Sorting a Subtotaled Column

To sort the dataset by a subtotaled column, follow these steps:

1.
Collapse the dataset so only the subtotals are in view.

2.
Select a cell in the column to be sorted.

3.
Go to Data, Sort & Filter, and select the quick sort button for the desired sort order.

2. Grouping and Outlining

Selected rows and groups can be grouped together manually using the options in Data, Outline, Group. The Expand/Collapse button will be placed below the last row in the selection or to the right of the last column in the selection.

If the data to be grouped includes a calculated total row or column between the groups, you can use the Auto Outline option. This option creates groups based on the location of the rows or columns containing formulas. If the dataset contains formulas in both rows and columns, though, the option will create groups for both rows and columns. This tool works best if there are no formulas within the dataset itself, unless you do want the groups to be created based off those calculations.

Use the Group option for absolute control of how the rows or columns are grouped. For example, if you have a catalog with products grouped together, users can expand or collapse each group to view the products, as shown in Figure 1. By default, the Expand/Collapse buttons will appear below the data. To get them to appear above the grouped data, first apply a subtotal to the dataset with Summary Below Data unselected. Then undo the change and apply the desired groupings.

Figure 1. Group items together to make it easier for users to view only the desired items.


Groups can be cleared one of two ways from the Data, Outline, Ungroup drop-down:

  • Ungroup— Ungroups the selected data. Will ungroup a single row from a larger group if that is all that is selected.

  • Clear Outline— Clears all groups on a sheet unless more than one cell is selected, in which case the selected item will be ungrouped. If used on data that was subtotaled using the Subtotal button, the subtotals will remain; only the groups will be removed.

Manually Grouping Rows

To manually group rows with the Expand/Collapse button above the grouped dataset, follow these steps:

1.
Select a cell in the dataset.

2.
Go to Data, Outline, Subtotal.

3.
A message may appear that Excel cannot determine which row has column labels. Click OK.

4.
In the Subtotal dialog, deselect Summary Below Data and click OK.

5.
Click the Undo button in the Quick Access toolbar.

6.
Select the first set of rows to group together. Do not include the header. For example, to create the Hitachi grouping in Figure 1, select rows 4 and 5 to group. To create the Haier group, select only row 2.

7.
Go to Data, Outline, Group, Group, or just select the Group button itself.

8.
Repeat steps 6 and 7 for each group of rows.

Other -----------------
- Microsoft Excel 2010 : Subtotaling by Multiple Columns
- Microsoft Excel 2010 : Formatting Subtotals, Applying Multiple Subtotal Function Types (part 2) - Combining Multiple Subtotals to One Row
- Microsoft Excel 2010 : Formatting Subtotals, Applying Multiple Subtotal Function Types (part 1)
- Microsoft Excel 2010 : Expanding and Collapsing Subtotals, Copying Subtotals
- Microsoft Excel 2010 : SUBTOTAL Function, Subtotal Tool
- Microsoft PowerPoint 2010 : Managing Slides (part 2) - Rearranging Slides
- Microsoft PowerPoint 2010 : Managing Slides (part 1) - Undoing Mistakes
- Microsoft PowerPoint 2010 : Inserting Content from External Sources - Inserting New Slides from an Outline
- Microsoft PowerPoint 2010 : Creating New Slides (part 2) - Creating a Slide from a Layout, Copying Slides
- Microsoft PowerPoint 2010 : Creating New Slides (part 1) - Creating New Slides from the Outline Pane
 
 
Top 10
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
- Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
- Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
- Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
- Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer