MrExcel Excel Videos

Episode 2490: The Android phone has had it for a long time, but the new Data From Picture has finally reached the Office Insiders beta channel on desktop Excel. Bill gives this new feature a spin with various pictures of data. Table of Contents (0:00) Speech Recognition 1989 to today (1:10) From Picture near Power Query tools (1:35) Creating data picture (2:00) Data From Picture no gridlines (3:40) Cleaning the results (4:15) The numbers all match (4:30) Gridlines don't help (5:35) Right-align headings (7:20) Need to proof (7:50) With real photo test 1 (8:40) From clipboard test 2 (10:03) This will improve

A question from Elijah at the UCF Accounting Conference. I was showing how to use the UNIQUE function to get a list of products. Elijah asked if I could then get the total sales for each product. Plus a heading row and a total row. With the items sorted high to low. The final result uses several new Excel functions: UNIQUE, SUMIFS, HSTACK, SORT, VSTACK, and LET. This lets you create something that is like a pivot table, but there is no need to refresh as the data changes. Table of Contents (0:00) Welcome (0:16) Formulas to Values (1:08) UNIQUE list of products (1:35) SUMIFS by product (2:12) HSTACK and SORT (2:44) Headings and Total Row (3:25) VSTACK into final report (3:53) With fewer helper columns (5:01) Using LET and no helper...

Today's Excel question from Willie at the UCF Accounting Conference: I am using a vlookup to get some information from a vendor info database that comes from the credit card website. My issue is that the vendor name sometimes comes with additional info at the end like …. “Contoso - cadet.biz” and in the table where I do the lookup the name is just “Contoso”. I was wondering if you think I can use the “TEXTBEFORE” function with the Vlookup formula to make the formula to look in the table only for the first word of the info coming from the credit card “Contoso - cadet.biz”. Table of Contents (0:00) UCF Accounting Show (0:39) Table with Vendor dash URL (1:15) Use TEXTBEFORE with VLOOKUP (1:50) Use TEXTBEFORE, XLOOKUP, for lookup...

This video is all about the Excel function for WORKDAY.INTL. It allows you to make sure that you are always scheduling an event to occur on a Saturday, or Tues/Thurs or Friday. The secret here is the alternate syntax for the Weekend argument. Table of Contents (0:00) Find a particular Saturday in Excel (0:25) WORKDAY introduction (1:37) Find a particular Saturday in Excel (2:32) Find the next Tuesday (5:02) Find next Tuesday or Thursday (5:46) Handling holidays (6:58) Wrap-up

Uncle S is trying to have a formula copy to new rows. So a table seems like a great way. But he also has merged cells and the table unmerges those. In this video, two surprising Table tricks: First, a table can contain Center Across Selection, which is better than Merging Cells in Excel. Second, a surprising discovery from the Excelforo channel about new headings in a table. Table of Contents (0:00) Auto-copying formulas to new rows in Excel (2:31) Can a table have a merged cell (4:00) New table columns using custom lists in Excel

You have a pivot table in Excel. The months and weekday names are appearing alphabetically instead of in calendar sequence. This happens when your pivot table is based on external data or the data model. There are two solutions. This video prefers using the Custom Sort Order option. This takes 8 clicks to fix the months and 8 clicks to fix weekdays. If your Pivot Table Slicers are in the wrong sequence, then you should use the Data Model setting to sort one column by another column.

Ronak asks: How can I use Excel to find out how many weekedays per month for the whole year? This video shows how to do it using SEQUENCE, TEXT and a quick pivot table in Excel.

Michele has 46000 rows of data. Each ID has several records in a row. Many records are marked as "Changed". When there is a change, Michele needs to check the record marked, plus the matching IDs just above and below. In this video, I try Find All and 2 Advanced Filters. I bet you have something better.

Microsoft Excel Tutorial: Version History has improved. A cool new feature from the MVP Summit: The Version History pane in Excel Online gives you an impressive visual view of who made what changes to your Excel workbook. I previously mentioned how Show Changes debuted 9 months ago. Somewhere in the last nine months, the obscure Version History feature (found under File, Info) has gotten much better. Save your document to OneDrive or SharePoint Online. Work as normal in Desktop Excel. If something gets screwed up, you can go to Excel Online. Use File, Info, Version History and preview every change made from the new Version History panel. These new improvements will make this an amazing feature. Buy Bill Jelen's latest Excel...