[1/5] How to set up a reference date table and summarise figures to get absolute and Year-On-Year (YOY) monthly figures


  • Challenge level: 1.5 out of 5

  • Estimated reading time: 9 min

  • Excel functions covered: YEAR(), MONTH(), VALUE(), LEFT(), VLOOKUP(), SUMIFS() and IFERROR()

  • Excel attachment reference sheets: raw data, template_Excel and template_date table


BEFORE YOU START: Get your FREE Excel template that accompanies this five-part tutorial by subscribing here!


 

Step 1. Get your date into a YYYYMM format

In sheet “raw data”, add a column that turns dates into YYYYMM format. This allows for easy reference when creating summary tables of your data based on time periods.

 Excel create additional column at the end of the table for the YYYYMM date format
 Excel formula YYYYMM from date using YEAR and MONTH

 

Step 2. Set up a reference date table

In order for the formulas that we will introduce later to automatically update their calculations when the referenced YYYYMM changes, we have set up a reference date table that sits on the sheet titled “template_date table”.  

We work further off this table to create reference cells on our main template sheet (titled “template_Excel”) that will form part of the formulas for Total Rolling Last 12 months, Year-to-Date, Financial Year-to-date figures etc.

 Excel set latest YYYYMM as reference point

Step 2.1. Manually input your data’s date range in YYYYMM format in column B. Next to each individual date entry, input their corresponding start of financial year in YYYYMM format as well in column C.

 Excel input range of YYYYMM and their corresponding start of financial year’s YYYYMM

Step 2.2. On a separate sheet (titled “template_Excel”), create a drop down list with the date range from step 1. With this drop down list, you can easily select the YYYYMM you want to focus on.

How to create a drop down list:

a. Select cell that you want to attach the drop down list to (we have selected cell G3 on sheet “template_Excel”).

b. Click on the “Data” tab and select Data validation.

 Excel Select data validation

c. Once the window pops up, select “List” as the validation criteria.

 Excel data validation criteria

d. You will now be able to reference the data source you want to populate the drop down list with. This is done under the “Source” field. Go to the sheet titled “template_date table” and select the cells holding your YYYYMM data.  

 Excel data validation criteria list

e. Finally, hit the “OK” button and voila!

 Excel dropdown list to choose YYYYMM

Step 2.3. Once the drop down list is set up, combine VALUE() and LEFT() functions to help obtain the correct Start of Calendar Year figure off your selected Latest YYYYMM. Our set-up of the LEFT() function allows us to extract the first 4 characters what appears in cell G3, which is effectively the year (i.e. YYYY). However, just using LEFT() function on its own would give us returned results in text form, rather than reflecting YYYY as numerical values. To fix this, we nest LEFT() within a VALUE() function to get numerical figures. To round off, we simply need to add “01” after the embedded LEFT() function so that the full text to convert into numerical figures reads as YYYY01.    

Step 2.4. To get the accompanying Start of FInancial Year to your selected Latest YYYYMM, we make use of the VLOOKUP() function. Our set up of the VLOOKUP() function looks up the selected Latest YYYYMM against the reference date table on the “template_date table” sheet. It then returns the value of the Financial Year Start that sits next to the selected Latest YYYYMM.

 

Step 3. Summarize your figures to get monthly values

It is always good practice to set up your summary tables on a separate Excel sheet ( this is why we have the sheet titled “template_Excel”) from the original data. So let’s proceed in getting our summary tables populated!

Step 3.1. Create summary table of absolute figures

 excel manually set up category list; formula that summarizes figures of raw data sheet
 Excel formula sum by category using SUMIFS

In simple terms, the formula is structured to return a summed-up value of only “Bluetooth Headphones" in “200701”. So, for example, if you edit your CRITERIA1 to cell $A11 but keep your CRITERIA2 unchanged, you will get a summarized value of “Camcorder” in “200701”.

Step 3.2. Create summary table of Year-On-Year (YOY) figures

excel-formula-year-on-year.png
 Excel formula year-on-year (YOY) using divide and IFERROR

The formula is basically a mathematical equation of dividing the difference between current and previous years’ figures by the previous year figure. We use the IFERROR function here to help keep a clean look across the table so that a blank cell is reflected where the equation returns an error value (i.e. when the denominator is zero).

You may be wondering: “why can’t I just set-up these tables using Pivot Table?”

It's true that Pivot Tables could help you summarize these figures according to the months easily.

However, Pivot Tables don’t work well when it comes to dynamically updating and calculating more complex figures like Totals for Rolling Last 12 Months, Year-To-Date and Financial-Year-To-Date. Tomorrow, we’ll learn how this set up helps to achieve these dynamic updates and calculations.