Slide 1 [Conditional Formatting examples for repeated items in a list]

Slide 2 [Looking up a number by criteria of intersecting row and column labels]

Slide 3 [Using a formula to create a unique list from a larger list of duplicates]

Slide 4 [Comparing lists with Conditional Formatting]

Slide 5 [Stamping your changed cells with time and date]

Slide 6 [Case-sensitive lookup]

Slide 7 [Using the LOOKUP function for the last occurrence of a lookup criterion]

Slide 8 [Converting date-like values to true dates]

Slide 9 [Calculating elapsed time across midnight]

Slide 10 [Explaining Excel’s VLOOKUP function]

Slide 11 [Showing a reverse lookup using the VLOOKUP and CHOOSE functions]
Dana: Please do not change and to & in the title. The ampersand is a concatenation operator and can confuse users in this case.</font color=”red”>

Slide 12 [Doing a lookup using the INDEX and MATCH functions]
Dana: Please do not change and to & in the title. The ampersand is a concatenation operator and can confuse users in this case.</font color=”red”>

Slide 13 [Doing a reverse lookup using the INDEX and MATCH functions]
Dana: Please do not change and to & in the title. The ampersand is a concatenation operator and can confuse users in this case.</font color=”red”>

Slide 14 [Returning the row, cell address, and value of the last number or text item in a list]

Slide 15 [Custom formatting dates as months, days, and years]

Slide 16 [Counting unique values in one or more separate lists]

Slide 17 [Keyboard navigation shortcuts on worksheets with and without frozen panes]

Slide 18 [Transposing a two-column vertical list into a single-row horizontal list]

Slide 19 [Using the +0 coercer to change a text value to a valid date]

Slide 20 [Looking up the row, column, and address in a multi-row, multi-column range]

Slide 21 [Table of all possible fiscal monthly quarter numbers]

Slide 22 [Omitting unwanted characters from a string]

Slide 23 [Consolidating a range of data into a single list]

Slide 24 [Table of fiscal years by quarter]

Slide 25 [Averaging the last five numbers in a list with intervening blank cells]

Slide 26 [Stripping the last 5 characters from a string in Excel]

Slide 27 [Counting and summing data between dates in Excel]

Slide 28 [Conditional formatting top 5 scores (5 highest numbers) in a list]

Slide 29 [Returning a cell comment’s text]

Slide 30 [Lookup highest number in one column to return value in the cell on that same row in another column]

Slide 31 [Populating a ComboBox or ListBox with the 7 weekdays in custom format]

Slide 32 [Explaining the FREQUENCY function with a vertical array]

Slide 33 [Showing Excel’s SUMIF function]

Slide 34 [Explaining the VLOOKUP function in reverse]

Slide 35 [Explaining Excel’s HLOOKUP function]

Slide 36 [Explaining Excel’s INDEX and MATCH functions for forward and reverse lookups]
Dana: Please do not change and to & in the title. The ampersand is a concatenation operator and can confuse users in this case.</font color=”red”>

Slide 37 [Using a formula to create a unique sorted list from a larger list of duplicates]

Slide 38 [Looking up a cell address in a horizontal row, or in an entire potential range]

Slide 39 [Opening a UserForm in the top left corner of your Excel window]

Slide 40 [Finding the position and character of the first number in an alphanumeric string]

Slide 41 [Performing a mathematical operation without formulas or programming]

Slide 42 [Adding text with a custom format]

Slide 43 [Replacing a cell’s formula with its static value without copying or pasting]

Slide 44 [Concatenating a date with text in a readable date format]

Slide 45 [Keyboard shortcuts to select the entire active row and entire active column]

Slide 46 [Showing INDEX and MATCH lookups, forward and reverse, for last number and last text entries]
Dana: Please do not change and to & in the title. The ampersand is a concatenation operator and can confuse users in this case.</font color=”red”>

Slide 47 [Returning the value that has the most characters (greatest length) in a list]

Slide 48 [Summing cells at intervals of every “Nth” row; example: every 4 rows]

Slide 49 [Counting percentages within a numerical range]

Slide 50 [Returning the date of the previous or current Monday]

Slide 51 [Adding and subtracting times, same day and across midnight]

Slide 52 [Finding the absolute number farthest from zero]

Slide 53 [Counting Multiple Criteria]

Slide 54 [Automatically dismiss a message box after 3 seconds if OK is not clicked]

Slide 55 [Listing column letters]

Slide 56 [Showing the last number and last text, and their cell addresses, in a list with blank cells]

Slide 57 [Getting a month’s number from a month’s text name]

Slide 58 [Rounding up and down by the 1/8 fraction or 0.125 decimal]

Slide 59 [Using the MIN and MAX functions for VLOOKUP criteria]

Slide 60 [Using the MIN and MAX functions for reverse VLOOKUP criteria]

Slide 61 [Getting the primary folder name of your workbook]

