Learn Excel from Bill Jelen with this daily 2 minute video podcast.

## Tuesday, April 13, 2010

### AVERAGEPRODUCT? - Episode 1199

Susan asks..."I have been using SUMPRODUCT a lot ever since you showed me how – since I can’t use SUMIFS for users with pre-2007 version. But, is there an AVERAGEPRODUCT or something similar? I know about AVERAGEIFS but, again, it can’t be used by pre-2007 version users. How about MAXPRODUCT?" Episode 1199 shows you how.

...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

## Friday, March 12, 2010

### Counting Mondays - Episode 1185

Mike and Bill get together for another live dueling podcast, counting the number of Mondays between two dates. Episode 1185 shows you how.

...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

## Thursday, December 31, 2009

Ahmed asks how to do a lookup that only looks at the text portion of the lookup value. Mike and Bill compare methods in this Dueling Excel Episode 1169.

...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Monday, December 28, 2009

### Sum Big 3 - 1166 - Learn Excel from MrExcel Podcast

Teo asks how to sum up the three largest values in a range. Episode 1166 shows you how.

...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

## Monday, December 21, 2009

### January Not = January - 1162 - Learn Excel from MrExcel Podcast

A simple MATCH formula is not working. In episode 1162, we wander through the Validation dialog, the Name Manager dialog, all trying to figure out why the MATCH formula is returning #N/A.

...This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Friday, November 27, 2009

### Weekly Report - 1153 - Learn Excel from MrExcel Video Podcast

Lotfy from Egypt asks how to create a weekly report from daily transactional data. Mike and Bill show you several different methods in Episode 1153.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

## Friday, November 20, 2009

### Duel: Eval Text Formulas - 1149 - Learn Excel from MrExcel Video Podcast

Someone put a bunch of mathematical calculations in column A as text and we want to evaluate those formulas. Mike and Bill provide dueling ways to solve this problem in Episode 1149.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

## Friday, November 13, 2009

### Duel: Unsorted VLOOKUP - 1144 - Learn Excel from MrExcel Podcast

Martin from Sweden sends in a question about doing a range VLOOKUP where the lookup table is not sorted. Mike and Bill duel it out in today's Episode 1144.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

## Tuesday, November 10, 2009

### Divide Range by Range - 1141 - Learn Excel from MrExcel Podcast

Rob from Cambridge needs to divide nine cells by nine other cells and sum them up. In Episode 1141, we look at an array formula to solve this problem.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Monday, November 09, 2009

### Avoid a Loop - 1140 - Learn Excel from MrExcel Podcast

A cool way to streamline a VBA loop using SpecialCells. Episode 1140 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Thursday, November 05, 2009

### TRIM VLOOKUP table - 1138 - Learn Excel from MrExcel Podcast

Rod from Cincinati asks how to solve the problem of trailing spaces in the lookup table. I had never tried it before, but you can apply a TRIM to the entire lookup table. Episode 1138 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Monday, November 02, 2009

### Embedding Lookup - 1135 - Learn Excel from MrExcel Podcast

Nick asks how to do a pretty lengthy bonus calculation. It might be too complex for the Excel 2003 nested IF limit, so I decided to use the range version of VLOOKUP. But...as a twist, I embed the table right in the formula. Episode 1135 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Friday, October 30, 2009

### Duel: Index:Index - 1134 - Learn Excel from MrExcel Podcast

Today's Dueling Excel Podcast is a question sent in by Hassan. Hassan wants to enter a name and a through-month and then add up that person's sales from January through the selected month. Mike and Bill show various methods in Episode 1134.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

## Friday, October 23, 2009

### First Week of Month - 1129 - Learn Excel from MrExcel Podcast

A dueling podcast...how to find the first week of the month. Mike and Bill show you various ways in Episode 1129.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

## Monday, October 12, 2009

### VLOOKUP 101 - 1120 - Learn Excel from MrExcel Podcast

VLOOKUP is my favorite function in Excel. In Episode 1120, we will take a look at the basics of VLOOKUP.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, October 06, 2009

### Rounding 0.3333 - 1116 - Learn Excel from MrExcel Podcast

Michael asks why when you add up three cells with 1/3, you don't get 0.99? Episode 1116 takes a look at how to solve this.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Friday, October 02, 2009

### Duel: Bill Counter - 1114 - Learn Excel from MrExcel Podcast

You need \$273 in cash. How many of each currency amount will you need? This Dueling Episode 1114 shows a few different ways to go.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Thursday, September 17, 2009

### Blank Row for Missing Dates - 1103 - Learn Excel from MrExcel

Sam from Vienna sends in today's question. In a database of events, how can he add new blank rows to represent every missing day? Episode 1103 shows you one method.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Wednesday, September 16, 2009

### Split Vertically at 20's - 1102 - Learn Excel from MrExcel

Dak asks how to take a long cell entry and to split it into 20 character entries going down a column without using wrap text. Episode 1102 shows a couple of ways to solve the problem.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of 377 tips from the book!

Labels: , ,

## Tuesday, September 08, 2009

### Removing Name - 1097 - Learn Excel from MrExcel Podcast

Shift-Del sends in some alternative formulas to solve the problem from episode 1043. To see uses of SUBSTITUTE and REPLACE in Excel, check out Episode 1097.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

## Friday, August 28, 2009

### Duel: Multiple Wildcards - 1090 - Learn Excel from MrExcel Podcast

In today's dueling Excel podcast, the question is how to delete all the records which contain either ski or swim. This requires multiple wildcards in a filter. Mike and Bill will compare various methods in Episode 1090 .

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

## Friday, August 21, 2009

### Dueling Bonus Calculation - 1085 - Learn Excel from MrExcel

Mike and Bill offer five different way to solve a simple bonus calculation. Episode 1085 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Thursday, August 20, 2009

### Last Match III - 1084 - Learn Excel from MrExcel Podcast

Jamie from the UK sends in yet another formula to solve the Last Match formula from Episode 1073. Episode 1084 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Wednesday, August 19, 2009

### Last Match II - 1083 - Learn Excel from MrExcel Podcast

Daniel from Quebec sends in a wild formula to solve the Last Match problem from Episode 1073. We'll look at using Evaluate Formula to study how the LOOKUP value actually works. Episode 1083 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, August 18, 2009

### VLOOKUP External Workbook - 1082 - Learn Excel from MrExcel Podcast

Shaun asks how he can link values in one worksheet to a lookup table in an external workbook. Episode 1082 will show you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Friday, July 24, 2009

### Dueling: CountIf 3 Criteria - 1065 - Learn Excel from MrExcel Podcast

How to use COUNTIF to count survey answers for a subset of rows. Bill and Mike show many methods in Episode 1065.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

## Monday, July 20, 2009

### Offset Minus One - 1061 - Learn Excel from MrExcel Podcast

Julian wants to point to the row above the current row. This formula works fine until he deletes a row then those formulas change to #REF! errors. Julian asks how to point to "the row above" without specifying a row number. Episode 1061 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Friday, July 17, 2009

### Dueling: VLOOKUP Left - 1060 - Learn Excel from MrExcel Podcast

In today's dueling Excel podcast, how to do a VLOOKUP left. In Episode 1060 Bill and Mike show differing methods, from INDEX and MATCH to LOOKUP.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

## Monday, July 13, 2009

### Linking Worksheets - 1056 - Learn Excel from MrExcel Podcast

Carrie from NYC asks how to set up links between worksheets. Episode 1056 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Friday, July 10, 2009

### Dueling: Paper/Sticker - 1055 - Learn Excel Podcast

Faizee from Youtube posts a question about looking for certain words in a cell in order to create a category. While this sounds simple, Bill and Mike manage to come up with a number of possible formulae in Episode 1055!

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

## Wednesday, June 24, 2009

### Parsing Variable Length - 1043 - Learn Excel from MrExcel Podcast

Dawn's file has name in A and address in B. Except: the address field in column B contains the name again. Episode 1043 shows how to parse the variable-length name out from column B.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, June 23, 2009

### Indian Pay Rounding - 1042 - Learn Excel from MrExcel

Another question from Gopal. New India Pay Commission guidelines suggests that pay should be rounded to increments of 10. If the amount over the last 10 is less than or equal to 0.99, then round down. Otherwise round up. While Excel’s built-in functions don’t handle this, Episode 1042 shows you how to handle this using three functions in Excel.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Monday, June 22, 2009

### Future IF - 1041 - Learn Excel from MrExcel Podcast

Mario has built a great nested IF formula for assigning points to soccer games. However, the formula is calculating 1 point for both teams for all unplayed games. In Episode 1041, I take a look at how to modify the original formula to calculate nothing until a score has been entered.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Thursday, June 18, 2009

### Round & Round - 1039 - Learn Excel from MrExcel Podcast

