Microsoft Excel 2013 – PivotTable Basics

1
41



Instructions and Music attributions

Before you start the PivotTable, make sure the data you’re using is in “tabular” form.
Create the PivotTable
From the worksheet that contains your data, click on “Insert”, then click on “PivotTable”.
When the “Create PivotTable” box appears, the field “Table/Range” will be selected by default.
Select all of your data – including headers, and this field will fill with the range of your data.
Next, choose whether you want to see the PivotTable on the worksheet with the data (Existing Worksheet) or on a New Worksheet.
Existing Worksheet: the PivotTable will appear on the same sheet with the data. If you want it to be in a specific cell, you have enter that cell in the “Location” field. Otherwise, Excel will use the default location.
New Worksheet: The PivotTable will automatically create a new tab when you select New Worksheet.
Click on “OK”

The PivotTable Fields window (Part 1)
The “Choose fields to add to report” section.

Select the column/field data that you wish to see displayed in your report.
Click on the field(s) to add to the PivotTable report.

The PivotTable Fields window (Part 2)
The “Filters” area.
This section allows you to choose which data you want to filter when the PivotTable is complete.

This gives you a searchable field and the ability to easily isolate certain data. Click in the “Select Multiple Items box” if you want more than one item’s data shown, or click on “All”.

To change a field’s settings, click on the drop-down box. In this example, I have clicked on the “Employee Name” drop-down in the “Filters” area.
This gives you options you may want to select depending on the type of data you are filtering.
TIP: The “Layout & Print” tab gives you display options for the field.
Whenever you click away from the PivotTable, the “PivotTable Fields” window will go away. Simply click anywhere within the PivotTable area to bring it back on screen.
The PivotTable Fields window (Part 2)
The “Columns” area.
If you want to see a certain field spread out in columns, you drag that field into the “Columns” area.
Click the drop-down menu on “Date” to format your selection. From this “Sub-totals and Filters” drop-down you can also change the format of the Date, if you wish.
Other options from the drop-down are to move the field around or move it to where it will be a filter.

The “Values” area.
The “Summarize Values By” tab is where you select whether you want to see a “sum” of data, a “count” or some other value.
The “Show Value As” tab gives you the option of calculating your value in different ways.
Formatting the Report
Change the look of your PivotTable via the “Design” tab in PivotTable tools.
Change the layout by using the drop-down options. You can eliminate subtotals, grand totals, and other options.
Adding Data to an Existing PivotTable
To add data to an existing PivotTable report:
Add the data to your worksheet. In my example, I have added April data to the report and changed it to a monthly report.
Click anywhere in the PivotTable. This will bring up the “PivotTable Tools” tabs.
Click on the “Analyze” tab.
Click on “Change Data Source”
The “Change PivotTable Data Source” box appears.
Re-select your data. When the “Move PivotTable” box appears, close it by clicking the “x”.
Your PivotTable will update automatically.
Changing Data in an Existing PivotTable
To change data in an existing PivotTable:
Make your change(s)
Click anywhere in your PivotTable.
Right-click your mouse.
When the selection box appears, select “Refresh”.
Alternatively, click the “Refresh” icon in the “PivotTable tools” “Analyze” tab.

Atlantean Twilight by Kevin MacLeod is licensed under a Creative Commons Attribution license (
Source:
Artist:

Daily Beetle by Kevin MacLeod is licensed under a Creative Commons Attribution license (
Source:
Artist:

Nguồn: https://dantrionline.com.vn

Xem thêm bài viết khác: https://dantrionline.com.vn/lam-dep/

1 COMMENT

LEAVE A REPLY

Please enter your comment!
Please enter your name here