MrExcel Excel Videos

Goal is to find the first & last dash and keep everything in between The hard part here is finding the last dash Bill Method 1: Flash Fill Manually fill in the first few (including some with different numbers of dashes) Select the blank cell below that Ctrl+E to Flash Fill Mike Method 2: Use Power Query In Excel 2016, Power Query is in the Get & Transform group in Excel 2016 In Excel 2010 & 2013, download Power Query from Microsoft. It creates a new Power Query tab in the Ribbon Convert your data to a table using Ctrl+T Use Split Data in Power Query - first to split at the leftmost dash, then to split at the right-most dash Bill Method 3: VBA Function that iterates from end of the cell backwards to find the last dash Mike Method 4: Use...

Kuldeep wants to select every third cell for 1500 cells The macro code to union all 500 cells is too long and causes a compile error! Solution: Build 1, blank, blank. Copy those three cells and paste to 1500 cells Select the 1500 cells. Home, Find & Select, Go To Special, Constants to select every third cell Then, one line of VBA to move the selection N rows or columns away. Finally, a VBA Macro to do the whole thing Cool Tip: If you use Ctrl+G for Go To dialog, it shows recently selected ranges. Cool Tip: In the Go To dialog, hold down Shift to select from Current Cell to the Go To cell. VBA Tip: Using UsedRegion to find the last row in the worksheet

Joe is looking for a way to save each worksheet to a different PDF. Others have asked for a way to split each worksheet to a different Excel workbook. This could be useful for Power Query or after using Show Report Filter Pages. Episode 2107 shows a free MrExcel Worksheet Splitter utility to accomplish this task. Download the workbook from here: http://www.mrexcel.com/split_workbook_by_worksheets.shtml

Great question: How can I generate a PDF from every possible combination of three slicers in Excel? Goal is to loop through all combinations in three slicers and generate a PDF for each VBA to loop through slicers Save as PDF using VBA Alternate solution is Szilvia Juhasz Show Report Filter Pages. The advantage is you can create a single PDF with all reports, but only if you have enough memory available to Excel. Outtake #1: Annoying feature that if you unselect all slicer items in VBA without selecting one, you will essentially select all. Outtake #2: Why you should never use a For Each loop in a collection of things that are constantly re-ordering themselves. Outtake #3: Can it be true that the code to select one item from a slicer...

Anderson is looking for a way to be able to copy blocks of data containing mixed conditional formatting Is there a way to remove the dollar signs once conditional formatting is set up? No - not without introducing dozens of new rules My solution: helper cells that use relative references to replace the mixed reference in conditional formatting Other techniques in this episode: If you have four conditional formatting rules, set up the first 3 and then make the fourth rule be the default color Outtake #1: Press F2 to stop Excel from inserting cell references in the conditional formatting dialog Outtake #2: setting up conditional formatting Bonus: Here is another solution from Mike ExcelisFun Girvin:

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