Redistributing Cell Values

Redistribute operations in the planning panel let you change the proportions among a group of cells.

Context

Say you’ve just copied values for marketing expenses from 2020 to your 2021 plan, but as part of a new marketing strategy, you want to change how the expenses are spread to different promotion channels.

Procedure

Select a group of source cells that includes the entire amount you want to redistribute.

The source area can include cells on different levels of a hierarchy, or from different members of multiple dimensions.

Note

If you select a group of dependent cells, such as EMEA, Germany, and Frankfurt, or Gross Margin and Total Revenue, you’ll only be able to enter values for one of them.

For example, you can select the marketing expenses account for different promotion channels in 2021, such as Online, Printed Media, TV, and Radio.
Open the planning panel by right-clicking the cells and selecting Redistribute Values.
There are a few other ways to open the panel, too:
- Select Distribute Values.
- Press Ctrl + Alt + D .
- Type ? in the source cell, on its own or followed by a value, and press Enter. (This starts a Distribute operation.)
The Planning Panel opens. In the source area, it shows the aggregate value of the cells that you selected.

To change the target cells, activate the (Add target) button and select or deselect cells in the grid.

You can also select Redistribute to switch to a Distribute operation.
From the Cell list, choose whether to overwrite existing target cell values, or append the new values.

In this case, you’ll use Overwrite to set new target values.
Use the Driver list to choose how to set the cell values.
- Input Values: Add the exact target values, either in the table or in the panel.
- Input Weights: Set proportional weights for each cell. For example, if you want to get weights from a different time period, you could copy and paste those values from the table.
- Equally: Divide the source value equally among the cells.
- Proportionally: Use the existing proportions between the cells.
You just want to set out a ratio between the different promotion channels, so you choose Input Weights.
Set values for the target cells.

If you chose Input Values or Input Weights, you can now set the target values by typing or copying and pasting. For Input Values, you can also change values directly in the table. Simple formulas such as +10% are available in the table or panel, too.

When you select a target, the table will highlight the cell. You can also hover over the cell coordinate to see its members. If you need to remove any targets, use the (Remove target) button.
Check the figures and then select Apply.

You can see a preview of the values in the table, and the percentage distribution next to the targets in the panel. If you used input values that don’t add up to the total amount, you’ll see a warning beneath the targets. You can still apply the operation in this case, though.

The table is updated to show your changes.