Excel for Accountants: Grouping, Outline and Trend Forecasting

Updated: Sep 30, 2020

Accountants need to manage a lot of data on everyday. One example is they usually group data and display them in different rows or columns in MS Excel. Outline consists of various features which can help you.


For management accountants, performing trend analysis is done often especially in projecting performance in future. Forecasting in Excel is excellent for you in projecting future trends.


We explain these tools below which can help you to improve your work efficiency.



Grouping


Spreadsheets can get extremely complicated and there are times when certain columns or rows, which may be involved in a stage of the calculation, need to be hidden for the sake of simplicity, but can easily be shown if necessary.


For example, there may be a set of tabular data on a spreadsheet


Image 1: A set of tabular data on a spreadsheet

The first name and last name of the customer may not be important enough to be shown initially. These would be required to be hidden, but easily displayed if required by the user.


To group the data, select the first name and last name data, and then click on ‘Group’ in the ‘Data’ tab of the ribbon


Image 2: ‘Group’ in the ‘Data’ tab of the ribbon

This will display a pop-up menu asking if you want to group by rows or columns


Image 3: Pop-up menu under Group

Select Columns, and click OK


Your spreadsheet will now look like this:


Image 4: Group by columns

To hide the first and last name, click on the ‘Hide Detail’ button (minus sign indicated by arrow in screenshot above)

Your spreadsheet will now look like this:


Image 5: Hide Detail for grouping under columns

Columns B and C are now hidden, but can be easily be unhidden with a single click on the ‘Unhide Detail’ button (plus sign indicated by arrow in above screenshot)


The process of grouping can also be used on rows in the case of a block of rows which are needed to be hidden until required


There is also an option called ‘Auto Outline’. This is found on the drop down for ‘Group’ on the ‘Data’ tab on the ribbon.


Image 6: Auto Outline button

If you have tabular data which has totals or sub totals within it, so that it looks like a continuous range of data with many totals, you can use this function to automatically group for the detail of each total. Note that the totals must have a formula e.g. SUM otherwise you will get an error message ‘Cannot create outline’


Your data should look like this:


Image 7: Auto Outline example

Take the ‘Auto Outline’ option and all the total rows will be grouped


Image 8: Hide Detail in Auto Outline example

You can use the ‘Hide Detail’ buttons to collapse the detail of each total


The ‘Clear Outline’ option on the ‘Ungroup’ drop down will clear all the groupings on the spreadsheet


Image 9: Clear Outline button



Sub Totaling


This function is on the ‘Outline’ group of the ‘Data’ tab on the ribbon. You need to select your range of tabular data first.


It is a useful way of grouping by a specified column and providing a sub total for that group. The tabular data needs to be sorted into order of the column that you wish to group and sub total by. In this case the grouped column will be ‘Order No’


Image 10: Subtotal example

You can choose the column that you want to be the group column and which function you want to use for each subtotal, e.g. Sum, Count


You select the column to be totaled – there can be multiple columns here.


Click OK and your data will look like this:


Image 11: Subtotal example result

You can use the ‘Hide Detail’ buttons to collapse the hierarchy and only show the totals, but with the detail readily available.


Image 12: Hide Detail in Subtotal example

The 1,2,3 boxes at the top left will show the levels of detail when clicked. 1 will only show the ‘Grand Total ‘whereas 3 will show everything



Forecast


What-If Analysis

This function is found on the ‘Forecast’ group on the ‘Data’ tab of the ribbon


It allows the user to set a target value for specific cell e.g. Profit / Loss based on altering a value in a cell that is part of the formula for that target cell.


For example, you could have a value for sales and values for fixed and variable costs. Assume that the variable costs are always 20% of sales. Using a simple formula, you can find the value of the profit.

<