Change the standard view of the data in a worksheet to suit your analysis and reporting needs.
Last updated
Was this helpful?
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.
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 for detailed information.
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 (search for items).
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.
By default, most tabs display a nested (hierarchical) view of the data. This structure lets you drill down through dimension levels to explore details, making it ideal for reviewing and understanding the budget.
However, this layout can take up significant screen space and may not be optimal for data entry tasks. Switching to a flat view transforms the nested levels into columns. This flattens the hierarchy, placing all reference information on a single level. As a result, you can view many more rows at once, making data entry faster and more efficient. It also makes it easier to copy-and-paste data from one place to another.
Click the Show flat view / Show nested view button on the top-left of the grid to switch between the views.
In flat view, you can hide the dimension columns to see even more data entry cells on screen, further condensing the layout. Click the Show / Hide dimension columns button near the Workflow column to show or hide those columns as required.
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.
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.
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.
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.
By default, the budget (Main tab) displays all selected dimension entities under their respective dimension levels. This is the levels view. You can also view the dimension entities as separate worksheet tabs, making it easier to focus on individual areas like branches, regions, or trading entities. This tabs view is especially useful when budgeting across multiple entities within the same dimension, as you can switch between the tabs for easier data entry.
For example, you might be budgeting you Profit and Loss for several countries and have Country as a budget dimension. That dimension's entities are the individual countries, such as AUS, UK, and US.
Each entity tab mirrors the Main tab, so the budgets look the same, but the entity tabs contain a subset of the overall budget values. The Main tab continues to be the consolidation of the entity tabs. Any changes to the separate entity tabs are reflected in the Main tab, and vice versa.
If you exporting to Excel when the View as tabs option is active, you get the option to include the entity tabs.
Click the applicable dimension button in the top-right corner above the grid (e.g. Trading entity, Branch, or Region).
Click the View as levels/View as tabs toggle button to activate the View as tabs option.
Click out of the box to apply your changes. New tabs are added to the workbook, one for each of the entities.
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.
In addition to recording the dimensions, you can .
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 > non zero option.
This feature coincides with the ability to exclude zero rows when you .
Put the period columns into .
(Optional) : Select the checkboxes for the entities you want to include in the budget.
