How to sum across optional column ranges using SUMPRODUCT

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.

Read More

How to use Excel Data Validation to protect cells

Excel’s Data Validation is one of the more underutilised and understated features in Microsoft’s spreadsheet application.

Quite simply it is a great, VBA macro-free tool, in which to improve the internal controls and corporate governance surrounding the management of a financial model.

Read More

Excel INDEX MATCH for two dimension lookups

The VLOOKUP function is a highly useful Excel function in financial analysis and business planning. It is a valuable one-dimension lookup tool – along with HLOOKUP. However in some cases, a financial analyst will require a two-dimension lookup tool, in order to reference the exact value from a data table, which a vanilla VLOOKUP function will be unable to perform.

The combination of the INDEX function with two MATCH functions can solve this matter.

Read More

How to create a dynamic drop-down list in your financial model

A dynamic drop-down list is a great way to force spreadsheet users, to select a specific array of inputs for a cell in a financial model. This dynamic approach combines the Defined Name function, OFFSET and COUNTA functions to provide a drop-down list, which flexes whenever there are additions or deletions to the data array of inputs.

Read More