Excel Workbook Operations

Spreadspeed offers spreadsheet utilities that operate on the workbook level, the worksheet level, and the range level. This page highlights the utilities that operate on the workbook level.

Create Table of Contents

The Table of Contents tool will insert a worksheet at the beginning of your workbook and list all the worksheets, full sheet charts, embedded charts, tables, and named ranges in the workbook. As the image below shows, you will be prompted with the Table of Contents listing and can opt to exclude any item from the output and choose the linking options.

Table of Contents Dialog

Table of Contents

Table of Contents Sample Output

The sample table of contents output below shows the links included to the side. Another thing to note is that worksheet-level named ranges are indented under ther parent sheet, whereas workbook-level named ranges are not, in order to highlight their scope.

Table of Contents Output

Sort Worksheet Tabs

If you have a small workbook, you can easily sort worksheet tabs by dragging and dropping them. but if the workbook is large with many sheets, the Sort Tabs tool is a fast way to rearrange them. The Sort Tabs dialog allows you to rearrange the tab order by clicking the up/down buttons, dragging and dropping rows, or clicking the column headers to sort by name or tab color. You can also double-click the Sheet Name items to edit and rename the tabs, if desired.

Sort Worksheet Tabs Screenshot

Sort Worksheet Tabs

Add Multiple Worksheets

The Add Multiple Worksheets dialog allows you to either enter in a list of sheets names, or import the sheet names from the current worksheet. If you import the sheet names from the worksheet, you can set the fill color for each cell, and it will automatically be assigned as the tab color. You can customize the color of any selected sheet using the color dialog shown below-right. Once the sheet names and tab colors are set, clicking OK will add the new worksheets to the end of the workbook.

Table of Contents Dialog
Add Multiple Worksheets
Color Dialog
Color Dialog

Remove Empty Worksheets

The Remove Empty Worksheets tool is a fast way to clean up large workbooks with unused worksheets. This tool iterates through each worksheet and identifies all the sheets with no content. On the dialog below, you can check any sheet that you want to delete and uncheck any you wish to keep.

Remove Empty Worksheets Screenshot

Remove Empty Worksheets

Quick Hide / Unhide Worksheets

As shown in the image below, the Quick Hide / Unhide Worksheets tool lists all the Visible and Hidden worksheets in the current workbook. Initially, only the current worksheets that are hidden are checked. When making sheets visible, this tool will unhide Hidden and Very Hidden sheets. When hiding sheets, it will set them to Hidden (but not Very Hidden).

Hide / Unhide Worksheets Screenshot

Hide / Unhide Worksheets

Save Worksheets to Separate Files

The Save Worksheets to Separate Files tool allows you to split out each worksheet and save it to a separate file. As shown in the dialog below, you can select which sheets to extract, set the file names, specify the output folder, and choose whether to remove the worksheet from the current workbook.

Save Worksheets Screenshot

Save Worksheets

Quick Protect / Quick Unprotect

The built-in protect tools in Excel allow you to protect the content of individiual sheets one at a time with Protect Sheet, or use Protect Workbook to protect the structure of the workbook (adding, removing, or re-arranging sheets). With the Spreadspeed Quick Protect tool, you can protect the content of all the sheets in a single action, plus opt to protect the workbook structure. Plus, you can leverage built-in and custom Cell Styles to have Spreadspeed automatically unlock cells for editing.

Quick Protect Screenshot

Quick Protect

Quick Unprotect Screenshot

The Quick Unprotect tool will allow you to unprotect the workbook or just the current sheet. (Please note that Spreadspeed does not store any passwords.)

Quick Unprotect

Close and Re-Open Last Save

The Close and Re-Open feature will close the current workbook without saving and re-open the last saved version. Microsoft Excel does not provide a mechanism for add-ins to tie into the Undo / Redo stack, so the Spreadspeed features that alter the contents of the workbook have a "Save Workbook" option. If you perform a Spreadspeed action that you'd like to revert, the Save and Re-Open tool will effectively act as an Undo.

Save a Backup Copy of Workbook

The Save a Backup Copy feature will save a time stamped backup of the workbook. The backups are saved to the same folder as the current workbook. The timestamp is in the form YYYY-MM-DD-hhmmss (year-month-day, followed by hour-minute-second).