Create mutli-level subtotals

[JR Hester]

HAve I confused two MS applications again? Working on spreadsheet with dates
in column "A". Column A is a standard date format, with dates formatterd as
mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the
subtotals to change each time the date changes.
I get subtotals for each day(date) and a grand total. Here is the issue I am
wrestling with; I would like to "automatically" get additional subtotals for
WEEK, MONTH, QTR, and YEAR.
I thought I had run across a way to do this some time ago, but have been
unable to find the secret passage back there, now that I have the need. Or do
I have to create additional columns and extract a Week, Month, Qtr, and Year
from teh date column in order to make this work?

I have been though MOST of the posts on subtotals without success. Any
p[ointers would be greatly appreciated.

Thanks in advance for the great support offered here.

 

[Debra Dalgleish]

You could use a pivot table to summarize the data, and group the dates
by year, quarter, month, or days there. There are instructions and links
here:

http://www.contextures.com/xlPivot01.html

HAve I confused two MS applications again? Working on spreadsheet with dates
in column "A". Column A is a standard date format, with dates formatterd as
mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the
subtotals to change each time the date changes.
I get subtotals for each day(date) and a grand total. Here is the issue I am
wrestling with; I would like to "automatically" get additional subtotals for
WEEK, MONTH, QTR, and YEAR.
I thought I had run across a way to do this some time ago, but have been
unable to find the secret passage back there, now that I have the need. Or do
I have to create additional columns and extract a Week, Month, Qtr, and Year
from teh date column in order to make this work?

I have been though MOST of the posts on subtotals without success. Any
p[ointers would be greatly appreciated.

Thanks in advance for the great support offered here.

 

[JR Hester]

Thanks for pointing me in that direction. I have not used pivot tables in the
past, and therefore am a bit of a novice with this feature. It took a few
tries to find the date "week" definition your document referred to, but have
it now.

That has developed another question.
The pivot table is report 8 "sums" for me. I also need to enter another 5 to
7 calculated fields. I successfully created two of these, however can't seem
to get more than two calculated fields added to the data area.
? Is there a limit of 9 or 10 data elements that can be placed in a pivot
table, or is there a limit to the number of calculated fields that can be
placed in the data area?

? Question #2. How do I delete or remove a data element from the active
pivot table?

Thanks again

You could use a pivot table to summarize the data, and group the dates
by year, quarter, month, or days there. There are instructions and links
here:

http://www.contextures.com/xlPivot01.html

HAve I confused two MS applications again? Working on spreadsheet with dates
in column "A". Column A is a standard date format, with dates formatterd as
mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the
subtotals to change each time the date changes.
I get subtotals for each day(date) and a grand total. Here is the issue I am
wrestling with; I would like to "automatically" get additional subtotals for
WEEK, MONTH, QTR, and YEAR.
I thought I had run across a way to do this some time ago, but have been
unable to find the secret passage back there, now that I have the need. Or do
I have to create additional columns and extract a Week, Month, Qtr, and Year
from teh date column in order to make this work?

I have been though MOST of the posts on subtotals without success. Any
p[ointers would be greatly appreciated.

Thanks in advance for the great support offered here.

 

[JR Hester]

IS there a simple way to change the data range teh pivot table is based upon
after the table has been prepared?

Thanks

You could use a pivot table to summarize the data, and group the dates
by year, quarter, month, or days there. There are instructions and links
here:

http://www.contextures.com/xlPivot01.html

HAve I confused two MS applications again? Working on spreadsheet with dates
in column "A". Column A is a standard date format, with dates formatterd as
mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the
subtotals to change each time the date changes.
I get subtotals for each day(date) and a grand total. Here is the issue I am
wrestling with; I would like to "automatically" get additional subtotals for
WEEK, MONTH, QTR, and YEAR.
I thought I had run across a way to do this some time ago, but have been
unable to find the secret passage back there, now that I have the need. Or do
I have to create additional columns and extract a Week, Month, Qtr, and Year
from teh date column in order to make this work?

I have been though MOST of the posts on subtotals without success. Any
p[ointers would be greatly appreciated.

Thanks in advance for the great support offered here.

 

[Debra Dalgleish]

The number of data elements may be limited by the number of columns
available. What happens when you try to create more calculated fields?

To remove a data field, click the arrow on the Data field button, and
remove the check mark for the data field.

Thanks for pointing me in that direction. I have not used pivot tables in the
past, and therefore am a bit of a novice with this feature. It took a few
tries to find the date "week" definition your document referred to, but have
it now.

That has developed another question.
The pivot table is report 8 "sums" for me. I also need to enter another 5 to
7 calculated fields. I successfully created two of these, however can't seem
to get more than two calculated fields added to the data area.
? Is there a limit of 9 or 10 data elements that can be placed in a pivot
table, or is there a limit to the number of calculated fields that can be
placed in the data area?

? Question #2. How do I delete or remove a data element from the active
pivot table?

Thanks again

:

You could use a pivot table to summarize the data, and group the dates
by year, quarter, month, or days there. There are instructions and links
here:

http://www.contextures.com/xlPivot01.html

HAve I confused two MS applications again? Working on spreadsheet with dates
in column "A". Column A is a standard date format, with dates formatterd as
mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the
subtotals to change each time the date changes.
I get subtotals for each day(date) and a grand total. Here is the issue I am
wrestling with; I would like to "automatically" get additional subtotals for
WEEK, MONTH, QTR, and YEAR.
I thought I had run across a way to do this some time ago, but have been
unable to find the secret passage back there, now that I have the need. Or do
I have to create additional columns and extract a Week, Month, Qtr, and Year
from teh date column in order to make this work?

I have been though MOST of the posts on subtotals without success. Any
p[ointers would be greatly appreciated.

Thanks in advance for the great support offered here.

 

[Debra Dalgleish]

Right-click a cell in the pivot table, and choose PivotTable Wizard.
Click the Back button
Select the new range for the pivot table.

Or if the range is changing because rows have been added, you could use
a dynamic range as the source. There are instructions here:

http://www.contextures.com/xlPivot01.html

IS there a simple way to change the data range teh pivot table is based upon
after the table has been prepared?

Thanks

:

You could use a pivot table to summarize the data, and group the dates
by year, quarter, month, or days there. There are instructions and links
here:

http://www.contextures.com/xlPivot01.html

HAve I confused two MS applications again? Working on spreadsheet with dates
in column "A". Column A is a standard date format, with dates formatterd as
mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the
subtotals to change each time the date changes.
I get subtotals for each day(date) and a grand total. Here is the issue I am
wrestling with; I would like to "automatically" get additional subtotals for
WEEK, MONTH, QTR, and YEAR.
I thought I had run across a way to do this some time ago, but have been
unable to find the secret passage back there, now that I have the need. Or do
I have to create additional columns and extract a Week, Month, Qtr, and Year
from teh date column in order to make this work?

I have been though MOST of the posts on subtotals without success. Any
p[ointers would be greatly appreciated.

Thanks in advance for the great support offered here.

 

[JR Hester]

Thanks for getting back to me. After about 10 entries in the data section new
formula calculation fields were siomply placed on the field list. I had not
noticed this when I fired off that question. I later noticed thses calculated
fields and simply moved them to eth data area. Worked like a charm.

I had not found a way to remove fields from teh pivot table so that was
REALLY a help. Thanks.

The reason I was wanting to redefine the data range, was that I had
inappropriately included several blank rows in the range( to allow additional
data entry). This naturally wreaked havoc on the regrouping of data into
weeks or months etc, so I need to remove those empty rows. The pointer to the
dynamic datasource was teh life saver too! That works great, because the
spreadsheet I am developing for another department will grow continuously, at
least until I can move them to Accessâ„¢.

This was my first real lif eexperience with Pivot Tables. Your responses and
your CONTEXTURES link saved the day.

A 1000 thanks

The number of data elements may be limited by the number of columns
available. What happens when you try to create more calculated fields?

To remove a data field, click the arrow on the Data field button, and
remove the check mark for the data field.

Thanks for pointing me in that direction. I have not used pivot tables in the
past, and therefore am a bit of a novice with this feature. It took a few
tries to find the date "week" definition your document referred to, but have
it now.

That has developed another question.
The pivot table is report 8 "sums" for me. I also need to enter another 5 to
7 calculated fields. I successfully created two of these, however can't seem
to get more than two calculated fields added to the data area.
? Is there a limit of 9 or 10 data elements that can be placed in a pivot
table, or is there a limit to the number of calculated fields that can be
placed in the data area?

? Question #2. How do I delete or remove a data element from the active
pivot table?

Thanks again

:

You could use a pivot table to summarize the data, and group the dates
by year, quarter, month, or days there. There are instructions and links
here:

http://www.contextures.com/xlPivot01.html


JR Hester wrote:

HAve I confused two MS applications again? Working on spreadsheet with dates
in column "A". Column A is a standard date format, with dates formatterd as
mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the
subtotals to change each time the date changes.
I get subtotals for each day(date) and a grand total. Here is the issue I am
wrestling with; I would like to "automatically" get additional subtotals for
WEEK, MONTH, QTR, and YEAR.
I thought I had run across a way to do this some time ago, but have been
unable to find the secret passage back there, now that I have the need. Or do
I have to create additional columns and extract a Week, Month, Qtr, and Year
from teh date column in order to make this work?

I have been though MOST of the posts on subtotals without success. Any
p[ointers would be greatly appreciated.

Thanks in advance for the great support offered here.

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that the information helped you.

Thanks for getting back to me. After about 10 entries in the data section new
formula calculation fields were siomply placed on the field list. I had not
noticed this when I fired off that question. I later noticed thses calculated
fields and simply moved them to eth data area. Worked like a charm.

I had not found a way to remove fields from teh pivot table so that was
REALLY a help. Thanks.

The reason I was wanting to redefine the data range, was that I had
inappropriately included several blank rows in the range( to allow additional
data entry). This naturally wreaked havoc on the regrouping of data into
weeks or months etc, so I need to remove those empty rows. The pointer to the
dynamic datasource was teh life saver too! That works great, because the
spreadsheet I am developing for another department will grow continuously, at
least until I can move them to Accessâ„¢.

This was my first real lif eexperience with Pivot Tables. Your responses and
your CONTEXTURES link saved the day.

A 1000 thanks

:

The number of data elements may be limited by the number of columns
available. What happens when you try to create more calculated fields?

To remove a data field, click the arrow on the Data field button, and
remove the check mark for the data field.

Thanks for pointing me in that direction. I have not used pivot tables in the
past, and therefore am a bit of a novice with this feature. It took a few
tries to find the date "week" definition your document referred to, but have
it now.

That has developed another question.
The pivot table is report 8 "sums" for me. I also need to enter another 5 to
7 calculated fields. I successfully created two of these, however can't seem
to get more than two calculated fields added to the data area.
? Is there a limit of 9 or 10 data elements that can be placed in a pivot
table, or is there a limit to the number of calculated fields that can be
placed in the data area?

? Question #2. How do I delete or remove a data element from the active
pivot table?

Thanks again

:



You could use a pivot table to summarize the data, and group the dates
by year, quarter, month, or days there. There are instructions and links
here:

http://www.contextures.com/xlPivot01.html


JR Hester wrote:


HAve I confused two MS applications again? Working on spreadsheet with dates
in column "A". Column A is a standard date format, with dates formatterd as
mm/dd/yyyy. Remaining columns have data to be subtotaled. I have set the
subtotals to change each time the date changes.
I get subtotals for each day(date) and a grand total. Here is the issue I am
wrestling with; I would like to "automatically" get additional subtotals for
WEEK, MONTH, QTR, and YEAR.
I thought I had run across a way to do this some time ago, but have been
unable to find the secret passage back there, now that I have the need. Or do
I have to create additional columns and extract a Week, Month, Qtr, and Year

from teh date column in order to make this work?

I have been though MOST of the posts on subtotals without success. Any
p[ointers would be greatly appreciated.

Thanks in advance for the great support offered here.