In Microsoft Excel, Pivot Tables are interactive tables allowing the user to summarize and group numbers of data in a tabular concise format for easy analysis and reporting. Excel Pivot Tables aim is to count, total and sort the data, thus, this table is also available in Microsoft Access. It was originally called Pivot Tables because the user can drop and drag fields while rotating the summary fields thus creating cross tabs similar to Access CrossTab Queries. One of the advantages of the Microsoft Excel Pivot Table is its ability to provide an alternating data view.
Steps to Create a Microsoft Excel Pivot Table:
- Start by launching Microsoft Excel
- Download the data “PivotTable Source File”
- Click Data menu then select PivotTable and then click PivotCharts Report
- Answer the question
- Specify the data location you have to summarize
- Click the “Next” button.
- Ensuring the option for New Excel Worksheet is chosen, once the user clicks the “Next” button from the previous step, and he or she can click the “Finish” button.
- Assemble or gather the Pivot Table
- Look for the Data. For instance, you want to see your sales representative’s accomplishment, all you have to do is to add the “SalesRep” field to the spreadsheet row area. Then click on the button that shows SalesRep, then change the decline to its row area and then click the button Add.
- Then, add its total field to the area of data.
- However, if you want to view their sales in every category, how would you do it? Well, just add the “Category Field” to the area of the column.
- If you don’t want condiment category to your analysis then you have to click the down arrow just beside deselect condiments and category.
- Click on the OK button. In this way, you will notice the recalculated grand totals not to include the condiment category results.
- To make your Pivot Table look more better and great, there available massive amount of Auto formats
- You are now done with your new Pivot Table.
Any experienced Excel consultant can tell you, Microsoft Excel is the most powerful and fantastic tool for analyzing data and report trends, charting, and reporting on data and its various relationships. Sometimes, the Excel consultant must produce various financial reports based on the data presented, to analyze numbers of data and then present it to their employer and colleagues. Very often beginners who start using Excel are intimated by reports and pivot tables since it is one way in which people determine your so called Excel skills level, and that initially, learning PivotTables were considered hard. Most Excel consultants learned PivotTables at work or some people gained knowledge about its function at home for use at work. Surprisingly it’s not used everyday.
Be reminded that the Microsoft Excel PivotTable can evaluate or examine numeric relationship in just a click. Thus, produce charts and reports with just a click even without going to different steps in creating report or chart.
- Open the Microsoft Excel Application then go to “File>New” to open the new spreadsheet. Then enter the data into spreadsheet and you need to format it accordingly.
- Move to PivotCharts Report and choose option
- Click on the first option database. Below its first question, click on the first option “Pivot Table”. Below the secondary option, click the Next button.
- Choose the data area, click enter and hit the Next button.
- Hit the first option, generate the table in the new spreadsheet, and click on the Finish button.
- Now, the user is building the Pivot Table through dragging items from the Field of Pivot Table or in the list of pop-up window. The fastest way is to choose “drag and drop”. Thus, you can choose and drop any items to the “drop column Fields”, “Drop Row Fields” and “Drop Data Items”. All these combinations are endless thus; the user can see quickly how various items will relate to one another.
- Click on the “City” button and drag it to the “Drop Columns Field” section.
- Hit the salesperson then drag it to “Drop Row Fields” section.
- Hit the Accounts Opened then drag it to the section of “Drop Data Items”. Now, the following two steps are to create a chart and report through clicking on its available options.
- Move on to the format menu of the Pivot Table then choose format report, through this, you can see all the report options series.
- If you want to see the information in a chart, then you have to go to the option of Pivot Chart.
Hopefully this free Excel help article covered all that you needed, and you now know what to do with Excel PivotTables. If not, please check the other Pivot Table articles listed as they may help you further. We also offer free Excel videos and free Excel workbook downloads on many of the articles, so look at those as well. If you are still stuck, and feel that you might be in the need of paid assistance, Excel consultant programming work for example, feel free to contact Christopher directly at 877-392-3539 for a free consultation, and a free estimate. The corporate office is located in the Irvine area of Orange county California, covering Brea, Costa Mesa, Newport Beach, etc. And we have developer offices in New York, Texas, Florida and Manchester, to name a few. The call is free, the consultation is free, and the results are real.