Gopal asks how to round a number up to the next whole number. Episode 1039 takes a look at several different functions available for rounding numbers in Excel.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Tuesday, June 16, 2009

### 2nd Smallest - 1037 - Learn Excel from MrExcel Podcast

If you need to find the smallest value in a range, you would use MIN. Episode 1037 will show you how to find the second smallest value, third largest value and so on. Also in this episode, sort with a formula.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Thursday, June 11, 2009

### Count Visible Rows - 1034 - Learn Excel from MrExcel Podcast

Isabel asks how to count only the visible entries in a range. =COUNTA() is returning all of the values. Episode 1034 will show you how to count or sum the visible rows in a range.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Thursday, June 04, 2009

### British Cattle (Really) - 1029 - Learn Excel from MrExcel Podcast

Yes - a podcast of use to 10,000 British Cattle Farmers but of no use to anyone else. Actually...it is a fun challenge. Play this one to see the problem then see if you can come up with a formula to extend the fine numbering system used for British Cattle! Episode 1029 will show you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Friday, May 29, 2009

### Dueling: Sort with a Formula - 1025 - Learn Excel from MrExcel Podcast

A dueling Excel podcast where Mike and Bill take a look at how to sort using a formula. While most people would use RANK and COUNTIF, Episode 1025 shows you how to use LARGE and ROW or ROWS.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Wednesday, May 20, 2009

### Elapsed Days - 1018 - Learn Excel from MrExcel Podcast

Figure out the number of billable days between two dates. Episode 1018 looks at ways to count the number of days, number of workdays, or number of Monday-Wednesday-Friday dates between two dates.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Friday, April 17, 2009

### MrExcel's Learn Excel #995 - Dueling: Last Entry in Row

Another Dueling Excel podcast. Bill and Mike discuss formulas to grab the last entry in a row. Episode 995 shows you two methods for solving the problem.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Thursday, March 19, 2009

### MrExcel's Learn Excel #974 - NonContiguous Spearing

You may have seen how to create a spearing or 3-D formula such as =SUM(Jan:Dec!B3). In Episode 974, an amazing way to create a 3D reference to non-contiguous sheets.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Friday, February 27, 2009

### MrExcel's Learn Excel #960 - Formula Tooltip

Paul sends in a few tricks to help deal with the Function Tooltip that appears when you edit a cell. Episode 960 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Thursday, February 26, 2009

### MrExcel's Learn Excel #959 - Split Alt+Enter

Someone used Alt+Enter to enter three fields in hundreds of cells. How can this be broken into separate columns. In Episode 959, I discuss MID, CODE, CHAR, and SUBSTITUTE functions to solve the problem.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Tuesday, February 03, 2009

### MrExcel's Learn Excel #942 - First of Next Month

Rod has a formula in column B that calculates the earliest month in a data set. He then wants formulas in C, D, E, and so on that show the first of the next month. Although you can right-click the fill handle to do this, Episode 942 will show you how to solve this using the DATE function.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Monday, February 02, 2009

### MrExcel's Learn Excel #941 - Count Combinations

Sergio from Mexico asks how to count unique combinations of two fields. Episode 941 shows three methods for solving this problem.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Friday, December 26, 2008

### Episode 916 - AutoNumbering & Twitter

The day after Christmas and you are at work? Or just checking out the podcast on your new iPod? Let's do a social networking experiment. Plus...how to autonumber rows. And...a podcast special at the MrExcel store. Watch it all in Episode 916.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Thursday, December 04, 2008

### Episode 902 - Decimal Hours

Calculate time difference and show the result as a decimal number of hours. Episode 902 will show you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Monday, December 01, 2008

### Episode 899 - Minus Minus

Kim asks about the minus minus in certain formulas. In Episode 899, I will break one of those formulas down and show exactly why people use the minus minus.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Friday, November 14, 2008

### Episode 890 - Offset as TableArray

An alternative to Episode 889...If you need to match two sorted columns with a VLOOKUP, you can make the table array be dynamically calculated with the OFFSET function. Episode 890 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Thursday, November 13, 2008

### Episode 889 - Concatenated Key

You need to do a VLOOKUP that will look up two values from a table. In Episode 889, I will show a method using a concatenated key field to enable VLOOKUP to work.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Thursday, November 06, 2008

### Episode 884 - HLOOKUP with VLOOKUP?

