An Excel Graph is a great way to communicate a company’s financial performance to stakeholders. A conventional Excel graph has a hard time updating for source data changes; unlike other Excel functions such as formulas or cell formats that update seamlessly. The introduction of dynamic data ranges in an Excel Graph will solve this problem.
This is a follow-up to “How to create a dynamic drop-down list in your financial model”, but this article focuses on creating a dynamic drop-down list from a horizontal range or data array. A dynamic drop-down list can equally update and expand seamlessly, whenever additional items are added to a horizontal array of data.
Excel’s VLOOKUP function is undoubtedly one of the most popular Excel functions. It can readily interpret and report values in large data arrays; unlike a PivotTable, which needs to be manually updated to reflect source data changes. Often this Excel function finds it difficult to reflect changes to source data. A dynamic VLOOKUP that incorporates the COLUMN function will overcome this challenge of changing source data.
The Excel SUMPRODUCT function is one of the most powerful and flexible aggregation functions. This example demonstrates how SUMPRODUCT can discriminantly sum cells in either column H (“Invoice Amount”) or column I (“Amount Confirmed”), based on whether there is a corresponding amount in column I.