Excel Power Expander’s “Sheets Sorter” is a utility for sorting the sheets in your workbooks in order, base on the criteria you specify. This function was designed to be very flexible, and will definitely become an indispensable component of your Microsoft Excel.
“Sheets Sorter” is divided into two modes: Normal Mode and Advanced Mode. Normal Mode serves more basic sorting needs, with simple and commonly used sorting criteria. Advanced Mode serves the needs of more complex situations. It provides sophisticated sorting facilities which helps sorting your sheets in almost any way you desire.
To switch between the “Normal” and “Advanced” modes, click onto the buttons labeled “Normal” or “Advanced” on the bottom-left corner of the Sheets Sorter screen.
have a general overview of the Normal Mode and Advanced Mode of Sheets
“Normal” sorting lets you sort the sheets in the active workbook in any of the following orders:
“Normal” sorting criteria Remark 1. Ascending (alphabetical) order 2. Descending (alphabetical) order 3. Length of tab names (ascending) 4. Length of tab names (descending) 5. Reverse order Reverse the order of the sheets
(i.e. 1,2,3,4 to 4,3,2,1)
6. Random order Organize the sheets (in the currently active workbook) in a randomized order.
Below shows a sample screen of the Normal Mode of Sheets Sorter:
There is a checkbox labeled “Sort selected sheets only”. Checking this checkbox would cause Sheets Sorter to sort only the currently selected sheets. (Or in Excel terminology, the currently “grouped” sheets.)
In Advanced Mode, you can sort your sheets base on complex and more customized criteria. These criteria are grouped into three categories : Basic, Custom Lists, and Date Formats.
Criteria Category Descriptions 1. Basic • Ascending (alphabetical) order
• Descending (alphabetical) order
• Length of tab names (ascending)
• Length of tab names (descending)
• Sheet Types [*not available in “Normal Mode”]
• Reverse order
• Random order
In “Advanced” sorting, the “sort by alphabetical order” is more flexible. It allows the consideration of only part of the sheet names. e.g. the last 2 characters, or the 2nd to 5th characters, etc.
2. Custom Lists Sheets Sorter lets you sort your sheets by referring to Excel’s “Custom Lists”. (See Note 1)
The feature is directly linked to Excel’s “custom lists” function. It means you can expand its sorting power by expanding the library of Excel’s custom lists.
Besides, while sorting by custom lists, you can also choose to have a case-sensitive sort based on a custom list, and/or you can choose consider only part of a sheet’s name while sorting. This is especially useful when you’re sorting by Month (e.g. Jan, Feb, Mar) while the sheet names contains the Year as well (e.g. Jan 14, Feb 14, Mar 14).
3. Date Formats This is a sorting method pinpointing the convention spreadsheets are often named -- by Date. However, this category only deals with dates with numbers, e.g. 8_8_98, but not with text, e.g. 8 August 98. (The later could already be dealt with using a combination of criteria in other categories.)
Besides, while sorting the sheets by date, Sheets Sorter will handle 2-character years well. E.g. it understands that year 00 is year 2000)
By default, Sheets Sorter considers any years smaller or equals to 29 to be in the 21st century. But you can change this setting easily by using a dialog box:
(Note 1) For more information about Excel custom lists, please refer to Excel’s on-line help.
Like in “Normal Mode”, on the “Advanced Mode” screen, there is a checkbox labeled “Sort selected sheets only”. Checking this checkbox will cause Sheets Sorter to sort only the currently selected sheets. (Or in Excel terminology, the currently “grouped” sheets.)
Below shows a sample screen of the Sheets Sorter in Advanced Mode:
In the following sub-sections,
let’s see a couple of real-life examples
of how the Normal Mode and Advanced Mode of Sheets Sorter can be applied.
running Sheets Sorter and had your sheets sorted, you can reverse the sorting
choosing UNDO from Excel’s “Edit” menu.
Or, you can press the shortcut key Ctrl+Z to undo.
Please refer to the Excel Power Expander documentation Part I : Automation Guide, for demonstrations of the Sheets Sorter function.