OEG Best Practice: Best practices about Excel add-In

The performance of our add-in has largely improved compared to the classic version (Excel add-in 2.6) as a result of a technology change. The performance can be impacted by multiple factors:

• Machine (in particular the available RAM)

• Network connection speed

• Number of connections to refresh

• Size of the connection (number of dimensions and cells)

• Number of cells edited (for the send and refresh functionality in Excel)

Please find below some indicative performance information in our testing conditions.

Read-only:

Send and refresh:

Try and minimize the amount of data you retrieve with the add-in in order to ensure great performance. If you want to retrieve a small amount of data from a large module, you might want to create a separate module just for the add-in.

You cannot copy/paste or copy the sheet with the standard Excel functionality, but you can use “clone the connection”.

As you cannot clone in a separate workbook, to duplicate your workbook you can:

• Once you have created workbook 1, save the file 1

• Take a copy of the file 1: this is now your file 2 and it is connected to Anaplan.

Saved views are great because you immediately retrieve the data you need, without having to pivot and filter it manually. They are great for reporting purposes because you can create a standard report quickly. However, you are reliant on a model builder to set up the saved view in Anaplan. If you have Application Lifecycle Management (ALM), this needs to be done in the development model before synchronizing with your production model).

Modules are more flexible because you can pivot and filter them. However, this means you might need more time to set up exactly the view you want. They are great for ad-hoc analysis because they make it easier to explore your data.

The approach of one big connection is appropriate in some instances. It is good because it limits the number of connections in a workbook and you do not have to change the page selector every month for instance (see paragraph 9 about page selector change). On the other hand, given the limitation on the number of cells that can be retrieved in a single import, you would have to be mindful of every change in your module that could result in going other this limit.

There is no way to identify which modules or views are used by the add-ins in Anaplan.

If you make changes to a module or a saved view that is used by the add-in, you may risk breaking your connection. Although small changes are handled by the add-ins (new list item or line item for example), bigger changes like a change in dimensionality cannot be dealt with automatically.

It is recommended that you use a specific naming convention to indicate that a view or module is used by the add-in (for instance P&L_report_XL to indicate it is used by the Excel add-in). You can also mention this in the “notes” column but it is less visible.

If you are using other add-ins, you may be experiencing conflict between those and the Anaplan add-ins. It is not possible for Anaplan to test the interactions with all the other add-ins available in the market. If you face issues, we can investigate it if you raise a ticket with Anaplan Support.

If the workbooks tend to crash with the add-in, one workaround is to turn the add-in off (via Excel Options -> Add-ins) when not actively using the add-in (ie during other Excel work).

The connections you create using the add-in are based on the model ID. Therefore if you delete a model, the model ID is lost and you will lose your add-in connections.

As a result, you will have to rebuild your files entirely. We recommend that you simply archive models instead of deleting them. This way, they can be unarchived easily if you need them back, they don’t take any space and their model ID is conserved.

When the page selector changes as a result of a saved view change (for example your saved view in Anaplan is filtered by the current month and you update this month every month in Anaplan directly), this is what happens in the background: the columns in your Excel spreadsheet are deleted first, and then the new data is retrieved. As a result, if you have a formula looking at one of the Anaplan data, it will change to #REF because the cell it was pointing at has been deleted. Also, the formatting gets removed for the same reason.
If you do have to reference a cell, we recommend you set up your report in a way that does not impact the page selector. For example, you could put the dimension you want in pages in rows or columns instead, and use vlookup or index(match) formulae to get the right row or column.

Alternatively, the #REF does not occur if the formula is in a different file and you don’t have this file open when you refresh. For example, you retrieve Anaplan data in file A, but your report which points at this data is in file B.

There may be occasions when you need to retrieve data from a module that will take you above the 1m cell limit currently in place with the Excel Add-in. Follow these steps as a workaround.

1. Identify the module you wish to bring data into Excel from.
2. Identify the lists used in the module and ensure each list item has a code
3. In the module where the data is held create a new line item Excel Code as Text
4. Create a formula concatenating the codes of the component lists together where data exists. Separate each code by an underscore.
5. Create a new list called Excel Code
6. Import into the list from the module where you have created the code
7. Create a new module dimensioned by the Excel Code list. In this module break the code down into it constituent parts by using Find, Left, Right and Mid text functions. Using the codes also return the name of the corresponding element. You are going to import this into Excel as a reference table.
8. Create a module to hold your data dimensioned by the Excel Code list and extract the data with reference to the module created above by means of a Lookup
9. If you don’t already have so, create a module detailing the contents of each list namely the code and name
10. In Excel, use the add-in to create a connection to pull the data into a sheet
11. Similarly, pull in the reference table created in Step 7 and all the supporting reference information
12. Create your report to display the information. This can be achieved by reconstructing the code based on the elements either in the rows and columns and or drop-down selectors.