Pivot Table is very useful in analysing, sorting and getting specific details.
Here we have data of 200 rows consisting of columns No, Product, Category, City, Amount and Date. Let's see how pivot table can be created to summarize all details.
1. Select any cell from the table. Go to Insert-> Select PivotTablein Tables group.E
Also see: Impress your boss using Pivot Chart
2. Create PivotTable dialog box will open. Check the Table range, if it differ from required data then change the range.
By default Pivot chart is selected in New Worksheet, You can choose option of Exisiting Worksheet if your worksheet is not loaded with data.
3. Excel will create new worksheet with Pivot Table fields. New PivotTable Tools- Options and Design Tabs are created. Selected Table titles will appear in PivotTable Field List. The layout of Column Fields, Row Fields and Filter Fields are created.
4. For basic understanding, drag Product field to the Row Labels and Amount field to Values as shown below.
5. You can see that summary of Product and Total Amount of each Product is prepared.
6. Now do one thing, drag Category to the Report Field.
7. Category field will appear in first row.
Choose category from dropdown to categorize your data. For example, we select Fruit category.
To see the summary City wise drag Country field to Column Labels. It also called as Two-dimensional Pivot Table.
We can easily summarize the total value of each fruits in different cities.
Explore more, It's Amazing…
4,699 total views, 1 views today