how to format pivot tables in excel 2013

If you have excel 2013 or above installed in your system, it has an option called Recommended PivotTables.

Do you feel it’s a fantasy? Let me take a moment to make you aware that this fantasy has become a reality in excel now. How good it would have been if you don’t need to worry about the questions like – “Which columns should be ideal for my pivot table?”, “Which columns should go under rows, columns, values, etc.?”. Note, however, this only works when you don't have column labels in your pivot table.īy setting the number format directly on the Value Field itself, you guarantee that all instances of the field will always use that number format, no matter how big the pivot table becomes.You can download this Pivot Table Examples Template here – Pivot Table Examples TemplateĮxample #1 – Automatically Creating a Pivot Table The third way set number formatting is to double-click directly on the value field name in the pivot table, then click Number Format. This brings you to the same dialog box, and then you can set the Number format as before. The second way to set number formatting is to right-click on a value directly in the pivot table, and select Value field settings from the menu. Then, in the Value Field Settings dialog box, click the Number Format option and apply the format you like. The first way is to click the field drop-down menu, and choose Value field settings. Let's undo this format, and look for a better way to apply number formats.Ī much better way to set number formatting is to apply it to the field itself.

However, this method becomes awkward when pivot tables get bigger, and it has a major weakness: As you try out various table layouts, and apply different value formatting, Excel can become confused and not apply the same formatting as expected to all like values. In general, this works pretty well, and later versions of Excel are pretty good at extending this format as the table grows when new data is added. The first way is to select cells directly in the pivot table and apply a format manually.įor example, we can select the sales values in this pivot table and apply a currency format with no decimal places. There are two ways you can format values in a pivot table.

You could just select the numbers in the pivot table itself, and format normally, but there's a better way. The moment you add numbers to your pivot table, you'll want to adjust the formatting.

How to format pivot tables in excel 2013