Microsoft(*) introduced the powerful Pivot Tables into Excel(*) to let the user display the data contained in a column of an Excel(*) list by means of subtotals (or other calculations, user selectable). By using the useful Pivot Table, also the relationships within the data may be found (row with the same summarization key). But a Pivot Table is not directly linked to its source data. If the data in a list (from which a Pivot Table has been created) is changed, the data in the Pivot Table is not directly updated to reflect the changes, unless the refresh option in the Data pull down menu is selected, or unless a refresh macro is invoked (associated to a button or to a worksheet selection), or by right clicking the Table and then ...
Page 1 of 5
Automatic refresh of pivot table like graphs
Hereafter is proposed and described, a method that, by using some simple native and existing Excel(*) functions, summarizes a range of spreadsheet cells (in a fix way, based on the content of a corresponding range of key cells), without using the advantages of an interactive Pivot Table and performs an automatic and real-time data refresh, without user intervention, as soon as the source list data is updated.
Moreover another important aspect is the sorting of the data shown in the graph. Usually if there are gaps of data, you can avoid that the labels are shown on the final graph, you can avoid that the 0 value is displayed, but the gap of the data cannot be automaticallyand dinamically avoided. Below is also described a "native" method to address this need.
The result is obtained without writing any line of code, without using macros,
just using native
Following are listed the steps of the proposed method to obtain the summarization with automatic refresh in case of source data change:
Steps of the Method
Spreadsheet definition
A spreadsheet
(ex. SHEET1) is defined with a range of cells containing data. A column contains a value to be used as summarization key and another column (or more) contains the corresponding value to be summarized. A second sheet can be defined
source sheet
excel functions available in a worksheet.
Let us start with this example. The figure 1 shows a range of cells containing the data to be summarized and an Excel(*) Pivot table created on them:
Fig. 1
1
[This page contains 2 pictures or other non-text objects]
Page 2 of 5
to divide elaboration from source and result data. Source key cells definition
In the second elaboration sheet (ex. SHEET2) a range of cells is defined containing the summarization key cells as duplicate of a range of cells present on a different sheet (ex. source sheet SHEET1). The elaboration sheet may match with the source sheet. The same elaboration can also be split in different elaboration sheets.
Ex. Sheet2!E10:E19 = Sheet1!E10:E19 as shown in Figure 2.
Fig. 2
Source values corresponding to key values definition
As in step 2, on the elaboration sheet, a range of cells is defined with the keys corresponding values from the source sheet range.
Ex. Sheet2!B10:B19 = Sheet1!G10:G19 in case of one column values. As shown in the second picture of figure 2.
Duplicate key cells entries identification
On the Elaboration sheet (SHEET2) a range of cells is defined to identify duplicate entries among the keys. It uses the IF conditional native function of the spreadsheet (as shown in figure 4) For each column the value of the key present in the cell is compared with all the other key values. For example =IF (E11=E10,1,0) meaning: if the value of the key present in the cell E11 matches the value of the key present in the comparison...
