Troubleshooting
Problem
When using the arrow keys to move from cell to cell in a IBM Planning Analytics for Excel workbook, the performance is sluggish/slow.
Cause
The cause of the performance issue in this case was due to thousands of hidden named ranges. These were not visible in Microsoft Excel and were not in use anymore.
Diagnosing The Problem
To diagnose this issue, one first needs to confirm this is the culprit:
- In Microsoft Excel, open the workbook that has the issue
- Navigate to Formulas > Name Manager
- Make note of the number of entries here for named ranges. This will give you an idea of how many named ranges you are using in the current workbook.
- Close Microsoft Excel
- In the folder where the workbook is saved, create a copy and rename it. We will use this copy for our investigation
- Renamed the copied workbook from .xlsx to .zip
- Unzip / open this file and navigate to the "xl" folder.
- Copy "workbook.xml" to another location and open in either a text editor or a web browser. The web browser layout makes it easier to read.
- You can now see all the named ranges listed between <definedNames> and </definedNames>.
Resolving The Problem
If there are many hidden named ranges, you can either remove them manually or run a VBA macro so that they are visible in Microsoft Excel for further investigation.
To manually remove all named ranges:
- Copy "workbook.xml" to another location and open in a text editor like Notepad++
- Deleted all the content between <definedNames> and </definedNames>
- Save the changes and copy it back into the zip file location, replacing the existing "workbook.xml" file.
- Rename the complete zip folder structure back to .xlsx
- Open in Microsoft Excel. Note, that the product may notice changes and will ask if you would like to recover as much as possible.
To make the hidden named ranges visible:
- Add the following VBA macro to your workbook and execute:
Sub unhideAllNames()
'Unhide all names in the currently open Excel file
For Each tempName In ActiveWorkbook.Names
tempName.Visible = True
Next
End Sub
Related Information
Document Location
Worldwide
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSCTEW","label":"IBM Planning Analytics Local"},"ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
21 September 2021
UID
ibm16259461