Workbook Analyzer

Use this feature to check the active workbook for any unwanted information contained in the Excel® file.

Best practice

It is highly recommended to use the Workbook Analyzer before sending any workbook to a third party. Often Excel® files still contain confidential data within external links or pictures that have already been deleted from the workbook. You may not see this information in Excel, despite it is still included in the file. The workbook cleaner performs a deep scan of the workbook and also shows information that may be hidden in orphant external links or media files like pictures.

Analyzing the workbook

Click the Analyze button on the top left of the Workbook Analyzer to start the analysis. Please note that the workbook must be saved as *.xlsx or *.xlsm file for the workbook cleaner to work. Only local files are supported for the analysis. If you are working on a file saved in Sharepoint® it is recommended to save a local copy and perform the operation on the local copy.

Please note that the Workbook Analyzer performs a deep analysis of all information contained in the Excel® file. This may take a long time,d epending on the file size and the complexity of the workbook.

Interpreting the information

Once the analysis has been performed the Workbook Analyzer will show the content of the Excel® file grouped by the main categories. In addition the list contains the file size for each item and the native size of each item. The native size is the size of the item before it is compressed within the Excel® file. The file size column shows the resulting size within the compressed Excel® file.

the categories show the total size of all the underlying items included in the respective category. The following categories are included in the analysis:

• Worksheets: This category contains a list of all worksheets and their respective size.
• External links: This category contains a list of all external links contained in the Excel® file. Please note: This category may also show external links that are not shown in the Excel® dialog for external links. This is because in some cases external links are not removed properly from the file once a links is deleted. This may lead to loss of confidential data. Please see the example below for details.
• Media: This category contains a list of all media objects like pictures contained in the Excel® file. Media do have the same issue as external links.
• Charts: This category contains all items associated with charts.
• Drawings: This category contains all items associated with drawings.
• Miscellaneous: This category contains the VBA project, shared strings and the calculation chain. This category also contains the size of the live map data from sheetRush, if available in the file.
• Other: This category contains the sum of the size of all unassociated items

Removing information from the file

If the Excel® file contains any information that is unwanted within the file it can easily be removed by checking the toggle in the Remove column of the respective item. After all items for removal have been selected press the Apply button to start the cleaning operation.

This operation will remove the items from the file. This operation may take a long time, please do not abort this operation. For security purposes a backup file is generated in the same folder as the original Excel® file as this operation may damage the Excel® file.

In rare cases when opening the file the next time Excel® may display a dialog offering to repair the file. This may happen because the Workbook Analyzer performs deep cleaning of the workbook and removes confidential data directly from the file. If Excel® displays this dialog select the Repair option. If anything goes wrong simply navigate to the folder of the file and restore the backup file that was automatically created.

Example: Orphant external links in the Excel® file

In some cases it may occur that the Excel® file contains external links that either cannot be deleted using the external data dialog from Excel® or have been deleted using the dialog but stay orphant within the file itself. This can result in the file containing confidential information the user is not aware of. If the file is sent to third parties but still contains confidential information like eg salery data this may result in legal issues or data protection issues.

It is important to know that Excel® stores a certain amount of external data within the external link itself. This data may remain within the file even after the link has been deleted. The following example shows the external data dialog of Excel® as well as the smartRush Workbook Analyzer. You can clearly see that the external link to Salary.xlsx is not shown by the Excel® dialog but by smartRush.

Even more interesting ist that Excel® caches some of the data from the external links. Clicking the Preview button in the smartRush Workbook Analyzer tries to recover as much of this cached information as possible.

This simple example shows that an Excel® file may still contain confidential data even after it has been deleted from the file. This may lead to dagnerous loss of confidential information.

Example: Large file sizes

If your Excel® file size suddenly gets very large and you do not have any clue where the large increase in file size comes from you can use the Workbook Cleaner to easily identify the source of the file size increase. In most cases sudden increases in file sizes are caused by formulas or formats on single worksheets that are copied across a very large range by mistake

Open the Worksheets category of the Worksheet cleaner to show all worksheets and their respective sizes. If any of the worksheets is by far larger than comparable worksheets this may be the source of the issue.

In the area below the worksheets the Worksheet Cleaner shows the used range determined by Excel® and the used content range. The used range shows the last cell in use on the worksheet. If this range contains a cell far to the right or to the bottom this may be the issue for the large file size. The last content cell shows the cell to the furthest bottom-right that contains any value. If this cell is not available stating not loaded you can click the link to determine the cell. Please note this operation may take a very long time. If cell addresses are shown in the used range and last content cell range you can click the link to jump to the cell.

If you have determined the worksheet that causes the large file sizes jump to the worksheet by double clicking the worksheet name in the list. Navigate to the last column you really use and click the column header to select the whole column. Press and hold Shift and press the Right key to select all columns to the right of the column. Delete the columns by pressing Ctrl + – or by using the context menu of the column header. Repeat the process for the last row. Save the file – the file size should be significantely lower now.

If you are sure you have identified the correct worksheet but the file size does not change you may need to perform one of the following operations: 1.) Click Clear/Clear All on the Home tab after you have selected the columns/rows. 2.) Press the shortcut Ctrl + Shift + Alt + F9 after you have deleted all columns and rows, but before saving the file. This shortcut recalculates the calculation chain. Please note this operation may take a long time but there is no progress bar. Once you can see a hover effect when moving the mouse over ribbon buttons again the operation is finished and you can save the file.