Excel Utilities - Workbook Tools

Page 2 of 4

Workbook Tools

Open files: is an organizer used for storing shortcuts to commonly used files and folders.
Save Copy: simply saves a copy of the open file, without switching the file.
This differs from the 'save as' function which closes the open workbook and saves the workbook as a new file.
Delete Names: Use with caution as this function will clear all names and named ranges in the active workbook..

Sheet Tools

Protect: provides the option to protect and unprotect an open workbook and its sheets by using a stored password. The tool also hides or unhides multiple sheets with a single click. Sheets can either be set to hidden or very hidden. A very hidden sheet is not displayed as a hidden sheet using Excels Unhide function on the sheet tab menu. This tool will list any very hidden sheets within a workbook. If the workbook is unprotected, then the sheets can be displayed using this tool.

Navigate: helps to navigate across large spreadsheets and to maintain a visual on selected rows. It works by listing column header names and activating a guide by selecting the desired sheet row cell. The guide comprises two adjustable lines that can be set to a fixed placement or move as required.

Bookmark: creates a table of contents based on sheet-tab names with bookmark-links to each sheet as well as return links. The tool also provides sorting options for the contents list and sheet order.

Selector: displays and lists open workbooks as well as sheet names providing the means to easily navigate between open workbooks and sheets.

Column Tools

Hide: checks for a background cell colour on row one column headings to determine the columns to hide. Columns without a background colour will be hidden.

Unhide: makes visible any hidden columns on the open worksheet

Select: will select cells top-down to cover the data range within one or more adjacent columns. The selection will ignore blank cells and cover the complete data range. whereas the ctl+shift+down-arrow selection key method will only extend the selection as far as the first blank cell.

Row Tools

Remove Blanks: removes blank or empty rows on the selected worksheet.

Insert on Change: separates sheet data when values change within a selected column. For example, applying 'Insert on Change' on a sorted vendor or buyer column will insert a blank row between different buyers or vendors. The blank row height and colour can be preset. Decreasing the row height and colouring the row, makes it appear as a line separating the report data. Run remove blanks to redo row height or colour.

Create Tabs: by selecting a single column with common recurring values, such as buyers or vendors. Create tabs will copy and separate the buyers and vendors onto separate tabs without changing the original worksheet.

Copy Transpose: copies selected sheet data and converts column data to row data and row data to column data and outputs the result onto a new worksheet.

Copy Section: after selecting one or more adjacent cells in a column, 'Copy section' will copy to the right, including the selected cells to the last cells within the selected row(s)

Cut Section: Similar to 'copy section' but uses cut instead

Paste Section: is a floating paste button which can be used in conjunction with either cut or copy section.

Cell Tools

Edit Cells: provides a means to locate and insert or delete text within selected cells. There is an undo operation available on the last change, so to be safe have a backup. When inserting a word within text use the space bar when a gap between words is needed. Use the undo function and reinsert if not sure where space should appear.

Tracker: is used for keeping track of position, while working through a list. When activated by double-clicking the cell, the tool will colour the cell and move to the next cell in a designated order and direction. When the copy function is turned on the next selected cell will be copied.


Hide Cells: is a format function that hides the cell contents of a selected range of cells from view. When the format is applied, values can only be seen within the formula bar. The formula bar can be hidden or unhidden using the form. Right-click on the hidden cells and select format cells to view the custom cell format code.