How to modify the look of a pivot table in excel? Step by step guide

One of the main advantages of working with this program is that it allows you to create different types of dynamic tables where you can include each of the data that you think is necessary . You can create them according to your needs, thus allowing you to choose your own style and format of the table .

Therefore, when creating these elements, you will have the opportunity to modify their appearance . All this with the aim that you can improve the design and format so that each of the data is much easier to understand and analyze. It must be taken into account that when applying it, they will take on an aspect already defined by the program .

Being able to modify the appearance of these elements will allow you to change the way in which the columns, fields, rows , cells , lines are displayed, among many other aspects. All this will allow you to improve the presentation and make them much easier to analyze . In accordance with this, here we are going to explain a little more how you can carry out this process in an easy and fast way .

What aspects of a pivot table can I modify to change its appearance?

When working with pivot tables you will have the opportunity to have 83 predesigned styles different from the one applied by default . So you can have a wide variety of totally different styles and that will undoubtedly offer you a better image compared to the one obtained by default.

All this will help you make your table much more attractive to the reader and also make it much easier to understand . Therefore, here we are going to show you which are the aspects that you can modify and that will allow you to change its appearance .

Apply bands to each of the grids

This is recommended to be done especially when the table has a large number of rows and columns, so it is a very good alternative to be able to add rows with bands that will allow the data to be read without many complications.

To be able to apply this modification it is necessary that you locate yourself in the “Active cell” on the dynamic table.

Next you must go to the “Design” tab located on the ribbon and there select “Pivot table style options”. Now you must activate the box “Rows with bands and columns with bands”.

Once this is applied, you will have the following result:

Mostly the banded columns option is omitted by almost all users, this is because the banded rows option is more than enough to make the table understandable and look very good for readers .

When applying this single element it will be as follows:

Format the values ​​area

When you want to change the appearance of these types of elements, it is also very important to take into account the format of the values, since only the appearance is not important in these cases.

To be able to carry out this modification it is necessary that you right click on the dynamic table, then you must select “Number format”.

Now in the dialog box “Format cells” you must choose the category number and decrease the decimal position to 0. Finally click on “OK”.

You can also apply the format of “Currencies” and the numerical data will take the following aspect.

In the same way you can go testing each of the options that appear there available until you get the one you want . All this will help you to give another type of format to the values ​​with which you are working .

Delete empty cells

One of the biggest problems when working with tables are those cells that are completely blank, so their value is zero . An excellent option for them is to eliminate them. Which will allow you to get a better visual appearance in the element.

Each time this happens, it is best to display a different character than an empty cell, be it the number zero . To achieve this you must right-click on the pivot table, there you must select “Pivot table options” and in the dialog box that appears on the screen you must go to the “Design and format” tab .

Now you must enable the box “For empty cells, show” and in the text box you must write the number 0 . Finally, you must click on “Accept” for the changes to take effect. In this way you will be getting all the tables that are left blank have the value 0 in them .

Modify the labels

Every time a default pivot table is applied, it can be seen that the labels for the different areas are not very descriptive, which can make the element look very simple . Being able to modify this will allow you to achieve a more attractive and clearer presentation .

To be able to change the labels, what you must do is place the active cell on the label that you want to modify, then you must go to the “Formula bar” and there you change the name to the one you want.

Show or skip grand totals

Not all the time it is convenient to have the grand totals within these dynamic tables, since this will depend mainly on what you want to present in them. Therefore, it is a very good option to be able to omit or not the grand totals of the rows and columns.

To be able to execute this procedure it is necessary that you go to the “Design Tab”, there you must go to “Design Group” and click on “Grand Totals”. In the drop-down list that appears you must choose the option you need, in this case you must deactivate it for both rows and columns .

As you can see in this image, the pivot table no longer has the grand total, since in this case it is not necessary.

If you want it to appear again in your element, then you must repeat the whole procedure but now you must “Activate for rows and columns”, this will make the general total appear there again.

Modify the table structure

If what you are looking for is a different aspect for your table then you must modify the “Report Design”, for this you must go to the “Design” tab and then to the “Design” group, there select the “Report Design” button ”.

As you can see there you will find different options so that you can change the structure of your dynamic table and thus give it another appearance.

Show in compact form

This is the configuration that the program assigns to each of the tables by default, it allows grouping all the elements found in the rows. This allows you to save space in the spreadsheet and thus be able to take advantage of the space in it in a better way.

Show in outline form

Another way to display the table data is through the schema form, in this case it is about selecting each of the fields in the row area and adding it to an individual column.

Which will result in the following:

Show in outline form

Another way to display the table data is through the schema form, in this case it is about selecting each of the fields in the row area and adding it to an individual column.

Which will result in the following:

In the event that what you need is a table to create a more advanced data analysis, then you can use the option “Repeat all element labels” which is also found in the “Design” tab in the ” Report design ”.

Once selected, the following will appear:

Here you can fill in the corresponding fields in a simpler way, in this way you will have the opportunity to more easily manipulate each of the data for the next analyzes .

Show in tabular format

The same thing happens as in the previous section on formatting in outline form, in this case the row fields are also divided into different columns and the subtotals for each of the categories are displayed .

All this will be as follows:

Keep in mind that the design of these tables is basically selected depending on their objective, so it is necessary to know what you want to reflect on it to know that you can choose and that it ends up being the most appropriate for what you are looking for.

As you can see, all these are the most basic aspects of the pivot tables that can be modified in order to achieve a better appearance and a better visualization in them.

Learn to fully customize Excel pivot tables to make them more attractive

Taking into account all the aspects that can be modified in the pivot tables, the following will explain how you can easily carry out this process .

For this, it will be necessary that you follow each of the steps that we will indicate below:

  • If what you are looking for is to achieve important changes in the design of the table, and in turn make it much more interesting and easy to understand for readers, then it will be necessary that you begin to modify each of the following aspects .
  • Compact format: When you choose this type of format are you going to display the items in the fields of area row in a column and a drain is used to

Leave a Reply

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

Back to top button