Inputs and Calculation sheets

It is good practice to keep inputs, or original data on one sheet, with calculations and helper columns on different sheets.

It would be common to see calculation sheets for each visualization or group of visualizations. These sheets are separate from the final dashboard.

Inputs

Inputs are the raw data that you will create your charts and dashboards from. These inputs are often updated with new data as time passes. The updates can occur manually, by typing entries into the spreadsheet. Or they could be live data connected with Power Query. Or even Excels linked data types.

It is important to make sure that you keep your inputs sheet up to date with the most recent data available. It is also important to make sure you have access to the data needed to create the outputs required. Imagine designing a dashboard only to find out you do not have access to the data.

Calculation Sheets

Calculation sheets are used to prepare the fields required for charting. For example, you might have an input sheets that contain detailed sales transaction data. The requirements for the dashboard include a count of unique sales, a count of unique customers, and the total sales value. These calculations would be carried out on a separate sheet to the detailed transaction data.

Smart Linking

Calculation sheets should use smart linking back to the original input data. Smart linking is often called Dynamic linking as it will change, update, expand or contract based on what is available in the inputs and what you need to chart.

Smart linking can be created with formulas, pivot tables, or even dynamic arrays. Its purpose is to pull data from the inputs sheet so it can be used for helper columns or named ranges.

Alternatively, you can create helper columns and name ranges directly from the inputs sheet to the calculations sheet without pulling in the data from the inputs sheet. This will help keep the workbook size down as there will not be a duplicate of data and there will be fewer formulas in the workbook.

However, when you are new to creating dashboards, there is no harm in bringing the data into the calculations sheet for easy and visual reference.

Helper Columns

Helper columns are columns or rows used to calculate the plot data or create dynamic ranges. Helper columns are also used to add special features to a chart. These features include but are not limited to, the ability to switch data in the chart, highlighting high or low points, or adding additional series.

Input and Calculations Example

We have two input sheets. A sheet with Budget figures for the year, which will not change over the course of the period and a sheet with Actuals figures. This Actuals sheet will be updated with new information as it becomes available.

We have been asked to include in our dashboard a chart showing actual v’s budget sales, along with the variance.

To achieve this, we can set up the calculations sheet as follows:

First, we will pull in the data needed from the inputs sheet. The data required would be the Actual sales and budget sales for each division. As these values are available in the original data source, these linked references do not need calculations. Instead, they can just reference the cells in the input sheets.

Using the linked data, we can then create the helper columns. These are the columns that we will actually use in the chart.