What are Pivot Table and How to Prepare and Use It


Create Pivot Table 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

Pivot Example

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

Mango Pivot
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

all fruits pivot

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

pivot city wise

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.

how to create a pivot

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

Now click on Insert Tab and Pivot..

image

The next window you will get something like this…

pivot range

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

image

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

image

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.

image

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

image

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

image

Results will be sorted according to Item

image

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

image

Some More Important things 

image

above 4 buttons you can get in Design Tab 

image

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


No comments:

Post a Comment

Your comments are Valuable to us.. It will take a while to get your comments approved and once done you can see them here........!!!