MrExcel Excel Videos

Tad from Springfield IL asks: How can I filter to 27 specific invoice numbers? With a large data set, scrolling to find the 27 invoices will not be fun. Searching for each invoice and remembering to click Add to Selection will not be fun. Instead - use the Advanced Filter Set up a criteria range… First cell contains the Invoice heading. List the invoices vertically below. Choose one cell in the data set. Data, Advanced Filter Filter in Place Use a Criteria Range Select the criteria range including the heading Click OK

R.A. asks? Can you filter data that has been subtotaled? How can you get the subtotals to appear at the top of each group? When you create the subtotals, uncheck Summary Below Data Can you use a Filter in a data set that has been subtotaled? Yes - but you should include blank cells as well How can you get the percentage calculation to work in the subtotal rows? Right now, the percentage is totaling the other rows instead of doing the calculation After adding subtotals, copy the percentage calculation from a non-subtotaled row to all other rows.

Rhonda from Cincinnati: How to look up both row and worksheet? Use the Date column to figure out which sheet to use Step 1: Build a regular VLOOKUP and use FORMULATEXT to see what the reference should look like Step 2: Use Concatenation and the TEXT function to build a reference that looks like the table array reference in the formula Step 3: Build your VLOOKUP, but for the table array, use INDIRECT( results from step 2) Step 4: Copy the formula from Step 2 (without the equals sign) and paste in to the formula from step 3

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...