MrExcel Excel Videos

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

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