A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Pivot table: How do I make a monthly breakdown of daily data?



 
 
Thread Tools Display Modes
  #1  
Old August 4th 09, 03:10 PM posted to microsoft.public.excel.worksheet.functions
metro
external usenet poster
 
Posts: 10
Default Pivot table: How do I make a monthly breakdown of daily data?

I have my data organized as date in column A, amount in column B and category
in column C, e.g. 3. Aug 2009 spent $ 200 on clothing and 4. Aug 2009 spent
$50 on restaurant.
I would like to keep track of montly spending on each category and the total
of each month.
My problem is that the date dimension doesn't seem to be possible to group
to a higher level than on the actual date from the data level.
How do I do it?
Ads
  #2  
Old August 4th 09, 04:34 PM posted to microsoft.public.excel.worksheet.functions
David
external usenet poster
 
Posts: 1,560
Default Pivot table: How do I make a monthly breakdown of daily data?

if you are using office 07- have date in date format- not text- click on a
date in the pivot table- click group and you should automaticly get an option
to group by month
--
HTH

"Metro" wrote:

> I have my data organized as date in column A, amount in column B and category
> in column C, e.g. 3. Aug 2009 spent $ 200 on clothing and 4. Aug 2009 spent
> $50 on restaurant.
> I would like to keep track of montly spending on each category and the total
> of each month.
> My problem is that the date dimension doesn't seem to be possible to group
> to a higher level than on the actual date from the data level.
> How do I do it?

  #3  
Old August 4th 09, 10:41 PM posted to microsoft.public.excel.worksheet.functions
Jason[_14_]
external usenet poster
 
Posts: 21
Default Pivot table: How do I make a monthly breakdown of daily data?

However, if your source data includes dates over more than one year,
then you will also need to group by MONTH *and* YEAR, by holding CTRL
down as you select them both. If you don't do this all data for each
month will be aggregated, so for example; January 2008 and January 2009
data will be aggregated into just one 'January' total unless you also
group by YEAR,

Jay
--

David wrote:
> if you are using office 07- have date in date format- not text- click on a
> date in the pivot table- click group and you should automaticly get an option
> to group by month

  #4  
Old August 5th 09, 07:51 AM posted to microsoft.public.excel.worksheet.functions
metro
external usenet poster
 
Posts: 10
Default Pivot table: How do I make a monthly breakdown of daily data?

I do have Office 07. When I right-click on one date in the pivot and select
Group, I get the error: "Cannot group marked area". When I select several
dates and then right click and Group, it groups everything without questions
and no monthly split.
Dates are formatted as date both in the data area as well as in the pivot.

I don't get it.

"David" wrote:

> if you are using office 07- have date in date format- not text- click on a
> date in the pivot table- click group and you should automaticly get an option
> to group by month
> --
> HTH
>
> "Metro" wrote:
>
> > I have my data organized as date in column A, amount in column B and category
> > in column C, e.g. 3. Aug 2009 spent $ 200 on clothing and 4. Aug 2009 spent
> > $50 on restaurant.
> > I would like to keep track of montly spending on each category and the total
> > of each month.
> > My problem is that the date dimension doesn't seem to be possible to group
> > to a higher level than on the actual date from the data level.
> > How do I do it?

  #5  
Old August 5th 09, 08:34 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,766
Default Pivot table: How do I make a monthly breakdown of daily data?

Hi,

There could be 2 reasons for this:

1. Atleast one row or column in your source data for the pivot is a blank;
or
2. Atleast one entry in the date column sof yoru pivot is a non date.

To correct for problem 2, go the source data and apply a custom filter
(select equals *) on the date column. This will show you all the non date
entries. Convert them to dates. Now get back to the pivot, refresh and now
try to group. IF it stiill does not group, then pull the date column off
the pivot, put in back in the pivot, refresh the pivot and now group

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Metro" > wrote in message
...
> I do have Office 07. When I right-click on one date in the pivot and
> select
> Group, I get the error: "Cannot group marked area". When I select several
> dates and then right click and Group, it groups everything without
> questions
> and no monthly split.
> Dates are formatted as date both in the data area as well as in the pivot.
>
> I don't get it.
>
> "David" wrote:
>
>> if you are using office 07- have date in date format- not text- click on
>> a
>> date in the pivot table- click group and you should automaticly get an
>> option
>> to group by month
>> --
>> HTH
>>
>> "Metro" wrote:
>>
>> > I have my data organized as date in column A, amount in column B and
>> > category
>> > in column C, e.g. 3. Aug 2009 spent $ 200 on clothing and 4. Aug 2009
>> > spent
>> > $50 on restaurant.
>> > I would like to keep track of montly spending on each category and the
>> > total
>> > of each month.
>> > My problem is that the date dimension doesn't seem to be possible to
>> > group
>> > to a higher level than on the actual date from the data level.
>> > How do I do it?


  #6  
