Change the data field summary operation and number format

- [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.