MrExcel Excel Videos

Do you love Excel? Do you have a feature that would make Excel easier for you? Write up your idea and post it to the new Feedback Forum for the Excel team. Other people can vote for your idea and many ideas each year are added to Excel. The Feedback portal for Excel is at: Community Vote for my idea to use the Custom Lists when renaming copied worksheets, vote here: Community If you post a great idea at the Feedback forum, let me know the title in a YouTube comment and I will vote for your idea as well. This video shows how to access the Feedback portal and talks about current Excel features that were suggested through the Feedback Portal. Table of Contents (0:00) The Excel team wants your improvement ideas (0:25) Feedback icon...

VLOOKUP, XLOOKUP, and SUMIFS all allow a wildcard in the Lookup_Value. But today, Chris wants wildcards wrapped around the lookup table! After trying XLOOKUP, FILTER, and COUNTIF, I finally end up with TEXTJOIN, IF, and SEARCH. Do you have a better way? Table of Contents (0:00) Use wildcard for the lookup range (0:34) XLOOKUP, FILTER, COUNTIF failed (0:54) SEARCH function on an array (2:11) TEXTJOIN, IFERROR, and SEARCH (3:15) Surprisingly difficult

Markus is looking for Excel help to pick numbers for the Euro Jackpot where you have to choose 5 numbers from 1 to 50. In this video, I share secrets from my late friend Dogtrack Bill. To use the number picker in a free Excel Online, follow this link: https://1drv.ms/x/s!As7G72Sl487JljvjcWPxwg0_pTe1?e=CMGO1E The Euro Jackpot payout for matching 5 numbers with no bonus numbers is a parimutuel system. A fixed percentage of the betting pool is split between all people that matched five numbers. To maximize the payout if you win, choose numbers that are not likely to be chosen by other people. Avoid drawing patterns on the betting card. And, most importantly, stop using dates to choose your lottery numbers. Table of Contents (0:00)...

Vicki: I have a list of dates and I'm looking for a formula to calculate the previous workday only if the date in the list is a weekend or holiday. Why: Accrue Fed Fund Interest Daily (Fed Funds Rate only published on Business Days). This is mildly complex, because you need to be able to create a named range for the holidays, and after building the long formula, Excel will likely give you the date serial number instead of the formatted date. Table of Contents (0:00) Introduction (0:23) Create a named range with holidays (1:23) Excel formula to detect if date is a workday using NETWORKDAY function (2:11) WORKDAY function to go back one work day (3:15) Formatting serial numbers as dates

What is your favorite font in Excel? Anna and Lisa showed me the Consolas font. It is great because you can tell zero from O and one from lower case L. Table of Contents (0:00) Axel font for Excel (0:19) Typewriters without a 1 key (0:54) People who type L instead of 1 (2:04) How I met ExcelisFun (2:37) Make a custom Office Theme with Consolas font (3:32) Applying a custom office theme (3:45) Making a theme be default (4:09) Default Font in Excel Options

The Microsoft Create platform is in preview. Sign in with a Microsoft 365 Home account to access free templates for Excel and more. It also includes training on social media from Kat Norton Miss Excel and promises future features. Table of Contents (0:00) Free templates in Preview (0:34) Start at Create.Microsoft.Com (1:30) Opening a template in Excel Online (1:54) Accessing from Win32 (2:24) Learning videos including Kat Norton (3:00) Designer & Clipchamp coming

Three new features coming to Excel. 1) Value Preview will evaluate part of your formula when you select it in the Excel formula bar. You don't have to press F9 and Esc anymore. 2) AutoSave moves from an icon to Words in the Excel Title Bar 3) Small preview of Personalized Toolbar in Excel Table of Contents (0:00) Three new Excel Features (0:30) F9 replaced with Value Preview (3:57) AutoSave icon moves to Title Bar words (5:07) Personalized Toolbar in Preview (10:38) Wrap-up

I was doing a seminar recently and many people at the company were getting reports downloaded as fixed width text files. I encouraged them to start using the Get & Transform tools in Excel for cleaning this date. After the seminar, I created a text file with many of the issues that were in their workbooks. Table of Contents (0:00) Fixed Width Text File for Excel (0:50) Excel Text Import Wizard UI for Marking Column Locations (1:19) Finding column start locations for Power Query (1:59) Importing to Excel from Text/CSV (2:18) Power Query incorrectly guesses comma as delimiter (2:42) Power Query make column wider (2:55) Power Query removing top 6 rows (3:30) Power Query Split by Position (4:20) Power Query Use First Row as Headers...

There are 7 pivot table secrets in this video, and you haven't seen six of them. The question: I have several projects in a pivot table. At the bottom, as part of the pivot table, I want an average of the non-zero rows. The person is currently using AVERAGEIF outside of the pivot table, but that formula must be adjusted. Today, we use a Data Model pivot table and a DAX Measure to calculate the average. The resulting pivot table has too many rows, so I use a new Set based on Rows. The DAX formula used in the video is: =DIVIDE ( SUM(LCosts[Amount]), COUNTROWS( FILTER( SUMMARIZE(LCosts, LCosts[Project], "Total", SUM(LCosts[Amount])), [Total] <> 0 ))) The 8 secrets: Secret 1: Unlock features with this checkbox Secret 2: Adding a...