MrExcel Excel Videos

OFFSET is a flexible function. It can be used to point to a variable top left cell, and then point to a range that has a variable shape. The problem with OFFSET is that it is volatile. Volatile functions calculate at every calculation. Instead of OFFSET, you can use INDEX(): or :INDEX() When INDEX is next to a :, it will return a cell address instead of the value at that cell. INDEX is not volatile

No one bothered to use Multiple Consolidation Range pivots from Excel 97-2003, so they were locked in a cave starting in Excel 2007. But you can still pull them out when you need them! If your goal is to create a pivot table, you can consolidate multiple ranges into the table. Use Alt+D P to start the pivot table Choose Multiple Consolidation Ranges Page fields are optional

Consolidate is an ancient feature in Excel Specify multiple ranges to consolidate Use labels in Top row & Left column Annoyances: A1 is always blank, column A is not sorted, blanks in data Go to Special, Blanks, 0, Ctrl+Enter Consolidate can point to external workbooks

How to get a list of the unique values Advanced Filter with Unique Values Only Pivot Table Conditional Formatting Formula =COUNTIF(G$1:G1,G2)=0 Remove Duplicates Array formula from Mike Girvin's book

Number formatting is a façade: Excel stores one thing, shows us another. For example: use decrease decimals to show a rounded version of the number. Excel still stores all of the decimals, but simplifies the display. There are 11 number formats in the drop-down on the home tab. Click the dialog launcher to get to Format Cells dialog. Today, we are talking about the formatting codes in the Custom group. I don't have all the knowledge here today, so if you have better tips, put them in the YouTube comments. A zero in the format code says Excel must display the digit. A # sign says they may display the digit if there is enough precision, but they don't have to when there is not. A ? will leave space for the digit to keep the decimal place...

SUMIF and COUNTIF have been around since Excel 97 They did not handle multiple conditions well - you had to use SUMPRODUCT Starting in Excel 2007, the Excel team added plural versions: SUMIFS, COUNTIFS They also shortened SUMIF()/COUNTIF into AVERAGEIF And they added AVERAGEIFS In the Feb 2016 release of Office 365, they added MAXIFS, MINIFS, IFS But still no ROMANIFS Thanks to Nathi Njoko, Abshir Osman, Scott Russell, and Ryan Sitoy. Excel Guru Mission Patch if you can figure out how ROMANIFS is working at the end.

How do you prevent #N/A! error from your VLOOKUP? Three ways are discussed in this episode. In the old days, you would use =IF(ISNA(Formula),0,Formula) Starting in Excel 2010, =IFERROR(Formula,0) But IFERROR treats DIV/0 errors the same was a #N/A! errors Starting in Excel 2013, use the =IFNA(Formula,0) to detect only #N/A errors Thanks to Justin Fishman, Stephen Gilmer, and Excel by Joe.

Flash Fill was introduced in Excel 2013 It is great for parsing (breaking apart) text It is great for concatenating as well Make sure the new column has a heading Type the entry for the first row Start to type the entry for the second row - the results will grey in. Press enter to accept the results You can correct an entry after Flash Fill and it will correct others that match the new pattern. You can invoke Flash Fill with Ctrl+E Flash fill tends not to work automatically with numbers. But you can still use it for numbers by using Ctrl+E

April Fools Day Trick for a co-worker. Right-click Quick Access Toolbar Choose Customize Quick Access Toolbar From the top left dropdown, choose Commands Not in the Ribbon Find Speak Cells on Enter. Click Add to add the icon to the toolbar Turn on the feature. Right-click the icon and Remove from Quick Access Toolbar Wait for your co-worker to start typing! Bonus trick as an outtake: Using VBA to add to Speak Cells.