MrExcel Excel Videos

The Excel macro recorder does not do a good job with recording sorting. Provided your data can be selected using Ctrl+* (known as the Current Region) Provided you aren't sorting by color or icon or more than three levels, Use the old school Range().CurrentRegion.Sort method in Excel.

Today's trick from Lisa in Indianapolis is a faster way to filter a chart You can filter a chart to just one customer, one sales rep, one anything. You used to do this by filtering the original data set. But, starting in Excel 2013, you can use the Funnel/Filter icon to the right of the chart. Bonus tip today: Excel offers an odd Font dialog with things like: ALL CAPS, Double Strikethrough, and more

Goal is to concatenate all of the text answers from a VLOOKUP Bill's method: Use a VBA Function called GetAll Unique list using Remove Duplicates Mike's method: Unique list using Advanced Filter TEXTJOIN function added in Office 365 TEXTJOIN(", ",,IF(OilChangeData[ID]=D2,OilChangeData[Comment],"")) Because of the IF function, the formula requires Ctrl+Shift+Enter any time you edit the formula Alt A Q O R Enter will re-run the Advanced Filter!

Sum a rectangular range on all worksheets =SUM( Click on Jan sheet point to the range Shift-Click on Dec sheet press Enter Or, type =SUM(Jan:Dec!B4) Bonus trick: Adding Total Row & Column in one click of AutoSum

Merged Cells are evil. My one rant about a merged cell is that the selected range gets larger when you touch a merged cell I was always frustrated because I couldn't make the selection narrower again. Steve from Huntsville: keep holding down shift key, click in the cell just below the merged cell. Or - keep holding down the shift key and press Down, Left, Left This assumes that the merged cell is at the top, not in the middle of your range. If the merged cell is in the middle, you can not narrow the selection - use Center Across Selection instead. To Vote on this issue, visit: Add Center Across Selection to the Merge & Center Drop-down

Search box in Filter lets you look for # or @ But if fails when you try to search for * or ~ or ? Using the Search box in Filter, you want to find an asterisk * Using the Search box in Filter, you want to find a tilde ~ Using the Search box in Filter, you want to find a ? Excel returns everything for the * and nothing for ? ~ To search for an asterisk, search for ~* To search for a tilde, search for ~~ To search for question mark, search for ~? To search for ~?*, search for ~~~?~* In the Find/Replace dialog, use ~~, ~*, ~? In the Find box, but not Replace When using VLOOKUP or MATCH, use ~~ ~* or ~? Addendum: How to use * and ? wildcards in Excel

You have spaces that TRIM won't remove You have a part number that ends with e and a digit You have a part number with more than 15 digits If you import as a CSV file, the part numbers change to Scientific Notation How to show extensions in Windows Explorer If you import by opening a .txt file, you can attempt to specify those columns are text, but when you find/replace the non-breaking space (character 160), the part numbers change to scientific notation The solution is to use Data, Get External Data, From Text. However, this command is missing from Office 365, having been replaced by Get & Transform. If you don't have From Text, right-click the Quick Access Toolbar and Customize In the top-left dropdown, change to All Commands. Find...

Tom needs to split data. Bill & Mike discuss four alternatives. Bill's first method using Text to Columns (found on the Data tab). In step 1, choose delimited. In step 2, choose a space. Skip step 3 by clicking finish.  The text will split at each space, so anything with three words will end up in 3 cells. Put those back together with =TEXTJOIN(" ",True,B2:E2) or with =B2&" "&C2&" "&D2 Mike's first method uses Power Query. Power Query is Get & Transform in 2016 or a free download for 2010 or 2013. First, convert your data to a table using Ctrl+T. Then, in Power Query, from Table. Split Column, by Delimiter. Select Space and then at the left-most delimiter. You can re-name a column by double-clicking! Close & Load To… and choose a new...

Steve wants to fill dates in ALL CAPS Choice 1: Felix Titling font Choice 2: Formula using UPPER and TEXT: =UPPER(TEXT(C2,"DDDD, MMMM D, YYYY"))