MrExcel Excel Videos

Frank from NJ downloads data where the "empty" cells contain some number of spaces Frank wants to replace the spaces with truly empty cells. Writing a formula and then Paste Values does not give us empty cells (Look in the status bar to get a Count of how many non-blank cells there are) Excel really needs to offer a =NULL() function to return empty cells My solution: Filter. Choose Blanks. Select. Press Delete To download this workbook: https://www.mrexcel.com/download-center/2018/05/replace-spaces-with-empty.xlsx

Quentin needs to generate an identical survey for 1000 different customers. He wants to repeat the 8 survey questions for each customer. While you could do this with VBA or a macro, this is Power Query week, so here is the PQ method. Add an extra blank question to the questions. Make the customers in to a table. Make the questions in to a table. Add the Questions to Power Query as a Connection Only Add the Customers to Power Query. Create a new custom column where the formula is #“Query B” Expand the column in the Power Query editor Close & Load To download this workbook: https://www.mrexcel.com/download-center/2018/05/loan-survey-explosion.xlsx List of upcoming seminars: Excel Seminar Schedule

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