MrExcel Excel Videos

A question from YouTube on how to write a macro to mark duplicates in each row where duplicates over the value of 5 are marked in red and under 5 are marked in blue. This can be done without a macro, using two formula-based conditional formatting rules. However - the formula will change depending on if you want to mark both halves of the duplicate or just the 2nd occurrence of the duplicate.

Four co-workers play 8-ball every day at lunch. They are currently calculating win rate on a white board. Can Excel be used to track the win rate of each player? Although the person is new to Excel, this video goes through a few somewhat advanced topics - Freeze Panes, Ctrl+T tables, Fill Handle, and a pair of COUNTIF functions.

The rent for a storage container starts out at $5 a day for the first 7 days, then $10 a day for the next two weeks, then $20 a day after that. How do you calculate the total rent? In this episode, Bill shows a pair of VBA User Defined Functions and Mike shows a pair of formulas.

Say that a co-worker writes a VLOOKUP to a closed workbook and refers to $A$1:$S$99,19 to grab a value from column S. The co-worker sends you the linking workbook but not the linked workbook. Perhaps there is some confidential information in columns B through R of that workbook. Everyone involved might be initially surprised that you can take the linking workbook to a new computer, without access to the closed workbook on the original computer, and drag out the entire table stored in A1:S99 of the closed workbook, including the confidential bits. I will admit I was initially surprised, but I guess I shouldn't be surprised. Everyone should be aware of this, and think before you set up wide VLOOKUPs to closed workbooks.

You have a 5x5 Risk and Severity Matrix. Separately, there is a database of events. Each is assigned a risk and severity. Place the record number from each event in the proper box. If more than one record falls in a cell, join them with commas in between. After consulting with ExcelisFun, I went with a custom function in VBA. Can you think of a formula that wouldn't be repetitively silly?

A question from Twitter asked how to have long text on an invoice extend down many rows. In today's podcast, Bill looks at three different ways.

Today, we have a database of date/time data. You don't care about time or even the day of the month - you just want to create a summary of how many records there are by month. This episode of the podcast offers two solutions - an array formula to count records by month or a pivot table that groups the date information up to month and year.

Today's duel involves evaluating math stored inside a text cell such as "1.35x2MVA+3x1.35MVA". Bill and Mike head off in different directions, with Bill doing a VBA macro to extract the digits from the cell and Mike using an Excel 4 Macro function to EVALUATE. Also - learn why Ctrl+F11 inserts a Macro sheet and how you can use the macro sheet to test your EVALUATE function.

The General number format doesn't get a lot of love in Excel, but it does decide if a decimal point is needed in your numbers. As you had off into Custom Number formats, deciding if a decimal point is needed is somewhat dicey. Luckily, the General steps in to solve the problem in today's Learn Excel Podcast.