In the previous texts, you could learn about several ways to consolidate data, and here we will deal with consolidation using a pivot table. To be able to achieve this we will use the Pivot Table and Chart Wizard. This is a hidden option that you can’t find in any of the ribbons (you can add it yourself, if you wish, by adjusting the layout of the ribbon), and it makes it easier to create pivot tables and charts …
Pivot tables are reports that, above all, are used to interpret data based on given criteria. Sometimes a pivot table can also be used as a data source to make one or more new pivot tables. This usually happens when you want to create a pivot table from an existing business report, based on a query to the transaction database, and thus use the data that are available in initial pivot table.
The Data Table is an old and somewhat neglected Excel option that allows you to see the results of applying different scenarios within a table. For a given formula and criteria, it returns alternative values of that formula. Criteria can be drawn from rows or columns, and there is the possibility of a report containing two groups of criteria. This is perhaps a better tool than a scenario if we perform simpler analyzes …
Calculated Items allow you to create formulas based on arithmetic operations between elements in rows and/or columns within a pivot table. Sometimes Excel can get confused and display incorrect results due to a misinterpretation of the order of computational operations. Solve order is an option with which we can change the order to get the correct result.
Fixing cell addresses is an old topic. However, when you create a structured table (data table) and try to copy a formula that points to a cell inside it, the rules of the game change. That is, the address will not be updated as desired. These addresses can also be fixed, and in the text that follows you will learn how to do this.