MrExcel Excel Videos

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

How to calculate a Month-to-Date report using formulas or a pivot table. Bill's method Add a helper cell with a MTD formula =AND(MONTH(TODAY())=MONTH(A2),DAY(A2)LessThan=DAY(TODAY())) Add that field as a Slicer where = True Bonus tip: Group Daily Dates up to Years Add a calculation outside of the pivot table while avoiding GetPivotData Mike's approach: Turn the data into a table using Ctrl+T. This allows more data to be added to the table and the formulas update. SUMIFS with DATE, MONTH, DAY functions Pressing F4 three times locks a reference to just the column. Watch out - if you drag a Table formula sideways, the columns change. Copy & Paste - no problems Using TEXT(date,format. Nice trick with \1 to insert the number 1 in the text

Lourdes wants to group text in a pivot table Method 1: Create the pivot table with Product in the Rows area. Select all of the items for the first group. Use Analyze, Group Selection. Select all of the items for the second group. Use Analyze, Group Selection. Type new category names instead of Group1 and Group2. Optionally, remove the original Product from the pivot table Method 2: Build a lookup table Make both tables be Ctrl+T tables (Don't forget to check Use Headers) Build a pivot table from both tables using the All link at the top of the field list Define a relationship.

Say that you have two pivot tables from different data. The original data changes in both data sets. You want to refresh all pivot tables. The Refresh button on the PivotTable Tools | Analyze tab only refreshes one pivot table. Use Ctrl+Alt+F5 or Data, Refresh All to refresh all pivot tables in the workbook

Type a name and have all the matching cells change to a certain color. How to highlight all cells that contain a name that you type Figure out where you will type the name Select all the cells that potentially contain a match Home, Conditional Formatting, New Rule, Highlight Cells that Contain, Specific Text, Contains, $E$1, Click OK, OK If you need to have it be an exact match, change Specific Text to Equals to

Use the new Combine Binaries feature in Excel to combine all of the workbooks in a folder. Revisiting the Clean Data with Power Query podcast # 2037 Power Query can now combine all Excel files in a folder. Improved: They automatically delete the headers from all but the first file. You choose which file to use as the Sample file. Choose which Worksheet, Table, Named Range to import Use the query editor to do any transforms Close & Load to combine all files Later, refresh the query to have it update

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...