FC2Video

Steve asks how to group records into age ranges. In Episode 949, I show how to use the range version of VLOOKUP to solve 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!

You get a CSV file from an International branch of your company and the dates are in M/D/Y when you use D/M/Y or vice versa. There is an easy solution. Episode 947 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!

Loh from Malaysia sends in a very hard question. How can you use COUNTIF to analyze only the visible rows from a filtered data set? I knew the solution would have to involve iterating through multiple SUBTOTAL functions, but Aladin from the MrExcel Message board provides the solution. Episode 946 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!

Rod from Australia sends in an excellent tip for today's episode. Rod receives a CSV file every day that needs to be transposed. Rod put together an elegant combination of INDEX, COLUMN, and ROW to solve this problem. Episode 945 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!

In Episode 943, I took data from Word and pasted to Excel for sorting. Now, I need to "type" that data back into Word, using the proper Style for each paragraph. Episode 944 shows you an Excel macro to automate this process. 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!

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!

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!

Pablo sends in a great question: why can't you copy a range of cells and paste only the total of those cells in a new cell? Microsoft should really add this functionality, but in the meantime, 2 lines of macro code solve the problem. Episode 936 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!

In case 54x20 formats is not enough for you, you can design your own table format. Episode 935 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!

The every-other-row formatting becomes simple in Excel 2007 using the Table functionality. Choose from 54x20 formats in seconds. Episode 934 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!

Excel 2003 used to offer an obscure conditional formatting option called Formula Is. It is still obscure in Excel 2007. Episode 933 will show you the ROW, MOD functions and a dynamic solution to formatting every other row. 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!

By the time I answered Lisa's question from yesterday, she had already formatted her spreadsheet, using a clever method that I would not have thought of. Episode 932 will show you how to format every other row using the fill handle. 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!

Lisa asks how to shade every other row in the worksheet. While there are several solutions, Episode 931 shows you how to use the old AutoFormat dialog to solve 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!

It is frustrating that commands in Excel 2007 are spread out amongst 7 different ribbon tabs. Episode 930 will show you how to add your favorite commands to the QAT - Quick Access Toolbar so that you can have one-click access to these commands. 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!

Yet another solution to matching data from two different worksheets...the Data Consolidate command. Episode 929 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!

Another solution to matching data on two worksheets is the Multiple Consolidation Ranges option in the pivot table. Unfortunately, it is much harder to find in Excel 2007. Episode 928 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!

Match two worksheets using a pivot table. We continue on this week with Jim's question about matching two worksheets with a common column. Rather than using VLOOKUP, you can use a pivot table to do the comparison. Episode 927 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!

Jim has two lists that he wants to match, but some records are missing from one list or the other. There are many ways to solve this. Episode 926 will show the classic double-lookup method. 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!

Apply icons only to the numbers that qualify for a red X. It is possible, but tricky. Episode 925 will show 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!

Adjust the ranges used by the icon sets. Episode 924 will show 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!

The one problem with icon sets is that they are always left-justified in the cell. Episode 923 will show you why this is a problem and how to overcome it. 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!

Add an icon set to a range in Excel 2007. Episode 922 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!

Change the default number of worksheets in a new workbook using Excel 2007. Episode 921 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!

As you switch to Excel 2007 and try to find things on the new Ribbon, look for the tiny dialog launcher icons to take you back to the old Excel 2003 dialogs. Episode 920 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!

Jamie from Hampshire UK offers a better way for hiding data in cells. Rather than changing the font color to white, you can use this custom number format. Episode 919 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!

Changing the chart background in Excel 2003. Episode 918 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!

Add a picture as a background for a chart in Excel 2007. Episode 917 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!

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 video is the podcast companion to the book Learn Excel 97-2007 from MrExcel.

Chuck has a spreadsheet where he has bolded the client name in a dozen cells. Unfortunately, only a portion of the cell is bolded and not the whole cell. The Excel 2007 Edit-Replace can not deal with this and changes the entire cell to bold. Episode 915 will show you a solution. 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!

Jack from Boston sends in a cool Windows trick to change the color of the selected cells. You would this this would solve the problem in Episode 907, but it doesn't. Still, you can watch Episode 914 to see how to enhance Excel 2003 and earlier. 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!

Yesterday, I talked about Jason's request to format a grouped pivot field in the row area of a pivot table. There is a solution, but it requires a bit of work in the original data set. Episode 913 will show you about the TEXT function and the other type of VLOOKUP.

Jason from Texas asks about formatting percentages in a pivot table. I thought this would be an easy question, but Jason wants to format the grouped percentages in the row area of the pivot table. Episode 912 talks about grouping in a pivot table but doesn't come up with a good solution to the problem.

Where is it Wednesday? A person in one of my seminars asked me about a feature missing from Excel 2007. While the feature sounded familiar, it was also missing from Excel 2003. In Episode 911, we will take a look at a faster way to start a formula using the mouse. This video is the podcast companion to the book Learn Excel 97-2007 from MrExcel.

Patrick has a tricky overtime calculation. Episode 910 will show how to calculate overtime for people who worked over 80 hours and to show a blank for people who worked less than 80 hours. This video is the podcast companion to the book Learn Excel 97-2007 from MrExcel.

Roseanne asks about the Scenario Manager in Excel. I hope that she will be able to use the Data Table discussed in Episode 794, because the Scenario Manager is relatively very difficult to use. Episode 909 will show you how. This video is a 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!

As you approach year end, you might want to calculate compound growth rates. Episode 908 will show you how. This video is the podcast companion to the book Learn Excel 97-2007 from MrExcel.

One of the annoying things about Excel 2007 is that the color for selected sheets and cells is almost identical to the color for unselected sheets and cells. Harry from Culver-Stockton college suggests a simple solution for the tab color in Episode 907.

Take mailing list data from Microsoft Excel and produce labels in Word. Episode 906 will show you how. Watch a new two minute video every workday to learn one of the 377 tips from the book Learn Excel 97-2007 from MrExcel.

Jan asks if it is possible to shade half of a cell. Episode 905 shows one cumbersome way to do it . This podcast is the video companion to the book, Learn Excel 97-2007 from MrExcel.

Mike from OK City asks how to delete all names in a workbook. In Episode 904, I will show you a way to delete all names or to delete only the invalid names. This video podcast is the companion to the book, Learn Excel 97-2007 from MrExcel.

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

Morne sends in a question about highlighting records that fall on a weekday. There are a couple of approaches. Episode 901 shows you how.

Continuing yesterday's discussion about minus minus, we'll take a look at using SUMPRODUCT vs the new Excel 2007 SUMIFS to solve the multiple conditional sum problem. Episode 900 shows you how.

Beardless Rot wants to know how to sort filtered data so that the top 10 autofilter will show items high-to-low. Episode 903 will show you how.

Morne sends in a question about highlighting records that fall on a weekday. There are a couple of approaches. Episode 901 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!

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!

Continuing yesterday's discussion about minus minus, we'll take a look at using SUMPRODUCT vs the new Excel 2007 SUMIFS to solve the multiple conditional sum problem. Episode 900 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!