MrExcel Excel Videos

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"))

Pam wants to sort the worksheet tabs alphabetically Going to use a macro in the personal macro workbook Alt+T M S and change macro security to allow macros Record a Hello World macro into the personal macro workbook Type new code into Personal Macro Workbook Assign that code to a QAT button

Todd is looking for a way to use Ctrl+F Find for worksheet names. Before solving Todd's problem, review the Navigation Worksheet tricks: Ctrl+Click Right arrow to go to last sheet Ctrl+Click Left arrow to go to first sheet Right-click in arrow area to bring up a list of sheets But none of that helps if the worksheets aren't in sequence. Solution: Ctrl+G or F5 to open Go To dialog. Type Sheet Name, then !A1 and click OK

Pam wants to to create a worksheet for each account number shown in cells in a range. Currently, she is using the Show Report Filter Pages of a pivot table which is clever, but she is looking for a faster way. Save the workbook as XLSM Check Macro Security with Alt+T M S & set to second level Alt+F11 Insert Module Type the code as shown Alt+Q to return to Excel Alt+F8 Select the macro & click Options Assign to Ctrl+Shift+W