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

## Tuesday, September 30, 2008

### Episode 857 - Smaller Pivot Files

Say that you have a pivot table and need to send the file via e-mail. Use the trick in Episode 857 to dramatically reduce the size of your Excel workbook.

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, September 29, 2008

### Episode 856 - Print 2-Up

Is there any way to make Excel print 2 pages per physical sheet of paper? The answer is maybe...it all depends on your printer! Episode 856 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, September 26, 2008

### Episode 855 - Dynamic Comment?

Sergiy asks if there is any way to have a cell comment or a validation input box reflect a value from a formula in a cell. I couldn't find a good solution, other than using VBA. Episode 855 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, 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 23, 2008

### Episode 852 - Tiny Blank Columns

Sometimes a manager demands that you insert tiny little columns between the columns of your data set. While I hate this, use the trick in Episode 852 to ensure that the data set sorts correctly.

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, September 22, 2008

### Episode 851 - Filter by Selection II

Wiebe points out a better solution for Episode #838 - Excel already has an icon for Filter by Selection. It is hard to find and poorly labeled, but it is in there. Episode 851 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, September 19, 2008

### Episode 850 - Copy Worksheet 750

I've shown how to make copies of a worksheet within a workbook before, but in today's podcast, someone asks if you can drag a worksheet to a new workbook. Episode 850 shows you how. Also - in honor of the 750th podcast episode, a new contest for Saturday.

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 18, 2008

### Episode 849 - Remove Group

After adding subtotals by Customer, Excel adds three group and outline buttons. Most people use the #2 and #3 buttons but rarely use the #1 button. Could you simply remove the #1 button? Podcast 849 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, September 17, 2008

### Episode 848 - Speak Status

Can Excel announce a status when someone opens the workbook? This macro will let someone know if they are over or under quota. Episode 848 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: ,

## Monday, September 15, 2008

### Episode 846 - 2007 Pivot Table 101

Creating your first pivot table in Excel 2007.
Episode 846 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, September 12, 2008

### Episode 845 - Stacked Column 3

Episodes 801 and 815 suggested many ways for getting the total of 3 series in a stacked column chart to appear above the column. Both Ute and John wrote in with yet another method, a method that is probably the easiest. Episode 845 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: ,

## Thursday, September 11, 2008

### Episode 844 - AutoSum Macro

Mark asks about how to replicate the AutoSum concept in a macro. The code in Episode 844 gets closer to the AutoSum functionality than the solution that I proposed in Episode 812.

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 10, 2008

### Episode 843 - WIIW - Custom Toolbars

Robert asks how I get the custom toolbar below my Excel 2007 ribbon. This is the Quick Access Toolbar. In Episode 843, I will show you how to customize this toolbar in Excel 2007.

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 09, 2008

### Episode 842 - SumIf a Range

Marc sends in an interesting question about SUMIF. Each time the value is equal to January, he wants to grab a five-cell range! SUMIF doesn't seem up to the challenge, but an array formula with OFFSET does the trick. Episode 842 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, September 08, 2008

### Episode 841 - Transpose Formula II

Kyle sends in a different way to transpose with a formula - the array function =TRANSPOSE will do the trick. Also, Joe and Adrien check in with alternate ways of closing Excel. Episode 841 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, September 05, 2008

### Episode 840 - Strange Pivot

Vaibhav sends in a question about creating a particular pivot table. There are a number of challenges in this project. In Episode 840, you will see how to re-order items along a pivot field, how to remove subtotals from one field, how to arrange multiple date fields to go across the page, changing the headings from Sum of X to just X, and filling in blanks with zeroes.

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 04, 2008

### Episode 839 - Filter by Selection

Shawn sends in a great tip for today's podcast. Access has a Filter by Selection icon. Using a few lines of VBA code, you can add this functionality to your personal macro workbook. Episode 839 shows you how.

Here is the code:
Sub Filter_by_Active_Cell()
Dim ColNum As Integer
ColNum = ActiveCell.Column - _
(ActiveCell.CurrentRegion.Column - 1)
Selection.AutoFilter Field:=ColNum, Criteria1:=ActiveCell
End Sub

Sub AutoFilterToggle()
Selection.AutoFilter
End Sub

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 03, 2008

### Episode 838 - WIIW - Move Direction

Donna asked a question at one of my Excel 2007 seminars: There used to be a way in Excel 2003 to prevent the active cell from moving downwards after pressing Enter. In today's Episode 838 - Where Is It Wednesday, I will take a look at how this feature works, where it is in Excel 2007, and how to simulate the feature using a shortcut key.

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

## Monday, September 01, 2008

### Episode 836 - Pie Chart on a Coin

This was an intriguing question...how can you set up a pie chart in Excel that appears to divide a dollar coin into wedges?

It is not as easy as you would think. If you try to fill the chart with an image of a coin, Excel repeats the coin image on each wedge of the pie. Cool, but not what we are looking for.

Episode 836 will show you how to successfully get the coin as the background of the pie chart.

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: