Hàm start date trong sheet
A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data. PivotTables work a little bit differently depending on what platform you are using to run Excel.
By clicking the down arrow on the button, you can select from other possible sources for your PivotTable. In addition to using an existing table or range, there are three other sources you can select from to populate your PivotTable. Note: Depending on your organization's IT settings you might see your organization's name included Get from External Data Source Get from Data Model Use this option if your workbook contains a Data Model, and you want to create a PivotTable from multiple Tables, enhance the PivotTable with custom measures, or are working
with very large datasets. Get from Power BI Use this option if your organization uses Power BI and you want to discover and connect to endorsed cloud datasets you have access to.
If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed. To refresh just one PivotTable you can right-click anywhere in the PivotTable range, then select Refresh. If you have multiple PivotTables, first select any cell in any PivotTable, then on the Ribbon go to PivotTable Analyze > click the arrow under the Refresh button and select Refresh All.
Summarize Values By By default, PivotTable 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 mix data types for value fields. You can change the default calculation by first clicking on the arrow to the right of the field name, then select the Value Field Settings option.
Next, change the calculation in the Summarize Values By section. Note that when you change the calculation method, Excel will automatically append it in the Custom Name section, like "Sum of FieldName", 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 PivotTable field name, it's best not to rename your PivotTable fields until you're done setting up your PivotTable. One trick is to use Find & Replace (Ctrl+H) >Find what > "Sum of", then Replace with > leave blank to replace everything at once instead of manually retyping.
Show Values As Instead of using a calculation to 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 and percentage. Simply drag the item into the Values section twice, then set the Summarize Values By and Show Values As options for each one.
Note: Recommended PivotTables are only available to Microsoft 365 subscribers.
You can change the data sourcefor the PivotTable data as you are creating it.
Get from Power BI Use this option if your organization uses Power BI and you want to discover and connect to endorsed cloud datasets you have access to.
In the PivotTable Fields pane, select the check box for any field you want to add to your PivotTable. By default, non-numeric fields are added to the Rows area, date and time fields are added to the Columns area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, drag it out from the list or uncheck it.
Summarize Values By By default, PivotTable 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 to make sure you don't mix data types for value fields. Change the default calculation by right clicking on any value in the row and selecting the Summarize Values By option.
Show Values As Instead of using a calculation to 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.
Right click on any value in the column you'd like to show the value for. Select Show Values As in the menu. A list of available values will display. Make your selection from the list. To show as a % of Parent Total, hover over that item in the list and select the parent field you want to use as the basis of the calculation.
If you add new data to your PivotTable data source, any PivotTables built on that data source will need to be refreshed. Right-click anywhere in the PivotTable range, then select Refresh.
If you created a PivotTable and decide you no longer want it, select the entire PivotTable range and press Delete. It won't have any effect on other data or PivotTables or charts around it. If your PivotTable is on a separate sheet which has no other data you want to keep, deleting the sheet is a fast way to remove the PivotTable. Your data should be organized in a tabular format, and not have any blank rows or columns. Ideally, you can use an
Excel table like in our example above. Tables are a great PivotTable data source, because rows added to a table are automatically included in the PivotTable when you refresh the data, and any new columns will be included in the PivotTable Fields List. Otherwise, you need to either
Change the source data for a PivotTable, or use a dynamic named range formula. Data types in columns should be the same. For example, you shouldn't mix dates and text in the same column. PivotTables work on a snapshot of your data, called the cache, so your actual data doesn't get altered in any way. If you have limited experience with PivotTables, or are not sure how to get started, a Recommended PivotTable 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 PivotTable. This helps give you a starting point for additional experimentation. After a recommended PivotTable is created, you can explore different orientations and rearrange fields to achieve your specific results. You can also download our interactive Make your first PivotTable tutorial.
PivotTable Fields list In the Field Name area at the top, select the check box for any field you want to add to your PivotTable. By default, non-numeric fields are added to the Row area, date and time fields are added to the Column area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes it so easy to quickly change its appearance. PivotTable Fields list
If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed. To refresh just one PivotTable you can right-click anywhere in the PivotTable range, then select Refresh. If you have multiple PivotTables, first select any cell in any PivotTable, then on the Ribbon go to PivotTable Analyze > click the arrow under the Refresh button and select Refresh All. If you created a PivotTable and decide you no longer want it, you can simply select the entire PivotTable range, then press Delete. It won't have any affect on other data or PivotTables or charts around it. If your PivotTable is on a separate sheet that has no other data you want to keep, deleting that sheet is a fast way to remove the PivotTable. Need more help?You can always ask an expert in the Excel Tech Community or get support in the Answers community. PivotTable Recommendations are a part of the connected experience in Office, and analyzes your data with artificial intelligence services. If you choose to opt out of the connected experience in Office, your data will not be sent to the artificial intelligence service, and you will not be able to use PivotTable Recommendations. Read the Microsoft privacy statement for more details. Related articlesCreate a PivotChart Use slicers to filter PivotTable data Create a PivotTable timeline to filter dates Create a PivotTable with the Data Model to analyze data in multiple tables Create a PivotTable connected to Power BI Datasets Use the Field List to arrange fields in a PivotTable Change the source data for a PivotTable Calculate values in a PivotTable Delete a PivotTable |