Slide 62 [Deleting a table in a closed Access database from Excel]

Slide 63 [Populating a ComboBox with several fields]

Slide 64 [Creating a new PowerPoint Presentation from Excel]

Slide 65 [Variable Data Types for Excel]

Slide 66 [Identifying the used range when no start or end cell is known]

Slide 67 [Custom formatting your formulas with text]

Slide 68 [Boolean leap year tests]

Slide 69 [Showing the SUBTOTAL function on visible and filtered cells]

Slide 70 [Showing a reverse lookup for the last instance of a specified item in a list]

Slide 71 [Keyboard shortcut embeds a chart onto your worksheet]

Slide 72 [Averaging the n (3 in this example) highest and lowest numbers in a list]

Slide 73 [Averaging a list while omitting its n highest or n lowest numbers]

Slide 74 [Formatting numbers for customized abbreviations]

Slide 75 [Keyboard shortcuts remove one or many VBA breakpoints]

Slide 76 [Creating a unique list with a single VBA command line]

Slide 77 [Opening PowerPoint and running a presentation slideshow from Excel]

Slide 78 [Open a new Word document from Excel with this macro]

Slide 79 [Selecting more than one worksheet as a group]

Slide 80 [Exposing the first or last sheet tabs]

Slide 81 [Selecting all worksheets as a group, or deselecting all but one worksheet]

Slide 82 [Keyboard shortcut to select the next or previous worksheet]

Slide 83 [Using the YEARFRAC function for differences between dates as percentage of a year]

Slide 84 [Showing the DATEVALUE function to convert dates stored as text to actual dates]

Slide 85 [Using the DATEDIF function for elapsed time in years, months, and/or days]

Slide 86 [Ranking With Conditional Omission]

Slide 87 [Conditional summing or averaging based on partial strings]

Slide 88 [Macro to open or activate a Word document and copy its third paragraph to Excel]

Slide 89 [Formulas to return a workbook’s individual path, name, and drive components]

Slide 90 [This macro moves a worksheet’s embedded charts to their own chart sheet]

Slide 91 [Automating a comment that logs changes to your worksheet]

Slide 92 [Sorting worksheet tabs in ascending order by their object CodeName or tab name]

Slide 93 [Creating an Outlook email to attach the active workbook for multiple recipients]

Slide 94 [Separating a date and time in the same cell using custom formats with text or an asterisk]

Slide 95 [Wrapping a date and time in the same cell]

Slide 96 [Sorting dates by birthdays]

Slide 97 [Showing a column’s width, quick and easy]

Slide 98 [Right-clicking the status bar for additional edit, formatting, and analysis options]

Slide 99 [Clicking the dialog launcher in a group’s bottom right corner to show all its options]

Slide 100 [Opening Excel in a new instance — 2 options]

Slide 101 [New in Excel 2016: Showing the “Tell me what you want to do” feature]

Slide 102 [Referring to the Name box during range selection]

Slide 103 [New in Excel 2016: the Multi-Select icon for pivot table slicers]

Slide 104 [Summing a Range of Separate Pairs of Delimited Numbers]

Slide 105 [Returning the first and last name from a mixed-name entry]

Slide 106 [Verifying that the same value exists in a vertical and horizontal range]

Slide 107 [Using a formula to transpose a vertical range horizontally, maintaining reference to the source values.]

Slide 108 [Refreshing a time by 5 seconds]

Slide 109 [Forward and reverse lookups for the last occurrence of lookup criteria]

Slide 110 [Converting date-like values to true dates]

Slide 111 [Rounding a number to the nearest 0.5]

Slide 112 [Calculating portions of elapsed and remaining time per month]

Slide 113 [Evaluating if a time falls within a range of times]

Slide 114 [Evaluating if a number is a multiplier of another number]

Slide 115 [Conditionally Formatting every 2nd Friday]

Slide 116 [Converting a length notation from feet and inches into decimalized feet]

Slide 117 [Subtracting time backward across midnight]

Slide 118 [Expressing large numbers in rounded thousands or rounded millions]

Slide 119 [Placing a workbook’s last saved date and time into a worksheet cell]

Slide 120 [Hiding your named ranges but still work with them]

Slide 121 [Using VLOOKUP without a lookup table]

Slide 122 [Rounding time to a 30-minute mark]

Slide 123 [Populating a ListBox or ComboBox with formatted month names]

Slide 124 [Showing a date’s month formatted in upper and lower case]

Slide 125 [Comparing today’s date to a person’s birthday]

Slide 126 [Adding and naming worksheets for every day of a desired year]

Slide 127 [Using a VBA line of code to change a worksheet’s codename]

Slide 128 [Printing a closed Word document from Excel]

Slide 129 [Opening an Access database file from Excel]

Slide 130 [Emailing an active worksheet using Outlook SendMail]

