Power User vs Top feature in Pivot Table – Part 4

Sort by your report other than A to Z

In the previous section I’ve talked about how to sort or filter your report. But in this time, it is slightly different. You might have a certain arrangement of how you would like your report to be. Not everytime you would want to sort the report in the most ordinary way. You may have a few items that you would like to be listed at the top and some to be at the bottom. There are just a few ways for you to achieve how the way that you wanted to. Let’s check it out:

a) You can use the drag and drop way to arrange the item. This is how you can do it:

This works if you only have a few items to drag around.

b) If you’re facing a tonnes of items, the previous way may not be a very ideal way to do it. Creating a custom list and sort your item using custom list is better, when there are many items to re-arrange.
Before that, you will firs have to create a list of item in a range, just like how you want them to be sorted. I have prepared mine just like this:

now let’s add this list into the Custom List. Go to File à Optionsà Advanced à Scroll right to the end. You should be able to see a button “Edit Custom List”. After hitting the button, import the this list using the “import” button. Click OK once you’re done. Back to the Pivot Table Setting, make sure under the tab “Tools and Filters”, the “Use Custom List while Sorting” is being enabled. Refresh your Pivot Report, you should be able to see the changes take effect immediately.

Give your pivot report a makeover- new look

There are many hidden gems features in Pivot Table many people are not aware about. In this section, I’m going to guide you through some of the “Very the awesome” layout you should explore to make your report is not only powerful on the inside, also looking guuuuuud!

The style of the pivot report from above is just the default style and layout that comes automatically as soon as you start building. In the name of god, I believe you can do so much better than that. Let’s get going.

a) Change the Compact Layout (which is the default layout) into Tabular Layout. This will immediate separate the Category field and Food Description field into different columns. The reason why I love this, so you don’t need to get confuse during the filtering and sorting process no more.
If you can recall, previously we talked about that confusing filtering and sorting process you have to go through when there is more than one field stacks on top on each other. That is because those fields are sharing that one little Filter command.

But if you were to switch the layout to Outline or Tabular, each column will comes with its own filter button. With that, you will be able to filter without select the field. Check out the screenshot below:

Besides, you can also pair it with the “Repeat all item labels”. This command will immediately fill in those empty cells with its category/ label in.

b) I believe many of you out there love the idea of merging cells, and most of the time it is easier to achieve this result by using the command “Merge and Center”. This is the look most people tryin’ to look for :

The period of the data are all merge at the top of the reports. In fact, you can easily achieve this just by enabling the command “Merge and center cells with labels”. But I also totally recommend you do this with removing the styles of your report. It looks much cleaner without any styles. This is what I usually will do.

  1. Go to Pivot Table – Options – enable the feature.
  2. go to the Style Gallery in the Pivot Table Tools: Design Tab, select “None” for the style.

Now, I’m having my report to look from this:

To this:

Leave a Reply

Your email address will not be published. Required fields are marked *