How to Use pivot tables to view data in Excel (Graphic tutorial)

How to Use pivot tables to view data in Excel (Graphic tutorial)
Excel 2003 worksheet is an ideal tool for organizing and analyzing financial data. For example, you may create a worksheet containing travel and accommodation expenses. If you need a Summary of the categories for each quarter, you know how to modify the worksheet accordingly.

However, what happens when you need to view the total timing of the expense category on a quarterly basis? What should you do if you only want to view a category or year? Can I implement this in an Excel worksheet? Of course. Is there any easier way? Yes.

· An easier method

Microsoft elastic tTable®Report is the answer. Without writing complex formulas, you can use the Wizard to create an interactive table to automatically extract, organize, and summarize your data. Then, you can use the report to analyze the data, compare, detect the style and relationship, and analyze the trend.

For example, you can use a workbook containing travel expenses...

Excel worksheet Source

... Create a pivot table that summarizes the total travel expenses of employees by category and quarter.

Billing by category, year, and quarter

Now, you don't have to change the original worksheet. You can create another PivotTable to view data in other ways. For example, you can view travel expenses by employee or by month without entering a formula.

The following describes how to quickly create a pivot table.

· Create data

First, create data in an Excel worksheet. You can import data from existing workbooks or databases to a new Excel worksheet. Make sure that the data can be used in the PivotTable:

Delete all empty rows or columns. Delete all auto subtotal. Make sure that the first row contains the descriptive titles of each column. Make sure that each column contains only one type of data-for example, one column contains text and the other column contains numeric values.

· Start the "pivot table and data perspective wizard"

In an Excel worksheet, select a cell from the table for which you want to create a pivot table. On the data menu, click pivot and pivot to start the pivot and pivot wizard ".

What should I do if I cannot select a pivot table or a data perspective?

If the workbook is shared (shared workbook: Allows multiple users on the network to view and modify the workbook at the same time. Each user who saves the workbook can see the revisions made by other users .), The "PivotTable and pivot" command is unavailable.

In the PivotTable and pivot wizard-Step 1 in step 3, specify the data source type to be analyzed:, click Microsoft Office Excel list or database ". Under "type of report to be created:", click "PivotTable ". Click Finish ".

Excel inserts a new worksheet in your workbook, which contains the layout area of the report (that is, the "PivotTable" toolbar) and "PivotTable Field List ".

Start building your pivot table

Drag the Category field from the PivotTable field list to the drag row field here area of the PivotTable.

Do not worry that fields will be misplaced. You can move them at will later.

Note that the Wizard will use the data in the "category" column in your worksheet to automatically fill in rows. In addition, the fields used in the "PivotTable Field List" are displayed in bold.

Pivot table with "category" in the row

What if the "pivot table Field List" is not displayed?

If you cannot see the "pivot table field list", click in the layout area with a profile. If you still cannot see it, click the "display field list" button on the "PivotTable" toolbar.

Where is the "PivotTable" toolbar located?

This toolbar should be displayed on the worksheet or on one side. If this toolbar is not displayed, click View, point to toolbar, and click PivotTable ".

· Add data to a report

Drag the field that contains the data to be summarized, for example, the "amount" field, from the "PivotTable Field List" to the "please drag data items here" Area of the PivotTable.

The Pivot table shows the total cost for each category.

Pivot table containing Category and total cost

· Add a page sorting field to the report

To filter data by category, you can create a drop-down arrow at the top of the page.

Drag the "class" field from the "data table Field List" to the "drag page field here" area. Click the drop-down arrow of "class" and select a class.

Now, you can see the charge category of a class at a certain time.

Results filtered by class

What else can this report be used?

Sort expense categories by amount

You can use the "auto sort" function to first view the maximum fee category.

Click the "category" field, click "PivotTable" on the "PivotTable" toolbar, and then click "sort and list the first 10 items ". Under "Automatic Sorting options", click "descending order ". In the "use field" box, click "sum of amounts", and then click "OK ".

Fee categories sorted by "amount"

View fee categories for each quarter

View the cost of each category by quarter:

Drag the date field to the line area. Right-click the "date" field, point to "group and show detailed data", and then click "group ". In the "by" box, click "quarter ". If the source worksheet contains data of multiple years, click year ". If necessary, clear the month option and click OK ".

Fees for each year, quarter, and category

View changes in each expense category in each quarter

You can change the rows and columns in a pivot table within a short period of time. Drag the Category field to the left of the year field.

Billing by category, year, and quarter

Filter results by employees

To filter data by employee, you can create a second drop-down arrow at the top of the page.

Drag the "employee" field to the "page field" area above or below the "class" field. Click the "employees" drop-down arrow, select an employee, and click "OK ".

Results filtered by "employees"

Display the results of each employee on different worksheets

In addition to viewing the results of an employee, you can also display the results of each employee in different worksheets.

On the "PivotTable" toolbar, click "PivotTable", and then click "pagination ". In the displayed by PAGE dialog box, click "employees" and then click "OK ".

Multiple pages are displayed as worksheet labels.

Employee results on a separate Worksheet

Refresh the PivotTable when the source data changes

When data in the source workbook changes, you can immediately update your PivotTable.

Click "refresh data" on the "PivotTable" toolbar.

Instantly make the PivotTable look amazing

Use automatic format to quickly obtain professional appearance.

On the "PivotTable" toolbar, click "Set Report Format.

Click a report or table style, and then click OK ".

· What else can a PivotTable do for me?

Basic PivotTable settings are fast and convenient. However, if you want to spend more time, you can also learn more ways to use pivot tables to process and analyze data. For example, you can import data from many different types of sources or connect to data from many different types of sources, customize the report format, and display results in a meaningful and easy-to-understand manner.