MrExcel Excel Videos

Today, Bill has data where the amount of indent is telling him some data. He shows how to use VBA and IndentLevel to add a column of data showing the indent level. Then, a second technique, using the Group button on the Data Tab to make an outline view in Excel.

May 20th, 2021 is Global Accessibility Awareness Day. It is a great day to take a look at the new Check Accessibility tab in the Excel ribbon.

Are you trying to use the Analysis ToolPak dialog box? There is a better version, for free, from Frontline Systems. Running into a bug? Microsoft knows about the problem. George Mount's book: Advancing into Analytics: From Excel to Python and R 1, Mount, George - Amazon.com Dan Fylstra's XLMiner info: XLMiner Analysis ToolPak

In an episode guaranteed to hack off many of my friends, Bill and Ann K. Emery discuss some Excel features they don't like, such as: The Action Pen Shoot a Photo Of Your Data Ctrl+T Tables Conditional Formatting Data Bars with a Gradient. Check out Ann's blog post on 24 conditional formatting features to avoid: 24 Conditional Formatting Visuals in Microsoft Excel that Should Be Retired | Depict Data Studio

More of my discussion with Ann K. Emery. This episode talks about TEXTJOIN and how it would be nice to have TEXTSPLIT. Check out Ann's blog at Blog | Depict Data Studio More Snippets here: (0:00) Welcome and intro (0:30) Discussion about TEXTJOIN and TEXTSPLIT (1:01) Demo of TEXTJOIN using a range in Excel (1:35) Generating random Yes/No using RAND in Excel (1:54) Converting Excel formulas to values by dragging (2:20) Using TEXTJOIN with an IF Function in Excel (3:00) The need for TEXTSPLIT (3:10) Counting names using LEN and SUBSTITUTE (3:40) Converting Excel data to a paragraph in Word (4:06) Concatenating text and numbers in Excel loses formatting

I recently made a guest appearance in Ann K Emery's class on data visualization. Over the course of an hour, I showed a few Excel tricks. This one, about Custom Lists was recently discussed in the YouTube comments for another video. Check out Ann's blog: Blog | Depict Data Studio

Another question from Columbus, Indiana. How can you choose a default style (from the PivotTable Design tab) for all future pivot tables? Yes, there is a Set as Default choice, but it only applies to future pivot tables in this workbook. My method involves adding a default to BOOK.XLTM. Also in this video, a pivot table formatting oddity from Excel MVP Wyn Hopkins. Table of Contents (0:00) Introduction (0:44) The colors on Page Layout impact the Pivot Table Styles (1:25) Changing a pivot table style default only affects this workbook (3:40) A FAST way to get to Browse during Save As (4:54) MVP Wyn Hopkins Discovers a bizarre pivot table formatting trick! (5:33) Change the colors and style of your borders using Format Cells (7:23) Pivot...

Another cool discovery by Bob Umlas. Say that you need to add a Total, Average, Max, and Min to the right of a data set. The AutoSum drop-down menu in Excel offers all four features. But how can you use them without the previous Sum getting in the way? Bob discovered a cool way.

A question from my Columbus Indiana virtual seminar: How can we sort a data set by Absolute Value? They have columns with Forecast, Actual, and Variance. A large variance is bad, whether it is positive or negative. My solution today involves using a Dynamic Array and the SORTBY function. Also: using a helper column for people who don't have dynamic arrays.