MrExcel Excel Videos

New in Office 365: Pivot Table Defaults! All future pivot tables can be in tabular form. Or, enable Classic Mode for all future pivot tables. Go to File, Options. The feature keeps moving between a new Data category or a Data category within Advanced. The big settings in the dialog let you change the layout But you can make small settings and Import those. If you have an idea that you would like in Excel, go to Excel.UserVoice.com and write up your idea. It works!

Becky from Huntsville shows a faster way to get to the Value Field Settings in a pivot table. You might drag Revenue to a pivot table several times Change the Value Field Settings for each column to show another calculation Rather than choose a cell, click Field Settings, you can double-click Also in this episode: Changing the field heading to Revenue with a space Percent of Column in a Pivot Table Rank in a pivot table Percentage Running Total in a pivot table

Sean wants to Paste Values for all formulas that have external links in Excel. While that might be possible with a macro, you can use Break Links to solve the problem. We have a workbook that has a mix of formulas, constants, and external links How can you convert all external links to values? Tip: Use Ctrl+` to show all formulas (press again to exit Show Formulas mode) Do you write some VBA to detect the opening square bracket in the external link? No! You use the very hard to find Break Links button! Save the file first. Save the file with a new name. Alt+E K Click Break Links Edit Links is also on the File, Info panel, lower right Thanks to Charles Williams and Debra Dalgleish for this tip

A date slicer in Excel is showing Jan, Feb, Mar... But your fiscal year starts in July. How can you get the slicer to show Jul, Aug, Sep? Bjorn from New Zealand provides a faster way to sort the slicers without using the Data Model or Power Pivot. How to change the sequence of items in a pivot table slicer Old method involved using the "Sort by" feature in Power Pivot New method: Add a custom list with the correct sequence The slicer will respect the custom list The last custom list in the dialog will "win"

Roger wants to clear text but not formatting in his VBA macro. This episode looks at all the ways to clear. How to Clear in VBA There are 7 different methods available .Clear clears everything .ClearComments clears comments .ClearContents keeps the formatting and clears values & formulas .ClearFormats clears the formatting .ClearHyperlinks clears hyperlinks .ClearNotes clears comments (Comments used to be called Notes) .ClearOutline gets rid of group & outline groups .SpecialCells can be used to select only constants or only formulas

Kristin asks if you can add an icon set for Text cells? There are two workarounds in this episode. The first is for up to three icons. The outtake shows another way for five icons. Can you apply Conditional Formatting Icon Sets to Text? No But, if you have three (or less) ratings, you can use the trick in this episode. Convert your text ratings to 1, 0, and -1 Apply the Icon set Use a custom number format of "positive";"negative";"zero" This will get words to appear instead of numbers

Go To Special, Formulas - but it is not "live" There is a new function… HASFORMULA? No! Great new feature in Office 365 to find a function by typing part of the name The function is ISFORMULA Select the range Home, Conditional Formatting, New Rule, Use a Formula The formula should point to the top left corner cell of your selection =ISFORMULA(B2)

Asad asks for a formula to calculate a progressive income tax rate. Set up a table in ascending order with the amounts from your income tax card You will do three Approximate Match VLOOKUPS to get the base tax, the percentage and the start of the level Subtract the Start Level from the income for the period. Multiply by the percentage. Used Ctrl+' to copy a formula down one cell without changing the references Using the F9 trick from Mike Girvin to embed the lookup tables in the formula.

There is an odd bug in Office 365 - when you insert a map chart, it won't recognize the "Democratic Republic of the Congo". You have to misspell it as "Democratic Republic of the Cong". In this temporary episode, a bizarre workaround to get the final o back into the cell.