Create a pivot table
A pivot table is a tool that allows you to present data from an ordinary flat table in a form that is easy to analyze.
Pivot tables allow you:
•To quickly place data from the source table columns into the pivot table columns and rows and swap them around.
•To perform calculations.
•To filter data.
When creating a pivot table, you can use data from the same document or data from an external file as the source.
When preparing the source data, it is recommended to take into account the following requirements:
•The table columns / top cells of the range with the source data must have headers.
•It is recommended to use data in one format within one column of the table.
You can create a pivot table on a new sheet or on the sheet you are working on.
Create a pivot table based on the data in the current file
To create a pivot table using the data from the current file, follow these steps: 1.Select one of the following elements required to create a pivot table: •A range of data in the source table which will be used as the basis of the future pivot table. The range of data should be selected with the column headings. •A single cell within the data range, or an empty cell adjacent to the range if you want to use the entire range. The range cannot consist of one line. The first row of the range must not contain empty or merged cells.
•The cell on the current sheet into which you want to insert the pivot table. This cell will become the upper left cell of the table. The selected cell should not contain any data. It is highly recommended to keep at least two empty rows above the pivot table. These lines are required for filters.
2.Open the Create Pivot Table dialog box in one of the following ways: •In the Insert menu, select Pivot Table > Use This File Data. •On the Toolbar, in the Insert section, click •On the Toolbar, in the Insert section, click 3.If you selected a cell to insert a pivot table in the first step, in the Data Source box of the Create Pivot Table window, specify the range of data from the source table from which you want to create the pivot table. 4.If you selected a data range, a single cell within a range, or an adjacent blank cell in the first step, specify where to insert the table: •New sheet: Insert the table in a new sheet. The new sheet will be created automatically. By default, the new sheet will be named Pivot table <No.>, where No. is the number of the pivot table in the document you are working on. If needed, you can edit the sheet name. •Existing sheet: Insert the table on the sheet that is currently displayed on the screen. In the Specify a destination cell box, enter the cell address manually or click to select an empty cell in the workspace where you want the upper-left cell of the pivot table to be located. There should be at least two empty rows above the initial cells for filters. 5.Click OK. |
Create a pivot table based on data from an external source
To create a pivot table using data from an external source, follow these steps: 1.Open the Create Pivot Table dialog box in one of the following ways: •In the Insert menu, select Pivot Table > Use External Data Source. •On the Toolbar, in the Insert section, click •On the Toolbar, in the Insert section, click 2.In the Create Pivot Table window, click Select Source. 3.In the External Data Sources window, click Add Source and select the data file for the pivot table in the file manager window that opens. If you want to use an external source already added earlier, proceed to step 5. For all operating systems, only .xlsx and .xlsm files are available as source files.
4.In the Select Sheet window, select the sheet of the external document from the list. Clear the The first line of the data range contains titles checkbox if you do not want to use the first row as a title. After the sheet is selected, click OK. 5.Select the required document from the list of current sources and click OK. 6.In the Create Pivot Table window, specify where to insert the table: •New sheet: Insert the table in a new sheet. The new sheet will be created automatically. By default, the new sheet will be named Pivot table <No.>, where No. is the number of the pivot table in the document you are working on. If needed, you can edit the sheet name. •Existing sheet: Insert the table on the sheet that is currently displayed on the screen. In the Specify a destination cell box, enter the cell address manually or click to select an empty cell in the workspace where you want the upper-left cell of the pivot table to be located. There should be at least two empty rows above the initial cells for filters. 7.Click OK. You can't use external sources in pivot tables when working in the Cloud.
|
