Skip main navigation

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 And Calculation Sheets
© paula guilfoyle

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.

Excel Sheets

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:

Excel Sheets

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.

© paula guilfoyle
This article is from the free online

Design and Create Custom Dashboards in Excel

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education