Report Template - Excel Formula VS Pivot Table in Excel
Most of Excel users even at beginning level make use of Pivot Table to summarize the raw data into table-format report without coding or writing some complex formula. It is easier for Excel beginner to build their own report based on their specific requirement with user-friendly interface and well-defined format. Nevertheless, Pivot table has its limitation of functional design, which Excel formula could make up such deficiency. If you still think that Pivot-table should be the only tools for reporting in the Excel, the function of Excel formula like SUMIF, COUNTIF seems to be under-estimated.
Sometimes, mainly in the working, several users abuse the Pivot Table, for instance, to call the data from the pivot table with the method of "copy and paste to values" instead of using the function: GetPivotData. That results in the complicated and less reliable process flow and thus difficulty of automation development and maintenance. In order to design a reliable and automatic reporting template, I will introduce how to develop with both Pivot table and Excel formula to the business requirement to achieve the goal.
Before developing the template, the advantage of using Pivot Table is discussed first. It is required few steps to create the Pivot table, select the source data with header columns, and drag the needed attributes to be the row/column/values in the report. It is definitely time-saving to build a report within short time when compared with using Excel formula. The dimension of Pivot Table can be defined by user which brings about high flexibility. The values of target attributes could be summarized with summation, mean, maximum or minimum and expressed the result in forms of %. As a result, many users who are not familiar with Excel formula love to use Pivot Table rather than report built by other ways.
Of course, Pivot Table still has its own limitation that it cannot refresh once there are any changes on the source data. Since it requires time to read the source data especially with great volume, it is not efficient to automatically refresh when tracking any changes. That usually leads to the figures in the report is still unsynchronized due to user forgetting to refresh the data. Apart from above, the source data selection is also the concern when using the pivot tables. For some cases of reporting monthly sales performance, the amount of transaction data varies in each month, the source data specified by user may not cover all transaction data to build the pivot table. It could only be solved by defining the range of source data including the cells contains "blank" value (in red circle). Such blank value, in fact, is meaningless in the report and it needs to be filtered in the Pivot Table.
It is believed that Excel user are preferred the Pivot Table as it can show the attribute values which exists in the data. It actually is very useful for the case when we only want to show the value of attributes existing in the data. For example, I am working on the staff performance report on monthly basis. The resigned staffs will not be shown in the report owing to missing in the raw data, which actually is preferable outcome in this task of analysis.
However, it is possible to bring about troubles to you if range or format of values has been well defined. Taking the machine performance report as example, as we known, a machine is assumed to be operated in 24-hour on demand and it is allowed to be idle for several hours. When we analyze its hourly productivity and choose the time as column of the Pivot Table, it is found that some of columns (hours) are missing and thus the axis representing time in the graphs is distorted. Worse still, some add-on formula related involve the Pivot Table may reference inappropriate range since Pivot table will resize in lines with existing data. To deal with this problems, well format column and row in the report is defined and then summarize the data with Excel function. In below example, SUMIF function is used for calculating the total movement of machines in each working hour.
Alternatively, the function, GETPIVOTDATA also is a feasible solution to build a new template by extracting the data in the Pivot table and changes the corresponding address of the value of the data field (refer to below figures). It is commonly used when dynamic value of data field is observed and fix-format template is required.
To sum up, Pivot table is not really feasible in all case for establishing a reliable and effective report template. It also could result in disaster when you abuse it. It is indispensable for users in the intermediate and expert level to apply the Excel formula with high flexibility on their routines. Consequently, I strongly encourage beginner to think about what is your requirement and expected outcome before designing and building your own template.
Sometimes, mainly in the working, several users abuse the Pivot Table, for instance, to call the data from the pivot table with the method of "copy and paste to values" instead of using the function: GetPivotData. That results in the complicated and less reliable process flow and thus difficulty of automation development and maintenance. In order to design a reliable and automatic reporting template, I will introduce how to develop with both Pivot table and Excel formula to the business requirement to achieve the goal.
Before developing the template, the advantage of using Pivot Table is discussed first. It is required few steps to create the Pivot table, select the source data with header columns, and drag the needed attributes to be the row/column/values in the report. It is definitely time-saving to build a report within short time when compared with using Excel formula. The dimension of Pivot Table can be defined by user which brings about high flexibility. The values of target attributes could be summarized with summation, mean, maximum or minimum and expressed the result in forms of %. As a result, many users who are not familiar with Excel formula love to use Pivot Table rather than report built by other ways.
Of course, Pivot Table still has its own limitation that it cannot refresh once there are any changes on the source data. Since it requires time to read the source data especially with great volume, it is not efficient to automatically refresh when tracking any changes. That usually leads to the figures in the report is still unsynchronized due to user forgetting to refresh the data. Apart from above, the source data selection is also the concern when using the pivot tables. For some cases of reporting monthly sales performance, the amount of transaction data varies in each month, the source data specified by user may not cover all transaction data to build the pivot table. It could only be solved by defining the range of source data including the cells contains "blank" value (in red circle). Such blank value, in fact, is meaningless in the report and it needs to be filtered in the Pivot Table.
It is believed that Excel user are preferred the Pivot Table as it can show the attribute values which exists in the data. It actually is very useful for the case when we only want to show the value of attributes existing in the data. For example, I am working on the staff performance report on monthly basis. The resigned staffs will not be shown in the report owing to missing in the raw data, which actually is preferable outcome in this task of analysis.
However, it is possible to bring about troubles to you if range or format of values has been well defined. Taking the machine performance report as example, as we known, a machine is assumed to be operated in 24-hour on demand and it is allowed to be idle for several hours. When we analyze its hourly productivity and choose the time as column of the Pivot Table, it is found that some of columns (hours) are missing and thus the axis representing time in the graphs is distorted. Worse still, some add-on formula related involve the Pivot Table may reference inappropriate range since Pivot table will resize in lines with existing data. To deal with this problems, well format column and row in the report is defined and then summarize the data with Excel function. In below example, SUMIF function is used for calculating the total movement of machines in each working hour.
Alternatively, the function, GETPIVOTDATA also is a feasible solution to build a new template by extracting the data in the Pivot table and changes the corresponding address of the value of the data field (refer to below figures). It is commonly used when dynamic value of data field is observed and fix-format template is required.
To sum up, Pivot table is not really feasible in all case for establishing a reliable and effective report template. It also could result in disaster when you abuse it. It is indispensable for users in the intermediate and expert level to apply the Excel formula with high flexibility on their routines. Consequently, I strongly encourage beginner to think about what is your requirement and expected outcome before designing and building your own template.
Comments
Post a Comment