Best practice for pivot table data layout

Hi there

I'm trying to find out what is the best practice for laying out data to use in a pivot table.

I will be using excel to examine sales data that is exported from our shop till. The data I want to be able to look at are the sales for each product by month then by year, ongoing.

My instinct is to create a worksheet for each year of sales. I currently have data for 2016,2017 and current year and combine it into one pivot table. Fingers crossed, I will be adding many more years of data to this.

Is this the best option or should all data be in one, long worksheet?

or join all tables (with the same structure I hope) via PowerQuery aka Get&Transform and create PivotTable from QueryTable

Originally Posted by Netlon

Hi there
My instinct is to create a worksheet for each year of sales. I currently have data for 2016,2017 and current year and combine it into one pivot table. Fingers crossed, I will be adding many more years of data to this.
Is this the best option or should all data be in one, long worksheet?

My opinion make 1 long worksheet. Your instinct is based on human nature. Humans don't like to scroll thru endless long list. Excel does not care it will warn you if it is to much for "him" (just over the 1 miljon rows mark)
Also I think sales data becomes more valuable if you can compare more than one year, then you can f.e. recognize seasonal trends easier.
If you make separate sheets for every year you still have to manual compares on year to year.
having 1 table for many years also ensures you are aware of impact of changes to the collected sales data, when you want to add a new column you then also have to decide if the history stays blank or that you can enrich those lines also with historical data. If you have separate tables people will much easier remove one column or randomly add one in the middle so when you do compares over years it can cause errors as data is not in the expected columns.

Many thanks for your quick replies and links. They have really helped.

You are welcome

I suggest PowerQuery, PowerPivot with DataModel

have a nice day