MrExcel Excel Videos

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

You need to copy formulas but not screw up the formatting in cells My Method: Ctrl+C to copy, Paste Special, Formulas, OK Adam in Raleigh: Right-drag the formulas, choose Fill without Formatting

A question from a seminar in Raleigh: Need to fill the first and 15th of each month Or, need to fill the 15th and the last of each month Enter the first 2 dates. Select both dates. Right-drag the fill handle When you finish dragging, choose Fill Months

If we make a lump-sum payment against our mortgage, how will the number of payments change? First step: plug in current values from your mortage statement. Then: Use NPER function to figure out how many payments are left Use a one-variable Data Table to see the impact on future payments. Using the DATE function with a number of months Using DateDIF function to figure out the number of months between two dates

Steve has to sum numbers that have been entered in a text column There are multiple lines in each cell, separated by Alt Enter Need to split those lines to rows, then parse the dollar amount from the middle of each cell Summarize by Cost Center Build a lookup table Get totals from the lookup table, using IFNA to ignore the errors in the blank row Bonus: Add an Event macro to update the worksheet when they change a cell.

Check a column to see if there are any digits in the code Bill Method 1: Flash Fill Mike Method: Use the SUBSTITUTE function with an Array Constant. You will not have to use Ctrl+Shift+Enter because it is an array constant This will remove one digit at a time Use the AND function to see if every item in the resultant array is equal to the original item Bill Method 3: Use a VBA Function to check for digits