Bạn đang xem bài viết How To Create A Pivot Table In Excel: A Step được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
The pivot table is one of Microsoft Excel’s most powerful — and intimidating — functions. Powerful because it can help you summarize and make sense of large data sets. Intimidating because you’re not exactly an Excel expert, and pivot tables have always had a reputation for being complicated.
The good news: Learning how to create a pivot table in Excel is much easier than you might’ve been led to believe.
But before we walk you through process of creating one, let’s take a step back and make sure you understand exactly what a pivot table is, and why you might need to use one.
What Is a Pivot Table?A pivot table is a summary of your data, packaged in a chart that lets you report on and explore trends based on your information. Pivot tables are particularly useful if you have long rows or columns that hold values you need to track the sums of and easily compare to one another.
In other words, pivot tables extract meaning from that seemingly endless jumble of numbers on your screen. And more specifically, it lets you group your data together in different ways so you can draw helpful conclusions more easily.
The “pivot” part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table in order to view it from a different perspective. To be clear, you’re not adding to, subtracting from, or otherwise changing your data when you make a pivot. Instead, you’re simply reorganizing the data so you can reveal useful information from it.
How to Use Pivot TablesIf you’re still feeling a bit confused about what pivot tables actually do, don’t worry. This is one of those technologies that’s much easier to understand once you’ve seen it in action. Here are seven hypothetical scenarios where you’d want to use a pivot table.
1. Compare sales totals of different products.Say you have a worksheet that contains monthly sales data for three different products — product 1, product 2, and product 3 — and you want to figure out which of the three has been bringing in the most bucks. You could, of course, look through the worksheet and manually add the corresponding sales figure to a running total every time product 1 appears. You could then do the same for product 2, and product 3, until you have totals for all of them. Piece of cake, right?
Now, imagine that monthly sales worksheet of yours has thousands and thousands of rows. Manually sorting through them all could take a lifetime. Using a pivot table, you can automatically aggregate all of the sales figures for product 1, product 2, and product 3 — and calculate their respective sums — in less than a minute.
2. Show product sales as percentages of total sales.Pivot tables naturally show the totals of each row or column when you create it. But that’s not the only figure you can automatically produce.
Let’s say you entered quarterly sales numbers for three separate products into an Excel sheet and turned this data into a pivot table. The table would automatically give you three totals at the bottom of each column — having added up each product’s quarterly sales. But what if you wanted to find the percentage these product sales contributed of all company sales, rather than just those products’ sales totals?
With a pivot table, you can configure each column to give you the column’s percentage of all three column totals, instead of just the column total. If three product sales totaled $200,000 in sales, for example, and the first product made $45,000, you can edit a pivot table to instead say this product contributed 22.5% of all company sales.
3. Combine duplicate data.That’s where the pivot table comes into play. Instead of having to manually search for and combine all the metrics from the duplicates, you can summarize your data (via pivot table) by blog post title, and voilà: the view metrics from those duplicate posts will be aggregated automatically.
4. Get an employee head count for separate departments.Pivot tables are helpful for automatically calculating things that you can’t easily find in a basic Excel table. One of those things is counting rows that all have something in common.
If you have a list of employees in an Excel sheet, for instance, and next to the employees’ names are the respective departments they belong to, you can create a pivot table from this data that shows you each department name and the number of employees that belong to those departments. The pivot table effectively eliminates your task of sorting the Excel sheet by department name and counting each row manually.
5. Add default values to empty cells.Not every dataset you enter into Excel will populate every cell. If you’re waiting for new data to come in before entering it into Excel, you might have lots of empty cells that look confusing or need further explaining when showing this data to your manager. That’s where pivot tables come in.
You can easily customize a pivot table to fill empty cells with a default value, such as $0, or TBD (for “to be determined”). For large tables of data, being able to tag these cells quickly is a useful feature when many people are reviewing the same sheet.
How to Create a Pivot Table
Enter your data into a range of rows and columns.
Sort your data by a specific attribute.
Highlight your cells to create your pivot table.
Drag and drop a field into the “Row Labels” area.
Drag and drop a field into the “Values” area.
Fine-tune your calculations.
Now that you have a better sense of what pivot tables can be used for, let’s get into the nitty-gritty of how to actually create one.
1. Enter your data into a range of rows and columns.Every pivot table in Excel starts with a basic Excel table, where all your data is housed. To create this table, simply enter your values into a specific set of rows and columns. Use the topmost row or the topmost column to categorize your values by what they represent.
For example, to create an Excel table of blog post performance data, you might have a column listing each “URL,” a column listing each URL’s “Post Title,” a column listing each post’s “Views to Date,” and so on. (We’ll be using that example in the steps that follow.)
2. Sort your data by a specific attribute.When you have all the data you want entered into your Excel sheet, you’ll want to sort this data in some way so it’s easier to manage once you turn it into a pivot table.
Select “OK” on the bottom-right of the Sort window, and you’ll successfully reorder each row of your Excel sheet by the number of views each blog post has received.
3. Highlight your cells to create your pivot table.Alternatively, you can highlight your cells, select “Recommended PivotTables” to the right of the PivotTable icon, and open a pivot table with pre-set suggestions for how to organize each row and column.
Note: If you’re using a version of Excel earlier than Excel 2023, “PivotTables” may be under “Tables” or “Data” along the top navigation, rather than “Insert.” In Google Sheets, you can create pivot tables from the “Data” dropdown along the top navigation.
4. Drag and drop a field into the “Row Labels” area.Note: Your pivot table may look different depending on which version of Excel you’re working with. However, the general principles remain the same.
5. Drag and drop a field into the “Values” area.Once you’ve established what you’re going to organize your data by, your next step is to add in some values by dragging a field into the “Values” area.
Sticking with the blogging data example, let’s say you want to summarize blog post views by title. To do this, you’d simply drag the “Views” field into the Values area.
6. Fine-tune your calculations.The sum of a particular value will be calculated by default, but you can easily change this to something like average, maximum, or minimum depending on what you want to calculate.
Digging Deeper With Pivot TablesYou’ve now learned the basics of pivot table creation in Excel. But depending on what you need your pivot table for, you might not be done.
For example, you may notice that the data in your pivot table isn’t sorted the way you’d like. If were the case, Excel’s Sort function can help you out. Alternatively, you may need to incorporate data from another source into your reporting, in which case the VLOOKUP function could come in handy.
To take a deeper dive into the world of Excel and learn about its various functions, download our comprehensive guide, How to Use Excel.
Want more Excel tips? Check out these design tips for creating charts and graphs.
Editor’s note: This post was originally published in December 2023 and has been updated for comprehensiveness.
How To Create Pivot Tables In Excel 2023
Initially, it worked fine from evaluating simple expenses for analyzing complex data. Today, the latest version – the Microsoft Excel 2023 is an excellent option but also potent tool for data analysis for business insights and watching trends.
For making better rational decisions, you not only need to process data quickly but also effectively. But the rapid accumulation of data can become overwhelming and can leave you gasping. In no time, you may feel lost, and you have a burden of extensive data to compile.
The magic lies in this Excel tool with one of its features like Pivot Table coming to play. It helps you exploit and play with the data stored in the cells. Pivot tables will help you utilize its prowess of data analysis, exploration and summarization to present it in a manner which is easy to comprehend.
What is a Pivot Table?The pivot tables are flexible and can be modified or presented at your choice. You can customize and adjust the way you want, and for as many cells you want. It is ideal for calculating, evaluating and displaying information in tables and breakdowns to the scale you need.
Pivot charts can also be created based on pivot tables. These charts will automatically be updated when your pivot tables get updated. Many times it can unravel the hidden facts buried under your data.
The images shown below is a pivot table and a pivot chart –
How to create a Pivot Table?Prerequisites
The data in the sheet should be in a tabular format, and no blank row or blank column should be left.
Data Types of the data in the columns should be the same. You should not mix numbers, date, currency, and text in a single column
If you alter data in your Pivot Table, your actual data will never be changed as Pivot Table works as a snapshot of the original data.
Steps:
Creating Pivot table is very easy, you need to follow the below steps –
Now the PivotTable options will be opened. You can select which PivotTable Fields you want to keep in your PivotTable. The fields that have numeric value can be dragged to the Values column to get the average or totals.
Like in our example we want to see which salesman has how much order amount each month. So, we select our fields as shown –
This gives a PivotTable like this –
The selected and checked fields add to the Row, and if you want to see a specific field in the column as we did, you need to drag it to the Column area below.
You can sort the data as a regular Excel table in the Pivot Table
The columns with the data for each row will be updated and refreshed according to the Rows.
You can select or deselect some rows from the table as per your requirement.
Similarly, you can omit some columns as per your choice from the table and see the data without those columns –
We have unchecked January from the table, and we can see data for only February and March in the table –
The PivotTable Fields can also add a filter to the table. In the example, we have added the Region field in the FILTERS.
Filter for Region has appeared here-
So, the data has been filtered for only East and North regions –
You can manipulate the fields on the chart, adding filters
In our example, we have unchecked the North, South and West regions. The graph is now showing us data according to East region only.
You can provide the Table name that you want to add as a new data source –
What is a Recommended Pivot Table?Recommended pivot table option enables you with an automatic pivot table. This option provides a template for creating Pivot Table. You can select any one of the types, change source data or create a blank pivot table in the Recommended Pivot Table Dialogue Box.
Recommended Pivot Table is an excellent functionality for those who have insufficient knowledge about Pivot Tables. After you created the recommended Pivot Table, you can then manipulate the filters like the Pivot Table you create manually. You also can change different orientations.
How to remove a Pivot Table?If you do not need the Pivot Table anymore, you can select the entire table and press Delete. Make sure the whole table is selected; otherwise you’ll get an error message – “Cannot change this part of a table Report.
Final Words:Create your tables in a manner of how you want your data to be displayed. Utilize the PivotTables and make your process simple. Invest your time and practice using the pivot tables and its uses and various data types.
How To Delete A Pivot Table In Excel
Delete a Pivot Table in a Microsoft Excel Workbook
Applies to: Microsoft ® Excel ® 2010, 2013, 2023, 2023 and 365 (Windows)
A pivot table can be deleted in an Excel workbook in several ways. You can delete a pivot table, convert a pivot table to values or clear data and customizations from a pivot table to reset it. When a pivot table is created from source data in a workbook, Excel creates a pivot cache in the background. If you delete a pivot table or a source worksheet with the original data, Excel still retains the cache.
Recommended article: 10 Great Excel Pivot Table Shortcuts
Deleting a pivot tableTo delete a pivot table:
Select a cell in the pivot table.
Press Delete.
Below is the Select All command in the Ribbon:
You can also delete a pivot table by deleting the worksheet on which it appears (assuming there is no other data on the sheet) or by deleting all of the rows on which the pivot table appears.
Deleting a pivot table and converting it to valuesYou can delete a pivot table and convert it to values. This can be useful if you want to share the pivot table summary information with clients or colleagues.
To delete a pivot table and convert it to values:
Select a cell in the pivot table.
Below is the Paste drop-down menu in Excel:
Deleting pivot table filters, labels, values and formattingYou also have the option of resetting a pivot table by deleting pivot table filters, labels, values and formatting but retaining the pivot table.
To delete pivot table data:
Select a cell in the pivot table.
Add or remove fields in the Pivot Table Fields task pane.
Below is the Clear All command in the Ribbon in Excel:
If pivot tables are sharing a data connection or if you are using the same data between two or more pivot tables, then if you select Clear All for one pivot table, you could also remove the grouping, calculated fields or items and custom items in shared pivot tables. A dialog box should appear if Excel is going to remove items in shared pivot tables and you can cancel the operation.
Did you find this article helpful? If you would like to receive new articles, join our email list.
More resourcesHow to Remove Blanks in a Pivot Table in Excel (6 Ways) How to Change Commas to Decimal Points in Excel and Vice Versa (5 Ways) How to Convert Seconds to Minutes and Seconds in Excel Worksheets
Related coursesHow To Use The Excel Pivot Table Field List
Tips for working with the PivotTable Field List. Move it, change its layout, sort the fields, to save time and work efficiently. Also see: List all Pivot Fields with Details
Pivot Table Field ListWhen you create a pivot table, and select a cell in it, by default, a pivot table field list should appear, at the right of the Excel window. You can use the field list to select fields for the pivot table layout, and to move fields to a specific area in the layout.
To see the PivotTable Field List:
The PivotTable Field List pane should appear at the right of the Excel window, when a pivot cell is selected.
Change the Pivot Table Field ListTo see the steps for adjusting the pivot table field list, please watch this short video tutorial. The written instructions are below the video. Download the sample file from this video, to follow along.
Change the Pivot Table Field List LayoutBy default, the field list shows a list of the fields at the top, and the four pivot table areas in a square at the bottom. You can change that layout, by using a command on the field list.
To change the layout:
The default layout is at the top of the list — Fields Section and Areas Section Stacked
Select one of the other layouts, such as Fields Section and Areas Section Side-by-Side
Which layout to select:
The Fields Section and Areas Section Stacked layout is best if the pivot table has long field names, or a short list of fields
The Fields Section and Areas Section Side-by-Side layout is better when field names are short, or there is a long list of fields.
I don’t use the other layouts — Fields Section Only, Areas Section Only (2 by 2), Area Section Only (1 by 4)
Adjust the Field List WidthTo adjust the width of the field list:
Point to the border between the field list and the worksheet
When the pointer changes to a two-headed arrow, drag left or right, to make the field list wider or narrower
Move the Field ListThe field list can be locked in place at the right or left of the worksheet, or it can float over the worksheet.
To move the field list:
Point to the Title area of the field list
When the pointer changes to a four-headed arrow, drag the field list to a new position
To lock the floating field list into its previous position:
Point to the Title area of the field list
It will go to the side where it was last locked.
Sort the Field ListThe fields in the field list can be sorted alphabetically, or in the same order that the fields are arranged in the source data.
To sort the fields in Excel 2013:To sort the fields in Excel 2010:
Download Sample File
Download the sample file that was used in the Pivot Table Field List video. The zipped file is in xlsx format, and does not contain any macros.
More Pivot Table ResourcesFAQs – Pivot Tables Pivot Table Introduction List all Pivot Fields with Details Remove Pivot Fields with Macros
Grouping Data
Summary Functions
Clear Old Items in Pivot Table
Cập nhật thông tin chi tiết về How To Create A Pivot Table In Excel: A Step trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!