The pivot table is one of emcanbaove.edu.vn Excel"s most powerful — & intimidating — functions. Pivot tables can help you summarize & make sense of large data sets. However, they also have a reputation for being complicated.
Bạn đang xem: Cách làm pivot table trong excel 2007
The good news is that learning how khổng lồ create a pivot table in Excel is much easier than you may believe.
We’re going khổng lồ walk you through the process of creating a pivot table và show you just how simple it is. First, though, let"s take a step back and make sure you understand exactly what a pivot table is, & why you might need khổng lồ use one.
What is a pivot table?
What are pivot tables used for?
How to lớn Create a Pivot Table
Pivot Table Examples
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 lớn track the sums of & easily compare khổng lồ one another.
In other words, pivot tables extract meaning from that seemingly endless jumble of numbers on your screen. Và more specifically, it lets you group your data 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 to view it from a different perspective. To lớn 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.
What are pivot tables used for?
If you"re still feeling a bit confused about what pivot tables actually do, don"t worry. This is one of those technologies that are much easier to lớn understand once you"ve seen it in action.
The purpose of pivot tables is to offer user-friendly ways to quickly summarize large amounts of data. They can be used lớn better understand, display, và analyze numerical data in detail.
With this information, you can help identify & answer unanticipated questions surrounding the data.
Here are seven hypothetical scenarios where a pivot table could be helpful.
1. Comparing Sales Totals of Different Products
Let’s say you have a worksheet that contains monthly sales data for three different products — product 1, product 2, & product 3. You want khổng lồ figure out which of the three has been generating the most revenue.
One way would be khổng lồ look through the worksheet and manually showroom the corresponding sales figure lớn a running total every time product 1 appears. The same process can then be done for sản phẩm 2, & product 3 until you have totals for all of them. Piece of cake, right?
Imagine, now, that your monthly sales worksheet has thousands upon thousands of rows. Manually sorting through each necessary piece of data could literally take a lifetime.
With pivot tables, you can automatically aggregate all of the sales figures for hàng hóa 1, hàng hóa 2, and product 3 — & calculate their respective sums — in less than a minute.
2. Showing sản phẩm Sales as Percentages of Total Sales
Pivot tables inherently show the totals of each row or column when created. That"s not the only figure you can automatically produce, however.
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 pivot table automatically gives you three totals at the bottom of each column — having added up each product"s quarterly sales.
But what if you wanted khổng lồ find the percentage these hàng hóa sales contributed khổng lồ all company sales, rather than just those products" sales totals?
With a pivot table, instead of just the column total, you can configure each column to give you the column"s percentage of all three column totals.
Let’s say three products totaled $200,000 in sales. The first sản phẩm made $45,000, you can edit a pivot table to instead say this hàng hóa contributed 22.5% of all company sales.
To show hàng hóa sales as percentages of total sales in a pivot table, simply right-click the cell carrying a sales total and select Show Values As > % of Grand Total.
3. Combining Duplicate Data
In this scenario, you"ve just completed a blog redesign and had to update many URLs. Unfortunately, your blog reporting software didn"t handle the change well & split the "view" metrics for single posts between two different URLs.
In your spreadsheet, you now have two separate instances of each individual blog post. To get accurate data, you need to lớn combine the view totals for each of these duplicates.
Instead of having to manually search for & combine all the metrics from the duplicates, you can summarize your data (via pivot table) by blog post title.
Voilà, the view metrics from those duplicate posts will be aggregated automatically.
4. Getting an Employee Headcount 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.
For instance, let’s say you have a các mục of employees in an Excel sheet. Next to lớn the employees" names are the respective departments they belong to. You can create a pivot table from this data that shows you each department"s name và the number of employees that belong to those departments.
The pivot table’s automated functions effectively eliminate your task of sorting the Excel sheet by department name & counting each row manually.
5. Adding default Values to lớn Empty Cells
Not every dataset you enter into Excel will populate every cell. If you"re waiting for new data to come in, you might have lots of empty cells that look confusing or need further explanation.
That"s where pivot tables come in.
You can easily customize a pivot table to lớn fill empty cells with a mặc định value, such as $0, or thái bình dương (for "to be determined"). For large data tables, being able lớn tag these cells quickly is a valuable feature when many people are reviewing the same sheet.
To automatically format the empty cells of your pivot table, right-click your table & click Pivot
Table Options.
In the window that appears, check the box labeled Empty Cells As and enter what you"d lượt thích displayed when a cell has no other value.
How to Create a Pivot Table
Now that you have a better sense of what pivot tables can be used for, let"s get into the nitty-gritty of how khổng lồ actually create one.
Step 1. Enter your data into a range of rows & 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 phối of rows & 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 "Top Pages."A column listing each URL"s "Clicks."A column listing each post"s "Impressions."We"ll be using that example in the steps that follow.
Step 2. Sort your data by a specific attribute.
Once you’ve entered all your data into your Excel sheet, you’ll want to lớn sort your data by attribute. This will make your information easier khổng lồ manage once it becomes a pivot table.
To sort your data, click the Data tab in the đứng top navigation bar & select the Sort icon underneath it. In the window that appears, you can sort your data by any column you want và in any order.
For example, khổng lồ sort your Excel sheet by "Views to Date," select this column title under Column & then select whether you want khổng lồ order your posts from smallest khổng lồ largest, or from largest lớn smallest.
Select OK on the bottom-right of the Sort window.
Now, you’ve successfully reordered each row of your Excel sheet by the number of views each blog post has received.
Step 3. Highlight your cells khổng lồ create your pivot table.
Once you"ve entered & sorted your data, highlight the cells you"d lượt thích to summarize in a pivot table. Click Insert along the top navigation, and select the Pivot
Table icon.
You can also click anywhere in your worksheet, select "Pivot
Table," and manually enter the range of cells you"d lượt thích included in the Pivot
Table.
This opens an options box. Here you can select whether or not to launch this pivot table in a new worksheet or keep it in the existing worksheet, in addition to lớn setting your cell range.
If you mở cửa a new sheet, you can navigate to & away from it at the bottom of your Excel workbook. Once you"ve chosen, click OK.
Alternatively, you can highlight your cells, select Recommended Pivot
Tables khổng lồ the right of the Pivot
Table icon, and mở cửa a pivot table with pre-set suggestions for how to organize each row và column.
Note: If using an earlier version of Excel, "Pivot
Tables" may be under Tables or Data along the đứng top navigation, rather than "Insert." In Google Sheets, you can create pivot tables from the Data dropdown along the top navigation.
Step 4. Drag và drop a field into the "Row Labels" area.
After you"ve completed Step 3, Excel will create a blank pivot table for you.
Your next step is to lớn drag and drop a field — labeled according khổng lồ the names of the columns in your spreadsheet — into the Row Labels area. This will determine what unique identifier the pivot table will organize your data by.
For example, let"s say you want lớn organize a bunch of blogging data by post title. To vày that, you"d simply click and drag the “Top pages” field khổng lồ 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.
Step 5. Drag and drop a field into the "Values" area.
Once you"ve established how you"re going khổng lồ organize your data, your next step is to địa chỉ in some values by dragging a field into the Values area.
Sticking with the blogging data example, let"s say you want lớn summarize blog post views by title. To bởi this, you"d simply drag the "Views" field into the Values area.
Step 6. Fine-tune your calculations.
The sum of a particular value will be calculated by default, but you can easily change this lớn something lượt thích average, maximum, or minimum depending on what you want khổng lồ calculate.
On a Mac, you can bởi this by clicking on the small i next lớn a value in the "Values" area, selecting the option you want, and clicking "OK." Once you’ve made your selection, your pivot table will be updated accordingly.
If you"re using a PC, you"ll need lớn click on the small upside-down triangle next to lớn your value & select Value Field Settings khổng lồ access the menu.
When you’ve categorized your data to your liking, save your work and use it as you please.
Pivot Table Examples
From managing money to keeping tabs on your marketing effort, pivot tables can help you keep track of important data. The possibilities are endless!
See three pivot table examples below khổng lồ keep you inspired.
1. Creating a PTO Summary and Tracker
If you’re in HR, running a business, or leading a small team, managing employees’ vacations is essential. This pivot allows you to seamlessly track this data.
All you need to vì is import your employee’s identification data along with the following data:
Sick time.Hours of PTO.Company holidays.Overtime hours.Employee’s regular number of hours.From there, you can sort your pivot table by any of these categories.
2. Building a Budget
Whether you’re running a project or just managing your own money, pivot tables are an excellent tool for tracking spend.
The simplest budget just requires the following categories:
Date of transactionWithdrawal/Expenses
Deposit/Income
Description
Any overarching categories (like paid ads or contractor fees)
With this information, you can see your biggest expenses and brainstorm ways khổng lồ save.
3. Tracking Your campaign Performance
Pivot tables can help your team assess the performance of your kinh doanh campaigns.
In this example, chiến dịch performance is split by region. You can easily which country had the highest conversions during different campaigns.
This can help you identify tactics that perform well in each region & where advertisements need lớn be changed.
Digging Deeper With Pivot Tables
You"ve now learned the basics of pivot table creation in Excel. With this understanding, you can figure out what you need from your pivot table & find the solutions you’re looking for.
For example, you may notice that the data in your pivot table isn"t sorted the way you"d like. If this is the case, Excel"s Sort function can help you out. Alternatively, you may need khổng lồ incorporate data from another source into your reporting, in which case the VLOOKUP function could come in handy.
Editor"s note: This post was originally published in December 2018 và has been updated for comprehensiveness.
A Pivot
Table is a powerful tool to calculate, summarize, & analyze data that lets you see comparisons, patterns, and trends in your data. Pivot
Tables work a little bit differently depending on what platform you are using to run Excel.
Note: Your data should be organized in columns with a single header row. See the Data format tips and tricks section for more details.
Select Insert > Pivot
Table.
Choose where you want the Pivot
Table report to lớn be placed. Select New Worksheet to place the Pivot
Table in a new worksheet or Existing Worksheet and select where you want the new Pivot
Table to appear.
Click OK.
By clicking the down arrow on the button, you can select from other possible sources for your Pivot
Table. In addition khổng lồ using an existing table or range, there are three other sources you can select from to populate your Pivot
Table.
Note: Depending on your organization"s IT settings you might see your organization"s name included in the button. For example, "From Power BI (emcanbaove.edu.vn)"
Get from External Data Source
Get from Data Model
Use this option if your workbook contains a Data Model, and you want khổng lồ create a Pivot
Table from multiple Tables, enhance the Pivot
Table with custom measures, or are working with very large datasets. 
Get from power nguồn BI
Use this option if your organization uses nguồn BI và you want to lớn discover và connect khổng lồ endorsed cloud datasets you have access to.
Note: Selected fields are added lớn their mặc định areas: non-numeric fields are added to Rows, date and time hierarchies are added to Columns, and numeric fields are added to Values.
To move a field from one area khổng lồ another, drag the field lớn the target area.
If you add new data khổng lồ your Pivot
Table data source, any Pivot
Tables that were built on that data source need lớn be refreshed. To refresh just one Pivot
Table you can right-click anywhere in the Pivot
Table range, then select Refresh. If you have multiple Pivot
Tables, first select any cell in any Pivot
Table, then on the Ribbon go to Pivot
Table Analyze > click the arrow under the Refresh button và select Refresh All.
Summarize Values By
By default, Pivot
Table fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. This is why it"s so important lớn make sure you don"t phối data types for value fields. You can change the default calculation by first clicking on the arrow to lớn the right of the field name, then select the Value Field Settings option.
Next, change the calculation in the Summarize Values By section. Cảnh báo that when you change the calculation method, Excel will automatically append it in the Custom Name section, like "Sum of Field
Name", but you can change it. If you click the Number Format button, you can change the number format for the entire field.
Tip: Since the changing the calculation in the Summarize Values By section will change the Pivot
Table field name, it"s best not khổng lồ rename your Pivot
Table fields until you"re done setting up your Pivot
Table. One trick is to use Find & Replace (Ctrl+H) >Find what > "Sum of", then Replace with > leave blank to lớn replace everything at once instead of manually retyping.
Show Values As
Instead of using a calculation lớn summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.
Once you"ve opened the Value Field Setting dialog, you can make your selections from the Show Values As tab.
Display a value as both a calculation và percentage.
Simply drag the công trình into the Values section twice, then phối the Summarize Values By and Show Values As options for each one.
Insert a Pivot
Table
Select a table or range of data in your sheet and select Insert > Pivot
Table to open the Insert Pivot
Table pane.
You can either manually create your own Pivot
Table or choose a recommended Pivot
Table khổng lồ be created for you. Vì one of the following:
On the Create your own Pivot
Table card, select either New sheet or Existing sheet to choose the destination of the Pivot
Table.
On a recommended Pivot
Table, select either New sheet or Existing sheet to choose the destination of the Pivot
Table.
Note: Recommended Pivot
Tables are only available to emcanbaove.edu.vn 365 subscribers.
Change the source data
You can change the data sourcefor the Pivot
Table data as you are creating it.
In the Insert Pivot
Table pane, select the text box under Source. While changing the Source, cards in the pane won"t be available.
Make a selection of data on the grid or enter a range in the text box.
Press Enter on your keyboard or the button lớn confirm your selection. The pane will update with new recommended Pivot
Tables based on the new source of data.
Get from power BI
Use this option if your organization uses power BI và you want to lớn discover & connect khổng lồ endorsed cloud datasets you have access to.
Working with the Pivot
Table Fields pane
In the Pivot
Table Fields pane, select the check box for any field you want to địa chỉ cửa hàng to your Pivot
Table.
By default, non-numeric fields are added to lớn the Rows area, date và time fields are added to lớn the Columns area, & numeric fields are added to lớn the Values area.
You can also manually drag-and-drop any available thắng lợi into any of the Pivot
Table fields, or if you no longer want an tòa tháp in your Pivot
Table, drag it out from the list or uncheck it.
Working with Pivot
Table Values
Summarize Values By
By default, Pivot
Table fields in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. This is why it"s so important khổng lồ make sure you don"t mix data types for value fields.Change the mặc định calculation by right clicking on any value in the row & selecting the Summarize Values By option.
Show Values As
Instead of using a calculation khổng lồ summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to lớn display as a % of Grand Total instead of the sum of the values.
Right click on any value in the column you"d like to show the value for. Select Show Values As in the menu. A menu of available values will display.
Make your selection from the list.
To show as a % of Parent Total, hover over that thành tựu in the list and select the parent field you want to use as the basis of the calculation.
Refreshing Pivot
Tables
If you add new data lớn your Pivot
Table data source, any Pivot
Tables built on that data source will need to be refreshed. Right-click anywhere in the Pivot
Table range, then select Refresh.
Delete a Pivot
Table
If you created a Pivot
Table and decide you no longer want it, select the entire Pivot
Table range and press Delete. It won"t have any effect on other data or Pivot
Tables or charts around it. If your Pivot
Table is on a separate sheet which has no other data you want khổng lồ keep, deleting the sheet is a fast way to remove the Pivot
Table.
Before you get started
Data types in columns should be the same. For example, you shouldn"t set dates & text in the same column.
Pivot
Tables work on a snapshot of your data, called the cache, so your actual data doesn"t get altered in any way.
Create by using a Recommended Pivot
Table
If you have limited experience with Pivot
Tables, or are not sure how lớn get started, a Recommended Pivot
Table is a good choice. When you use this feature, Excel determines a meaningful layout by matching the data with the most suitable areas in the Pivot
Table. This helps give you a starting point for additional experimentation. After a recommended Pivot
Table is created, you can explore different orientations và rearrange fields khổng lồ achieve your specific results. You can also download our interactive Make your first Pivot
Table tutorial.
Click a cell in the source data or table range.
Go lớn Insert > Recommended Pivot
Table.
Tables to lớn have Excel create a Pivot
Table for you" loading="lazy">
Excel analyzes your data and presents you with several options, like in this example using the household expense data.
Select the Pivot
Table that looks best to lớn you and press OK. Excel will create a Pivot
Table on a new sheet, & display the Pivot
Table Fields List
Manually create a Pivot
Table
Click a cell in the source data or table range.
Go khổng lồ Insert > Pivot
Table.
Excel will display the Create Pivot
Table dialog with your range or table name selected. In this case, we"re using a table called "tbl_Household
Expenses".
In the Choose where you want the Pivot
Table report to lớn be placed section, select New Worksheet, or Existing Worksheet. For Existing Worksheet, select the cell where you want the Pivot
Table placed.
Click OK, & Excel will create a blank Pivot
Table, and display the Pivot
Table Fields list.
Working with the Pivot
Table Fields list
Pivot
Table Fields list
In the Field Name area at the top, select the kiểm tra box for any field you want to địa chỉ to your Pivot
Table. By default, non-numeric fields are added to lớn the Row area, date & time fields are added khổng lồ the Column area, and numeric fields are added to lớn the Values area. You can also manually drag-and-drop any available cống phẩm into any of the Pivot
Table fields, or if you no longer want an thành quả in your Pivot
Table, simply drag it out of the Fields list or uncheck it. Being able lớn rearrange Field items is one of the Pivot
Table features that makes it so easy khổng lồ quickly change its appearance.
Pivot
Table Fields list
Pivot
Table Values
Summarize by
By default, Pivot
Table fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. This is why it"s so important to make sure you don"t phối data types for value fields. You can change the mặc định calculation by first clicking on the arrow to lớn the right of the field name, then select the Field Settings option.
Next, change the calculation in the Summarize by section. Chú ý that when you change the calculation method, Excel will automatically append it in the Custom Name section, lượt thích "Sum of Field
Name", but you can change it. If you click the Number... button, you can change the number format for the entire field.
Tip: Since the changing the calculation in the Summarize by section will change the Pivot
Table field name, it"s best not to rename your Pivot
Table fields until you"re done setting up your Pivot
Table. One trick is to lớn click Replace (on the Edit menu) >Find what > "Sum of", then Replace with > leave blank to lớn replace everything at once instead of manually retyping.
Show data as
Instead of using a calculation to lớn summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts lớn display as a % of Grand Total instead of the sum of the values.
Once you"ve opened the Field Settings dialog, you can make your selections from the Show data as tab.
Display a value as both a calculation and percentage.
Simply drag the sản phẩm into the Values section twice, right-click the value và select Field Settings, then set the Summarize by & Show data as options for each one.
Refreshing Pivot
Tables
If you địa chỉ new data to your Pivot
Table data source, any Pivot
Tables that were built on that data source need to be refreshed. Khổng lồ refresh just one Pivot
Table you can right-click anywhere in the Pivot
Table range, then select Refresh. If you have multiple Pivot
Tables, first select any cell in any Pivot
Table, then on the Ribbon go to Pivot
Table Analyze > click the arrow under the Refresh button & select Refresh All.
Deleting a Pivot
Table
If you created a Pivot
Table & decide you no longer want it, you can simply select the entire Pivot
Table range, then press Delete. It won"t have any affect on other data or Pivot
Tables or charts around it. If your Pivot
Table is on a separate sheet that has no other data you want to lớn keep, deleting that sheet is a fast way to remove the Pivot
Table.
Data format tips & tricks
Organize your data in columns, not rows.
Make sure all columns have headers, with a single row of unique, non-blank labels for each column. Avoid double rows of headers or merged cells.
Need more help?
You can always ask an expert in the Excel Tech Community or get emcanbaove.edu.vn in the Answers community.
Xem thêm: Cách tính khoảng thời gian trong excel dễ thực hiện, tính toán chênh lệch giữa hai thời điểm
emcanbaove.edu.vn Privacy Policy
Pivot
Table Recommendations are a part of the connected experience in emcanbaove.edu.vn 365, và analyzes your data with artificial intelligence services. If you choose lớn opt out of the connected experience in emcanbaove.edu.vn 365, your data will not be sent to lớn the artificial intelligence service, và you will not be able lớn use Pivot
Table Recommendations. Read the emcanbaove.edu.vn privacy statement for more details.
Related articles
Create a Pivot
Chart
Use slicers to filter Pivot
Table data
Create a Pivot
Table timeline khổng lồ filter dates
Create a Pivot
Table with the Data mã sản phẩm to analyze data in multiple tables
Create a Pivot
Table connected to Power BI Datasets
Use the Field các mục to arrange fields in a Pivot
Table
Change the source data for a Pivot
Table
Calculate values in a Pivot
Table
Delete a Pivot
Table
Need more help?
Expand your skills
EXPLORE TRAINING >
Get new features first
JOIN emcanbaove.edu.vn 365 INSIDERS >
Was this information helpful?
Yes
No
Thank you! Any more feedback? (The more you tell us the more we can help.)Can you help us improve? (The more you tell us the more we can help.)
What affected your experience?
Resolved my issue
Clear instructions
Easy khổng lồ follow
No jargon
Pictures helped
Other
Didn"t match my screen
Incorrect instructions
Too technical
Not enough information
Not enough pictures
Other
Any additional feedback? (Optional)
Submit feedback
Thank you for your feedback!
×
What's new
emcanbaove.edu.vn Store
Education
Business
Developer & IT
Company
English (United States) California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices © emcanbaove.edu.vn 2023