Wrap performance cookbook

Get to know ExcelWraps from the inside. Learn what you can do to make your wraps run much faster.

Excel is a powerful development tool. We strive to support everything that can be done in Excel also with ExcelWraps, but there are a few common pitfalls that all ExcelWraps developers must know about.

Create smaller wraps

Any visible data you add to a wrap will make it slower. Every cell must be fetched from the database, rendered by the browser, then saved back to the database. Dynamically hidden data, e.g. using HideRowIf(), also slows the wrap down.

Avoid having more cells in a wrap than you would have used in the corresponding paper form. If you want to provide easy access to supplemental data, use Wraplinks or text links.

Saving intermediate results in sheets, rows, and columns that are hidden in the Excel template using Excel methods costs very little.

Use compact spreadsheet templates

Every cell will be delivered to your device and will contain a value that needs to be stored in the database. Hold down the Ctrl key while you press the End key to select what Excel considers the last cell in your spreadsheet. If Excel doesn’t select the expected cell, your spreadsheet may be carrying dead weight that will also become part of the wrap. Note the row and column number of the selected cell before you remove the surplus cells.

  • Locate the last row you are actually using and delete the rows below it, down to and including the row number you noted above.
  • Locate the right-most column you are using and delete the columns to the right of it, up to and including the column number you noted above.
  • Verify with Ctrl-End that Excel now considers the correct cell to be the last cell in the spreadsheet.
  • If you still disagree, or if you just wish to keep the extra rows and columns without including them in the wrap, select the cell that is on the bottom row and right-most column of the cell range you want to include in the wrap and mark it with Tools > Force last cell.

Screenshot of the Force last cell option in the Tools menu

Be careful with the signatures

We encourage you to use all the signatures required by the business in your wraps – it is one of the major strengths of ExcelWraps. If you insert more than three signature fields on the same tab, however, you may be doing something wrong. Don’t create an electronic form in a way that you would never consider for a paper form.

If your business needs do dictate that a wrap has many signatures, you may get a performance improvement from running the wrap as a TeamWrap.

Don’t use too many MyWrap Aggregates

Clients often want a Dashboard Wrap that pulls in a lot of summarized data. Waiting while these sums, averages, and other aggregates are calculated in MyWraps will slow down the loading of the Wrap significantly.

It is more efficient to start with a small dashboard that only shows the essential numbers, and then zoom in on any interesting data using Wraplinks or text links to other wraps that provide more detail.

There may also be ways to use checkboxes or similar to dynamically activate selected areas of a dashboard on demand. Contact Client Services to learn more!

Never store text in numeric cells

Cells are faster when they know what type of information they will be holding. A cell that can hold either a number or text will be slower to access than a cell that knows it will contain only a number or a cell that knows it will take only text.

Error messages in cells

=IF(car_no=0,"Error",car_no)

You would never do something like this, would you? Inserting a text string in a numeric field will not only reduce performance considerably, it may also introduce errors when a text value is used in calculations. Read more about how to use numeric formats for error messages.

Zero suppression

Another common example is zero suppression, where an otherwise numeric cell is set to an empty string or blank if it is zero.

=IF(car_no<>0,car_no,"")

You would never do something like this, would you? Again, inserting a text string in a numeric field will slow down your wrap even if the string is empty, and may introduce errors when empty string values are used in later calculations. Read more about how to use numeric formats for zero suppression.

Use the most efficient functions

WraplinkAggregate2 is much faster than MyWraps2.

With MyWraps2, avoid asking for more than three columns of data.

Don’t use HideRowIf or HideColumnIf for unnamed fields.

Secondary Hyperlink cells that could be replaced by a HyperlinkLong function.

Short string MyWraps is almost 6 times as fast as full MyWraps links.

Make MyWraps links as short as possible

This MyWraps report takes 9.29 seconds to load:

TaskManager?Options.Columns=Task(taskquery).string,Raised(created).date,Initiator(clientimplementer).string,Category(costcentre).string,Priority(priority).string,Deadline(deadline).date,EW Response(responsecomments).string,Days(estimateddays).double,Team(xlwteam).string,EW Quoted(actionedby).sig-date,EW Price(pricequoted).double.C2,HRL Approved(raised).sig-date,Completed(responseactioncomplete).sig-date,Closed(closeout).sig-date,PO(purchaseorderno_).string&Options.Aggregates=Days.sum,EW Quoted.counta,HRL Approved.counta,Completed.counta&Options.AggregatesOnly=False&Options.SortBy=PO.desc&Options.State=Live&Options.Creator=Any&Options.AutoNumberFilter=None&Options.MaxRows=500&Filter.PO=5500
If you only need the days aggregate this link loads in only 2.69 seconds:

TaskManager?Options.Columns=Days(estimateddays).double,PO(purchaseorderno_).string&Options.Aggregates=Days.sum,EW Quoted.counta,HRL Approved.counta,Completed.counta&Options.AggregatesOnly=true&Options.SortBy=PO.desc&Options.State=Live&Options.Creator=Any&Options.AutoNumberFilter=None&Options.MaxRows=500&Filter.PO=5500

Knowing MyWraps Syntax give more compression that results in this link, that takes just 1,62 seconds to load:

TaskManager?Options.Columns=Days(estimateddays).double&Options.Aggregates=Days.sum&Options.AggregatesOnly=true&Options.State=Live&Filter.purchaseorderno_=5500