Todays question is a follow-up to yesterdays question. How can you make sure that a pivot table shows you one row per day, even if there were no sales on a given day? Episode 592 shows two annoying solutions. If you have a better solution, please send in your ideas to bill @ MrExcel.com.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a follow-on question to yesterday's question.
Now, if you have a question for the podcast, please feel free to either email it in or send us a voicemail and we'll get to it.
Yesterday we talked about how do we make sure that there's no missing dates along the x axis of a chart.
Today we have a Pivot Table, same problem where we have missing dates and I'm not sure that I have the best solution for this.
They wanna make sure that even though there may not have been any sales on a particular day that there's still a row for that day in the data. if we go and double-click the date field, and choose show items with no data, that doesn't help because if there was no sales for that day, in the source data set that something is not gonna help us.
So, one thing we can do is right click on the date choose Group and Show Detail and then Group and say that we want to just simply group it up to Days, leaving it at number of days one and then provided that we have the setting that says show items with no data.
We will get all of the dates in the year, but that's the really frustrating thing it gives us all of the dates in the year.
So, if you'd had maybe just a range of data from July to September, it's going to show you from January 1st, all the way out to December 31st, even though you don't have any data there.
It also adds these weird tags greater than Eleven 2707 that was the last day that I have and less than January 2nd 2007, the first date that I had sales.
So, it's kind of bizarre now one other solution that I have which I think is much more convoluted is to go back to your original data set and at the bottom of the data set, add in a series of dates with zero sales.
So, I'm gonna put in 1/1/2007.
Basically, drag that down 365 rows to make sure that I get every date for the year, Stop at 1231.
I'll fill in the adjacent section with zeros, double click the fill handle.
And now, create a pivot table from this data set.
So, Data, Pivot Table, Finish.
Put dates along the left hand side and reading in the center and that forces Excel to give us a row for every single date, seems like a lot of hassle to have to go through and add those 365 extra rows with zeros just to get the effect that we want.
Now, if you happen to have a better solution to this, please drop me an email and we'll get to that on a future podcast.
Hey thanks for stopping by.
We'll see you next time for another net cast from MrExcel.
I'm Bill Jelen.
Today we have a follow-on question to yesterday's question.
Now, if you have a question for the podcast, please feel free to either email it in or send us a voicemail and we'll get to it.
Yesterday we talked about how do we make sure that there's no missing dates along the x axis of a chart.
Today we have a Pivot Table, same problem where we have missing dates and I'm not sure that I have the best solution for this.
They wanna make sure that even though there may not have been any sales on a particular day that there's still a row for that day in the data. if we go and double-click the date field, and choose show items with no data, that doesn't help because if there was no sales for that day, in the source data set that something is not gonna help us.
So, one thing we can do is right click on the date choose Group and Show Detail and then Group and say that we want to just simply group it up to Days, leaving it at number of days one and then provided that we have the setting that says show items with no data.
We will get all of the dates in the year, but that's the really frustrating thing it gives us all of the dates in the year.
So, if you'd had maybe just a range of data from July to September, it's going to show you from January 1st, all the way out to December 31st, even though you don't have any data there.
It also adds these weird tags greater than Eleven 2707 that was the last day that I have and less than January 2nd 2007, the first date that I had sales.
So, it's kind of bizarre now one other solution that I have which I think is much more convoluted is to go back to your original data set and at the bottom of the data set, add in a series of dates with zero sales.
So, I'm gonna put in 1/1/2007.
Basically, drag that down 365 rows to make sure that I get every date for the year, Stop at 1231.
I'll fill in the adjacent section with zeros, double click the fill handle.
And now, create a pivot table from this data set.
So, Data, Pivot Table, Finish.
Put dates along the left hand side and reading in the center and that forces Excel to give us a row for every single date, seems like a lot of hassle to have to go through and add those 365 extra rows with zeros just to get the effect that we want.
Now, if you happen to have a better solution to this, please drop me an email and we'll get to that on a future podcast.
Hey thanks for stopping by.
We'll see you next time for another net cast from MrExcel.
