Skip to main content

Spreadsheet Optimizer

Introduction​

We do not have to suffer from slow spreadsheets. Very often, inefficiant spreasheets contain much room of improvement, which we can identify and optimize. One type of optimization relies on the optimization of spreadsheet formulas. For example, we could use more appropriate functions to make formulas faster, shorter and easy to understand.

Spreadsheet Optimizer helps you optimize your formulas in terms of efficiency and readability.

  • Spreadsheet Optimizer automatically identifies the formulas that can be improved in terms of efficiency and readability.
  • It proposes better ways to rewrite these formulas, and tells you why they are better.
  • Anytime, you could accept its recommendation or undo the change.

Getting Started​

Besides following Installation to install the 10 Studio add-in, the quickest way to try out Spreadsheet Optimizer is to click below and download the workbook sample.

  Download 10Studio-Sample-Auto.xlsx

Once the 10 Studio add-in has been loaded, click on the Spreadsheet Optimizer button under the 10 Studio tab to launch the application.

Instructions​

  • If there are spreadsheets that are protected by password, manually unprotect them
  • Load the 10 Studio add-in, click on the Spreadsheet Optimizer button in the ribbon under the 10 Studio tab.
  • Click the button Optimize in the task-pane.
  • If the tool has not found formulas to optimize, The workbook does not have bad formulas with regard to our optimization rules will appear. Otherwise, the tool lists the formulas to optimize. You could click on the item to effectively replace formulas (and undo the change).

Optimization rules​

In this stage, we apply the following rules to optimize formulas. Our tool tries to detect the bad pattern inside formulas and suggest a good pattern. At the moment, we don't apply two rules simultaneously in one single formula.

VLOOKUP => INDEX+MATCH​

VLOOKUP formulas can be fragile with regard to a structure change of a worksheet. When a user inserts a column in the table, the third parameter of VLOOKUP referring to the column number will nevertheless not change automatically; as a consequence, due to the column insertion, VLOOKUP will not return values from the same column as before. Whereas, INDEX+MATCH formulas will always refer to the initial column regardless of a structure change of the worksheet.

Examples:

   =VLOOKUP(A1,C2:G9,2,false)
=> =INDEX(D2:D9,MATCH(A1,C2:G9,0))

SUMIFs => SUMIFS​

The addition of several SUMIFS expressions can be costly over a big table, because it iterates through the table several times. If the SUMIFS expressions share a similar structure, the formula can be re-written in a SUM+SUMIFS way. It is more efficient, because it iterates through the table only once.

Examples:

   =SUMIFS(N1:N9,L1:L9,C1,M1:M9,"H1")+SUMIFS(N1:N9,L1:L9,C1,M1:M9,"H2")
=> =SUM(SUMIFS(N1:N9,L1:L9,C1,M1:M9,{"H1","H2"}))

IF+ISERROR => IFERROR​

IFERROR is a relatively new function that can replace the combination of IF and ISERROR.

Examples:

   =IF(ISERROR(a),b,a)
=> =IFERROR(a,b)

nested-IF => IFS​

IFS is a newly introduced function, which can be used to replace a certain type of nested-if formulas.

Examples:

   =IF(a,b,IF(c,d,IF(e,f,g)))
=> =IFS(a,b,c,d,e,f,g)

nested-IF => IF​

There may be surplus in a nest-if formula. We could for example have less IF to make the formula more succint.

Examples:

   =IF(a,b,IF(a,b,c))
=> =IF(a,b,c)

nested-IF => IF+AND/OR​

Some logic in a nested-if can be expressed by logical operators such as AND or OR, which makes a formula more succint.

Examples:

   =IF(B6=a,x,IF(C6=b,x,y)) 
=> =IF(OR(B6=a,C6=b),x,y)

   =IF(B6=a,IF(C6=b,x,y),y) 
=> =IF(AND(B6=a,C6=b),x,y)

nested-AND/OR => AND/OR​

Logical expressions such as nested-AND/OR can be re-written in a more succint way.

Examples:

   =AND(a,AND(b,c,d),e) 
=> =AND(a,b,c,d,e)

   =OR(OR(a,b,c),d,e) 
=> =OR(a,b,c,d,e)