Slide 131 [Identifying which Option Button was selected]

Slide 132 [Identifying your selected CheckBox(es)]

Slide 133 [Populating a UserForm’s ComboBox with noncontiguous items]

Slide 134 [Setting your ComboBox’s Style property to allow or limit entries]

Slide 135 [Using a CheckBox to toggle a ListBox’s visible source data]

Slide 136 [Using SUMPRODUCT to summarize multiple parsed criteria]

Slide 137 [Performing mathematical operations in a UserForm’s TextBox and Label controls]

Slide 138 [ Converting time from hours and minutes (hh:mm) to minutes [mm] ]

Slide 139 [Creating a web query to get the true current date and time]

Slide 140 [Flipping a contiguous list]

Slide 141 [Toggling worksheet range visibility and Form button caption]

Slide 142 [Putting a space between alpha and numeric characters]

Slide 143 [Finding the address and value of the first non-blank cell not containing 0]

Slide 144 [Verifying the name of your active printer]

Slide 145 [Advanced Filtering a list for “contains” and “does not contain” criteria]

Slide 146 [Summing a range while ignoring an error type]

Slide 147 [Filtering with wildcards for a character and its position (example, n is the 3rd character)]
Dana: Please do not change and to & in the title. The ampersand is a concatenation operator and can confuse users in this case.</font color=”red”>

Slide 148 [Defaulting the No button in a YesNo message box]

Slide 149 [Identifying protected cells with Conditional Formatting]

Slide 150 [Within a selected range, move clockwise to each corner cell with Ctrl+.]

Slide 151 [Verifying the author of a workbook]

Slide 152 [Using keyboard shortcuts to show the Insert and Delete dialog boxes]

Slide 153 [Returning a date’s day number of its calendar year]

Slide 154 [Showing the right-click worksheet menu without using the mouse]

Slide 155 [Getting the cube root of a number]

Slide 156 [Totaling the character count for many cells, not just one cell]

Slide 157 [Comparing text for a perfect case match]

Slide 158 [Inserting the registered trademark ® character with keyboard shortcuts]

Slide 159 [Toggling to hide (and keep) or show Subtotal groups with keyboard shortcut Ctrl+8]

Slide 160 [Calculating employee overtime pay]

Slide 161 [Summing a range by double-clicking the AutoSum icon, or with the Alt+= shortcut]

Slide 162 [Extracting the date and time from a NOW() function]

Slide 163 [Custom formatting negative numbers in square brackets]

Slide 164 [Custom formatting numbers for aligned decimals]

Slide 165 [Clicking the AutoSum’s drop-down arrow to show common (and more) functions]

Slide 166 [Inserting a space before the nth last character in a string]

Slide 167 [Toggling a keyboard shortcut to show or hide worksheet formulas]

Slide 168 [Entering a static date and time into a worksheet cell]

Slide 169 [Entering the current date or time using keyboard shortcuts]

Slide 170 [Verifying a range’s relative row of the cell containing the most characters]

Slide 171 [Conditionally formatting times for AM and PM]

Slide 172 [Summing numbers in even and odd numbered rows.]

Slide 173 [Custom formatting large numbers to be in thousands with a K or in millions with an M]

Slide 174 [Converting trailing negative signs to a true negative number]

Slide 175 [Returning a random number within a pair of numbers]

Slide 176 [Forcing a specific character to be included in a cell’s data entry]

Slide 177 [Conditionally formatting weekend dates]

Slide 178 [Summing multiple criteria with SUMIF]

Slide 179 [Returning various nth highest and nth lowest numbers in a range]

Slide 180 [Changing how many recent files to show]

Slide 181 [Counting numbers in a list between a numeric range]

Slide 182 [Identifying valid and invalid dates]

Slide 183 [Formatting 0 to look like “zero”]

Slide 184 [Conditional formatting cells for numbers within an allowable percentage range]

Slide 185 [Counting for a specific character and its case in a string]

Slide 186 [Verifying if a specified case-sensitive character is present in a cell]

Slide 187 [Showing the Find dialog box, defaulting to look in Values]

Slide 188 [Getting the percentage of weight gained or lost]

Slide 189 [Adding minutes to a time]

Slide 190 [Coloring cells with formula errors]

Slide 191 [Removing Subtotal groups with a keyboard shortcut]

Slide 192 [Measuring a circle]

Slide 193 [Measuring a sphere]

Slide 194 [Calculating roots]

Slide 195 [Changing fonts to a size other than those shown in the font size list ]

Slide 196 [Showing the Page Setup dialog box with a keyboard shortcut]

Slide 197 [Listing and going to other open workbooks]

Slide 198 [Showing the Windows Task Manager with a keyboard shortcut]

Slide 199 [Counting cells that contain a specified count of characters]

Slide 200 [Showing Excel’s legacy (pre-version 2007) data form with a keyboard shortcut]