Format PivotTable values: Currency, percentage, and more

It's extremely important to make sure that the values in our PivotTable are formatted correctly. Not only does it add to the overall structure and look and feel of the pivot table, more importantly, it makes your PivotTable report so much easier to read. So let's dive back into our product data. This is the current state of our pivot table. Now, I'm going to make a small change. I'm just going to remove the average of revenue field so we just have the sum. Now, let's take a closer look at the formatting of these numbers. They look pretty good, right? And I would agree. We have a comma separator in there and we're currently displaying two decimal places. Now, bear in mind, a lot of the time when you add a field into a PivotTable, the formatting won't be number format. You can see if we look in the home ribbon in the number group, this PivotTable actually has number formatting applied. But what if I want to change the way that these numbers are formatted. Well, if you are an Excel user, you might be thinking to yourself, oh, that's easy. I simply select the numbers that I want to format, go to the home ribbon and change them to whatever I want to change them to, for example, general format. Now note, this isn't the correct way to format numbers in a PivotTable. When you do it this way, you're simply applying the general format or whichever format you've selected to the selected cell range and PivotTables by their nature can change. So whilst this number might be located in cell B6 right now, if I were to move these fields around, that number is going to move and then the formatting is not going to carry with it. So the way that we get around that is simply by right clicking on the field and accessing number format this way instead. Then we can go in and we can choose what we want. So let's say that I want accounting format. I don't want a currency symbol, but I'm happy with two decimal places and click on okay. That way if I was to move my fields around that number formatting is going to carry through. So always remember that when you're formatting numbers in a PivotTable. Another way that we can essentially apply formatting to numbers in a table is by showing the values as something else. So let me demonstrate that to you. Let's add another revenue field into the values area. Again, we're getting a sum of the revenue. Notice that when I've added that new field in, the number formatting doesn't carry across. And this is something that is a little bit annoying with PivotTables. Every time you add a new field into the pivot table, you need to go back in and reapply your number formatting. So let's just quickly do that and click on OK. Now, maybe this time, instead of displaying the numbers in this way, I want to show them as a percentage of the total. If we right-click, we have a little option down here called Show Values As, and this allows us to format our numbers in an entirely different way. We can show the percentage of the grand total, the percentage of the column, the difference from the previous value, so on and so forth. We have so many options in here. So if I just change this to percentage of grand total, check out what I'm getting now. I'm now getting to see those numbers as a percentage of that grand total. So very easily there, I can see that monitor sales accounted for 14.22% of the overall total. Again, a really valuable way to analyze your data. So I highly encourage you to right-click and have a little look through some of these, because I think you'll find them extremely useful.