Excel Magic Trick 100: Instead of Long IF, Use MEDIAN function Commission W Hurdle Calculation
Add to EJ Playlist Formula from Mr Excel Challenge at Mr Excel web site. You will not believe your eyes: See how to replace a complicated IF formula with the MEDIAN function, or a MIN MAX formula. See 3 amazing examples. When you have a hurdle such a Commission Hurdle or a Payroll Taxable Earning Hurdle, and you are using an IF function formula, it may be easier to use the MEDAIN function in your formula. See a Payroll example, a Sales Commission Example and an extra-credit homework example. |
Excel Magic Trick #119: Is Item In List? 2 Examples
Add to EJ Playlist See how to determine if a particular item is in a list of items using two formulas: ISNUMBER & MATCH function formula & COUNTIF function formula. These tricks are from Aladin at the Mr Excel Message Board and djquackquack at YouTube. |
Excel Magic Trick #180: COUNTIF function formula 21 Examples of Different Criteria
Add to EJ Playlist See methods for counting: specific words, specific numbers, numbers greater than a hurdle, numbers between two values, values than are NOT equal to an amount, words that end in es, all words, all numbers, all content, all blanks, not words, and more! Learn about the syntax that the COUNTIF function uses and see different methods to achieve the same goal. See COUNTIF, COUNTBLANKS, COUNTA, COUNT functions |

Excel Magic Trick #184: Setup Database in Excel
Add to EJ Playlist See how to create a simple database in Excel using the List or Table feature. A simple database can be created in Excel using the Excel 2003 "List" feature or the Excel 2007 "Table" feature. Fields names must be in first row (no blanks). Records are in rows (no blanks). Other data in the sheet cannot be next to the Table/List (at least one blank row or column between other data and the Table/List Keyboard shortcuts: Excel 2003 List: Ctrl + L. Excel 2007 Table: Ctrl + T. The ranges are dynamic: formulas, pivot tables, charts will all automatically update |
Excel Magic Trick #183: Income Statement by Year & Month
Add to EJ Playlist See how to sum revenue from a table of data by year and month using the SUMPRODUCT & TEXT & SUM & IF & LEFT functions in an array formula. SUM items based on Two Conditions. SUM items based on two criteria. SUM items based on multiple conditions criteria. Also see these videos: YouTubersLoveEx cel#45: SUMPRODUCT & TEXT functions YTL Excel #130: Income Statement from Excel Table: Array For |
Excel Magic Trick #181: Count Words Case Sensitive
Add to EJ Playlist See how to Count Words Case Sensitive using the SUM and EXACT functions in an array formula. |

Excel Magic Trick #179: Are All Four Word The Same?
Add to EJ Playlist Determine whether four words in four cells are exactly the same (case sensitive). Use the IF, AND and EXACT functions in 2 different formulas: A long IF formula A short array formula. Check to see whether More than 2 two words are exactly the same (case sensitive). Compare Four 4 text strings to determine whether they are equivalent (exactly the same). |
Excel Magic Trick #178: Best Value Per 1 Unit Of Stats Value
Add to EJ Playlist Excel Magic Trick #178: Best Value Per 1 Unit Of Stats Value. See how to calculate the best value from two categories of values. Find the Best Dollar Value Assigned for Every One Unit Of Statistical Value. / is the division symbol in Excel. How to divide in Excel. Fantasy League Football Baseball Hocky Sport Spreadsheet analysis. |
Excel Magic Trick #177: Data Validation Trouble
Add to EJ Playlist See how to format a column with the Text Number Format to avoid Data Validation Trouble. Excel interprets 10-8895 as the date Oct, 8895 Excel interprets 0856 (if there is no custom format to allow leading zeroes) as 865 So if you have data validation based on those values, you can only use the drop down arrow to enter the values; you CANNOT type the values in You can type the values in if you format the column as Text. To apply Text Format: Ctrl + 1 opens the Format Cells Dialog Box, Number tab, then select Text The keyboard shortcut for Data Validation is Alt + D + L (2003) or Alt A + V + V (2007) Data Validation List Drop Down dropdown Arrow Date Format Formatting Number Formatting Format |

Excel Magic Trick #182: Workbook References
Add to EJ Playlist See how to create formulas with workbook references. See how to make a formula that looks at a range of cells in another workbook. Learn about the syntax used with workbook references. See how to update the workbook reference and how to fix it when the workbook references are broken (Change source file). The square brackets, [ and ], are what Excel puts around the workbook name. The apostrophes, ' and ', are what Excel puts around the workbook name and worksheet name. The explanation point, !, always comes after the worksheet name. By default, when you use a workbook reference, the cell reference is absolute (with two dollar signs). You can change it with the F4 key. When using workbook references save the two files in a permanent location so that the workbook with the workbook reference can be updates if necessary. To edit Links: 1) in Excel 2003, go to the Edit menu, Links; 2) in Excel 2007, go to the Office Button, Prepare, Edit Links. |
Excel Magic Trick #176 Get Last Item in Column on Diff Sheet
Add to EJ Playlist See how to get the last number or word from a column on a different sheet. See how to use the LOOKUP, OFFSET, MAX and ROW functions, as well as an array formula. See the Big Number Theory of the LOOKUP function (BigNum = 9.9999999999999 9E+307). See how to get the last value from column A from across multiple sheets using the LOOKUP and INDIRECT functions. |
Excel Magic Trick #175: Count Between Upper & Lower Value
Add to EJ Playlist See how to use the SUMPRODUCT function and an array of TRUE & FALSE to count values between an upper and lower bound. Also, see how to count the number of values between an upper and lower value using two COUNTIF functions. Also, see three other methods using the SUM and the IF functions. 5 different methods to count values between an upper and lower bound. Count values between Upper & Lower Value. Count values between an upper and lower bound. Count Values based on Two Conditions. Count Values based on two criteria. Count Values based on multiple conditions criteria. Count values between an upper and lower Limit. |

