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]
