BUDGETING TIP OF THE MONTH

Named Cell

When building templates and reports for budgeting, individuals in finance teams may find themselves inputting the same value across many cells. These reports tend to reference some of the same details such as version for budget, actuals, and forecast all for the same year, or comparisons of a single month such as December over many years.

One method to alleviate the painful process of updating these hard-coded cells within a worksheet is establishing one cell to be used for updating, while referencing that cell as a formula to have the changes align across other cells. The pitfall with this method is the inability to reformat or shift cells, which would lead to formula references to change.

A great solution to this that is widely used in Vena report creations is the Named Cell. This allows you to designate one cell within your worksheet to be referenced by simply typing in the name in the formula bar. Changes to your structure make no impact to this named cell unless you delete the actual cell. Additionally, referencing it across your worksheet is much easier because all that you need to do is type out the named cell as the formula for the cell you want the value to be referenced in.

To create a Named Cell, select the cell you want to name. In the name box to the left of the formula bar, type in the name you would like to define that cell as and hit enter.

Here is an example of a Vena report with named cells that update multiple cells with the selected year, highlighted in yellow. The formula bar shows how the defined cell is easily sourced as a formula for this range of cells. (The gray shaded area is designated for mapping reports to interact with the Vena database, which is controlled by managers, and hidden from end users.)