FREQUENTLY OCCURRING PROBLEMS IN PIVOT TABLES

Table of Contents

INTRODUCTION

PIVOT TABLE is a well known feature of EXCEL which everybody of us might have heard of. But many times we don’t know how to effectively use the PIVOT TABLE.

PIVOT TABLE is a dynamic table which we can create in Excel. We called it dynamic as we can transform it within seconds. The original data remains the same.

We know that whatever is hinged to a pivot, can rotate here and there, so is the name given to these tables.

PIVOT Table is a very powerful tool to summarize, analyze explore the data in very simple steps. Its very important to learn the use of pivot tables in excel if we want to master excel. Pivot tables give us the facility to put different simple operations on a selected data in seconds.

We have already learnt about PIVOT TABLES HERE.

In this article we will focus upon the frequent difficulties which users face while using the pivot tables.

HOW TO CHANGE THE LAYOUT OF PIVOT TABLE IN EXCEL

SOLUTION:

It is really easy to change the layout of the pivot table.

After we have created a pivot table, many times we need to change the layout of the pivot table.

LAYOUT OF THE PIVOT TABLE means to rearrange its rows and columns etc. to different positions as per our requirements.

Let us see how we can change the layout of the pivot table.

For the example we take a table which we have already pivoted.

STUDENT ID NO. REMARKS AMOUNT DAVE 12 A 12000 KABIR 13 A 13000 HELEN 14 B 11000 NAVAM 15 B 23000 POONAM 16 B 21000

In the table shown above, let us try with the simple operation of changing the position fields.

Let us try to put the AMOUNT COLUMN in the center.

FOLLOW THE STEPS TO CHANGE THE LAYOUT OF THE PIVOT TABLE

  •  As both of the columns to be swapped are values, it can be done easily.
  • Click anywhere in the PIVOT TABLE.
  • The right side PIVOT TABLE FIELDS box will open.
  • Currently the PIVOT TABLE FIELDS BOX looks like this.
CHANGE THE COLUMNS OF PIVOT TABLES
  • Find out where the column fields are placed.
  • As we can see they are in the VALUES column.
  • Just hold the ID FIELD and drag it down.
  • As soon as we do this, the AMOUNT FIELD comes to the top and ID NO. field to the bottom.

The subsequent change in the table takes place.

PIVOT TABLE AFTER CHANGED SEQUENCE

HOW TO ADD OR REMOVE FIELDS FROM A PIVOT TABLE IN EXCEL

SOLUTION:

It is so frequent in Excel when we play with the rows and columns while working with our sheets. But is it so easy?

Not really!!

We have to cut the Row and then adjust it somewhere else. With this, we need to keep in mind that our formulas should be intact and there should not be any problem with them. Similar is the case when we need to shift or delete or insert any new column. But in PIVOT TABLES, we can do that in a snap. That also without worrying about the formulas.

In fact, in pivot tables we need to just worry about the initial data, so that Excel doesn’t find hard to pick the data, rest PIVOT TABLE does everything.

So let us find out how we can add or remove any row or column in a PIVOT TABLE in Excel.

STEPS TO INSERT COLUMNS IN THE PIVOT TABLE:

  •  When we had chosen the data at the first step for creating a PIVOT TABLE, we should have chosen all the data which can be in demand at any time. [CREATE PIVOT TABLE]
  • After the creation of pivot table, all the fields are found in the PIVOTTABLE FIELDS box on the right side of the EXCEL.[LOOK PICTURE BELOW].
  • In the picture below, we can see that we didn’t choose the REMARKS COLUMN for the first time. So if we want to insert the column just drag the REMARKS FIELD and drop it in the COLUMNS box in the lower portion as shown in the picture.
  • The column will be added to the table in a second.
CHANGE THE COLUMNS OF PIVOT TABLES

STEPS TO REMOVE COLUMNS FROM THE PIVOT TABLE:

  •  We saw how we inserted a column in the pivot table which was quite easy.
  • Now let us find out how we can remove the columns from the pivot tables.
  • Find out the column which we want to remove from the pivot table.
  • DRAG it OUT of the box and drop anywhere. A x sign is visible as shown in the picture which means it will be removed.
  • Or RIGHT CLICK the field in the COLUMNS BOX and choose delete.
  • The column will be deleted or removed from the pivot table.
PIVOT TABLE AFTER CHANGED COLUMN SEQUENCE

HOW TO COPY DATA FROM PIVOT TABLE IN EXCEL

SOLUTION:After we have created the pivot table as per our preference, the final target is to take out the table and place it as a normal table in other sheet or the same sheet. Let us find out the ways to copy the table to some other place. 

COPYING COMPLETE TABLE DATA:

We can copy the table data in many forms.

  • With the color theme of the Pivot table
  • Without the color theme of the pivot table
  • Partial pivot table

COPY THE PIVOT TABLE EXACTLY WITH THE COLOR THEME:

  • Select the complete PIVOT TABLE including the headers.
  • PRESS CTRL+C or RIGHT CLICK>COPY.
  • Click the cell where we want to paste the table.
  • Right Click the cell where you want to paste the pivot table.
  •  Choose PASTE SPECIAL>PASTE SPECIAL as shown in the picture below
COPY THE PIVOT TABLE AND RIGHT CLICK AND CHOOSE PASTE SPECIAL
  • Click PASTE SPECIAL and the following PASTE SPECIAL DIALOG BOX will open.
  • Under the PASTE SECTION choose ALL USING SOURCE THEME and click OK.
  • The data will be converted to a simple table having the same color theme.
