[ Pivot Table to Reorganize Data ]

I have data in the below format

However, I need to convert it into the below format using pivot tables or other formuals such as

where 2015 row is just split by months (divided by 12). Kindly help me with a logic that I can use. Thanks in advance for your help.

Regards, Navs

Answer 1

If your data has a lot of IDs and years, a pivot table is the quickest way to get your first two columns, but the monthly breakdown will need to be done using formulas (e.g. index/match to pick up the FY value and divide by 12).