Cập nhật nội dung chi tiết về How To Create A Pivot Table In Excel: A Step mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác 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 Tables
If 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 2016, “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 Tables
You’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 2018 and has been updated for comprehensiveness.
How To Delete A Pivot Table In Excel (Easy Step
Pivot Table is one of the best features in Excel.
You don’t need to know any formula or coding to quickly crunch thousands of rows of data and create quick summaries out of it. All you need to know is how to drag and drop and a little bit of knowledge of how to create a Pivot Table.
In this tutorial, I will show you various ways to delete a Pivot Table from Excel.
How to Delete a Pivot Table in Excel
Since you’re reading a tutorial about removing or deleting a Pivot Table, I am assuming you already have a Pivot Table(s) in place that you want to remove.
When it comes to deleting a Pivot Table, there are a few different ways you can do this.
The method you choose will depend on how you want to delete the Pivot Table.
Below are some scenarios that I will be covering in this tutorial:
Delete the Pivot Table & the resulting data (the summary created using the Pivot Table)
Delete the Pivot Table but keep the resulting data
Delete the resulting data but keep the Pivot Table
Delete all the Pivot Tables in one go
Let’s dive in and see each of these methods.
Delete the Pivot Table and the Resulting Data
Below is an example where I have created a Pivot table and used it to get the Sum of Revenue for different regions (to which I will be referring to as Pivot Table summary data in this tutorial).
Below are the steps to delete the Pivot table as well as any summary data:
Select any cell in the Pivot Table
Hit the Delete key.
The above steps would delete the Pivot Table.
Note that you can also select the entire Pivot Table by selecting any cell and then using the keyboard shortcut ‘ Control + A ‘. In case you have filters applied in the Pivot table, Control A will not select the entire Pivot Table. You need to use the method shown above (or select the Pivot Table manually)
Another quick way to delete the Pivot Table is to simply delete the worksheet that has the Pivot Table. Of course, you wouldn’t want to do this if you have any other data in the worksheet.
Delete the Pivot Table but Keep the Resulting Data
There may be cases when you want to delete the Pivot table, but not the resulting data that you have got after using the Pivot table. This could be the case when you have used the Pivot Table and then want to send to your manager/client only the resulting data.
Another case where this may be needed is when your Pivot Table is too heavy and is bloating your worksheet. Deleting such a Pivot table can drastically reduce the Excel file size.
For example, in the below example, I want to remove the Pivot Table, but I still want to keep the data in cell A3:B8
Below are the steps to do this:
Select any cell in the Pivot Table
The above steps would delete the Pivot Table but still keep the resulting data.
Some Keyboard Shortcuts you can use:
Select any cell in the Pivot Table and use the Keyboard ‘Control + A’ to select the entire Pivot Table
Once you have selected the entire Pivot table and copied the data, you can use the following keyboard shortcut to paste as values – ALT+E+S+V+Enter (one key after the other)
You can also use the same steps shown above to copy the data from the Pivot Table and pasting it as values at some other location (somewhere in the same worksheet or some other worksheet/workbook). Once you have the data, you can then delete the Pivot Table.
Note: When you copy data (Control C) from a Pivot Table and paste it (Control V) anywhere in the worksheet, it simply creates another Pivot Table. If you want to keep the resulting data only, and not the Pivot Table, make sure you paste as values.
Delete the Resulting Data but Keep the Pivot Table
Suppose you have created a Pivot Table and summarized the data (using filter and columns/rows headers to get the data you want) as shown below.
If you want to only remove the data that you have got while keeping the Pivot Table (so that you can rearrange and create another summary), you can do that as well.
If you select the data and hit the delete key, it will delete the Pivot Table as well.
Below are the steps to keep the Pivot table and remove the resulting data only:
Select any cell in the Pivot Table
Delete All Pivot Tables in One Go
In case you have multiple Pivot tables and you want to delete all of these at one go, you can do that using a simple VBA code.
Caution: Once you delete these Pivot Tables with the VBA code, you’ll not be able to get these back. So be absolutely sure when you do this, and if needed, create a backup copy to avoid losing these.
Below is the VBA code that will delete all Pivot Tables in one go:
Sub DeleteAllPivotTables() Dim Ws As Worksheet, Pt As PivotTable On Error Resume Next For Each Ws In ActiveWorkbook.Worksheets For Each Pt In Ws.PivotTables Ws.Range(Pt.TableRange2.Address).Delete Shift:=xlUp Next Pt Next Ws End SubThe above coded uses the For-Each-Next loop to go through each worksheet in the active workbook. In each worksheet, it checks if there is a Pivot table or not and deletes it (if there is).
Where to Put this VBA code?
This code needs to be placed in the regular module in the VB Editor
Below are the steps to put this code in the module:
Open a workbook from which you want to remove the Pivot Tables.
Use the shortcut ALT + F11 (this opens the VBA Editor window).
Hover the cursor on Insert.
In the module window (that will appear on the right), copy and paste the above code.
Here is an article where I cover different ways to run a VBA macro in Excel.
As soon as you run this code, it will remove all the Pivot tables from all the worksheets in the workbook.
You May Also like the following articles:
How To Create Pivot Chart In Excel?
How to create pivot chart in Excel?
In Excel, you may usually insert a chart to help you analyze the data, but do you know in some cases, a pivot chart is better than the normal chart for analyzing in Excel? Now this tutorial is talking about pivot chart creating in Excel.
Create pivot chart in Excel
Create pivot chart in Excel
To create a pivot chart, you can do as the following steps:
2. In the popping Create PivotTable with PivotChart dialog, choose the location you want to place the new PivotTable and PivotChart in Choose where you want the PivotTable and PivotChart to be placed section. See screenshot:
4. Now drag the fields you want to show in the PivotChart to the areas as you need. Here I drag Saler and Order ID fields to Axis Fields (Categories) section, and drag Amount field to Values section. See screenshots:
Note: When you create a pivot chart, a pivot table is created at the same time.
Tip:
If you want the legend series shown as Saler, you can drag Saler field to the Legend Fields (Series) section. See screenshots:
Notes:
(1) The pivot table will change as the pivot chart changes.
(2) In Excel 2007, we cannot filter data in the Pivot chart, but we can filter data in the Pivot table and the data in the pivot chart will change automatically.
Relative Articles:
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse:
Quickly insert
complex formulas, charts
and anything that you have used before;
Encrypt Cells
with password;
Create Mailing List
and send emails…
Super Formula Bar
(easily edit multiple lines of text and formula);
Reading Layout
(easily read and edit large numbers of cells);
Paste to Filtered Range
…
Merge Cells/Rows/Columns
without losing Data; Split Cells Content;
Combine Duplicate Rows/Columns
… Prevent Duplicate Cells;
Compare Ranges
…
Select Duplicate or Unique
Rows;
Select Blank Rows
(all cells are empty);
Super Find and Fuzzy Find
in Many Workbooks; Random Select…
Exact Copy
Multiple Cells without changing formula reference;
Auto Create References
to Multiple Sheets;
Insert Bullets
, Check Boxes and more…
Extract Text
, Add Text, Remove by Position,
Remove Space
; Create and Print Paging Subtotals;
Convert Between Cells Content and Comments
…
Super Filter
(save and apply filter schemes to other sheets);
Advanced Sort
by month/week/day, frequency and more;
Special Filter
by bold, italic…
Combine Workbooks and WorkSheets
; Merge Tables based on key columns;
Split Data into Multiple Sheets
;
Batch Convert xls, xlsx and PDF
…
More than 300 powerful features
. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Read More… Free Download… Purchase…
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
Enable tabbed editing and reading in Word, Excel, PowerPoint
, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by
Read More… Free Download… Purchase…
How To Refresh Pivot Table In Excel (Manually + Auto
Once you have created a Pivot Table, it doesn’t automatically refresh when you add new data or change the existing data.
Since your Pivot Table is created using the Pivot Cache, when the existing data changes or when you add new rows/columns to the data, the Pivot Cache does not update itself automatically, and hence, the Pivot Table also does not update.
You need to force a refresh every time there are changes. Once you force a refresh, the Pivot Cache gets updated, which is reflected in the Pivot Table.
This tutorial covers a couple of ways to do this.
Refresh Pivot Table
This option is best suited when there are changes in the existing data source and you want to refresh the pivot table to reflect these changes.
Here are the steps to refresh a Pivot Table:
Select Refresh.
This will instantly refresh the Pivot Table.
You can also by selecting any cell in the Pivot Table and using the keyboard shortcut ALT + F5.
Quick Tip: It’s a good practice to convert the data source into an Excel Table, and use this Excel Table to create the Pivot Table. If you do this, you can also use the refresh technique to update the Pivot Table even when new data (rows/columns) are added to the data source (since an Excel Table automatically accounts for new rows/columns that are added).
Update Pivot Table by Changing the Data Source
If you’ve added new rows/columns to the data source, you need to change the data source to make sure new rows/columns are a part of the dataset.
To do this:
Select any cell in the Pivot Table.
In the Change PivotTable Data Source dialog box, update the range to include new data.
Note that if you change the data source into an Excel Table and then use the Excel table to create the Pivot Table, you don’t need to use the change data source option. You can simply refresh the Pivot Table and it’ll account for the new rows/columns.
Autorefresh Pivot Table Using a VBA Macro
To make it more efficient and auto-refresh the Pivot Table whenever there is a change in the data source, you can use a simple one-line VBA macro code.
Here is the VBA code:
Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh End SubDecoding the Code: This is a change event which gets triggered whenever there is a change in the sheet that contains the source data. As soon as there is a change, the code refreshes the Pivot Cache of the Pivot Table with the name PivotTable1.
You need to modify this code to make it work for your workbook:
“Sheet1” – change this part of the code with the name of the sheet that has the Pivot Table.
Where to put this VBA code:
Press Alt + F11. It will open the VB Editor window.
In the VB Editor, there would be Project explorer on the left (that has the names of all the worksheets). If it’s not there, press Control + R to make it visible.
In the code window on the right, copy paste the given code.
Close the VB Editor.
Now when you change anything in the data source, the Pivot Table would automatically get refreshed.
Note: Since there is a macro in the workbook, save this with .xls or .xlsm extension.
You May Also Like the Following Pivot Table Tutorials:
Bạn đang đọc nội dung bài viết How To Create A Pivot Table In Excel: A Step trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết 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. Chúc bạn một ngày tốt lành!