How to use pivot tables excel
A free Office suite fully compatible with Microsoft Office
Pivot tables are handy and very powerful tools to analyze complex data and break down into group of similar data and do much more. This tool summarizes, analyzes, compare calculate and patterns and trends in your data. If you are wondering how to use pivot tables excel, you have come at the right place.
In this tutorial, a basic example is shown to just let you know how pivot tables work. If you got through the detailed steps, you will surely get the answer of how to use pivot tables excel. So stay tuned and keep reading.
Open and use pivot table by shortcut key
1. This example shows various expenses done in multiple years in different categories. As you can see the data is scattered so you need to make it more understanding and see how much expense is done on certain stuff etc. This is a great example to demonstrate the power of pivot table.
2. Now press Alt+D+P to access the pivot table directly. A dialogue box opens with multiple options.
3. In the select range section, you need to specify the cells are to apply pivot table. To do this, click on the arrow button at the right side of field.
4. Bring cursor at cell A1, click/hold left mouse button to drag diagonally towards the bottom right uptil E23. Notice the cells are highlighted and also mentioned in the floating field bar.
5. Hit Enter. The cell ranges appears in the field.
6. Now enable Pivot Table in the next section and New Worksheet in the last section.
7. Click Ok. You will see the blank pivot table is created in the new workbook.
8. Notice the selected fields are mentioned in the right side of the woksheet.
9. Click and drag the Year field in the rows section of Pivot Table Areas. This is the section where you decide how data is arranged for display. As you do it, notice the year section appears in the table.
10. Now do this for all fields like category, product, and sales. All the data is displayed in the pivot table arranges in ascending year. The pivot table arranged scattered informtaion to year-wise category.
Open and use pivot table from menu
1. Open the file you wish to add pivot table. This is a test file and here various expenses done yearly with sales and other relevant information are mention.
2. Click Insert tab. The Insert ribbon appears with multiple options.
3. Now click Pivot Table
4. The Pivot Table dialogue box appears.
5. Click the arrow at the right side of field.
6. Bring your cursor at Year cell in A1, click and hold left mouse button and drag diagonally to the bottom right uptil E23. Notice the cells are highlighted and mentioned in the floating field bar.
7. Hit Enter. The cell ranges appears in the field.
8. Enable New Worksheet in the last section.
9. Click Ok. You will see the blank pivot table is created in the new workbook.
10. Repeat the same process of adding fields in the table. Hence, the pivot table is created.
So now you know how to use pivot tables excel. You can organize and compare the data anyhow you wish to.
Need to edit Word/Excel/PPT file free of charge? Download WPS Office edit files like without any cost. Download now! to get enjoyable working experience.
Was this helpful?
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. How to compare two excel sheets and highlight differences
- 3. Check if value is in list in Excel (3 easy methods)
- 4. How to copy file names in Excel from a folder?
- 5. How to color cell based on value in Excel?
- 6. How to add month name from date in Excel formula