MrExcel Excel Videos

Superscript and Subscripts were hard to do in Excel June 2017, in response to customer votes on Excel.UserVoice.com there are now two items you can add to your Quick Access Toolbar

Breaking news on a bad recalc bug in Excel. If you ever use RANK or Sort, you could be impacted. Fundamental belief: Excel stores 15 digits of precision. How RANK returns duplicates How RANK+COUNTIF eliminates duplicates Mystery where RANK+COUNTIF still returns a duplicate Four cells contain 1.15 =A2=A3 shows the cells are equal Ctrl+` shows the cells are equal But the four cells do not sort correctly. Something is different. Change the XLSX to a Zip file Open the XML inside the Zip File What? Excel is storing 17 digits!?! So - is that a good thing? More accurate, right? Maybe 16-digit credit card numbers stored as numbers, right? Hooray? But... while some functions only use 15 digits (COUNTIF and equality tests) others use 17 digits...

New feature in Excel, Word, PowerPoint - Office 365 Exclusive Insert 3D Models in Excel Lots of 3D models available for free on the Internet Resize - change view, or free rotate With VBA, you can move the objects container, but you can't rotate.

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