MrExcel Excel Videos

Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
Learn Excel Replace OFFSET with INDEX - Podcast 2048
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
Learn Excel - Formatting as Facade - Podcast 2044
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...
Learn Excel - SUMIFS - Podcast 2043
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.
Learn Excel - Preventing Formula Errors - Podcast 2042
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.
Learn Excel - Flash Fill - Podcast 2041
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
Learn Excel - April Fools Day Trick - Podcast 2040
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.

Forum statistics

Threads
1,226,730
Messages
6,192,703
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top