Excel Magic Trick #173: Time Over or Under (Negative Time)
Add to EJ Playlist See how to show Negative time with the "Use 1904 date system". See how to create a formula that will show the time over or under a hurdle. The IF function uses a logical test to put one of two things into a cell. The OR function allows you to have multiple logical tests and if any one (at least one) comes out to be TRUE, then the OR function delivers a TRUE. To add Custom Format, Ctrl + 1 (to open Format Cells Dialog Box), click on the number tab, click on Custom. To change the settings to "Use 1904 date system": 1) Excel 2003, go to Tools menu, Options, Calculation tab, then check "Use 1904 date system"; 2) Excel 2007: Office Button, Excel Options, then click on the Advanced tab on the left, then scroll down to "When calculating this workbook", then check "Use 1904 date system". |
Excel Magic Trick #174: Smartest Excel People In The World
Add to EJ Playlist See an incredible Excel Trick From Aladin at the Mr Excel Message Board. See how to lookup up the last value in an unsorted list. Learn about the LOOKUP function and how, like the SUMPRODUCT function, it does not require Ctrl + Shift + Enter to accommodate arrays. |
Excel Magic Trick #172: Running Total Formula or Pivot Table
Add to EJ Playlist See how to create a Running Total with a Formula or a Pivot Table. For formulas, the trick is to use the SUM function and lock one of the cells, but not the other. For example: =SUM(C$13:C13). For Pivot Tables, the trick is to add the field to the Values (or Data) area, and then right click, point to "Value Field Settings", then click on the "Show values as" tab, then select "Running total" from the "Show values as" drop down arrow, then select the "Base field" (the field in the Row area). |

