PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

FREQUENTLY OCCURRING PROBLEMS IN PIVOT TABLES

PIVOT TABLE IN EXCEL
OUR FIRST PIVOT TABLE

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.

PROBLEM: 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 CHART
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 COLUMN SEQUENCE

PROBLEM: 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 CHART
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

PROBLEM: 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 COMPLETE PIVOT TABLE WITH THEME AND CONVERT IT TO SIMPLE TABLE
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.
COPY PIVOT DATA ONLY
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.

copy partial table or part of table from one location to other in excel
COPYING A PART OF PIVOT TABLE FROM ONE PLACE TO OTHER

PROBLEM: 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.
copy pivot table to other location in excel
STEPS TO COPY THE PIVOT TABLE AND MOVE IT SOMEWHERE ELSE
  • Click PASTE SPECIAL and the following PASTE SPECIAL DIALOG BOX will open.

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

YOU MAY LIKE

PREVIOUS ARTICLE
NEXT ARTICLE

LEARN

Encourage us by following at …

Facebook
Twitter
Pinterest

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]