How to calculate growth (%) percentage: WoW,YoY,MoM,QoQ and others

 Any report or dashboard is not complete if you are not showing the difference. For that matter, anything life doesn't make sense if there is no difference over time. Or even if there is a difference if you are not showing or presenting them.

Now what is the difference? 

Its a comparison between the current state versus "something". The "something" may be future or past. In business terms the interest is in comparing current week versus previous week, month over month, quarterly, half-yearly, yearly growth in terms of percentages.

How do we calculate the growth percentage. Its a simple formula, let me explain in generic terms.

The growth percentage between two period is calculated 

Current period divided by previous period minus 1

Remember this is as easy as it gets and there are no brackets involved here.

VBA Script: How to split data into different sheets based on a value in a column

Lets say you a 10000 row data dump which has atleast 30 types of unique values in a column. Now your task it to split this data into multiple sheets with these sheets being named based on the unique value.

Doing this task manually would take atleast 30 mins and is prone to human error.

So how do we do this using a macro. Here are the steps based on a hypothetical data.

Our data is below. For this exercise prepare something similar on your Excel. 

Now hit Alt+F11 and click on create new module as shown below.

Once you have created new module. On the right hand white space you would need to enter the below VBA code. I will explain you on each line so that you are not simply copy pasting but you know what you are doing.

VBA code:

Sub filter()

Application.ScreenUpdating = False

Dim x As Range

Dim rng As Range

Dim last As Long

Dim sht As String

'specify sheet name in which the data is stored which needs to be split

sht = "Macro 2"

'choose the filter column in the following code

last = Sheets(sht).Cells(Rows.Count, "B").End(xlUp).Row

Set rng = Sheets(sht).Range("B1:I" & last)

'N1 is the column where the code temporarily stores unique values of  the "to-be-split" 'column from previous step, so specify a empty column 

Sheets(sht).Range("B1:B" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("N1"), Unique:=True

For Each x In Range([N2], Cells(Rows.Count, "N").End(xlUp))

With rng

.AutoFilter

.AutoFilter Field:=1, Criteria1:=x.Value

.SpecialCells(xlCellTypeVisible).Copy

Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.Value

ActiveSheet.Paste

End With

Next x

' Turn off filter

Sheets(sht).AutoFilterMode = False

With Application

.CutCopyMode = False

.ScreenUpdating = True

End With

End Sub

Once you have entered the above code. You can have a button/object linked with this code so that evey time you click the data is split based on the specified column into multiple sheets and with name of the sheet as per the unique value used to split.

If you need a copy of the excel file readymade, please email me at askme.kkhelps@gmail.com

Hope this helps!