MrExcel Excel Videos

Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
Learn Excel - "Risk Factor / 3 Way Lookup" - Podcast #1742
Tom sends in today's question. Enter age, gender, lab result and then lookup the rating in a table to indicate if someone's risk is low, moderate, high, or very high. Tom wants to return the result as a color (green, blue, yellow, red) via conditional formatting to the lab result cell. Today's podcast has a little of everything: rearranging the table, exact match, Less than Match, subtracting the number 1, OFFSET, assigning a formula to a defined name, then even a linked picture (aka Camera Tool) lookup for good measure. Bill always says that 99% of his VLOOKUPs end in False. For today's solution, that statistic drops to 25%.
Learn Excel - "Point to a New Range" - Podcast #1741
You have a formula with 6 absolute references. You want to keep one of those constant, but change the other 5 references to point to a new range. Today's episode shows two methods - one involves dragging a range five times, the other is even more convoluted. However - the big discovery in this episode is that you can use F4 to change all nine references from Absolute to Relative, provided your selection does not begin or end on a cell reference.
Learn Excel from MrExcel - "Dots" - Podcast #1739
Jim sends in today's question. Jim is a fan of an iPad game called Dots. You have a 6x6 grid of dots that appear in four colors. You are trying to find the longest connection of dots of the same color. Jim wondered if Excel could generate a random 6x6 grid of four colors of dots to look for patterns. In today's episode, see how to use RANDBETWEEN, conditional formatting and a custom number format to generate the grid of dots. But finding patterns is going to be tougher. While the human brain can detect the patterns, a formula in Excel to find the longest connection would be tougher. The video ends with some conditional formatting to highlight the possible connecting cells but no method for finding the longest connection.
Learn Excel - Hide Blank Rows with Event Handler - Episode 1737
In episode 1736, the goal was to hide blank rows without using a macro. It turns out the real requirement is to hide blank rows without having to remember to run a macro. Thus, an Event Handler macro that would automatically run in the background is acceptable. In today's episode, learn how to set up an event handler macro that silently runs every time the worksheet is changed. This macro will hide rows that are non-numeric in column C.
Dueling Excel - "Median of Last 10" - Podcast #1735
Calculate the MEDIAN of the last 10 entries in a column. Bill uses OFFSET with COUNT. Mike uses an array formula from his upcoming Ctrl + Shift + Enter book to find the last 10 non-blank cells. To pre-order Mike's book: Amazon.com: Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic (0783324907186): Mike Excelisfun Girvin: Books
Learn Excel - "Price List with PowerPivot" - Podcast #1734
Yesterday, in episode 1733, it required two MATCH and one INDEX to merge the data from two worksheets. Today, using PowerPivot in Excel 2010, another way to find the price for each customer/item combination. While PowerPivot is supposed to make life easier, this method still requires two concatenation formulas, two Ctrl+T, two Add Linked Table, one drag from Key to Key, then one RELATED function and an application of Mike Alexander's trick from my episode 493. Which is easier? 1733 or 1734? Make your note in the comments below.

Forum statistics

Threads
1,226,737
Messages
6,192,737
Members
453,753
Latest member
SORD

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