Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Monthly Breakdown | Excel Worksheet Functions | |||
sum daily production data to monthly | Excel Worksheet Functions | |||
HOW DO I FREEZE DATA THAT CHANGES DAILY. dAILY/MONTHLY BUDGET | Excel Discussion (Misc queries) | |||
Pivot Table with Data Added Monthly | Excel Discussion (Misc queries) | |||
Monthly Budget Breakdown | Excel Discussion (Misc queries) |