How does it work?
Once you’ve opened the spreadsheet, you’ll automatically be directed to the control page - you'll need to choose ‘Trust this macro’. Next, you'll need to delete the rows of sample data and paste your own data into the spreadsheet, only keeping the columns (variables) that you want taken into consideration in the calculations. Note that you may use different headings/data to those in the sample data (e.g. store region, salesperson, etc) and you may include as many or as few columns/rows as your data requires.
In order to create charts in the spreadsheet, you’ll need to delineate a Y variable and a number of X variables.
- The Y variable is the one column of data you want to analyse in relation to the X variables. What you choose it to be will depend on the information you want to get out of the tool, but in many cases this will either be the units sold or sales in dollars. Type in your Y variable where it says ‘Y-variable name’ (see the red box below), making sure it matches the relevant column heading.
- The X variables will be things like date, day of week, size, season, store location etc. – whatever factors you think might have historically impacted your Y variable (e.g. if you wanted to know the average number of bagels sold at your central store location, the Y variable would be bagels sold and the X variable would be store location). The headings for each of your other columns will be the X variables for your charts (see the blue box below).
Once your data has been pasted into the spreadsheet with the correct Y and X variables, choose whether you want to summarise the data by total sum or by average.
- Sum charts will show the total sum of the Y values associated with each value of the X variable
- E.g. for each salesperson, what is the total number of units sold?
- Average charts will show the mean value of the Y variable for each X variable
- E.g. for each salesperson, what is the average dollar value of each sale made?
After choosing whether you want to know the sum or the average of your data, click ‘Create Charts’. You can later return and click ‘Clear Charts’ if you wish to input different data.
Where do I see charts and tables?
Once you’ve created the charts, you’ll see a list of spreadsheet tabs appear at the bottom with all your X variables. Clicking on each of these will take you through to a new page showing two things:
- A table with the mean Y variable for that specific X variable, as well as the mean of the total
- A visual showing the same information in chart form
To use externally, simple copy/paste the chart or the table to the final location.
Can I specify a data range?
Yes – the simplest way to achieve this is to copy only the data from the relevant date range into the control sheet. Alternatively, this can be done using the pivot table or pivot chart controls (recommended for advanced users).
What is the data limit?
There is no limit on rows of data that can be processed by this tool – however, keep in mind that the larger your dataset is, the less digestible the charts/table will be. For example, here is a chart with ~150 distinct dates, versus one with ~25 date values.