What are Pivot Table and How to Prepare and Use It

Every time when you go for an interview if peoples finds you well versed with excel than they will definitely ask What are Pivot Table ??  and best thing this best utility from excel is still unknown for most of the users.

Ok Lets learn it today and know how you can use best of it to simple your office tasks.

First lets have a look at below excel sheet…A1 to C21

There are three columns Item Qty and where Sold

Now lets assume that you have to decide how much qty of Mango you have sold to states simple….I will use Pivot Result for example only

Isn’t easy to find out with the help of Pivot You can also do the same this with filter and sum formulas but if the work is being done in single click why to do unnecessary task…..make sure the always concentrate on your most important task and try to do them as soon as possible

Now lets assume that you have to decide how much qty of all fruits you have sold to states

Now lets assume that you have to decide how much qty of fruits to which city you have sold. 

Now tell me can u do all this work without pivot with ease as single click…. not at all.. it means this will definitely perfect your working in excel and will make your reporting much more easier and fast

Ok lets come to the point how to create your first Pivot Table

Just create an excel sheet as mentioned on top of this article, this is for reference only once you understand can use the way you want.

Now Select All the Rows and Columns to which you want to insert in your Pivot Table

Now click on Insert Tab and Pivot..

The next window you will get something like this…

Choose the option New Worksheet to create on different sheet and existing for current worksheet and click OK

Your Raw Pivot Table is ready, after this just we have to tick the fields required for our report

Report Filter :- You can choose any field like Fruits or City to filter the report i.e. your report will be filtered with this like like below where we have put item in report filter column city in row labels and qty in sum of Values, you can choose what ever you want and if not required you can keep report filter blank

Raw Labels :- any text Fields can be kept here like in our above example we have kept Sold Filed i.e. the cities where we have sold our fruits you can see the field structure as below.

Column Labels : if you want to keep your data in column structure you can use this one the result will be something like this..

If columns are less this type of report can also be useful but it depends of your data only.

Value : This field is used for sum of numeric values which is like qty in our example

You can sort or filter your data according to position of your raw label fields or column fields like if item can on top and sold in bottom in row label the result will be like this

Results will be sorted according to Item

And if you replace i.e. sold on top and item on bottom the results will be like this..

Results will be sorted according to City

Some More Important things 

above 4 buttons you can get in Design Tab 

First subtotals can be used if you want to get sub totals or not

You can use second button to decide whether you needs grand total or not.

Third button is important choose report layout as tabular to make your report like a table which we have used in our above example

If you want to insert blank lines after every field use the forth button.

Also you can have some more options to choose your table design colour etc…

Hope this will be a useful article for your if yes than don't forget to share with your friends…

TIP:- Practice is the only key which can make this useful for you , practice until you become ease in using this useful and powerful tool of excel

Your Comments are Valuable to us please share your view about this article

Technorati Tags: how to create a pivot table,how to use pivot,pivot table,pivot chart what are pivot table