MrExcel Excel Videos

Text in column A contains a title and a date. The date always includes a month name, but might be in different formats. Mike and Bill offer alternate strategies. Bill's super wide approach: Put all 12 months in separate columns Use the FIND function to see if this month is in the original text To find the minimum starting position, use =AGGREGATE(5,6,… A few extra formulas to look for a number 2 or 3 positions before the month Mike's approach: Use SEARCH instead of FIND. Find is case-sensitive, Search is not. Create an function argument array operation by specifying B13:B24 as Find_Text. The formula returns #VALUE! Error, but if you press F2, F9, you will see that it is returning an array. The first 13 functions in AGGREGATE can not...

A calculated field in a pivot table is sometimes showing #DIV/0 errors for a calculated field. This episode shows how to wrap the formula in IFERROR to suppress the errors. Create a calculated field within a pivot table Give the calculated field a name that you don't like so you can change it later. That field might be returning errors such as Division by Zero Choice 1: Suppress Errors when Printing Choice 2: Wrap the calculation in IFERROR In a pivot table, the second argument of IFERROR has to be numeric Use a Number Format of 0.0%;-0.0%;; to hide the zeroes

You want to subtotal data by customer, but see the largest customers at the top and within the customer, see the largest revenue at the top. This requires a pre-sort and a post-sort. Most people don't realize that you can sort subtotals after collapsing This sorts an entire group of records into a new position. But it does not sort within the group. Jeff from Columbus Indiana offers a simple solution: Sort by Revenue descending before adding subtotals.

How to nest functions in the Functions Arguments dialog Start using fx When you need to insert a function in an argument, use the Name Box The Name Box is to the left of the formula bar When you finish the nested function, click on the Original Function, in the formula bar not in the cell

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