Group period columns
- Denise McGettigan
Use the Group periods forward option to group several period columns in a worksheet into one period column that displays the total of the values in the individual periods.
You can repeat this action manually for other periods in the worksheet or use the Group periods forward option to simultaneously quickly create a batch of ‘repeating’ column groups based on the number of period columns in the first group.
How a group works
The column header of the period column group identifies the periods (columns) that are in that group. The values you see in a period column group are the total of the values in the individual periods.
If you are the budget owner and you create column groups, the groups display initially in a ‘collapsed’ state for you; you cannot edit the values in the cells (white background). However, you can use the spread feature to change the total value. When contributors open the workbook, they will see those groups in the expanded state (yellow background), so they can edit the values (according to the workflow).
Contributors also have the ability to create column groups, to meet their data entry needs. These changes do not impact the column groupings set by the budget owner.
Column groups are retained the next time you (budget owner and contributors) load the workbook. However, they are removed when you copy a budget forward or change the start or end dates.
Why groups are useful
There are many reasons why you might want to group columns, such as to:
Group past periods into one summary column, so that data is hidden from view and you can focus on the remaining months in the budget period.
Group a couple of weeks together to view a fortnightly budget.
Group three months together to get a summary total of the budget values in that quarter.
View your budget values at a summary time period level, such as quarters, and avoid the need to scroll across the worksheet.
Create a group
Click the header of the first period column you want to include in the group > hold and drag your mouse across the headers of the other columns you want to include in that group > release your mouse. The headers of the selected columns are highlighted blue and the grouping menu displays.
Select the required grouping method:
Click Group period to group the selected period columns into one ‘total’ column.
Click Group period forward to group the selected period columns into one ‘total’ column and group the subsequent period columns in the same way.
Expand or collapse a group
To view the period columns within a group, click the header of the group to expand the group. You can then edit the values as usual (yellow background) if you have been assigned tasks in the workflow.
Click anywhere in the blue highlighted column header area to collapse the group and view the group total again.
Spread totals within a group
In the collapsed state, you can use the spread feature to change the total values of a group and spread it across the periods within that group.
Ungroup columns
Right-click anywhere in the blue highlighted column header area and click Ungroup period to ungroup that group, or click Ungroup all to remove all groupings.