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

Friday, September 28, 2007

Episode 601 - Redocking Properties

If, in the VBA editor, you accidentally undock the properties window, it is nearly impossible to redock it. Episode 601 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!

Thursday, September 27, 2007

Episode 600 - 500th Episode

I wonder if there is a mile-high club for podcasting? This episode was taped at 30,000 feet above the Microsoft campus. To celebrate our 500th episode, Bill makes a special offer for you today. Episode 600 has the details.

Monday, September 24, 2007

Episode 599 - Text Box Length

Joe Marten sends in a better way to limit the number of characters entered in a cell. Episode 599 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, September 21, 2007

Episode 598 - Evenly Distributing Numbers

Jerry from Georgia asks how to randomly distribute numbers from a list of numbers. For example, you might want to evenly distribute a list of prospects to sales reps. Episode 598 shows the somewhat convoluted method to make sure you don’t get any duplicates.

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, September 20, 2007

Episode 597 - Show All

After you’ve used an AutoFilter to see just a few records, how can you quickly show all records? Episode 597 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, September 19, 2007

Episode 596 - Auto Formula Copying

Rod notes that Excel has a cool feature that will cause Excel to automatically copy formulas when you add new rows of data. In Episode 596, we take a look at how this feature works, and how to turn it 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!

Tuesday, September 18, 2007

Episode 595 - Faster Fill Handle

Today, Dr Fill Handle provides a faster way to use the Fill Handle. Episode 595 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, September 17, 2007

Episode 594 - Run Macro Button

You’ve created a simple macro and want to assign the macro to a button on a worksheet. In today’s question, someone is trying to assign a macro to a button, but it is not working. She can assign the macro to a text box and it works, but not with a button. Episode 594 shows how to assign a macro to a text box, an AutoShape, or a button on the Forms toolbar.

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

Episode 593 - Unprotect Green Cells

Today, the questioner asks how to unprotect the green cells. There isn’t any good way to do this in the Excel user interface, so in Episode 593, I use a tiny bit of VBA code to achieve the effect.

The code used in the podcast is:

Sub UnProtectGreen
For each Cell in Selection
If Cell.Interior.ColorIndex = 4 then
Cell.Locked = False
Else
Cell.Locked = True
End If
Next Cell
End Sub
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, September 13, 2007

Episode 592 - Missing Pivot Dates

Today’s question is a follow-up to yesterday’s question. How can you make sure that a pivot table shows you one row per day, even if there were no sales on a given day? Episode 592 shows two annoying solutions. If you have a better solution, please send in your ideas to bill @ MrExcel.com.

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, September 12, 2007

Episode 591 - Missing Dates

When you create a chart from data that might have missing dates, you might want Excel to plot the missing dates along the horizontal axis. In Episode 591, we take a look at why Excel sometimes chooses to use a time scale and sometimes does not.

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

Episode 590 - Rolling Charts

Say that you need to show a rolling 6 month chart. Usually, I show how to drag the blue outline around the source data. However, in today’s podcast, Kirk offers an alternative tip – include all of the columns in your chart and hide/unhide the columns that you want to appear in your chart. Episode 590 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, September 10, 2007

Episode 589 - Ctrl+Alt+X

Bill from Addison TX sent in a great tip to allow you to open Excel with Ctrl+Alt+X, Notepad with Ctrl+Alt+N. Nominally, this makes the technique in Episode 578 faster, but in reality it will make everything you do in Windows much faster. Episode 589 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, September 07, 2007

Episode 588 - Sample Spreadsheets

When you use the New command, you have access to hundreds of free sample spreadsheets from Office Online. In Episode 588, we take a look at this feature in both Excel 2003 and 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:

Thursday, September 06, 2007

Episode 587 - Concatenate Range in VBA

Back in Episode 578, I used an incredibly complex method using NotePad for joining cells together. Today, Jonathan from the UK points out that a few lines of VBA code would have made the solution incredibly simple. Episode 587 shows you how.


Function ConcatenateRange(rCells As Range)
Dim vTemp As Variant
Application.Volatile

For Each vTemp In rCells
ConcatenateRange = ConcatenateRange & vTemp & " "
Next vTemp
End Function



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, September 05, 2007

Episode 586 - Shrink To Fit

Here is another solution to Episode 585: Instead of trying to limit how much data the person can type into a field, you can use the Shrink to Fit setting to reduce the font size to allow all of your information to appear. Episode 586 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!

Tuesday, September 04, 2007

Episode 585 - Text Length

Ron from Dallas asks how he can limit the number of characters that will fit in a certain cell. Episode 585 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!