Robert asks how he can combine an HLOOKUP and a VLOOKUP together into a single formula. This is a common problem, but you don't use either VLOOKUP or HLOOKUP. Instead, Episode 884 will introduce a function called MATCH. This obscure but versatile function is similar to both VLOOKUP and HLOOKUP. Episode 884 shows you how to solve this problem.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Thursday, October 16, 2008

### Episode 869 - Final Pivot Row?

Richard is building a formula outside of the pivot table that needs to divide by the grand total of the pivot table. In Episode 869, I show two solutions, one of which is an incredibly geeky use of the other type of VLOOKUP.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Tuesday, October 07, 2008

### Episode 862 - Color Precedents

Dan from Texas asks: is there a way to change the color of cells that are precedents of a particular formula. Use the technique in Episode 862 to quickly complete this task.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Thursday, September 25, 2008

### Episode 854 - VLOOKUP Below

Can the VLOOKUP return a value one row below the matched value? Episode 854 shows you how to achieve this result using two other functions.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Wednesday, September 24, 2008

### Episode 853 - Preventing #N/A

You love VLOOKUPs, but you hate the #N/A error that results. In our Where is it Wednesday edition of the MrExcel podcast, I will show you two ways to prevent #N/A as the result of your VLOOKUP formulas. One harder method in Excel 2003 and an easier method in Excel 2007. Episode 853 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, September 16, 2008

### Episode 847 - Filling Blanks

Seiichi from Japan asks how to fill in blank cells in column A with the value above the blank. Episode 847 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, September 02, 2008

### Episode 837 - Add up C5 cells

Interesting question from the Southeastern Accounting Show: I want to sum from cell A5 and sum down a certain number of cells. However, the number of cells is stored in cell C5!

Episode 837 will show you the obscure but flexible OFFSET function that can solve this problem.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Friday, August 29, 2008

### Episode 835 - Transposing Formulas

Tim asked a question about writing a formula that he could copy across a row which would automatically grab successive values from down a column. (Basically doing a Transpose that is always updating). In Episode 835, I will show you my formula method and also discuss Kathy's easier suggestion that will work in some cases.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Friday, August 22, 2008

### Episode 830 - Dates II

Continuing the discussion from Episode 829, I show how to use the array of dates from a start date to an end date in order to perform a calculation on each date in the array. Episode 830 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, August 19, 2008

### Episode 827 - SUMIF

The reason that people create data like in podcast 826 is so that they can get totals by section to carry through to another report. However, if that other report used SUMIF, the problem would be solved. Episode 827 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, July 01, 2008

### Episode 792 - Substitute Total

Another way to solve yesterday's problem is to use the =SUBSTITUTE function instead of LEFT and LEN. Episode 792 will show you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Monday, June 30, 2008

### Episode 791 - Trimming Total

After adding Subtotals, Excel will append the word " Total" to the field being subtotaled. In Episode 791, we take a look at how to use a formula to remove the totals so that the data can be used as a lookup table.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Wednesday, May 28, 2008

### Episode 768 - Multiple IF

How do you deal with many levels in an IF statement? Episode 768 discusses nesting IF statements.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Friday, May 16, 2008

### Episode 760 - No Outliers

How can you calculate the MIN or MAX value ignoring one outlier? Or, how can you figure out the MIN or MAX throwing out 5% of the outliers? Episode 760 takes a look at SMALL, LARGE, and PERCENTILE functions.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , , ,

## Wednesday, May 07, 2008

### Episode 753 - Text Date/Time

When Romas sent in the question for podcast 751, I couldn't tell from the screenshot if his data actually was date/time or text that looked like a date/time. In Episode 753, I take a look at how to tell if the cell contains a date or something that looks like a date. You will also see how to use the TEXT, LEFT, MID, DATE functions to convert the text date/time to a real date.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , , ,

## Tuesday, May 06, 2008

### Episode 752 - Truncating to Date

In today's podcast, we take a look at breaking Romas' date/time column into a date column using the INT function. Episode 752 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Thursday, May 01, 2008

### Episode 749 - Sum Overdue

Rob has a spreadsheet showing install dates for several batteries. He needs to sum all of the batteries that are overdue for being replaced. This requires a tricky variation of the SUMIF formula. Episode 749 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, March 11, 2008

### Episode 712 - First Subtotaled Item

Dick sends in a cool question today. First, he figured an amazing way to grab the first value from a subtotaled group. But, Episode 712 shows three lines of VBA code to finish the process.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Monday, August 20, 2007

