Customize your view of a worksheet
- Denise McGettigan
When you open a budget or forecast worksheet, it looks the way the budget owner set it up, without an individual audience in mind. As access to a workbook is restricted, you might only see some of the rows in the worksheet. You can customize your view of a worksheet to meet your needs in several ways.
Use layouts
If layouts are available, applying a layout is a quick way to change your view of a worksheet. A layout can include dimension filters, search results, and other configurations to change the look of the worksheet and the data it displays. The budget owner or administrator might have saved a layout for you, to help you complete your tasks in the workflow. You can save your own layouts to get different views of the same budget or forecast. See Use layouts for detailed information.
Filter the worksheet
Several filter options allow you to remove rows from the worksheet so you can focus on the rows that are important to you. For detailed information, see Filter a worksheet (search for items).
Expand or collapse the rows
There are two expand/collapse options:
Expand/collapse single - show or hide one level in the hierarchy at a time.
Expand/collapse all - show or hide all the lower-level items in the hierarchy at the same time.
Switch between a pruned and unpruned view
The Prune feature is applicable when a filter is applied within a worksheet or the workbook setup.
By default, the Prune feature is switched ON, which means that all budgets and forecast worksheets load in a pruned (condensed) view. If there is no account row underneath an entity row, that entity row is removed from the budget hierarchy because it is not required.
To view the full budget hierarchy, including any redundant rows, you need to switch to the unpruned view. This is a useful option for budget owners and administrators who want to add entities and accounts to the budget hierarchy, as they can clearly see where to add them.
Click the Prune/Unprune button to switch the Prune feature on or off.
In the following example, the worksheet on the left is in the unpruned view, so you can see the IRE row, which has no child rows. The worksheet on the right is in the pruned view, so the IRE row no longer displays.
Reorder the items
The order in which the dimensions display in the dimension filters area above the grid determines how the levels are organized in the budget. If there are multiple levels in a budget, you can change the order in which the middle levels display. For example, in the following image, you cannot change where the top-level (Category) or bottom-level (Account) dimensions sit but you can reorder the other levels, as indicated by their Move buttons.
Click the Move button on a dimension, then drag that dimension to the left or right of the other dimension(s) to the new position.
Continuing with the same budget example above, the following image shows what that budget looks like when the Branch dimension is moved before the Country dimension.
In addition to recording the dimensions, you can filter the dimensions.
Hide the code and/or name of entities
By default, the code and name of each entity are displayed in the grid. You can remove one of these if they are surplus to requirements. This is particularly useful when the code or name is long.
In the filters area above the grid, click the applicable dimension, then open the View list and select the item(s) you want to display in the grid.
Hide zero-value rows
By default, all baseline rows display in a budget, including those with 0 values. You might want to hide the rows that have zero values, so you can focus on the other rows. You can achieve this by using the filter by condition functionality > non zero option.
For example, if there is a row in your budget (which comes from the baseline data) that you do not want to budget for, you can enter a zero into each cell, then filter the worksheet to display only the non zero rows. This action only hides rows from the underlying structure of the budget; it doesn't affect any rows you have added, such as multiple measures.
This feature coincides with the ability to exclude zero rows when you publish a budget.
Group the columns
Put the period columns into groups.
Expand or collapse sparklines
Sparklines are mini, simple line charts that display by default next to each of the values in the Total columns in budgets. Sparklines also display in forecasts but they behave in a slightly different way.
Sparklines provide a visual representation of your data, so you can quickly see trends and spot anomalies. You can hover over the data points in the sparklines to see the corresponding value and date. The sparklines update in real-time as you change the data in the grid, so you can immediately see the impact of your changes.
If you do not want to see the sparklines, you can turn this feature off by clicking the Collapse sparklines button in the column header.