Learn how pivot tables simplify date analysis by grouping data into meaningful time periods. Gain efficiency by using Excel's timeline feature to quickly filter your sales data.
Key Insights
- Pivot tables enable users to group data by different time periods such as days or months without manually creating additional columns, helping to simplify data analysis.
- Excel's "Insert Timeline" feature significantly streamlines filtering dates, reducing the number of clicks required to filter specific months from 10 clicks down to only two.
- Timelines offer flexible filtering options, allowing users to easily switch between viewing data by individual months or broader quarters, thereby enhancing overall reporting efficiency.
Note: These materials offer prospective students a preview of how our classes are structured. Students enrolled in this course will receive access to the full set of materials, including video lectures, project-based assignments, and instructor feedback.
Pivot tables, grouping and timelines. In this section we'll take a look at how pivot tables make it really easy for you to work with dates in your data set. We're going to start by creating a pivot table by moving in some fields.
The good news here is you don't have to create the pivot table, it's already been created. All I have to do is click within the pivot table and then I'll choose the fields that are listed in the exercise. I'm going to bring date into rows.
I'm going to bring sales into values and then I'll take market and move that into columns. I've just created a pivot table that no one is going to be impressed with. What you can see right away is that this is too much information.
I'm looking at sales for every individual day for all the markets. This is not going to give me a nice summary or overall view of trends as far as it relates to sales. So someone might look at this and say the issue with this pivot table is that there's too much information for us to get a sense of it.
Could you please group all of the sales into months? Now if I go back to the original table and take a look, I will see I do not have any month columns here. The best I could hope for I see is a day of the week. Perhaps maybe I should create a new column and come up with some kind of function that will convert individual dates to months.
Well that's not going to be necessary. Within the pivot table itself there is a feature that allows you to group dates by different time periods. All I need to do is right click on any individual date and choose group and currently what I will see here is confirmation that all of my information is grouped by days.
Now I don't want the information grouped by days so I'm going to unselect it and instead choose months. All I'm going to need to do now is click okay and now all my information is grouped by month. Now the next issue is going to be filtering the data.
What do I mean filtering the data? Well let me just show you what it looks like to filter this information for the month of April and then November and I'll audibly say what the problem is. One click, two clicks, three clicks, four clicks, five clicks, seven clicks, eight clicks, nine clicks, ten clicks. It took me ten clicks to filter for April and then November.
I'm going to clear this filter. I want to be able to filter my information much more efficiently so if I click on the pivot table analyze tab I'll look at the filter group and I'll see an option called insert timeline. Insert timeline was made specifically for dates.
I'm going to click insert timeline. You'll see the only field I am able to select is a field that has dates in it so it's not going to be too difficult to figure out what to do here. I'll just click the check box for date and then I'll click okay and now I have this display of all the months and I can simply click on the month that I want.
In this case let's do the same exercise we did last time. I'm going to click April that's one click then I'm going to click November that's two clicks. I've reduced my ability to filter by April and November from ten clicks down to just two clicks.
Now I could also select January and if I go to the edge and see a double arrow I can move it over to March. Now I'm looking at three months at a time and that's basically the first quarter. I can then click April and then drag it over to June.
There we go and then click July and move it over to September. Another option though is to clear the filter and simply just click the drop down for months and choose quarters. Now this timeline doesn't have to be as wide and I can simply click to view any individual quarter with one click and then I can go back to unfilter all the information and now I have the entire table back.
So in this section we looked at how you can group your information for different time periods and also how you can use the insert timeline feature to insert a timeline to allow you to filter your information also based on different time periods.