Old August 5th 09, 09:22 AM posted to microsoft.public.excel.worksheet.functions
metro
external usenet poster
 
Posts: 10
Default Pivot table: How do I make a monthly breakdown of daily data?

Thanks, my defined data area was larger than the actual data, e.g. some rows
were blank.

"Ashish Mathur" wrote:

> Hi,
>
> There could be 2 reasons for this:
>
> 1. Atleast one row or column in your source data for the pivot is a blank;
> or
> 2. Atleast one entry in the date column sof yoru pivot is a non date.
>
> To correct for problem 2, go the source data and apply a custom filter
> (select equals *) on the date column. This will show you all the non date
> entries. Convert them to dates. Now get back to the pivot, refresh and now
> try to group. IF it stiill does not group, then pull the date column off
> the pivot, put in back in the pivot, refresh the pivot and now group
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "Metro" > wrote in message
> ...
> > I do have Office 07. When I right-click on one date in the pivot and
> > select
> > Group, I get the error: "Cannot group marked area". When I select several
> > dates and then right click and Group, it groups everything without
> > questions
> > and no monthly split.
> > Dates are formatted as date both in the data area as well as in the pivot.
> >
> > I don't get it.
> >
> > "David" wrote:
> >
> >> if you are using office 07- have date in date format- not text- click on
> >> a
> >> date in the pivot table- click group and you should automaticly get an
> >> option
> >> to group by month
> >> --
> >> HTH
> >>
> >> "Metro" wrote:
> >>
> >> > I have my data organized as date in column A, amount in column B and
> >> > category
> >> > in column C, e.g. 3. Aug 2009 spent $ 200 on clothing and 4. Aug 2009
> >> > spent
> >> > $50 on restaurant.
> >> > I would like to keep track of montly spending on each category and the
> >> > total
> >> > of each month.
> >> > My problem is that the date dimension doesn't seem to be possible to
> >> > group
> >> > to a higher level than on the actual date from the data level.
> >> > How do I do it?

>

  #7  
Old August 5th 09, 09:32 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,766
Default Pivot table: How do I make a monthly breakdown of daily data?

You are welcome.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Metro" > wrote in message
...
> Thanks, my defined data area was larger than the actual data, e.g. some
> rows
> were blank.
>
> "Ashish Mathur" wrote:
>
>> Hi,
>>
>> There could be 2 reasons for this:
>>
>> 1. Atleast one row or column in your source data for the pivot is a
>> blank;
>> or
>> 2. Atleast one entry in the date column sof yoru pivot is a non date.
>>
>> To correct for problem 2, go the source data and apply a custom filter
>> (select equals *) on the date column. This will show you all the non
>> date
>> entries. Convert them to dates. Now get back to the pivot, refresh and
>> now
>> try to group. IF it stiill does not group, then pull the date column off
>> the pivot, put in back in the pivot, refresh the pivot and now group
>>
>> --
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVP
>> www.ashishmathur.com
>>
>> "Metro" > wrote in message
>> ...
>> > I do have Office 07. When I right-click on one date in the pivot and
>> > select
>> > Group, I get the error: "Cannot group marked area". When I select
>> > several
>> > dates and then right click and Group, it groups everything without
>> > questions
>> > and no monthly split.
>> > Dates are formatted as date both in the data area as well as in the
>> > pivot.
>> >
>> > I don't get it.
>> >
>> > "David" wrote:
>> >
>> >> if you are using office 07- have date in date format- not text- click
>> >> on
>> >> a
>> >> date in the pivot table- click group and you should automaticly get an
>> >> option
>> >> to group by month
>> >> --
>> >> HTH
>> >>
>> >> "Metro" wrote:
>> >>
>> >> > I have my data organized as date in column A, amount in column B and
>> >> > category
>> >> > in column C, e.g. 3. Aug 2009 spent $ 200 on clothing and 4. Aug
>> >> > 2009
>> >> > spent
>> >> > $50 on restaurant.
>> >> > I would like to keep track of montly spending on each category and
>> >> > the
>> >> > total
>> >> > of each month.
>> >> > My problem is that the date dimension doesn't seem to be possible to
>> >> > group
>> >> > to a higher level than on the actual date from the data level.
>> >> > How do I do it?

>>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Monthly Breakdown roy.okinawa Excel Worksheet Functions 2 March 15th 09 10:46 PM
sum daily production data to monthly olga Excel Worksheet Functions 4 March 9th 09 11:26 PM
HOW DO I FREEZE DATA THAT CHANGES DAILY. dAILY/MONTHLY BUDGET mike64149 Excel Discussion (Misc queries) 4 September 22nd 08 08:11 PM
Pivot Table with Data Added Monthly Dawg House Inc Excel Discussion (Misc queries) 1 March 28th 07 01:30 AM
Monthly Budget Breakdown GF_Chris Excel Discussion (Misc queries) 8 March 15th 07 03:28 PM


All times are GMT +1. The time now is 07:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.