MrExcel Excel Videos

Todd is looking for a way to Search in cell comments in Excel Use Ctrl+F for the Find dialog Click the Option button to get more features Look In: change to Comments Within: Change to Workbook Click Find All Use Ctrl+A to select all of the found cells on the current worksheet Change the fill color so you can find them later. Click Feb in the search results to move to the next sheet

Creating Stratifications with a Pivot Table Yesterday, in episode 2101, used automatic grouping That forces all buckets to be the same size - $10K in yesterday's example To create buckets of varying sizes, there are two options: Select a manual group and choose Group Use a Lookup table to apply a category and add that to the pivot table

Creating Stratifications with a Pivot Table Build a pivot table with Revenue in the Rows and Customer in the Values (Backwards!) Select the first revenue cell in the pivot table Use the Group command Choose a bucket size and a starting point Add Additional Fields, such as Profit (normal), Profit (Percent of Column), Profit (% Running Total In) When you re-use Revenue, you will have to change from Count to Sum

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.