12 Pro PivotTable Formatting Tricks = No more UGLY PivotTables!

one of the downsides of pivot tables is they have a very distinctive look some might even say they're ugly in this tutorial I'm going to cover my pivot table formatting tricks that will transform their look and feel from this to this plus some bonus tips that I've accumulated over the 20 plus years I've worked with Excel by default row and column labels are sorted alphabetically now we can use the built-in sort options like the drop down list sorting A to Z or Z to A and in more sort options we can sort them in ascending order based on one of the value fields and likewise in descending order we can also go into more options and in here we can sort based on a custom list I don't have a custom list that relates to this table so let's cancel out of there and there instead I'm going to manually sort them which is often quicker here I can type in the field name so let's say I want Revenue first you can see it just shuffles the pivot table around the other way to rearrange manually is to select the cell containing the label you want and you get the four headed Arrow left click and drag you can see it's going to place it here and I'll release and now it's sorted the way I want so I have Revenue cost of goods sold gross profit expenses net profit by default the column and row labels for Value fields are prefix with sum of or average of Etc which is often unnecessary not to mention this helpful row labels header you can't simply type actual in place of sum of actual because this field name is already taken however you can add a sneaky space before or after actual to differentiate it I like to add a space at the front so I can right align the column label and keep in with the numbers in the column below while I'm here I can replace the row labels header either with a space to override it or if you don't want the fill to drop down and the header name here up on the analyze tab I can turn off field headers and you can see that's got rid of the drop down and the row labels header if you want the drop down just type a space over row labels to get rid of it pivot tables can often look too busy and cramped we can improve their readability by adding blank rows beneath each section this is done on the design tab blank rows insert blank line after each item and it immediately looks more like a profit and loss report rather than a pivot table by default subtotals are placed at the top of each section however this may not always be appropriate you can easily change the position to the bottom of the group by the design tab subtotals show all subtotals at bottom of group while I'm at it I'm going to get rid of the grand total row we can do that here via the grand totals drop down or for rows and columns or you can just right click on it remove grand total it's starting to come together but it still has that distinctive pivot table formatting we can remove that by creating a custom style that has no formatting and to do that go to the design Tab and in the Styles Gallery I like to pick the style that has the least formatting ideally it would be this one but I can't duplicate that but I can this one so let's start by duplicating this style that's quite plain we'll give it a name I'll call it no formatting and then for each table element simply click clear now you only need to do it for the elements that are in bold font and you can see the formatting that's been applied to each one the ones that aren't bold don't have any formatting so we can skip over those and there's a few more okay that's it all the formatting is removed I'll click ok now I need to apply that format here it is here in the custom group no formatting and now I have a blank canvas to apply formatting to now the trick here is to select the elements in the pivot table by hovering your mouse over them until you get the arrow to the left selecting it you can see I've selected all of those like elements in the pivot table now in the Home tab I can simply go and apply the formatting here I want top border and then let's repeat for the gross profit and profit I have to select them separately but holding down control allows me select them together these ones I want a single top and double bottom border now by selecting them this way if the pivot table changes shape gets bigger or smaller the formatting is applied to the elements rather than the cells themselves and so as those elements move in the worksheet the formatting goes with them expand and collapse buttons enable you to quickly hide and unhide groups of rows and columns however they can be a bit ugly if you don't need them we can turn them off by the analyze Tab and then deselect the buttons icon on the ribbon adding visual indicators with conditional formatting can help your audience quickly interpret your report in this example I can add indicators to the variance column now ideally I'll format the whole column and that'll have the same indicators but here I have a combination of income and expenses which means negative variances for income are good but negative variances for expenses are bad so I need two separate rules I'll start by selecting the income figures and then on the Home tab conditional formatting icon set and I'll go with these shapes here we need to modify them so we'll go back in and manage rules and double click to edit this one so first of all I need to reverse the icon order so that red he's at the top and then our number and number and zero zero that's fine and click ok and ok so now we can see that the negative variances have a green Dot and the positive variances have a red diamond let's repeat that for the expense items so conditional formatting icon sets shapes let's go back in and manage the rule it's the top one and here I want the icon order the same that's okay it doesn't need to change and we'll change this to number and number and click OK and OK and now we have the icons in the correct order so we have a negative variance here which says our expense for depreciation is higher than we'd planned so that's bad all the others are positive variances so they get a Green Dot because that's good now I should say if you only have one rule per column which is the preferred way to apply conditional formats for pivot tables you want to select either all cells showing variance values or all cells showing variance values for account this way if the pivot table grows or contracts the conditional formatting is going to follow it you won't have to edit the rules it will just adapt to the size of the pivot table I can't do that here because I have different rules for different types of variances now if you're not familiar with conditional formatting there's a link in the video description to a tutorial on it when working with pivot tables in a tabular layout you may want to repeat the row labels for the purpose of using them in lookup formulas or just for Aesthetics there are a few ways you can do this we can turn them on for all item labels via the design tab report layout repeat all item labels and there they are alternatively we can turn them off here if we only want to turn them on for one field at a time we can right click the field go into field settings on the layout and print tab we want repeat item labels I'll click OK having them repeated like this means I can now reference this pivot table in lookup formulas because I have the category row label repeated on each row by default pivot tables are created in a compact form which we can see here where the row labels are nested but we can choose different layouts on the design tab we've got report layout so compact form is what it's currently in we can choose outline form which it adds a blank row between each group or we have tabular layout which is Handy if we need to reference the data in a lookup formula in which case you'll want to repeat all item labels like we looked at previously when you have blank cells in your Source data those blanks get labeled blank in the pivot table row and column labels as you can see here now ideally you should never have blanks and columns that use in row or column labels but in the real world that's not always possible so the next best thing is to hide the blanks we can do this quickly by selecting one of them and then simply pressing the space bar and then enter and you can see it hides them all and if any new rows get added to the source data when they're brought into the pivot table that blank format will be applied to them of course you could change the format instead of having a space that hides the word blank you could put TBA in there or some other useful message once you've spent a load of time formatting your pivot table you'll want to make sure that formatting sticks you can do this in the pivot table options right click pivot table options and then on the layout and format tab preserve cell formatting on update you may also want to deselect auto fit column widths on update if you've set the column width to something specific once you've got the formatting the way you like it you can set a default pivot table layout via the file tab and then options and then on the data tab make changes to the default layout of pivot tables click on edit default layout if you have a pivot table already formatted the way you like you can simply select it using the selection button here I already have the pivot table selected when I went into the file options so it's picked up the cell and we can import the formatting of that pivot table you can set preferences for the subtotals grand totals and Report layout you can have blank rows inserted after each item you can repeat item labels and include filtered items in totals we can also set some pivot table options so for example the preserving of the formatting the autofit column widths the default value for empty cells so instead of blank that we saw earlier you can type that value in here now unfortunately you can't set preferences for number formats or for Styles they have to be done on a pivot table by pivot table basis I hope you found this tutorial useful if you like this video please give it the Thumbs Up And subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for watching [Music]

Channel: MyOnlineTrainingHub

Views: 24,261

Keywords: excel, pivottables

Id: pl0jgbuOqhk

Length: 12min 12sec (732 seconds)

Published: Thu Jul 06 2023