Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Changing the data field summary operation

From: Excel 2008 for Mac: Pivot Tables for Data Analysis

Video: Changing the data field summary operation

Most of the data lists you summarize using Pivot Tables contain some sort of financial or personnel data, such as sales or hours worked, which means that you'll usually want to find the sum of the values in the list. You are not limited to adding values together though. You can choose from several summary calculations and also have the ability to change the type of value shown in the data area. For example, you can express individual values as a percentage of the column or row total or compare values to those of the previous year. To change the summary calculation used in the Pivot Table, click any cell in the PivotTable data area. And then, on the PivotTable toolbar, click the Field Settings button.

Changing the data field summary operation

Most of the data lists you summarize using Pivot Tables contain some sort of financial or personnel data, such as sales or hours worked, which means that you'll usually want to find the sum of the values in the list. You are not limited to adding values together though. You can choose from several summary calculations and also have the ability to change the type of value shown in the data area. For example, you can express individual values as a percentage of the column or row total or compare values to those of the previous year. To change the summary calculation used in the Pivot Table, click any cell in the PivotTable data area. And then, on the PivotTable toolbar, click the Field Settings button.

Then in the Summarize by list, click the operation that you want to use. So if I wanted to change the summary operation to average, I click Average, click OK and Excel now displays the average. For 2008, the monthly average would have been 80.25. For 2009, that number there, and so on. You'll notice that the grand total values also take on the average. So 67 and 128 averages out to 97.5. 69 and 131 averages out to 100 and so on.

I will show you one other operation, in this case, Count. The Count operation counts the number of occurrences for each data point. In other words, how many measurements we had for January of 2008 for FirmA, for 2009, same thing. So if I click Count and click OK, I get one measure for each for a grand total of two. Again, it doesn't work that well in this circumstance, but you should use Count later on in case your Pivot Tables contain data that doesn't communicate discrete elements. So I will click Undo. Another way to summarize data inside your Pivot Table is to change how Excel calculates the values, not only by operation but the way it displays the value in relation to other values in the Pivot Table.

I will give you an example. Suppose that I wanted to display the 2009 values as a percentage difference from the values from 2008. To do that, I would click any cell in the data area. Click Field Settings button and then click Options. Then in the Show data as area, instead of Normal I would display it as % Difference From. I can then select the base item from my comparison. In this case, I want to compare 2009 to the previous year, which is 2008, so I will click Previous.

If I had more data, for example, if I had a column for 2010, clicking previous would compare that data to 2009. If I were to select 2008, then the Pivot Table would compare the data in 2009 to 2008 and then it would also compare the data in 2010 to 2008. So make sure that you have your basis for comparison set properly. But in this case, I am comparing percentage different from the previous year, so I can click OK. 2008 disappears because there is no earlier year to which to compare it and 2009 is displayed as a percentage difference from 2008.

Just by way of example, I'll show you another comparison. Go back to the Field Options button. Suppose I wanted to show the data as a running total. So in this case, I would have a running total in. And let's see a running total by month. When I click OK, Excel changes the Pivot Table. And here's what's going on. You'll notice that the values in each column go up continuously. The reason that happens is because Excel adds the value for February 2008 for FirmA to the value from January of the same year.

It then does the same thing for March, for April, for May and so on. You'll notice that the subtotal row is blank. That's because the value in December for 2008 and 2009 is the subtotal. You should take the time to experiment with the summary operations and settings available for use in the data area. You will probably find one or more operations, other than addition, that provide meaningful information about your data.

Show transcript

This video is part of

Image for Excel 2008 for Mac: Pivot Tables for Data Analysis
Excel 2008 for Mac: Pivot Tables for Data Analysis

26 video lessons · 5341 viewers

Curt Frye
Author

 

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Join now "Already a member? Log in

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Excel 2008 for Mac: Pivot Tables for Data Analysis.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.