Creating Pivot tables

Pivot tables (Pivot table reports) are a tool for creating interactive reports based on the range of selected data or structured tables. Columns from source tables are used as reporting dimensions by aggregation of data (measures) or using them to analyze in rows, columns, as filters (dimensions) … Once made, the Pivot tables are easily changed by adding or removing dimensions, giving us more different look at the unique original dataset.

Regardless of the data source by which Pivot tables are made, it is important that we first have well-prepared data. Pivot table data must be within a structured table or traditional Excel table. It is important to emphasize that such a table must have a header, in which column data are described (eg S/N, ITEM, QUANTITY, PRICE, etc.); also, records in rows must be uniform.

We create a pivot table by first selecting a data range or a structured table. Perhaps the easiest way to do this is to click on any cell inside the table and press the CTRL + A key combination. Then go to the Insert ribbon and select the Pivot Table option. The Create PivotTable dialog box opens. In the dialog box, first it is necessary to assign the data source. This can be a selected range, e.g. $A$1:$G$1001 or it may be the name of a structured table. It is recommended that Pivot tables are always made on the basis of structured tables, because if adding new records (rows) in the table it will not be necessary to change the data source to further update the Pivot table. Also, the data can also be added from an external database source by selecting a table, view, or dataset created by writing some SQL query. Then, it is necessary to determine whether the Pivot table report is created in an existing worksheet, ina a given position, or in a new worksheet. Finally, at the bottom of the dialog boc, there is an option Add Data to Data Model. This is a novelty that brings Excel 2013, and it refers to the ability to add a table to a data model (a quasi database inside an Excel document), which can be used to create complex reports using the PowerPivot tool.

Lets create a Pivot table report based on a given data range in a new worksheet. The Pivot table will appear on the sheet, as will the control panel (Pivot Table Fields) on the right side of the window. In the upper part of the panel there is a list of field reporting dimensions, and in the lower part, in the form of square, there are four areas: Values, Rows, Columns and Filters. By selecting or dragging the dimensions into one of these four fields we make a report. In the case of selecting dimensions from the list Excel will itself determine in which area a dimension should appear, while by dragging we choose where it will be used. In the value field (Values) we should drag the columns we want to aggregate (count, sum, average etc.). In rows (Rows) or columns (Columns) we’ll drag dimensions by which we want to look at the report, while filters (Filters) represent those dimensions by which we want to filter all the data in the Pivot table. For example, if we want to create a report which shows sales amounts by items, we should drag AMOUT dimension into Values, and ITEM NAME into Rows.

When we create a Pivot table, if we click on it, two contextual ribbons will appear: Analyze and Design. In the first, there are options that are important for functionality, and in another options that deal with  Pivot table appearance. These options will be more of a word in the following text…