MrExcel Excel Videos

The Round menu option in Power Query does not round the same way that Excel rounds. This is a shorter video covering the same information that Excel MVP Celia Alves covered in episode 2391. In order to have Power Query ROUND and Excel ROUND return the same results, you have to edit the formula in Power Query to add RoundingModeAwayFromZero. This video shows you how.

Excel has specialty rounding functions CEILING.MATH and FLOOR.MATH. Think of them like a ROUNDUP version of MROUND and a ROUNDDOWN version of MROUND. Why did they not call them MROUNDUP and MROUNDDOWN? That is a great question. Today, I show you how CEILING.MATH and FLOOR.MATH work and the equivalent formulas in Power Query.

If you are a fan of the INT or TRUNC functions in Excel, you might be surprised to learn that there is no built-in equivalent in Power Query's M function language. Today's episode shows a simple formula that you can use in a Power Query custom column to replicate TRUNC and INT.

Excel offers the cool MROUND function to round to the nearest 5 or 25 or any number. There is no built-in equivalent function in Power Query's M code. However, using a formula such as =Number.Round([Number]/25,0,RoundMode.AwayFromZero)*25 you can do the same thing.

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