MrExcel Excel Videos

A cool trick from Bob Umlas for our 2000th podcast episde Fast Motion version of Straight Line Regression All of those steps can be replicated with the fill handle Fast motion version of interpolation Select first, last, and blank cells. Go to Home, Fill, Series The Fill Series dialog will give you the correct steps Special Price on both Bob Umlas books: Excel Outside the Box Techniques

How to subtract a value from a column in Excel Method 1: Temporary column with =D6-3, Copy and Paste values Method 2: Put the 3 in any cell. Copy it. Paste Special, Subtract

You can sort by a custom list, but are you allowed to sort numbers by a custom list? Apparently... Yes. But it requires some workarounds. Custom Lists can be 254 items but the total length of the items (including a theoretical column between each item) is 2000 characters. Don wants to sort by a custom list that is numeric! Will this work? It seems to work! But you can not import numeric cells to the custom list dialog box. So, try to type numbers into the Custom List dialog box…. You are struck with a foolish 255 character limit when typing. WTH is the limit? 254 items? Aha - 254 items, but less than 2000 characters when you add the invisible comma between each item Did some text math with =SUM(LEN()) +CtrlShift+Enter and...

Dustin has a column with 1 to 16 values separated by ; There are 8 columns total in the data He wants the ; separated to rows The May 2017 update to Power Query adds the ability to split a column to Rows

You have an Excel VBA Macro that is not working VBA has amazing debugging tools Rather than run your code, you can step through the code using F8 The line in yellow is the line that it about to be run Hover over any variable to see the value of that variable. Toggle back and forth to Excel to see what is happening

This might happen in Excel 2013 or newer: You open a workbook in Excel. The workbook is not visible. It is in the View, Windows list. But you can not see it. Somehow… the workbook was last saved when I had two monitors. The workbook is remembering its position as somewhere on the second monitor. But I don't have two monitors anymore. Use View, Window to switch to the other workbook. Then, hold down the Windows key and press the left or right arrow. Win+Left Arrow will move the application back to the left half of the screen.

The Excel macro recorder does not do a good job with recording sorting. Provided your data can be selected using Ctrl+* (known as the Current Region) Provided you aren't sorting by color or icon or more than three levels, Use the old school Range().CurrentRegion.Sort method in Excel.

Today's trick from Lisa in Indianapolis is a faster way to filter a chart You can filter a chart to just one customer, one sales rep, one anything. You used to do this by filtering the original data set. But, starting in Excel 2013, you can use the Funnel/Filter icon to the right of the chart. Bonus tip today: Excel offers an odd Font dialog with things like: ALL CAPS, Double Strikethrough, and more

Goal is to concatenate all of the text answers from a VLOOKUP Bill's method: Use a VBA Function called GetAll Unique list using Remove Duplicates Mike's method: Unique list using Advanced Filter TEXTJOIN function added in Office 365 TEXTJOIN(", ",,IF(OilChangeData[ID]=D2,OilChangeData[Comment],"")) Because of the IF function, the formula requires Ctrl+Shift+Enter any time you edit the formula Alt A Q O R Enter will re-run the Advanced Filter!