MrExcel Excel Videos

THERE IS A NEWER & EASIER WAY! See this video: How can you have a slicer drive two pivot tables? If both pivot tables came from the same data set: Select Slicer, Report Connections, Choose Other Pivot Tables But if the pivot tables came from different data sets: Use Save As to change the workbook extension to XLSM instead of XLSX Use Alt+T M S and change macro security to second setting. Alt+F11 to get to VBA Ctrl+R to display the project explorer Find the worksheet that contains your first pivot table and slicer Insert the code for Worksheet_Update Hide the second slicer away so it keeps existing but no one can ever choose from that slicer To copy the code from this episode, browse to: Sync Slicers in Excel

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