MrExcel Excel Videos

Kaley from Nashville wants to "return all VLOOKUP results". Kaley asks: I need to do a VLOOKUP and return all of the matches, possibly inserting new rows. Kaley's data is a list of upcoming shows. Each show contains up to sixteen ticket types that must be loaded into a ticketing system. I will call this a VLOOKUP Explosion - because each show will explode into up to 16 rows. Power Query can solve this Monday Morning Quarterbacking: Add a Sequence Field to Ticket Type Make both data sets into a table with Ctrl+T Get Data, From Table, Close & Load to, Only Create Connection Get Data, From Table, Close & Load to, Only Create Connection Data, Get Data, Combine Queries, Merge Choose Events. Choose Tickets. Click on Ticket Type in Both...

Ian in Nashville gets data from the system. The date column has date and time. This really screws up the pivot table, because instead of daily dates, he gets time. One solution: Group the pivot table by Date Better solution: Power Query Make the downloaded data set in to a table with Ctrl+T Data, From Table. Select the Date Time column and transform to Date Close and Load Build the pivot table from that The next time you get data, paste to original table. Go to Query. Refresh. Refresh the Pivot Table To download this workbook: https://www.mrexcel.com/download-center/2018/05/date-time-to-date.xlsx List of upcoming seminars: Excel Seminar Schedule

Joy in Houston asks: Can I show the information selected in the slicer as a title above the pivot table? Historically, going back to Excel 2007, the Report Filter would say (Multiple Items) when you selected multiple items. What are the multiple items? Slicers are an improvement because you can see the items. But what if we want to get the Slicer values back above the pivot table, better than (Multiple Items) Build a pivot table and add a slicer. Copy the pivot table and paste it to the right. This makes the pivot table automatically connected to the slicer. Put only the slicer field in Rows. Delete the Grand Total row Use TEXTJOIN to put the slicer in a cell To download this workbook...

A question from my Nashville Power Excel seminar: How to deal with downloaded data where some cells contain trailing minus numbers? It is tempting to fix them manually or to use =IF(RIGHT(B2,1)="-",-1*LEFT(B2,LEN(B2)-1),B2) Text to Columns is a fast way to deal with this Choose the whole column Data, Text to Columns, Delimited, Next, Make sure there are no lines, Next, Options: Trailing Minus In fact, since Trailing Minus is the default, there is a faster way: Select whole column and Alt+D E F To download this workbook: https://www.mrexcel.com/download-center/2018/05/some-trailing-minus-numbers.xlsx List of upcoming seminars: Excel Seminar Schedule

You have a pivot table data source with daily dates. You would like to filter your pivot table by Quarter. Follow these steps: 1) Build a new pivot table 2) Put Dates in the Rows area 3) Group daily dates to Quarters 4) Move Date to the Report Filter 5) Build the rest of your pivot table 6) Optionally, add a slicer by quarter Thanks to Lindsey at my Nashville Seminar for the Lincoln Trail Council of the IMA. To download this workbook: https://www.mrexcel.com/download-center/2018/05/filter-by-quarter-in-pivot-table-with-daily-dates.xlsx List of upcoming seminars: Excel Seminar Schedule

Problem from my Atlanta Power Excel seminar for the Gulf South Council of the IMA Two columns of numbers. The AutoSum at the bottom of the second column stops working sometimes. The problem happens if you are inserting rows and sometimes leave out a number One best practice is to use a blank row between the AutoSum and the numbers. Always insert the new rows above that blank row. Pro Tip: Make the blank row small and change the fill color To download this workbook: https://www.mrexcel.com/download-center/2018/05/problem-with-two-autosum.xlsx List of upcoming seminars: Excel Seminar Schedule

Rick from NJ asks if one slicer can control pivot tables that come from multiple sources See episode 2104 for a VBA solution But this can be done with the Data Model First - delete any pivot table that are not based on the data model Find the field(s) in common between your two data sets. Copy each field to a new table and use Remove Duplicates to get a unique list of that field Make each of the data sets in to a table using Ctrl+T Build a relationship between each data set and the new Slicer Source table When you build each pivot table, choose Add this Data to the Data Model Build a Slicer - click the All tab - choose from the Slicer Source Figure out which pivot table is not tied to the slicer. Select a cell in that pivot table and...

Alex from Houston asks: Can you do a Median in a Pivot Table The traditional answer is No! But thanks to the Data Model, you can create a Median Ctrl+T to make your data into a Table Insert, Pivot Table, Add this Data to the Data Model Right-click the Table name and choose New Measure The Measure will be =MEDIAN([Sales]) To download the Excel workbook used in this video, see the link near the end of Pivot Table Median

The Pivot Table tools in Excel offers Drill Up and Drill Down buttons that are always greyed out. How can you use those? You have to find someone who has the Power Pivot tab in their Excel (Office 365 E3 or Office 365 Pro Plus) Power Pivot, Manage, Diagram View. Select fields for hierarchy. Right click and Create Hierarchy. If you don't have Office 365, you can download a workbook with a hierarchy from Creating a Hierarchy in a Pivot Table