The old, manual process usually looks something like this: Access data from a tool 2. Manipulate your data 3. Add data to a table manually 4. Right-click on your graph 5. Redefine the data powering your graph If you repeat this process for several charts and graphs each week, that time can add up.
Our approach will eliminate the need to complete any manual steps besides grabbing a report and dumping it into your Excel file. All of the tables, charts and graphs will all keep themselves updated automatically. Accessing data from a tool 2. Those named ranges, in turn, will be defined with offset and count formulas. This means that you can dump data in one location and then have a table update itself automatically, which in turn updates a graph automatically.
Our sumifs simply keeps track of the sum of each metric for each month this process is explained in our appendix for those of you who are new to this formula. We then use named ranges that point towards our automatically updated tables.
The trick here is that a named range can be defined using a formula. In this case that means 3 ranges for the axis, spend, and revenue values. The final step to automating this process is to define the series and axis in our graph with the named ranges that we just completed.
This step has a small curveball included in it. The problem that you can run into is that Excel will redefine the axis values if one of your series has a different shape. The result of this setup is a chain reaction where you dump data into a worksheet, your table keeps itself updated with sumifs formulas and your graph automatically keeps itself updated after that.
You can experiment with this functionality by clearing the contents of cells in the Data For Our Graph worksheet and replacing it with the data from the Additional Data worksheet.
You can apply the same principle to graphs that represent a rolling data set last 12 months by utilizing the second and third parameters in the offset function, or by creating a table that automatically updates the header column to contain the appropriate months. When applied correctly, this technique can save you and your team every time you end up needing an updated version of preexisting chart or graph.
Appendix of formula explanations Count: This formula simply counts the number of cells within a range that contain numbers. There are many variations of this formula in Excel. Simple test of a logical argument that returns one value when the argument is true, and another value when it is false.
Also has many useful derivatives such as iferror, ifnumber, etc. Found under the Formulas ribbon, this Excel function allows you to create a name for a range of cells that you can then use in other formulas and functions. We defined our named range using a formula, but you can use a static definition as well. We also took advantage of our named range as a definition for a series on a chart, but you can also use named ranges as parameters in formulas.
Defining Series Values in a Charts and Graphs: Pretty straightforward Excel function. Right-clicking on a chart allows you to redefine the data that powers the chart. You can also define individual series, and the axis, by editing them manually after right-clicking on the chart and clicking on Select Data. The sumifs formula works a little bit like a filter works. With a filter, you choose one column to include based on the value it has.
Then you can select a different column to add values from, like summating the values in a spend column. The result is that you can extract all of the spend value from a table that meets your criteria for engine, or any other values. The sumifs formula does exactly that. You define one metric that you want to add the first parameter and then define subsequent criteria ranges and values that will define filter ranges and values that you want to include in your summation.
In the worksheet titled Sumifs Example, we tried to demonstrate how this works, one column at a time. In the D and E columns, we have logical arguments that test the values for Engine and Campaign within our table. When these logical arguments are true, we pass 1s, otherwise we pass 0s. We can then take the product of all of those arguments plus our desired metric. The summated result is the sum of our metric from columns that meet our filtering criteria.
Things might get a little tricky here. The offset function can define an individual cell, or a range of cells. That cell or range can be defined dynamically.
The first parameter in this formula defines a starting point to be used. The second and third parameters define how you would like to offset from that starting point in terms of rows and columns, and the fourth and fifth parameters define the shape of the array that you want to create only if you want to create an array, and not reference a fixed cell. Our example uses the count formula in the fifth parameter, with the count formula pointed towards the cells in our table that were defined with the sumifs formula.