Summarize Pivot Tables

Shamoun

New Member
Joined
Dec 22, 2010
Messages
15
Default options in Excel for summarizing Pivot tables is Sum, average, max, min, product etc. Is it possible to summarize a pivot table by the last value in the given columns?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Hi. Please, can you give an example of what is wanted? Thank you.
 

Shamoun

New Member
Joined
Dec 22, 2010
Messages
15
Hi.
i downloaded Quarterly Earning Per Share for 600 companies for last 5 years and made a pivot table. I have companies and quarters in the rows section and years in the column section. So any companies given data looks like following

CompanyXYZ 2006 2007 2008 2009 2010 2011
Q1 1.1 1.2 1.3 1.4 1.5 2.0
Q2 2.3 2.4 2.5 2.6 2.7
Q3 3.4 3.6 3.8 4.0 4.2
Q4 5.0 5.3 5.5 5.8 6.0

Currently what the pivot table does is summarize the "years" column either on the basis of sum, average, max, min etc.

What i would like it to is summarize by the basis of last entry in that year. So the summary should look like following:

2006 2007 2008 2009 2010 2011
5.0 5.3 5.5 5.8 6.0 2.0

Notice the last entry in 2011 was in Q1, so i want it to pick that value up.

Thank you for your help.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Maybe, drag the "quarter" field to the top LHS where it becomes a 'page' field and then select Q1.

Or, where it is right now, hit the little drop down that should show field entries and deselect all, then select Q1.

Would that be OK or are you after something else? Or automatic? Or ??
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

Or maybe it could be a different quarter for each different company?

If you can explain it, it likely can be done somehow...
 

Shamoun

New Member
Joined
Dec 22, 2010
Messages
15
Its a different quarter for each company, so the page field/selection does not work.

I understand what you are suggesting, and yes it would work manually where i select relevant quarter for different companies. But i want something automatic.

The only i way figured out was that in the original source data, i added a field and with the help of a function, was able to pull the last entry of each year (for the relevant company) in that column (field). Then i made a pivot table using that field. It gets me the summary i wanted, but then i do not have the entire data in table for comparison.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

OK, I understand the particular quarter will be specific to each company.

I can understand you could identify that by an extra field. (And, BTW, that could be done without an extra field by using a different approach if need be.)

I don't understand the last sentence, "It gets me the summary i wanted, but then i do not have the entire data in table for comparison."

I also don't 100% understand the result from your second post, #3 in the thread.
What i would like it to is summarize by the basis of last entry in that year. So the summary should look like following:

2006 2007 2008 2009 2010 2011
5.0 5.3 5.5 5.8 6.0 2.0
Can you give some further explanation, please, of exactly what is wanted?

In the quoted section, why are years 2006-2010 the Q4 results and 2011 Q1?

Is the rule: for other than the last year give Q4 results and for the last year, the last Q available?
 

Shamoun

New Member
Joined
Dec 22, 2010
Messages
15
if i can explain it simply (forgetting the example of my dataset)- As per default the pivot table can summarize giving you either SUM, MAX, MIN, AVERAGE etc of the values. All i want is the pivot table to summarize the data by last value.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
Well, we've gone back to the original question.

All i want is the pivot table to summarize the data by last value

Which I don't understand.

And now we're wanting to forget the example dataset.

On this basis I can't help. Sorry. I don't understand the requirement.

Like I wrote earlier, "If you can explain it, it likely can be done somehow..."

Regards
 

Claymationator

Well-known Member
Joined
Sep 26, 2006
Messages
705
if i can explain it simply (forgetting the example of my dataset)- As per default the pivot table can summarize giving you either SUM, MAX, MIN, AVERAGE etc of the values. All i want is the pivot table to summarize the data by last value.

I am not sure if that is possible in the Pivot Table without changing the underlying data. The Pivot is summarizing the data based on the year, quarter, and company. Each of the values that you have listed there corresponds to one company, but different quarters/years for each specific value. You say that you only want to show the last value (assuming the last value input for any specific year), but if your Pivot is summarizing it this way, then you have defined the 2.0 value as being in Q1 of 2011. So, if you are going to show quarters, it will only correspond with Q1 of that year. However, if you take the quarters out, you will then have a SUM, AVERAGE, COUNT, etc. of all of the previous years...2011 would be correct in this case as there is only one quarter, but all other years would not show the desired result.

If that is correct, and I understood your question correctly, I am not sure how you would display it with the way you have it currently set up. You could re-classify the data as Q4, but that isn't quite correct.

If the data always follows the same pattern that you have here, you could show it as a MAX as Q4 is always the highest number of each year...so that could be a workaround if your data always keeps that pattern.
 

Forum statistics

Threads
1,141,019
Messages
5,703,763
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top