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

Thursday, May 31, 2007

Episode 519 - Center Sorting

George has a dataset today where a serial number contains 3 groups of digits in a single column. George needs to be able to sort by the middle group of digits. Episode 519 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, May 30, 2007

Episode 518 - Copy Across

Richard sends in todays question: How do you copy a formula horizontally when it’s reading from a vertical list? Episode 518 shows the easy, but unintuitive solution.

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, May 29, 2007

Episode 517 - Conditional Find

Rod calls in a question today; how to use conditional formatting to highlight all the track athletes from a particular school. This requires the conditional format to look for the school name within a cell. This is very easy in Excel 2007 but very difficult in Excel 2003. Episode 517 discusses the functions required to make it work.

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

Labels: , ,

Friday, May 25, 2007

Episode 516 - Event Handlers

In honor of Memorial Day in the United States, a question sent in from a naval aviator who has to enter a series of time values. Using a tiny bit of VBA code, we can eliminate the need to type the colon in the middle of the time value. Even if you don’t have to enter times all day, the techniques in Episode 516 can ease the redundant data entry tasks.

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, May 24, 2007

Episode 515 - Paste Options Redux

Last Thursday, we pondered how to get rid of the annoying Paste Options icon after using the fill handle. In today’s podcast, suggestions from viewers Bob and Tom solve the problem. Episode 515 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!

Wednesday, May 23, 2007

Episode 514 - Subtotals Count

Why does the automatic subtotals command sometimes choose to Sum and sometimes choose to Count? Episode 514 shows you why Excel seems to arbitrarily count or sum.

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, May 22, 2007

Episode 513 - Group Mode

Joe writes in with a faster way to get an entire workbook into Group mode. This is a fast way to make changes to all of the sheets in a workbook. Episode 513 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: , ,

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: , , ,

Friday, May 18, 2007

Episode 511 - Filling Blanks

While yesterday’s podcast talked about using the Fill Handle, there are times when there are methods faster than using the fill handle repeatedly. Episode 511 shows how to fill in a whole column of blank cells in one process.

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, May 17, 2007

Episode 510 - Dismissing AutoFill

When you drag the fill handle, Excel offers an extremely useful AutoFill Options dropdown. Well, it is extremely useful UNLESS you really want to get rid of the dropdown so you can see the value in the next row, then the dropdown is really annoying! In Episode 510, you will learn how to control whether Excel will copy or fill the series so that you can turn this annoying icon off.

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!

Wednesday, May 16, 2007

Episode 509 - Corralling Students

Christina asks how to make sure that the students in her class stay within a tiny range of the spreadsheet. Episode 509 discusses two methods to solve this problem.

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!

Tuesday, May 15, 2007

Episode 508 - Add a Dash

If you need to spice up a column in your report, Episode 508 will teach you how to add a dash to a column. Viewer George writes in to say that his vendor needs the orders transmitted with a dash in the middle of the part number, but George’s data does not have the dash. Learn how to use MID and LEFT and concatenation to put a dash in the middle.

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 14, 2007

Episode 507 - JanFebMar Conversion

Your crazy software exported a file where the date column has the not-so-useful values like “Jan”, “Feb”, and “Dec” in a column. Episode 507 looks at a function to convert those values to real dates.

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, May 11, 2007

Christina asks how to create a gradebook in Excel. Episode 506 shows off how to create absolute references and also the obscure TRUE version of the VLOOKUP function. Everyone – teachers and non-teachers should check out the VLOOKUP in this 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!

Labels:

Thursday, May 10, 2007

Episode 505 - Wildcard CountIf

Thanks to many viewers who wrote in after Episode 497, there is a much easier way to count the number of cells that contain a certain substring. In Episode 505, learn how to use the wildcard options in COUNTIF and AutoFilter.

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, May 09, 2007

Episode 504 - Connecting Shapes

If you use the shapes on the drawing toolbar such as the rectangle, oval, and arrow, you will discover that the shapes do not stay connected when you move them. Rather than using the arrow, Episode 504 shows you how to connect the shapes using a connector that will always stay attached to the shapes.

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, May 08, 2007

Episode 503 - Pivot Sections

Say that you have a pivot table with region and product in the row area. If you would like a blank row between each region, or even a page break between each region, change the Layout properties for the field. Episode 503 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:

Monday, May 07, 2007

Episode 502 - Color Pivot

There is a cool but subtle way to select all of the subtotal rows in your pivot table in order to format them with a different color. Episode 502 shows you how. This tip came from an audience member at the 33rd annual Meonske Accounting Conference at Kent State. Thanks to Dr. Norm for inviting me back as a speaker at this excellent conference.

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, May 04, 2007

Episode 501 - Data Entry

Today’s podcast shows a better way to do data entry. Rather than having to change the Move Selection After Enter Direction, you can use the Tab and Enter keys to quickly navigate through a data entry range. Episode 501 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:

Thursday, May 03, 2007

Episode 500 - 500!

As we hit this milestone, you can win one of a treasure trove of prizes, including a video iPod, great books from QUE, and more. There are over 40 prizes in all. Also in Episode 500, I draw the winner for the March/April challenge of the month, using a combination of Advanced Filter, INDEX, and RANDBETWEEN. I will use a similar method to award the prizes from our podcast #500 giveaway.

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, May 02, 2007

Episode 499 - Spelling Numbers

George sends in a question – how can you convert a number to words? For example, how can you write \$1234.56 as One Thousand Two Hundred Thirty Four dollars and Fifty Six cents. Although many utility packs offer this functionality, the VBA code is actually available for free from Microsoft. Episode 499 shows you how to add the code to your workbook. Be sure to stop back tomorrow for episode #500, when we will give away a treasure trove of prizes.

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, May 01, 2007

Episode 498 - Running Average

In today’s netcast, a question about how to create a running total and a running average. Episode 498 discusses how to solve both of these formulas.

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: