If you have a column of daily dates, how can you subtotal by month? Episode 568 shows you how.
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 Mr. Excel netcast.
I'm Bill Jelen.
Today we have a question that's sent in by Murray.
If you have a question for the netcast, please feel free to either call in or leave a voicemail or drop me an email.
Murray has a set of data with dates going down the left-hand side and some sort of amounts, and she wants to be able to add subtotals at every change in month.
Now, normally when we do this Data, Subtotals, and we have a chance to say, At each change in, Date.
But, that doesn't do what we want, that gives us a subtotal for every single day.
So, my solution to this is to choose column A, use Format Cells, and say that you want to display the data, just simply as a month.
So, March 01, Click Ok.
Now, you can add the subtotals, we use Data, Subtotals, and say, At each change in, Date, Click Ok.
Excel adds in the monthly subtotals, and now, we go back and select column 1, and change the date back to a regular format.
And now, we have daily dates going down the side the subtotals are only in at each change in month.
Requires a little bit of a hassle, but, certainly easier than adding a new column to calculate the month and things like that.
Great question form Murray.
Again, if you have a question for the podcast, please feel free to call or drop us an email and we'll get to you in our future podcast.
Thanks for stopping by.
See you next time for another netcast from MrExcel
I'm Bill Jelen.
Today we have a question that's sent in by Murray.
If you have a question for the netcast, please feel free to either call in or leave a voicemail or drop me an email.
Murray has a set of data with dates going down the left-hand side and some sort of amounts, and she wants to be able to add subtotals at every change in month.
Now, normally when we do this Data, Subtotals, and we have a chance to say, At each change in, Date.
But, that doesn't do what we want, that gives us a subtotal for every single day.
So, my solution to this is to choose column A, use Format Cells, and say that you want to display the data, just simply as a month.
So, March 01, Click Ok.
Now, you can add the subtotals, we use Data, Subtotals, and say, At each change in, Date, Click Ok.
Excel adds in the monthly subtotals, and now, we go back and select column 1, and change the date back to a regular format.
And now, we have daily dates going down the side the subtotals are only in at each change in month.
Requires a little bit of a hassle, but, certainly easier than adding a new column to calculate the month and things like that.
Great question form Murray.
Again, if you have a question for the podcast, please feel free to call or drop us an email and we'll get to you in our future podcast.
Thanks for stopping by.
See you next time for another netcast from MrExcel
