You’re 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.
- [Narrator] Most of the data lists that you summarize using pivot tables will have some sort of financial or personnel data. This could be sales totals or hours worked. That usually means that you need to do some formatting to your data, so it doesn't just appear as general numbers, and also you can change how they're summarized. I will show you how to change the appearance of your data as well as the summary operation in this movie. My sample file is the summary and format Excel workbook, and you can find that in the chapter nine folder of the exercise files collection.
This pivot table, which is on sheet one of the workbook, summarizes the sum of the value of orders by state and then by product category. So for Alabama, you have landscape, light bulbs, and solar panels, for Alaska, we just have batteries and solar panels, and so on. What you'll notice is that the values are just numbers. They should be accounting or currency data. You can change the format of your data by clicking any cell in the pivot table and then on the pivot table analyze contextual tab, which for me is already selected, click the field settings button.
That displays the pivot table field dialog box, and you have the name here. You can change it if you want. I usually don't. I want to change the format of the data, so I will click the number button. That displays the number page of the format cells style log box. I will click accounting, and I see I have a currency symbol, and I will go to two decimal places. If I were working with data perhaps from the UK or the EU, then I could select other symbols.
You see a lot here for dollars. We have Canadian dollars, Hawaiian dollars, Malay, and if I scroll down, you can see the Chinese Yuan, Armenian currency, Pashto, and so on. In this case I'll just stay with American dollars. With what I have, click OK. Click OK, again, and the data is formatted and now it's easier to read. And in particular, I like that I have two decimal places, so I have $25,015.70, instead of just 0.7.
But now let's say that I want to change how the field values are summarized. For that, I'll click any cell in the data area, and then go back to field settings on the pivot table analyze contextual tab, and here I have the summarize by panel. I can do sum, count, average, maximum, minimum, product, count numbers, and standard deviation. Let's say that I want to find the count of values. So I'll click count, click OK, and I see I get eight total orders from Alabama, two for landscape lighting, three for light bulbs, and three for solar panels.
I have three for Alaska, 42 for California, and so on. I'll press Command + Z to go back. And now let's say that I want to display the values as percentages, perhaps percentages of each of my subgroups. For that, with a cell in the data area selected, I can click field settings, and then click show data as. I currently have no calculation, but if I click that controls down arrow, you see that I can have difference from, percentage of, and so on.
I can also do a running total, or percentage of row total, percentage of column total, and so on. Let's say that I want to do percentage of column total, so I will click that item and click OK. And you see that I get the percentage of the entire column. So I have Alabama at a little over half a percent, Alaska at 1.51%, California at 29.62 and so on. Now let's go back to field settings, show data as, and instead of percentage of column total, let's do running total.
So I have running total in, OK, and I'll press Command + Z again to switch back. You should take some time to experiment with the summary operations and settings available for use in the data area. You'll probably find one or more operations other than addition that provide meaningful information about your data.
Author
Released
9/24/2018- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks
Skill Level Beginner
Duration
Views
Related Courses
-
Monday Productivity Pointers
with Jess Stratton26h 25m Intermediate -
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
macOS Mojave Essential Training
with Nick Brazzi4h 45m Beginner
-
Introduction
-
What you should know1m 11s
-
1. Getting Started with Excel
-
Introduce the ribbon3m 20s
-
Set program preferences4m 12s
-
Get help in Excel3m 16s
-
2. Managing Workbooks
-
Set workbook properties3m 52s
-
Create and modify templates3m 33s
-
3. Working with Worksheets, Cells, and Cell Data
-
Copy and paste cell data4m 17s
-
Create named ranges4m 41s
-
Create an Excel table4m 56s
-
4. Sorting, Filtering, and Managing Worksheets
-
Sort worksheet data3m 23s
-
Create a custom sort order5m 29s
-
Filter worksheet data4m 13s
-
Manage worksheets3m 54s
-
-
5. Summarizing Data Using Formulas and Functions
-
Add a formula to a cell4m 56s
-
Create an AutoSum formula3m 34s
-
6. Formatting Worksheet Elements
-
Manage text alignment5m 29s
-
Copy cell formats2m 30s
-
Manage cell styles4m 31s
-
Manage Office themes3m 14s
-
Manage conditional formats3m 41s
-
7. Working with Charts
-
Create bar and column charts3m 20s
-
Create line charts2m 56s
-
Format chart elements3m 58s
-
Create sparkline charts5m 11s
-
-
8. Working with External Data and Objects
-
Use hyperlinks5m 14s
-
Create and format shapes4m 26s
-
Add and adjust images4m 59s
-
Align and layer objects4m 49s
-
9. Exploring PivotTables
-
Create a PivotTable3m 21s
-
Pivot a PivotTable2m 51s
-
Filter a PivotTable4m 2s
-
-
10. Reviewing and Sharing Spreadsheets
-
Check spelling2m 36s
-
Manage workbook comments4m 19s
-
Set and remove print areas3m 36s
-
-
Conclusion
-
Further information1m 2s
-
- Mark as unwatched
- Mark all as unwatched
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.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Change the data field summary operation and number format