Group, Ungroup and Subtotal in Outline Excel

Group, Ungroup and Subtotal in Outline Excel

Aggregate:

Excel offers visual aggregate for better data representation using Group, and Subtotal by which data can be grouped and summarize in a single click. Let’s see how can do group (Ungroup works opposite to group) and Subtotal in Excel:

  • First rule the data must be sorted in one of the orders (Ascending or Descending)
  • Select rows or columns that you would like to bring under group
  • Navigate Data ribbon tab
  • Click on Group command under Outline group
  • A popup comes asking grouping by Row or Columns
  • Make a choice and hit OK

In this example I chose Row Grouping hence data grouped row wise, Clear Outline will clear grouping:

figure 1.1
figure 1.2

Subtotal: click on Subtotal command under Outline group in Data tab a popup will appear asking followings:

  1. At each change in: Select the column on which you would like to track sub total.
  2. Use function: you can select various aggregate functions based on your choice from the dropdown
  3. Add subtotal to: select the column on which you want to put aggregate which should have numeric figures
  4. Replace current subtotals: default it set set to true which ensures overriding existing subtotal if available.
  5. Page break between groups: a page break would be inserted if sets to true.
  6. Summary below data: puts summary as Grand Total at the end of data
figure 1.3

Summary with subtotal:

figure 1.4

Lets automate the process by putting code. We are using VSTO since code is built in using Visual Studio Tools for Office Excel Addin, hence following prerequisites are recommended:

  1. Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  2. Create Excel Addin in C# or VB code style (Visual Studio Tools for Office)
  3. Excel 2010 or above
  4. Create a ribbon designer and put button

C# code example : Row Grouping, you can change Rows collection to Columns to perform grouping over columns

private void btnGroup_Click(object sender, RibbonControlEventArgs e)
{
	//retain selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	//Group selection, change oRange.Columns.Group if needs column grouping
	oRange.Rows.Group();
}

C# code example : Ungrouping

private void btnUngroup_Click(object sender, RibbonControlEventArgs e)
{
	//retain selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	//Ungroup selection, change oRange.Columns.Ungroup if needs column ungrouping
	oRange.Rows.Ungroup();
}

C# code example : Clear outline

private void btnClearOutline_Click(object sender, RibbonControlEventArgs e)
{
	//retain selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	//Clear Outline
	oRange.ClearOutline();
}

C# code example : Subtotal

private void btnSubtotal_Click(object sender, RibbonControlEventArgs e)
{
	//retain selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	//Subtotal
	oRange.Subtotal(1, excel.XlConsolidationFunction.xlSum, oRange.Columns.Count, true, false,
		excel.XlSummaryRow.xlSummaryBelow);
}

Arguments: Subtotal function take following arguments:

  • GroupBy: takes integer argument. The field to group by, as a one-based integer offset.
  • Function: there are list of built in constants derived from XlConsolidateFunction enum
  • TotalList: number of columns are involved in subtotal operations
  • Replace: boolean data type default true, overrides existing subtotal
  • PageBreaks: true to add page breaks after each group. The default value is false.
  • SummaryBelowData: offers two constants XlSummaryBelow and XlsummaryAbove derived from XlsummaryRow enum

VB.Net code example : Row Grouping, change Rows collection to Columns to perform column level grouping

Private Sub btnGroup_Click(sender As Object, e As RibbonControlEventArgs) Handles btnGroup.Click
	'retain selection
	Dim oRange as excel.Range
	oRange = Globals.ThisAddIn.Application.Selection

	'Rows Grouping, Change oRange.Columns.Group() if needs column level grouping
	oRange.Rows.Group()
End Sub

VB.Net code example : Rows Ungrouping, change Rows collection to Columns to perform column level ungrouping

Private Sub btnUngroup_Click(sender As Object, e As RibbonControlEventArgs) Handles btnUngroup.Click
	'retain selection
	Dim oRange as excel.Range
	oRange = Globals.ThisAddIn.Application.Selection

	'Rows Ungroup, Change oRange.Columns.Ungroup() if need column ungrouping
	oRange.Rows.Ungroup()
End Sub

VB.Net code example : Clear grouping

Private Sub btnClearOutline_Click(sender As Object, e As RibbonControlEventArgs) Handles btnClearOutline.Click
	'retain selection
	Dim oRange as excel.Range
	oRange = Globals.ThisAddIn.Application.Selection

	'Clear outline
	oRange.ClearOutline()
End Sub

VB.Net code example : Subtotal

Private Sub btnSubtotal_Click(sender As Object, e As RibbonControlEventArgs) Handles btnSubtotal.Click
	'retain selection
	Dim oRange as excel.Range
	oRange = Globals.ThisAddIn.Application.Selection

	'Subtotal
	oRange.Subtotal(1, excel.XlConsolidationFunction.xlSum, oRange.Columns.Count, true, false,
			excel.XlSummaryRow.xlSummaryBelow)
End Sub

Next >> Auto Correct and Spelling in Excel

Leave a Reply

Your email address will not be published.