MrExcel Excel Videos

Today's trick is from Katie Sullivan on the Word team There are a few cases where Microsoft Word can do things better than Excel One of those is changing case. If you needed to convert all of this text to Upper case, you could add a helper column and use UPPER, plus then Copy & Paste Values. The video shows how to right-drag a selection and choose Copy Here as Values Only But, you can also round-trip the data to Word In Word, there is a choice to convert text to UPPER, lower, Sentence, tOGGLE cASE, or Proper Case

Today's trick is from Sam Radakovitz on the Excel team Sam's signature formatting is white Segoe UI on Excel Green RGB(26,116,61) Creating your own icon set to show items in top 25% Creating a formula-based conditional formatting to highlight entire row Use Filter dropdown to filter by color or by icon Right-click to filter by color or icon Right-click to sort by filter or icon Idea to allow filtering by two colors Vote at mrx.cl/filtertwocolors

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.