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 fileds 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 ie. the cities where we have sold our fruits you can see the field structure as below.
image
Coloumn Labels : if you want to keep your data in coloumn 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 fieds or coloumn 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..