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:

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

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

Summary with subtotal:

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

Share on Facebook

Tweet