Excel Magic Trick #171: MACRO Data Validation from 3 lists
Add to EJ Playlist Create a MACRO using Relative and Absolute cell references in order to create a Data Validation List from 3 separate lists on 3 separate (different) worksheets. A good video to watch if you want to see the logic of when to use a Relative Macro and when to use a Absolute Macro. Also see how to create a dynamic range using a named formula and the OFFSET function. Also see how to add data Validation list Drop Down Arrow based on a dynamic range using a named formula and the OFFSET function. |
Excel Magic Trick #170: Conditional Format To Hide Items
Add to EJ Playlist See how to use conditional format to hide or show items in a list using a TRUE FALSE formula. |
Excel Magic Trick #169: Randomly Select Employee From List
Add to EJ Playlist See how to randomly select employee from list using the INDEX and RAND and INT functions (also RANDBETWEEN functions). |

Excel Magic Trick #168: Cross Tabulation For a Survey
Add to EJ Playlist See how to summarize survey results with a Pivot Table (PivotTable) or a Formula. See how to create a Pivot Table in Excel 2003 or 2007. See how to create an Array formula that will count with two criteria (conditions). |
Excel Magic Trick 167p1 IF function formula: 12 Examples!
Add to EJ Playlist Part 1. See 12 different examples of how to use the IF function. This video starts with IF function basics and then shows 12 different examples of how the IF function can be used in formulas. Also see these functions: AND, OR, SUM, AVEARAGE, STDEVP, and VLOOKUP. See how to use the IF function to put: 1 of 2 numbers in a cell or formula. 1 of 2 words in a cell or formula. 1 of 2 text strings in a cell or formula. 1 of 2 formulas in a cell or formula. 1 of 2 functions in a cell or formula. 1 of 2 cell ranges in a cell or formula. How to have more than one IF (Nested IF Ifs). When to switch to the VLOOKUP function. How to build multiple criteria formulas with the IF function. How to use the AND function with the IF function. How to use the OR function with the IF function. Excel If statement Excel Magic Trick 165p2 MACRO Advanced Filter Extract B-days Other amazing videos at YouTube: Excel Trick, Making a Gantt Chart with Excel, Recording Macros - Excel 2003, VLOOKUP in Excel 2007. |
Excel Magic Trick 167p2 IF function formula: 12 Examples!
Add to EJ Playlist Part 2. See 12 different examples of how to use the IF function. This video starts with IF function basics and then shows 12 different examples of how the IF function can be used in formulas. Also see these functions: AND, OR, SUM, AVEARAGE, STDEVP, and VLOOKUP. See how to use the IF function to put: 1 of 2 numbers in a cell or formula. 1 of 2 words in a cell or formula. 1 of 2 text strings in a cell or formula. 1 of 2 formulas in a cell or formula. 1 of 2 functions in a cell or formula. 1 of 2 cell ranges in a cell or formula. How to have more than one IF (Nested IF Ifs). When to switch to the VLOOKUP function. How to build multiple criteria formulas with the IF function. How to use the AND function with the IF function. How to use the OR function with the IF function. Excel Magic Trick 167p1 IF function formula: 12 Examples! |

