Pivot tables are interactive tables of data, which allow large amounts of information to be grouped and matched. This data becomes concise and easier to analyze, especially in the context of reporting. Excel makes it easy to create pivot tables. To do this, simply drag the right information into the right boxes. You also have the option of filtering and sorting the data to bring out patterns and trends.
Steps
Part 1 of 3: Create a PivotTable
Step 1. Open the Excel spreadsheet that will be the subject of the pivot table
Thanks to the pivot table, you can create illustrated reports, based on the information contained in your sheet. Thus, you will be able to make calculations without having to enter formulas or copy the contents of the very many cells. To create a PivotTable, you will need to create a worksheet that has multiple entries.
You can also create a PivotTable in Excel, but using another data source, such as Access. Moreover, you can increment the PivotTable in a new Excel sheet
Step 2. Make sure the data can be used to compose a PivotTable
Besides, the dynamic board may not be the solution for your needs. In order to use all the possibilities offered by the PivotTable, your Excel spreadsheet must contain the following information.
- Your worksheet must contain at least one two-valued column. More clearly, this means that at least one column must have repeating information. In the example that illustrates the following section, the “Product Type” column has two statements: “Table” or “Chair”.
- The Excel sheet in question must also contain figures. These are the same numbers that will be compared and calculated. In the example that illustrates the following section, the “Sales” column contains quantified data.
Step 3. Start the PivotTable Creation Wizard
If you are on Excel 2010, click on the option Insertion, located next to Menu. Select option TblCrossDynamic.
- If you are using Excel 2003 or an earlier version, click Data, then the sub-menu PivotTable Report.
Step 4. Select the data you are going to implement
Excel selects by default all the data entered on your worksheet. However, you can select the desired range or enter its coordinates manually.
- If you opt for a source of information outside of Excel, click on the option Import external data. Choose connection…. Locate the connection database that you saved on your computer.
Step 5. Choose the location for your pivot table
Once you have selected the data range, choose the location, from the same window. By choosing the option Existing spreadsheet, Excel will save your table in a new sheet. This will allow you to navigate between the different sheets of a single workbook, without having to change windows. The option New spreadsheet will allow Excel to save the table to the sheet that contains the data source, which will allow you to place the cells where you want them.
Once everything looks consistent, click OK. The pivot table is now active, modifying the interface of the worksheet at the same time
Part 2 of 3: Configure the PivotTable
Step 1. Add the lines
Creating a crosstab is to separate information using columns and rows. The elements you add will determine the structure of your table. To insert the information, drag one of the fields that are in the section of the fields, located to the right of your pivot table.
- For example, if your business sells two types of products, namely tables and chairs. Your Excel sheet will contain information in figures (Number of sales), the quantity of each product (Type of product), sold in your five stores (Stores). The purpose of the operation is to determine the number of products sold in each store.
- Drag the field Stores from the list to the right of your table, to the box Lines. Thus, you will see the list of your stores appear and each store will occupy a separate row.
Step 2. Add columns
Like rows, columns are used to sort and display data in order. In the example given above, the Stores field has been added to the rows section. To see the result of the sales, simply drag the field Type of products in the section Column.
Step 3. Fill in the Values box
Now that you've organized your crosstab, it's time to add the information to display. Select and drag the field Number of sales, up to the zone Values of your pivot table. Thus, you will be able to consult the information inherent to the sales for each store, but also their total, all the stores combined.
You can perform all the above steps by dragging the fields into the corresponding boxes, thus avoiding dragging them to the table. These boxes are located to the right of the table below the list of fields
Step 4. Add multiple fields in a single area
You can add multiple fields per area, giving you more control over the arrangement of information in your PivotTable. Using the example above means that you can take more than one type of table and chair. Your spreadsheet records the Product Type (table or chair), but also the Model (of the table or chair sold).
- Drag the field Model in the section Columns. Thus, the columns will show the distribution of sales between models and product types. Obviously, you can change the order in which the fields are placed, using the arrow button. The latter is located on each field button. By choosing up Where down, you will change the order of placement of the fields.
Step 5. Change the layout of the information
You can modify the display of values by clicking on the arrow located in the field located in the area. Values. To do this, click on one of the arrows in a field and select Property. You can change the layout of the fields by clicking on the arrow icon, located next to each value, in the fields area. Data. Select Parameter of the Values fields, to modify the calculation mode of the values. For example, you can have values expressed as a percentage or as an average, instead of a standard total.
- You can add a single field multiple times in the values. In the example above, the sales total shown is for each store. By adding a field Total sales additionally, you can modify the properties of this value, so that it is expressed as the total sales as a percentage.
Step 6. Learn to handle values
Several functions allow you to modify the way the values are calculated, thus adapting to your needs.
- Sum: this is the default function for the control Values. Excel will total the values of all selected fields.
- Number (NB): this function counts the number of cells that contain data, in a set of selected cells.
- Mean: this function will take the average of the values of a selected field.
Step 7. Add a filter
The area Filters contains fields that allow you to browse the summary data in the PivotTable. Thus, the data in these fields is filtered. For example, by putting the field Store in the zone Filters, instead of the zone Lines, you will be able to see the total of each store, individually or even, do this operation for several stores at the same time.
Part 3 of 3: Use a PivotTable
Step 1. Sort and filter the results
Among the key features of the pivot table, you have the ability to sort results and view dynamic reports. Each field can be sorted and filtered by clicking on the down arrow. This arrow can be found on the field buttons. You can sort the items in the list and filter them, so that only the information you want is displayed.
Step 2. Update your spreadsheet
The PivotTable automatically updates when you edit the base worksheet. This can be very useful for tracking changes made in spreadsheets.
Step 3. Edit your pivot table
PivotTable is a format that makes it easy to change the location and order of fields. Thus, you can place the different fields more precisely, according to your needs.
The pivot table was named so for this reason! We say that data is "rotated" when it is moved and that this influences the display of information
Step 4. Also create a PivotChart
Likewise, you have the option of creating dynamic reports while viewing them. The PivotChart can be created directly from a PivotTable. The operation is done in the blink of an eye!
Tips
- By going to the menu Data, then the sub-menu Import data, you will find different import options. It ranges from Office Database, Excel Files, Access Databases, Text Files, DSNs in ODBC, Web Pages, OLAP, and XML / XSL. You can then work with your data just as you would with an Excel list.
- If you normally use Excel's "Automatic filter" (located in "Data", "Filters"), consider disabling it while you create your pivot table. You can do the reverse manipulation once the table is complete.