Creating pivot tables: a guide
With pivot tables, you can display large amounts of data clearly and analyze them more easily. In addition, all questions that begin with "How many" or "How much" can be answered. For example: "How much revenue did a company generate in the 3rd quarter of 2011?"
Create pivot table - how it works
Before you can create a pivot table, you have to enter your data. This example is about a sales overview per quarter of a year. To do this, create a table in Excel with the columns year, quarter and sales in CHF million (see graphic).
- From the "Insert" tab, open the "Assistant" by clicking on "PivotTable".
- Select the table you just created in the upper input field. Further down, mark the option "New worksheet" and confirm with a click on "OK".
- On the right side of the new worksheet in the "PivotTable field list" mark all columns: year, quarter and sales.
- In order for the display to make sense, you have to move the "Year" and "Quarter" fields to the "Line labels" area (see graphic).
The pivot table is now ready. You can format them as normal. For example, you can define the sales column as "Currency" and add the currency symbol. You can also filter the data using the arrow in the "Year / Quarter" column. Select "Quarter" as the field and display the sales for the third quarter of each year.
Become an Excel professional with the new course at the CHIP Academy
With the course in the CHIP Academy "Excel: Pivot tables in less than an hour" even beginners learn how to handle a large amount of data quickly and easily.
- Learn in 40 minutes from our lecturer Daniel Kogan what pivot tables are and how to use them sensibly.
- You will learn how to use pivot tables and pivot charts to gain insights and insights from your data that you would otherwise not have been able to access.
- Visit the CHIP Academy and get the extensive online video workshop for 19.90 euros.
The instructions refer to Excel 2010. Tip: Use the conditional formatting to graphically highlight the values.