MrExcel Excel Videos

Microsoft Excel Tutorial: A very detailed discussion today about how Excel rounds. We were all taught in school that 8.4 rounds to 8 and 8.5 rounds to 9. But not in Power Query. Without letting you know, Power Query defaults to a rounding mode known as ASTM-E29 rounding or Banker's Rounding. In this scenario, anything ending in 5 rounds to the even number! I am joined by Excel MVP Celia Alves today. Read Celia's great blog post on this topic at https://solveandexcel.ca/2021/03/09/rounding-in-power-query-default-rounding-mode-and-the-binary-decimal-conversion-issue-in-excel/ Also subscribe to Celia' channel for a free weekly Excel course: https://www.youtube.com/channel/UCzWFp4hOnIBHHCJi_SEWV7g Table of Contents for this video. (0:00)...

Microsoft Excel Tutorial: Three ways to write a function to reverse the characters in a cell in Excel. Fast Excel from Charles Williams offers a REVERSE function: FastExcel V4 SpeedTools Table of Contents (0:00) Welcome and set up (1:09) Using a VBA macro to reverse the characters (2:20) Using a friendly LAMBDA that calls a recursive LAMBDA. (6:38) Using the SEQUENCE function and CONCAT

Building a SLUGIFY function in Excel using the brand new recursive LAMBDA function. This video shows how to use an Index argument to act as a loop counter in an Excel formula. Thanks to Smozgur who posted this example at the MrExcel Message Board LAMBDA forum: Excel LAMBDA Functions The recursive nature of LAMBDA functions is somewhat hard to wrap your head around. This example shows how one LAMBDA formula replaces 220+ rows of regular Excel formulas.

Microsoft Excel Tutorial: Inserting cut-out people into Excel to convey emotion about your data. Microsoft Excel has a new feature. There are now 925 poses of 41 different cutout people available in Excel, Word, and PowerPoint. Four have been cut from the cast. Nine have been made harder to find. Tips on using the new cutout people. Fans of Schoolhouse Rock and Multiplication Rock will notice that Interplanetary Janet makes a brief appearance in this episode. Why should you use a person to decorate your spreadsheet? 1) Human Connection: Faces are naturally engaging, and your audience tends to connect more with pages that have a human element. Seeing a person's face on a report can create a sense of familiarity and trust, making the...

The old Custom View feature in Excel never worked with Tables. A new Sheet View feature is coming to Win32 versions of Office 365. This new Sheet View allows different sorting and filtering in each view. Episode 2333 takes a look at this feature. Table of Contents (0:00) Introduction (0:15) Question from Bob about new greyed-out feature on Review tab (1:30) Creating a Sheet View (2:33) User Interface if multiple people editing (3:05) How do they handle SUBTOTAL or AGGREGATE functions? (3:25) Different Sorting in each view

Nas T has to convert sport names to a 4-letter abbreviation. If the sport name has one word, then use the left four characters. But, if the sport name has two words, use the left 3 letters from the first word followed by the first letter from the second word. AND... Nas says we have to use the IF formula. My solution includes some LEFT, MID, IFERROR, FIND functions. Also in this episode: Flash Fill.

Microsoft Excel Tutorial: ExcelHash participants discuss lessons learned and memorable moments. #ExcelHash #Excel #microsoftexcel Buy Bill Jelen's latest Excel book: MrExcel 2022 Boosting Excel You can help my channel by clicking Like or commenting below: Why clicking Like on a YouTube video helps my channel #excel #microsoft #exceltutorial #exceltips #microsoftexcel #exceltricks

Microsoft Excel Tutorial: Amazing way to rearrange the columns or rows in an Excel pivot table. In this episode of the MrExcel Podcast, the host discusses rearranging a pivot table. He mentions playing the Rocket Mortgage Super Bowl Squares and shares the numbers that were chosen. He explains how he chose his square randomly and discusses his wife's square as well. The host then demonstrates how to rearrange the numbers in a pivot table by typing them in the desired order. He also mentions that he fixed the data in the table to have AFC and NFC in the correct order. The host concludes by inviting viewers to share their favorite square in the comments and offering a free copy of his new book to anyone whose number comes up in the game...

You have a list in Excel. You want to know how many times each entry appears in the list. This week, Bill and Mike show a solution using a pivot table or Advanced Filter with COUNTIF.