Is the duration of reporting in Excel very long? Are the amounts of data hardly manageable? Is new data constantly being added and do incorrect entries have to be corrected? To create really practical Excel tables, filters, sorting, and formulas are often not enough; then a pivot table is much more practical. With Microsoft Excel pivot tables, the tech giant from Redmond provides another practical tool for data processing in the Office package.
In such a table, Excel collects and evaluates the data. Elsewhere, the data can be changed as desired. These changes are reflected in the pivot table, but the table itself does not change the data or its order.
With this tool, it is much easier to keep track of the data, and creating a pivot table only takes a few clicks! The larger the amount of data that needs to be processed, the greater the benefits that a pivot table offers; it is not for nothing that pivot tables are also used by experienced Excel users as the ultimate tool.
Select the data areas for the Excel table.
To create a pivot table with Excel, go to the “Insert” tab at the top and select the “Pivot Chart” drop-down menu under the “Charts” menu area. In the next step, Excel will prompt you to select the data areas for the pivot table.
After confirming your selected data ranges, you can choose where you want to place your pivot table.
Tip: To make your worksheets clearer, you should place your pivot table on another worksheet.
When you select the Pivot Table, another interface called “Pivot Table Field List” will appear on the screen. Here you can choose which fields of the selected data series you want to display in the Pivot Table. When you select the fields, you can next determine what function these fields have.
Design the pivot table
· Report filter: This feature allows you to quickly select sections later when the pivot table is ready.
· Column Labels: Here you can define the data to be displayed as a column.
· Row labels: The data to be listed in the rows is defined here.
· Values: This is where the data that is actually summed up or analyzed is determined.
The individual contents for these four windows are determined by dragging them from the upper window into one of the four lower fields.
You have now created your first pivot table. You can continue to add more filters and sort the data as much as you want until you are satisfied with the result.
Customize the pivot table design
Like other tables, you can customize the pivot table according to your needs. To do this, click on a field in the pivot table and the Pivot Chart Tools tab will appear at the top of Excel. Here you can select various options to perfect the chart.
You can not only select the coloring in the table, but you can also use these tools to specify the format of numbers, for example. Instead of real numbers, you can display your numbers as currency or dates.
Filter in the pivot table
By dragging new filters into the Report Filter field in the Pivot Table Field List interface, you can filter by categories that you set in the original list. This allows you to filter by year or month, for example.
Depending on how you want to apply these filters, you can also drag them into the Column Labels box. Depending on where you drop the filters, the pivot table changes its appearance, it’s up to you what works best for you.