Tom’s Tips For Excel, Volume 1 (workbooks 1 to 100)


• You get 2 complete sets of the first 100 TTFE workbooks, all interactive with formulas and code, most with bonus features.
• You get a searchable index with hyperlinks to the workbooks.
SPECIAL OFFER – 2 FREE XAT (Excel Aptitude and Skills Tests), a $40 value. One test for you, and the other (hint, hint) to show XAT FAQs to your employer, school, Excel trainer, or placement agency. An email will be sent to you with your log-in credentials.
• All workbooks are of the Macro Enabled .xlsm extension.
Try before you buy! Workbooks 28, 50, and 53 are free.
Tips before downloading, to avoid 2 possible false errors:
(1) Check your Protected View settings. From the File tab select Options > Trust Center > Trust Center Settings > Protected View. Deselect the check boxes and click OK.
(2) Download to your desktop or folder, and extract from there, instead of detaching and opening the workbooks directly from your browser, to avoid the false “path is too long” error.
This is Volume 1, with many more volumes of 100 Excel examples each that will be available during 2021 and 2022.


TTFE Workbook Name
Index_Search_TTFE_Volume1.xlsm (a list of all workbooks with their searchable keywords, and hyperlinks to those workbooks)
TTFE_0001_Conditional Formatting for repeated items in a list.xlsm
TTFE_0002_Looking up a value by its intersecting row and column labels and Conditionally Formatting the lookup result.xlsm
TTFE_0003_Using a formula to create a unique sorted list from a larger list of duplicated entries.xlsm
TTFE_0004_Comparing items in two lists using Conditional Formatting.xlsm
TTFE_0005_Entering the date and time of changes to your worksheet cells.xlsm
TTFE_0006_Case-sensitive lookup.xlsm
TTFE_0007_Using the LOOKUP function in forward and reverse for the last entry of a lookup criterion.xlsm
TTFE_0008_Using the SUBSTITUTE function to convert date-like values to true dates.xlsm
TTFE_0009_Adding and subtracting times in the same calendar day or across midnight.xlsm
TTFE_0010_Explaining the VLOOKUP function.xlsm
TTFE_0011_Using the CHOOSE function with the VLOOKUP function for a reverse (right to left) lookup.xlsm
TTFE_0012_Doing a forward (left to right) lookup using the INDEX and MATCH functions.xlsm
TTFE_0013_Doing a reverse (right to left) lookup using the INDEX and MATCH+ functions.xlsm
TTFE_0014_Returning the value, row, and cell address of the last number and text entry in a vertical (column) list.xlsm
TTFE_0015_Showing options to custom format dates as months, as days, and as years.xlsm
TTFE_0016_Counting unique values in one or more separate lists.xlsm
TTFE_0017_Returning each weekday’s first and last dates for any month in any year.xlsm
TTFE_0018_Transposing a 2-column range of data into a single row or a single column.xlsm
TTFE_0019_Using the +0 coercer to change a text value to a valid date.xlsm
TTFE_0020_Looking up the row, column, and address in a multi-row, multi-column range.xlsm
TTFE_0021_Showing a table of all possible fiscal monthly quarter numbers, using just one formula.xlsm
TTFE_0022_Summing and averaging the last 8 (for example) numbers in a list while ignoring empty cells.xlsm
TTFE_0023_Showing SUMPRODUCT and SUMIFS examples for counting and summing between dates.xlsm
TTFE_0024_Returning a cell’s comment text with a user-defined function.xlsm
TTFE_0025_Looking up the MIN and MAX numbers to return a value in their same row of another column.xlsm
TTFE_0026_Populating a ComboBox and ListBox without using a loop or the RowSource property.xlsm
TTFE_0027_Showing the FREQUENCY function to count values that occur within a range of values.xlsm
TTFE_0028_Showing a reverse (right to left) lookup using VLOOKUP with an IF function and array constant
TTFE_0029_Identifying the last row, last column, and addresses of a worksheet’s data-only range, and its used range.xlsm
TTFE_0030_Custom formatting to show prefix text in a text-containing cell and in a formula-containing cell.xlsm
TTFE_0031_Summing or averaging cells at intervals of every Nth row, for example every 4 rows.xlsm
TTFE_0032_Counting percentages within a numerical range.xlsm
TTFE_0033_Automatically dismissing a message box.xlsm
TTFE_0034_Using a formula to transpose a vertical range horizontally, maintaining reference to the source values.xlsm
TTFE_0035_Calculating the portions of elapsed and remaining for any month.xlsm
TTFE_0036_Evaluating if a time falls within a range of times.xlsm
TTFE_0037_Evaluating if a number is a multiplier of another number.xlsm
TTFE_0038_Using the LOOKUP function without a lookup table.xlsm
TTFE_0039_Toggling a worksheet’s range visibility, and a Form button’s caption and font color.xlsm
TTFE_0040_Defaulting the No button in a YesNo message box.xlsm
TTFE_0041_Identifying protected cells with Conditional Formatting.xlsm
TTFE_0042_Calculating employee overtime pay.xlsm
TTFE_0043_Conditionally formatting times for AM and PM.xlsm
TTFE_0044_Custom formatting large numbers to be in thousands with a K or in millions with an M.xlsm
TTFE_0045_Using a formula to convert trailing negative signs to true negative numbers.xlsm
TTFE_0046_Bubble-sorting a ListBox at Initialization, without touching the source data.xlsm
TTFE_0047_Bubble-sorting, by a specified field, a multi-column ListBox at Initialization, without touching the source data.xlsm
TTFE_0048_Dynamic data validation – adjusting the drop-down list depending on another cell’s data validated selection.xlsm
TTFE_0049_Showing a vertical divider between ListBox fields without affecting the worksheet or source data range.xlsm
TTFE_0050_Double-clicking any cell in a range of data to toggle-sort the range by ascending or descending order of that cell’s column
TTFE_0051_Coloring the selected cell as you move about the spreadsheet.xlsm
TTFE_0052_Coloring the single selected cell, and its entire row or column, as you move about the spreadsheet.xlsm
TTFE_0053_Coloring the single selected cell’s row or column within its current region
TTFE_0054_Formatting a contrasting color for partial text between specified characters.xlsm
TTFE_0055_Listing incremental cell values into a contiguous range.xlsm
TTFE_0056_Getting your desktop’s directory path.xlsm
TTFE_0057_Saving and closing all open workbooks (in this instance), except this workbook.xlsm
TTFE_0058_Closing a single specific workbook if it is open in this instance.xlsm
TTFE_0059_Listing which embedded ActiveX CheckBoxes are checked, if any.xlsm
TTFE_0060_Showing a class module example to monitor 12 text boxes in a UserForm for numeric entry only.xlsm
TTFE_0061_Creating a new PowerPoint Presentation and adding a Slide to it, all from Excel.xlsm
TTFE_0062_Finding your worksheet’s used range; it doesn’t always start in cell A1 or end in a predictable cell.xlsm
TTFE_0063_Identifying which OptionButton was selected.xlsm
TTFE_0064_Verifying the author of a workbook.xlsm
TTFE_0065_Listing a few rules and tips for VBA variable names.xlsm
TTFE_0066_Preventing the addition of new worksheets if you don’t want to protect the entire workbook.xlsm
TTFE_0067_Controlling the times of day a workbook can be saved.xlsm
TTFE_0068_Showing a UserForm at your preferred position on the monitor.xlsm
TTFE_0069_Stripping the last 5 characters from a string, using a formula.xlsm
TTFE_0070_Stripping the last 5 characters from a string, using a VBA User-Defined Function and a macro.xlsm
TTFE_0071_Extracting numbers, letters, and special characters from a mixed alphanumeric string.xlsm
TTFE_0072_Showing the API calls and syntax for a macro to call and work with a modeless (floating) Message Box.xlsm
TTFE_0073_Showing Excel VBA’s UNION method to simultaneously select (or operate upon without selecting) non-contiguous ranges.xlsm
TTFE_0074_Automating your SpinButton to revert to its next valid value within a min and max numerical range.xlsm
TTFE_0075_Showing a macro that plays 3 Windows Media sounds with message boxes.xlsm
TTFE_0076_Showing how to stop seeing those annoying menu floaties.xlsm
TTFE_0077_Disabling the pop-up menu when right-clicking onto a worksheet tab.xlsm
TTFE_0078_Parsing a string to the left and to the right of the position of the first numeric character.xlsm
TTFE_0079_Showing an original 2-column list as a 1-column list by its columns or rows, or as a 2-row list of paired items.xlsm
TTFE_0080_Using the HYPERLINK function that takes you to the first or last empty cell in a range.xlsm
TTFE_0081_Using VBA’s ENVIRON function to list your PC’s operating environment information.xlsm
TTFE_0082_Converting text values to a numerical value scale.xlsm
TTFE_0083_Limiting a UserForm’s ListBox to a specified count (3 in this example) of selected items.xlsm
TTFE_0084_Automatically updating and sorting a comment’s text when a new unique word or name is added to a list.xlsm
TTFE_0085_Using the INDEX, MATCH, and CELL functions when the lookup value and the lookup range are in the same column.xlsm
TTFE_0086_Converting a column letter to its number, and from a column number to its letter.xlsm
TTFE_0087_Consolidating data from its range of multiple columns and rows into a single list ordered by column or by row.xlsm
TTFE_0088_Showing the keyboard shortcuts to select the active cell’s entire row, or to select its entire column.xlsm
TTFE_0089_Getting a month’s number from a date, and from a non-date text month name.xlsm
TTFE_0090_Rounding up and down by the one-eighth fraction or 0.125 decimal.xlsm
TTFE_0091_Using the MIN and MAX functions for VLOOKUP criteria.xlsm
TTFE_0092_Using the MIN and MAX functions for reverse VLOOKUP criteria.xlsm
TTFE_0093_Averaging the ‘n’ (3 in this example) highest and lowest numbers in a list.xlsm
TTFE_0094_Using the DATEDIF function for elapsed time in years, months, and days.xlsm
TTFE_0095_Using a VBA macro to sum many columns with varying entries on the single next available row.xlsm
TTFE_0096_Using a VBA macro to sum many columns with varying entries on the next available row of each column.xlsm
TTFE_0097_Comparing text for a perfect case match.xlsm
TTFE_0098_Counting for a specific character and its case in a string.xlsm
TTFE_0099_Verifying if a specified case-sensitive character is present in a cell.xlsm
TTFE_0100_Defaulting the Find dialog box to look in Values.xlsm


There are no reviews yet.

Be the first to review “Tom’s Tips For Excel, Volume 1 (workbooks 1 to 100)”

Your email address will not be published. Required fields are marked *