SELECT OPTION-ALL USING SOURCE THEME
  • Click PASTE SPECIAL and the following PASTE SPECIAL DIALOG BOX will open.

COPY THE PIVOT TABLE DATA WITHOUT THE THEME:

  • Select the complete PIVOT TABLE including the headers.
  • PRESS CTRL+C or RIGHT CLICK>COPY.
  • Click the cell where we want to paste the table.
  • Right Click the cell where you want to paste the pivot table.
  •  Choose SECOND OPTION having a clipboard showing numbers. [It is a direct option for paste special>values only]
  • The table data will be pasted without any theme.
RIGHT CLICK AND COPY PASTE VALUES ONLY

COPY THE PARTIAL DATA FROM A PIVOT TABLE

  • Select the partial table i.e. any column.
  • Right-Click and press copy.
  • If we want to keep the color theme choose PASTE.
  • If we don’t want color theme, choose PASTE VALUES.

NOTE: IF WE CHOOSE THE COMPLETE TABLE AND PASTE IT, IT WILL PASTE THE COMPLETE TABLE IN THE PIVOT FORM ONLY.

COPYING A PART OF PIVOT TABLE FROM ONE PLACE TO OTHER

HOW TO DELETE PIVOT TABLE IN EXCEL?

Deleting a pivot table can create some confusions many times.

FOLLOW THE STEPS TO DELETE A PIVOT TABLE IN EXCEL

IF THE PIVOT TABLE IS ON A SEPARATE SHEET:

  1. Simply Right Click the sheet and choose DELETE SHEET.
  2. The Pivot Table will be removed.

or

  1. Select the complete table and press Delete button on the keyboard or Right Click and choose Delete.
  2. The Pivot Table will be removed but sheet won’t get deleted.

IF PIVOT TABLE IS ON THE SAME SHEET:

  1. Select the complete table and press DELETE BUTTON on the keyboard or Right Click and choose Delete.
  2. The Pivot table will be deleted without affecting the original data.

HOW TO COPY PIVOT TABLE IN EXCEL ?

SOLUTION: We can copy the pivot table very simply. 

NOTE: IF YOU WANT TO COPY THE DATA OR VALUES ONLY, SEE PREVIOUS SECTION.

Follow the steps to copy the pivot table as such to some other location.

  • Copy the complete pivot table.
  • Choose the location where you want to paste it. The location can be same sheet or other sheet.
  • Right Click and choose paste or press CTRL+V.
  • The complete PIVOT TABLE will be copied and would have same properties as the original one.
  • There won’t be any connection between the original pivot table and new one.
STEPS TO COPY THE PIVOT TABLE AND MOVE IT SOMEWHERE ELSE

So these were a few frequent issues and their solutions which we find while dealing with PIVOT TABLES in EXCEL.

Let us discuss a few doubts in the FAQ section.

HOW TO REFRESH DATA IN PIVOT TABLES TO BRING IN FRESH DATA?

As we know that Pivot Table is a representation of the original data which help us to perform many predefined functions within seconds.

But what if the original data is changed. 

THE PIVOT TABLE DOESN'T CHANGE THE ORIGINAL DATA BUT CREATES A COPY OF THE DATA ONLY.

REFRESHING THE DATA MEANS TO BRING IN THE FRESH COPY OF THE DATA.

FOLLOW THE STEPS TO REFRESH THE DATA IN PIVOT TABLE.

  1. Right Click anywhere on the pivot table and click REFRESH.
  2. The fresh copy of the data will be fetched from the original address

or

  1. Click anywhere on the pivot table.
  2. A new tab PIVOTTABLE ANALYZE will appear.
  3. Select the tab.
  4. Choose REFRESH to refresh the selected pivot table or click REFRESH ALL to refresh all the pivot tables.

FAQs

CAN PIVOT TABLES BE SHARED IN ANOTHER EXCEL WORKBOOK AND UPDATE CHANGES
DYNAMICALLY ?

Yes and No.

Yes, you can share a single table or copy the pivot table from one workbook another but

No, you can’t make it to make the changes dynamically, but you need to refresh the data.

PIVOT TABLE IS A VISUAL ELEMENT WHICH CAN BE UNDERSTOOD AS , PICKING UP THE DATA FROM THE TABLE AND CREATING SOMETHING ELSE FROM THE DATA. IF YOU CREATE THREE PIVOT TABLES FOR THE SAME DATA, ALL THE THREE WILL BEHAVE SEPARATELY AND THEIR WON'T BE ANY CHANGE IN THE PIVOT TABLE DATA UNTIL YOU REFRESH THE DATA , THAT TOO FOR EACH OF THE THREE PIVOT TABLES SEPARATELY.



WHAT IS THE REQUIREMENT OF PIVOT TABLES IN EXCEL?

Pivot tables help you to analyze the data with an ultrafast speed without touching your actual data which sits at the back of the pivot tables.

Pivot table segregates all the rows and columns. Within the seconds you can choose which rows to be used and which are not to be used. Similarly , the required columns can be picked .

You can tell the pivot table what needs to be done and it’ll be done within seconds.

Now, think of the standard procedures where you need to cut or copy and paste your columns from one position to other, the you need to subtotal various categories , if there is any addition or deletion, you need to incorporate that very carefully and so on.

Yes, PIVOT TABLES may seem to be confusing and a bit tough at first, but they are extrememly powerful and handy.