### Episode 575 - Editing Formula Ranges

A quick trick showing how you can make a formula point to a new range without typing the range. Episode 575 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , ,

## Tuesday, July 17, 2007

### Episode 551 - Red-Flag Dates

Van writes in with a suggestion about podcast 539. In that podcast, we were trying to calculate which customers had their birthday coming up soon. Van suggests using conditional formatting and the TODAY() function to flag the customers to whom you need to send birthday cards. Episode 551 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , , ,

## Friday, June 08, 2007

### Episode 525 - MegaFormula Redux

Gary sent in a better way to build MegaFormulas. Gary must be catching up on old podcasts, because he sent a note about Episode 387. Compare my way and Gary’s way of building MegaFormulas in Episode 525.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , ,

## Monday, May 21, 2007

### Episode 512 - Moving Average

Ricardo sends in a tricky formula question for today’s podcast. How can you calculate a moving average of all sales in the last 90 days, particularly when your data does not include one row for every day? In Episode 512, I discuss the really convoluted solution in Excel 2003 and the only slightly convoluted solution in Excel 2007.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , , ,

## Wednesday, April 25, 2007

### Episode 494 - Time Stripping

Jonathan from California has a column with date & time information, but would like to strip out just the times to do an analysis by hour. Episode 494 shows you two formulas that can be used to analyze the data by hour.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , ,

## Wednesday, March 28, 2007

### Episode 474 - Daily F9

Dick Kusleika is our guest podcaster today. Dick is the host of the Daily Dose of Excel. He shows how to troubleshoot formulas by evaluating parts of a formula right in the formula bar. Episode 474 shows you how.

Labels:

## Tuesday, January 16, 2007

### Episode 423 - Table Formulas

One of the best features of the new Excel 2007 table functionality is the new table formulas that Excel can create. Once you enter a formula in the first row of a dataset, Excel 2007 will automatically copy the formula down to all of the rows. Episode 423 shows you the details.

This blog is the video netcast companion to the upcoming book, Excel 2007 Miracles Made Easy. Download a new two minute video every Tuesday and Thursday to learn one of the tips from the book!

Labels: ,

## Monday, December 11, 2006

### Episode 400 - OffSheet Precedents

It is a real pain to try to trace precedents that are on other worksheets or in other workbooks. Episode 400 shows some formula auditing tricks, including a trick to see all of the precedents on other worksheets. This is our 300th episode!

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book.

## Monday, November 20, 2006

### Episode 387 - MegaFormula Copying

Sometimes when you have a difficult formula like the one in Episode 386, you end up creating the formula in several steps. Once you have the formula working, but spread out over several columns, use the technique in this episode to bring everything back together into one megaformula. Episode 387 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels:

## Friday, November 17, 2006

### Episode 386 - 2nd Tuesday

Ken from Chicago calls with a tough question - how can Excel calculate the 2nd Tuesday of November for a series of years. The solution involves a series of obscure Excel functions. Episode 386 shows you how. If you have a question for the Netcast, call 1-866-581-0221 and leave your message on the recording.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels:

## Monday, November 06, 2006

### Episode 377 - GetPivotData Function

In Excel 2002+, try to create a formula next to your pivot table. When you copy this formula down to other cells, the formula keeps pointing at the original cells! Is this a bug? No, it's a feature! Well, whatever it is, in Episode 377 I will show you how to stop the functionality so that things continue to work like they did back in Excel 2000.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , ,

## Thursday, October 26, 2006

### Episode 370 - AutoShape Formulas

Rather than static text in the AutoShape, use a formula to add dynamic text to an AutoShape. Episode 370 shows you how to create a shape that shows off the total of all sales made today.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , , ,

## Tuesday, September 26, 2006

### Episode 348 - Max or If

Calculate Sales over Quota using either the IF or MAX functions. Episode 348 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Labels: , ,

## Wednesday, January 04, 2006

### Episode 160 - Get characters after the Dash

Yesterday's podcast showed how to use LEFT and FIND to get all of the characters before a dash. What if you need all of the characters after the Dash? There are a couple of options. The first option discussed in the episode is to combined MID, FIND, and LEN to get the characters. Alternatively, you can use TRIM and leave off the LEN function. Or, in Excel 2003, just leave off the LEN function. Episode 160 will show you how.

This new blog is the video podcast companion to the new book, Learn Excel from MrExcel. Download a new two minute video every workday to illustrate one of the 277 tips from the book!

Labels: , , , ,