Pivot table as data source

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.

Compressing pictures

“A picture speaks more than 1000 words” so you leave a better impression when you enrich the report with pictures. However, many users do not care how big they are and, accordingly, Excel files grow progressively. Even Office 2010 got an option for image optimization that you can use whether you use Word, Excel, PowerPoint … Optimization reduces images, which loses quality, but files take up less space. How to determine the right ratio?

Navigation

We often come across Excel documents that have multiple reports found in several worksheets. If you want to find the report you want, click on the tab with the name of the corresponding worksheet. However, this is not always the optimal solution. In order to facilitate positioning on the desired report, we introduce links that we have attached to certain forms. That is, we introduce a navigation panel to navigate within the document.

Constants

Few Excel users know about array formulas and I believe they have found themselves in a situation where they need them at least once. They were mentioned in one of the previous “recipes”, and here you will be able to learn how to use them in combination with constants. Constants are values ​​inside curly braces, separated by a comma (or semicolon), and we use them for arithmetic operations within an array formula.

Data 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 …