How to Use a Pivot Table in Excel (A Complete Guide)
A free Office suite fully compatible with Microsoft Office
Pivot table is great tool to analyze complex data and also helps to summarize, analyze, calculate and compare patterns and trends in your data. If you don’t know how to use a pivot table excel, you are at the right place.
To simplify the process, we have taken basic example to simplify the process, although there is various usage of pivot tables. If you got through the detailed steps, you will surely get the answer of how to use a pivot table excel. So, stay tuned and keep reading.
Open and use pivot table by shortcut key
1. Open the file you wish to add pivot table. In this test file,expenses done yearly with sales and other relevant information are mention.
2. To access the pivot table directly, press Alt+D+P. A dialogue box opens with multiple fields.
3. In select range, you need to mention cells to apply pivot table. For this, click on the arrow button at the right side of field.
4. Put your cursor at Year cell in A1, click and hold left mouse button and drag diagonally till E23. Notice the cells are mentioned in the floating field bar
5. Hit Enter. The cell ranges appear in the field.
6. Now enable Pivot Table in the next section and New Worksheet in the last section
7. Click Ok. Blank pivot table is created in the new workbook.
8. At the right side of the worksheet you will see selected fields.
9. Click and drag Year field in the rows section. Here, decide how data is arranged for display. After you’ve done it, see the year section appears in the table
10. Now repeat the process for category, product, and sales. Notice data is arranged in ascending year. The pivot table function has displayed scattered data in 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. Go to the Year cell in A1, click and hold left mouse button and drag to the bottom right until E23. See cells mentioned in 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 a pivot table excel. So organization and comparison of the data will get easy you.
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 compare two excel sheets and highlight differences
- 2. How to expand cells to fit text in Excel
- 3. How to copy file names in Excel from a folder?
- 4. How to import external data in WPS Spreadsheet
- 5. How to use the VLOOKUP function across multiple sheets in WPS Office Excel?
- 6. Top 10 simple family tree template Excel free download