MrExcel Excel Videos

From a popular certification test - how to do a VLOOKUP when column A is in outline format Advice from Bill: Anything on that test is not that difficult! Bill's Method: Do the straight VLOOKUP Wrap the VLOOKUP in the IFNA function. If you get an #N/A, then use the value from above This method only works in Excel 2013 or newer. In Excel 2010, use IFERROR instead. Mike's Method: Inside the VLOOKUP function, use the LOOKUP function to find the last text in an expanding range. LOOKUP ZZZZZZ so it will return the last non-blank item Use an expanding range to find the last text value in the column down to this point In Mike's second method, he shows where trailing spaces will cause Bill's formula to fail. Mike's second method uses ISBLANK...

How to delete hundreds of tiny blank columns in Excel Method 1: Delete first column and then use F4 a lot Method 2 from Carmella in Green Bay: Select a row, Go To Special Blanks, then Delete columns Method 3: Sort Left to Right

Nick from Utah wants to show the dates chosen from a pivot table Timeline to a readable title. Copy the pivot table that is already tied to the timeline and paste it out of view Change that pivot table to have the date field twice in the Values area Double-click each heading to get to Field Settings. Choose Min in the first cell and Max in the second cell. Use the text function to concatenate together a useful heading

Megan in Fort Myers Florida: How to add subtotals by Month when the data is stored by date? This episode shows two methods: Add a new column to the left of Date Use the TEXT function with a second argument of "MMMM YYYY" Add Subtotals by the new column To move the Subtotals back to the Data column: Collapse to #2 view, Select blanks in date column, Alt+; to select visible cells = Left Arrow Ctrl+Enter Go back to #3 View Method #2 Temporarily change number format for dates to show only month Add Subtotals by Date - you will get one total per month Change number format back to short date

Rebecca in Springfield MO asks: How to move the subtotals right one column? Sort the data by account Data, Subtotals, At Each Change in Account, Sum the Amount Use the #2 group and outline button to show only the totals Select all of the blanks in one column to the left of the subtotals Use Home, Find & Select, Go To Special, Visible Cells Only or Alt+; Home, Insert, Cells, Shift Cells Right Click the #3 Group & Outline button

Build a Sales Report by Region and Team Original data has sales rep and region A second (badly shaped) table organizes the sales reps in to teams Bill method 1: Re-shape the team hierarchy data. Make both ranges into Ctrl+T tables Create a pivot table, adding the data to the data model. Pull Team from second table. Create a relationship Mike Method2: Build a SUMIFS where the Criteria2 field is an array! Pass the SUMIFS in to the SUMPRODUCT function Bill Method 3: Rearrange the hierarchy table so sales rep is on the left. Add a VLOOKUP to the original data Build a pivot table Mike Method 4: Use the Relationship icon on the Data tab of the ribbon When you create the pivot table, choose Use this Workbook's Data Model Bill Method 5: Power...

I was in Lakeland Florida at Florida Southern College for an Excel seminar There are five ways to do everything in Excel. For example: If you need tiny columns between the columns, you could do Alt+I C Alt+O C W 1 Enter over and over and over But the F4 key will repeat the last action. By breaking the task in to two pieces, you can do Alt+I C once and then repeatedly hit LeftArrow F4 until all the columns are inserted Then, do Alt+O C W 1 Enter once and repeatedly hit RightArrow RightArrow F4 until all columns are smaller But Lacie had a faster way: Ctrl+Click each column and Alt+I C once That will insert a column between each column Ctrl+Click each new column and Alt+O C W 1 once You could turn this trick sideways and use in to...

How to calculate a Golf Handicap in Excel You have your recent golf scores in Excel The handicap is the average of the lowest 10 scores in the last 20 rounds Getting the last 20 is easy thanks to Excel Getting the smallest using MIN But… to get the 2nd smallest through 10th smallest, use SMALL() function. Slow but understandable: =SMALL(,1)+SMALL(,2)+SMALL(,3)…+SMALL(,10) divided by 10 Easier to enter: Array constant {1;2;3;4;5;6;7;8;9;10} as the second argument of SMALL This will return all 10 values at once Send that in to the AVERGE function: =AVERAGE(SMALL(A1:A20,{1;2;3;4;5;6;7;8;9;10})) My fast way for typing the array constant: Type a 1 in a cell. Ctrl+Drag the fill handle from the 1 down to get 2 through 10. Point a formula at...

Question from Jon: Do a SUMIFS that only adds the visible cells Bill's first try: Pass an array into the AGGREGATE function - but this fails Mike's awesome solution: SUBTOTAL or AGGREGATE can not accept an array But you can use OFFSET to process an array and send the results to SUBTOTAL. Use SUMPRODUCT to figure out if the row is YES or not. Bill's second try is like checking to see if the refrigerator light goes out when the door closes: Add a helper column with AGGREGATE of a single cell in each row Point the SUMIFS at that column