This article and video will outline one approach to generate a Goal Seek across multiple columns in Excel. As per the following screenshot, we have a standard financial model. It is calculating down to EBITDA, and referencing from our input sheet.

The standard Goal Seek tool in Excel only permits users to seek a goal value on one cell. However in the above screenshot example, we want to achieve different goal values across multiple columns.

We want a customized Goal Seek, which will deliver a 30% EBITDA margin based on each year’s respective revenue number.

Conceptualizing the Goal Seek macro

To realize a Goal Seek across columns solution. We must write a macro in Visual Basic for Applications.

We want to Goal Seek our EBITDA in row 14, based on a specified EBITDA goal in row 18 – by achieving a uniform 30% margin. Our changing precedent variable will be Cost of Goods Sold in row 9.

Conceptually, we need the Goal Seek across columns macro to operate like this. It will need to Goal Seek from column J across to column S.

Writing the Goal Seek macro

Go to Visual Basic editor and insert a Module. The macro will be called, gsMultiColumns.

We want to insert an input box prompt, whenever someone runs the macro, as it will permit users to exit the macro where necessary.

Next we need to declare the macro’s variables to prevent run-time errors. The narratives in green are optional and won’t impact the macro – they merely serve as a guide for the macro user.

Then we want to disable certain key Excel properties, which would otherwise hinder the macro’s performance. Here is the first part of the code, which video will present to you.

The following is a key part of the macro. Clearly, specifying the range of cells to change for our column Goal Seek. For this example, it will be for row 9.

Given the cells in row 9 contain formulas; which is referencing to our input sheet. It is paramount to change or convert these formula cells to their values – just like copying and pasting values.

The i integer will enable the macro to move across the columns of data – as per the referenced range J9:S9.

As described, cCell references the changing cells in row 9. In addition, sCell denotes the set cells in row 14 of the Goal Seek macro. Whilst, gCell are the cells in row 18 dictating the goal values.

Running the macro

We are now ready to insert the code, which will enable the EBITDA goal seek values in row 18 to be reflected in row 14. The code that will trigger the Goal Seek is presented in-depth in the following video.

Remember to re-enable those Excel properties, which we initially disabled at the top of the macro. We are now ready to insert a form control button, and insert the Goal Seek macro on multiple columns to generate our EBITDA goal.

As we see, our multi column Goal seek macro works. In summary, here is the example workbook, which the video illustrated to write the above macro.

Download SpreadsheetVault – Excel Goal Seek across columns (solutions).xlsb