How to Create a Pivot Table in Excel 2013
What Is a Pivot Table in MS Excel 2013?
A Pivot Table is a powerful feature in Microsoft Excel 2013 that helps users summarize, organize, analyze, and present large amounts of data in an easy-to-understand format.
When we need to calculate totals, count records, compare categories, or summarize a large data list, a Pivot Table can perform these tasks in a few simple steps without changing the original data.
For example, if a company has hundreds of sales records, a Pivot Table can summarize total sales by product, employee, city, or month.
Steps to Create a Pivot Table in Excel 2013
Follow these steps to create a Pivot Table in Microsoft Excel 2013:
Open Microsoft Excel 2013 and open the worksheet containing your data.
Make sure your data has column headings, such as Date, Product, City, and Sales.
Select any cell inside your data list, or select the complete data range.
Click the Insert tab on the Ribbon.
Click PivotTable in the Tables group.
The Create PivotTable dialog box will appear.
Check the selected table or range.
Choose where you want to place the Pivot Table:
New Worksheet: Creates the Pivot Table on a new worksheet.
Existing Worksheet: Places the Pivot Table in a location you specify.
Click OK.
Excel will create an empty PivotTable layout and display the PivotTable Field List.
Drag the required fields into the Filters, Columns, Rows, and Values areas.
Excel will summarize the selected data according to the fields you choose.
Your Pivot Table is now ready.
Example of a Pivot Table in Excel 2013
Suppose we have the following sales data:
| Salesperson | Product | City | Sales |
|---|---|---|---|
| Amit | Laptop | Jaipur | 30000 |
| Neha | Mobile | Delhi | 15000 |
| Amit | Mobile | Jaipur | 10000 |
| Ravi | Laptop | Delhi | 25000 |
| Neha | Laptop | Jaipur | 20000 |
| Ravi | Mobile | Delhi | 12000 |
We want to calculate the total sales for each salesperson.
How to Summarize Sales by Salesperson
Select any cell in the sales data.
Click Insert → PivotTable.
Choose New Worksheet and click OK.
In the PivotTable Field List, drag Salesperson into the Rows area.
Drag Sales into the Values area.
Excel will display a summary similar to this:
| Salesperson | Sum of Sales |
|---|---|
| Amit | 40000 |
| Neha | 35000 |
| Ravi | 37000 |
| Grand Total | 112000 |
The Pivot Table automatically groups records by salesperson and adds their sales amounts together.
Understanding the PivotTable Field Areas
When you create a Pivot Table, Excel displays four main field areas.
1. Filters
The Filters area allows you to filter the entire Pivot Table according to a selected field.
Example: Drag City into Filters to display sales for a particular city.
2. Columns
The Columns area displays the selected field across the top of the Pivot Table.
Example: Drag Product into Columns to compare Laptop and Mobile sales side by side.
3. Rows
The Rows area displays categories vertically in the Pivot Table.
Example: Drag Salesperson into Rows to display each salesperson on a separate row.
4. Values
The Values area performs calculations on numerical or other suitable fields.
Example: Drag Sales into Values to calculate the total sales amount. Depending on the data and field type, Excel may use Sum or Count by default.
How to Change the Calculation in a Pivot Table
Excel can calculate the sum, count, average, maximum, minimum, and other summaries for suitable data.
Follow these steps:
Right-click any value inside the Pivot Table.
Select Summarize Values By.
Choose the required calculation, such as Sum, Count, or Average.
Alternatively, open Value Field Settings from the field's drop-down menu in the Values area to select a summary calculation.
For example, you can change Sum of Sales to Average of Sales to calculate the average sales amount for each salesperson.
How to Filter Data in a Pivot Table
You can filter a Pivot Table to display only the records you need.
Add the required fields to the Pivot Table.
Use the drop-down arrow beside a row or column heading to select specific items.
Alternatively, drag a field into the Filters area.
Select the item you want to display.
Example: You can filter the sales report to show only Jaipur sales or only Laptop sales.
How to Refresh a Pivot Table
If the source data changes, the Pivot Table may not update automatically. You can refresh it to recalculate the summary.
Click anywhere inside the Pivot Table.
Click the PivotTable Tools – Analyze tab. In some Excel 2013 layouts, the tab may be labeled Options.
Click Refresh.
You can also right-click inside the Pivot Table and select Refresh.
Important: If you add new records outside the original source range, you may need to change the Pivot Table's data source or use an appropriately sized Excel Table as the source before refreshing.
Advantages of Pivot Tables in Excel 2013
The main advantages of Pivot Tables are:
Quick summaries: Summarize large amounts of data quickly.
Easy comparison: Compare sales, expenses, quantities, and other values across categories.
Flexible analysis: Rearrange fields to view data from different perspectives.
Built-in calculations: Calculate sums, counts, averages, minimums, and maximums.
Filtering: Display only the information required for a report.
Time-saving: Reduce the need to create many manual formulas for summaries.
Conclusion
A Pivot Table in MS Excel 2013 is a useful tool for summarizing and analyzing large datasets. You can create one by selecting your data, clicking Insert → PivotTable, choosing the destination, and arranging fields in the Rows, Columns, Values, and Filters areas.
By using Pivot Tables, you can quickly summarize information, compare categories, filter records, and create meaningful reports without modifying the original data.
0 टिप्पणियाँ
सबसे पहले अपने विचार लिखें।