All the tables we make have some structure. However, in order to connect with external data sources, Excel requires the structure of the table to be strictly formal. The structured table (Table) represents the named range with a clearly defined header and an applied autofilter. In addition to facilitating connection with other data sources, structured tables allow easy addition of new columns and formulas based on their names …
When making extensive reports, there is often a need to calculate subtotals by reporting categories. Subtotals are, most of the time, sums and they might also be an average, a minimum, a maximum, a product, or a count of articles within a report. Subtotals can be made manually and in this „recipe“ it will be about how to use Excel for automatically adding subtotals.
When we work with large tables containing the subtotals according to categories (article classification, sales program, type of partner, etc.), we can perform a grouping by to one of the given criteria. Grouping can be done by rows or columns, and the goal is to make the report more transparent. That is, if necessary, we can display or hide the analytical elements of the report in order for the data to take up less space.
Consolidation is the term that denotes the unification of several parts into a single entity. In practice, we use consolidation when we want to receive a summary report; for example, when we want to summarize the sale by months, quarters, years … The simplest way to do this is by summarizing the data entered in several worksheets in the last sheet using the formula. But what if the data in the worksheets is not uniform? Excel also has a solution for this scenario …
When entering the parameters of a calculation, it is useful when we can check that the correct values are entered. This type of control serves to check whether a good type of data is entered, whether the values are in the reference range, and can be used to help when entering data into a table. In this way, Excel can also be used as a tool for completing a questionnaire with predefined answers.