3 tell-tale signs that your date is stored wrongly
1. You have one worksheet for one year of data.
Your data for all the different years should be stored in one worksheet. If you break them into multiple worksheets, you are storing your data wrongly. What is the implication when you store them in multiple worksheets? You cannot analyze your data across different years and you will find them you are spending hours trying to comparing the seasonal patterns in a year.
2. Your header contains descriptions Jan, Feb, Mar … Dec
Data should be stored vertically. The moment you store your data in this format, you will notice that your Pivot Table looks ugly and cumbersome. In fact, your report can be too complex for presentation. The report wouldn’t look pretty and you will struggle when you need to create different views for your report
3. You have a column called day for each calendar day 1 to 31.
You have a column and that column only showed the day of the month. If you store your day of the month this way, you will not be able to make instant references to lots of information that you might need in future, such as the day of the week, week no, etc.
So what would be the correct way to present the data? You should store each row of data with a date that is presented in this format, 1 Jan 2020. When you have a column to store the date, you will be able to make reference to the day, month and year of each row of data. Pivot Table recognizes the dates and will be able to organize the data according to day, month and year for you instantly with little effort from you.