Excel Magic Trick # 166: Join Text: CONCATENATE function & Ampersand
Add to EJ Playlist Advantages and disadvantages of the Ampersand symbol and the CONCATENATE function (both join two (2) or more elements together into one text string). Also, see the DOLLAR and TEXT functions. The Ampersand symbol and the CONCATENATE function both join 2 or more elements together into one text string. When using the Ampersand (&), you must remember to out Text in Quotes (Double Quotes) and to add an & every time you join something or add something to the text string The DOLLAR function takes a number and formats it with Currency Number format and converts the number to text. The TEXT function converts a number to text and applies a number format that you specify |
Excel Magic Trick 165p2 MACRO Advanced Filter Extract B-days
Add to EJ Playlist Part 2 shows the MACRO and Advanced Filter and the Forms button. See the INDIRECT, ROW, TEXT and OR functions in a TRUE FALSE formula that will help to extract records of people who have a birthday in the next 14 days. See Advanced Filter and How to Create a MACRO and then add it to a Form button on the worksheet. Also see: From a beginning and ending date, see how to create a list of dates (serial numbers) in memory using the ROW and INDIRECT functions: Excel MT142 p2: Array Formula for Counting Dates. Start Advanced Filter From One Sheet and Go To Another sheet. |
Excel Magic Trick 165p1 MACRO Advanced Filter Extract B-days
Add to EJ Playlist Part 1 Shows TRUE FALSE formula for a Series of Dates using the ROW and INDIRECT functions. See the INDIRECT, ROW, TEXT and OR functions in a TRUE FALSE formula that will help to extract records of people who have a birthday in the next 14 days. See Advanced Filter and How to Create a MACRO and then add it to a Form button on the worksheet. Also see: From a beginning and ending date, see how to create a list of dates (serial numbers) in memory using the ROW and INDIRECT functions: Excel MT142 p2: Array Formula for Counting Dates. Start Advanced Filter From One Sheet and Go To Another sheet. Excel Magic Trick 165p2 MACRO Advanced Filter Extract B-days |

Excel Magic Trick # 164: CEILING function in Price Formula
Add to EJ Playlist See how to how to use the IF and CEILING functions in one formula to calculate the Price for shipping. The CEILING function is a great solution when you need to always round to a certain amount, like $0.50 ($0.50, $1.00, $1.50, etc.). |
Excel Magic Trick 163 Formula Return Descending Sorted List
Add to EJ Playlist Using an array formula, return a descending sorted list of country names based on the values in a row. See how to use the functions INDEX, MATCH, RANK, COLUMN, COLUMNS in one array formula. See how to break ties using the RANK function by adding a small amount to each value. Also, see: Excel Magic Trick #52: RANK function 1st to 5t. Extract and Sort records with array formula. |
Excel Magic Trick # 162: LARGE IF & INDEX w 4 Criteria
Add to EJ Playlist See how to how to extract the 10 largest values based on three criteria and then extract the product name for each large sale. See how to use INDEX and MATCH functions with 4 criteria. See how to deal with duplicates with the MATCH function by using the COUNTIF function. See the IF, OR, LARGE, ISERROR, MATCH, INDEX and COUNTIF functions in one array formula. |

Excel Magic Trick # 161: Go To Tricks Complete Story
Add to EJ Playlist See how to highlight all occurrences of an item or Excel element and then change them all quickly See how to how to Use Go To to select all occurrences of: Objects Comments Constants "Numbers" Constants "Text" Formulas "Numbers" Formulas "Logical" Formulas "Text" Formulas "Errors" Current Region Current Array Blanks Visible Cells Last Cell Data Validation Conditional Formatting See how to use Go To Blanks for the AVERAGE function and Pivot Tables See how to use Go To Visible Cells Only for Subtotals See how to delete all imported objects after you copy and paste from the internet!!! |
Excel Magic Trick # 160: Find & Replace Format or Formula
Add to EJ Playlist Find all the occurrences of a word, number, format or formula and then change or replace all of them! See how to use the Find and Replace feature in Excel. Find, Replace, Go To, GoTo, Find All, Replace All Change All Edit All. |
Excel Magic Trick # 159: VLOOKUP Duplicates 1st Column
Add to EJ Playlist Solve Duplicate VLOOKUP function problem with COUNTIF formula that creates a column of unique values for the first column in the lookup table. COUNTIF function & ampersand & concatenation to create column of unique values in first column of lookup table so that VLOOKUP function can deal with duplicates. Also see: Excel Magic Trick #148: Dynamic List from Table #3 |

NEWS Top Videos Music Classical Listen Funny Fails Artist List Aww Gaming Minisode Science Technology TED TWiT Trailers M.M. PBS WSJ AP CSPAN CNN RT Mox TMZ E! ABC CBS Politics Sports ESPN WTF Conspiracy