MrExcel Excel Videos

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.

Sometimes, you have to key data from a piece of paper. You want to proofread the data in Excel. You will get whiplash going back and forth between screen and paper. Right-click Quick Access Toolbar Choose Customize Quick Access Toolbar From the top left dropdown, choose Commands Not in the Ribbon Find Speak Cells. Click Add five times to move all the Speech icons. Select some cells, click Speak Cells. Some of my co-workers should have the Stop Speaking icon on their foreheads! You can control if they read by columns or by rows. Pro tip: speed up the voice in the Control Panel Microsoft Anna is the voice in Windows 7

Plot your Excel data on a map using Power Map in Excel 2013 or 3D Maps in Excel 2016. Episode Recap: Power Map is in Office 365 editions of Excel 2013 Power Map Preview can be downloaded for Excel 2013 In Windows editions of Excel 2016, renamed to 3D Maps Works with one worksheet (as a Ctrl+T table or not) Works with multiple worksheets added to the Data Model Once in Power Map, choose Geography Category changes color Height plots the height Consider a flat map to see the whole world Add a time field to animate over time Wheel mouse to scroll in Alt+ mouse up or down to tilt Alt+ mouse left or right to rotate Add labels Theme 2 Use the time scrubber to animate data over time