Error! Not a valid filename.

Strip

Remove all formatting, delete empty columns and rows, generally remove any distraction from the calculation structure of the workbook.

Reveal

A good first step is to unhide, unprotect all sheets and data.

UnhideAllSheets

Use the macro UnhideAllSheets to unhide all sheets in the workbook.

I often find hidden worksheets that have been lying around for months, even years, where the current owner of the workbook received it in that state, and was unaware of the volume of data lurking there.

SortSheetsAscending

Use the macro SortSheetsAscending to sort the worksheets in ascending sequence from left to right.

Why do this?

I find it useful when I need to co-ordinate a set of workbooks and bring them to a consistent format and layout. Sorted I can predict where my ctrl-PgUp and Ctrl-PgDn will take me.

UnprotectAllSheets

Use the macro UnprotectAllSheets to remove protection from all worksheets in the workbook.

What!?

Some of them require a password?

Try an empty string, then try “password”.

• Then call the client before everybody leaves for the weekend.

UnOutlineEachSheet

Use the macro UnOutlineEachSheet to remove outlining. We can always return to the original worksheet if we feel the outlining might tell us something, otherwise we are better off stripping away all the cosmetics and getting down to bedrock.

UnfreezeEachSheet

Use the macro UnfreezeEachSheet to remove any frozen panes. We can always return to the original worksheet if we feel the outlining might tell us something, otherwise we are better off stripping away all the cosmetics and getting down to bedrock.

UnSplitEachSheet

Use the macro UnSplitEachSheet to remove any frozen panes. We can always return to the original worksheet if we feel the outlining might tell us something, otherwise we are better off stripping away all the cosmetics and getting down to bedrock.

Clear

We eliminate distractions from the cells.

ClearCosmeticFormattingWorkSHEET

Clear all local formatting in a worksheet by running the macro ClearCosmeticFormattingWorkSHEET.

• Alignment is set at Left.

• Alignment is set to General

• Borders are cleared.

• Font color is set to Automatic

• Font enhancement is set OFF

• Font is set to "Arial", 10 point;.

• Interior Color is cleared.

• Number Format is set to "General".

• Orientation is set to Normal.

• Text wrapping is turned OFF

ClearCosmeticFormattingWorkBOOK

Clear all local formatting in all worksheets of a workbook by running the macro ClearCosmeticFormattingWorkBOOK.

ClearSpacesLeading

Trim leading spaces off all pure-text cells in the selection.

ClearSpacesMultiple

Trim redundant spaces from all pure-text cells in the selection.

ClearSpacesTrailing

Trim trailing spaces off all pure-text cells in the selection.

ClearSpaces

Trim leading, trailing, and redundant spaces off all pure-text cells in the selection.

ClearZeroFormulae

For each cell in the selection, if the cell contains a formula that evaluates to zero, the cell contents are replaced with an empty string.

Delete

We delete extraneous cells and rows.

DeleteNullColumns

If all cells in any column in the selection are null, the column(s) will be deleted.

Null is defined here as either the Excel function COUNTA returning zero OR the sum of the absolute values of all cells being zero.

DeleteNullRows

If all cells in any row in the selection are null, the row(s) will be deleted.

Null is defined here as either the Excel function COUNTA returning zero OR the sum of the absolute values of all cells being zero.

Other

DeleteUnusedRangeNamesInWorksheet

StainWorksheetClear

UnStainWorkbook

DeleteAllRangeNames

DeleteStylesUser

UnlockUserStyles

Search