MrExcel Excel Videos

How to do a two-way lookup during import in Excel Power Query. Sweeps URL: Microsoft Forms Recorded at Ignite 2019 in Orlando, this video shows you how to do a two-way lookup when merging files in Power Query - even if the fields are in the "wrong" order.

The MSFT Sweeps URL: Microsoft Forms Thanks to Matt Allington for this technique. Check out Matt's book: Supercharge Power BI with DAX book Today's video: How to combine data from multiple worksheets using Power Query. Combing multiple workbooks is easy, but combining Sheets is now possible, even though the method is mostly hidden. Thanks to Matt Allington, see how to solve this common problem.

A new feature announced at #MSIgnite and now available to Office Insiders: Excel can use Artificial Intelligence to Answer Questions About Your Data.

The URL in the video is Microsoft Forms While importing data into Power Query, you want to VLOOKUP to a summarized version of the data. In this video, the first step is a quick query to produce total revenue by region as a lookup query. Then, when you merge in Power Query, you will VLOOKUP to bring the Region total to each row, so you can calculate that row's percentage of region. This video debuts the Group By feature in Power Query.

URL in video: Microsoft Forms Recorded Live at Ignite 2019 in Orlando: How to import a file using Excel Power Query while doing a VLOOKUP during import.

Here is a look at the final version of XLOOKUP, along with 10 benefits of XLOOKUP over VLOOKUP, HLOOKUP, LOOKUP, INDEX/MATCH. Table of Contents (0:00 Welcome and Introduction to Excel XLOOKUP Deep Dive (0:25) List of Benefits (2:30) First XLOOKUP (3:02) 3rd argument is not an integer (3:29) Handle items not found (3:51) Range lookup find value just less like VLOOKUP(,,,True) (4:41) Find value just larger like MATCH - without sorting (5:16) HLOOKUP replaced by XLOOKUP (5:30) Search from bottom of table (6:21) Wildcards in XLOOKUP versus VLOOKUP (6:56) Return all 12 months from table with single formula (7:38) XLOOKUP can return a cell reference XLOOKUP:XLOOKUP (9:10) Two-Way Match (10:09) Sum All lookups (10:44) Twisted XLOOKUP (11:13)...

Today, November 3, 2019, the function arguments for XLOOKUP changed. The if_not_found argument moved from 6th to 5th. That means any old XLOOKUP functions that used arguments 4, 5, or 6 might break at the next Recalc.

This is a recording of the October 2019 monthly webinar in the Excel MVP Webinar series. In this free 59-minute webinar, Bill Jelen covers many pivot table techniques: Table of Contents (0:00) Welcome to the Pivot Table Webinar (1:36) Traditional Pivot Cache Pivot Tables (2:45) Setting up data for a successful pivot table (3:40) Using Alt+Enter for headings (4:00) Avoiding tiny blank columns between columns (4:15) Accounting Style Underlines (5:35) Pivot Table 101 – Your First Pivot Table (7:55) Changing Number Format in Pivot Tables (8:35) Moving fields around in a pivot table (9:40) Drill-down on any pivot table number (10:45) Refreshing pivot table if the underlying data changes (11:25) Year-over-Year Pivot Tables – Grouping Dates...

Vote for the improvement to Filtering: https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/31201495-filtering While thinking about the Filter problem, I decided to check to see if the data profile improvements had been added to Power Query in Excel, and they are! You can now see how many distinct and unique items there are in each column.