Learn Excel from MrExcel Video Podcast

Episode 861 - Subtotal Anomolies

consult@MrExcel.com (Bill Jelen) — Mon, 6 Oct 2008 13:00:00 GMT

The subtotal command is a fantastic time-saver, but it occasionally behaves erratically. In Episode 861, I will show two situations which can cause the subtotal command to whack out.

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!

Episode 860 - Header Logo

consult@MrExcel.com (Bill Jelen) — Fri, 3 Oct 2008 13:00:00 GMT

Adding a company logo jpg to your worksheet header. Episode 860 shows how to add a header, and then poses a question about if your image is scaling in size as you add more data.

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!

Episode 859 - Two Mysteries

consult@MrExcel.com (Bill Jelen) — Thu, 2 Oct 2008 13:00:00 GMT

Its two-for-the-price-of-one-Thursday (sounds like we need a new theme...). Question 1: When editing a long formula, the formula covers up adjacent cells, making it difficult to point to those cells with the mouse. Question 2: When you use Shift+Ctrl+Right+Ctrl+Down to select a large range, the Error icon scrolls off the screen. I'll propose solutions to both of these in Episode 859.

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!

Episode 858 - WIIW - Copy as Picture

consult@MrExcel.com (Bill Jelen) — Wed, 1 Oct 2008 13:00:00 GMT

In our Where Is it Wednesday podcast, we go looking for a command that most people never would have been able to find in Excel 2003! If you want to send a perfect copy of a formatted spreadsheet in an e-mail, you could use Snag-It or PrtScrn, but by holding down the shift key, you can find a hidden Copy as Picture command in Excel 2003. Episode 858 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!

Episode 857 - Smaller Pivot Files

consult@MrExcel.com (Bill Jelen) — Tue, 30 Sep 2008 13:00:00 GMT

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!

Episode 856 - Print 2-Up

consult@MrExcel.com (Bill Jelen) — Mon, 29 Sep 2008 13:00:00 GMT

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!

Episode 855 - Dynamic Comment?

consult@MrExcel.com (Bill Jelen) — Fri, 26 Sep 2008 13:00:00 GMT

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!

Episode 854 - VLOOKUP Below

consult@MrExcel.com (Bill Jelen) — Thu, 25 Sep 2008 13:00:00 GMT

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!

Episode 853 - Preventing #N/A

consult@MrExcel.com (Bill Jelen) — Wed, 24 Sep 2008 13:00:00 GMT

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!

Episode 852 - Tiny Blank Columns

consult@MrExcel.com (Bill Jelen) — Tue, 23 Sep 2008 13:00:00 GMT

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!

Episode 851 - Filter by Selection II

consult@MrExcel.com (Bill Jelen) — Mon, 22 Sep 2008 13:00:00 GMT

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!

Episode 850 - Copy Worksheet 750

consult@MrExcel.com (Bill Jelen) — Fri, 19 Sep 2008 13:00:00 GMT

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!

Episode 849 - Remove Group

consult@MrExcel.com (Bill Jelen) — Thu, 18 Sep 2008 13:00:00 GMT

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!

Episode 848 - Speak Status

consult@MrExcel.com (Bill Jelen) — Wed, 17 Sep 2008 13:00:00 GMT

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!

Episode 847 - Filling Blanks

consult@MrExcel.com (Bill Jelen) — Tue, 16 Sep 2008 13:00:00 GMT

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!

Episode 846 - 2007 Pivot Table 101

consult@MrExcel.com (Bill Jelen) — Mon, 15 Sep 2008 13:00:00 GMT

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!

Episode 845 - Stacked Column 3

consult@MrExcel.com (Bill Jelen) — Fri, 12 Sep 2008 13:00:00 GMT

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!

Episode 844 - AutoSum Macro

consult@MrExcel.com (Bill Jelen) — Thu, 11 Sep 2008 13:00:00 GMT

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!

Episode 843 - WIIW - Custom Toolbars

consult@MrExcel.com (Bill Jelen) — Wed, 10 Sep 2008 13:00:00 GMT

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!

Episode 842 - SumIf a Range

consult@MrExcel.com (Bill Jelen) — Tue, 9 Sep 2008 13:00:00 GMT

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!

Episode 841 - Transpose Formula II

consult@MrExcel.com (Bill Jelen) — Mon, 8 Sep 2008 13:00:00 GMT

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!

Episode 840 - Strange Pivot

consult@MrExcel.com (Bill Jelen) — Fri, 5 Sep 2008 13:00:00 GMT

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!

Episode 839 - Filter by Selection

consult@MrExcel.com (Bill Jelen) — Thu, 4 Sep 2008 13:00:00 GMT

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!

Episode 838 - WIIW - Move Direction

consult@MrExcel.com (Bill Jelen) — Wed, 3 Sep 2008 13:00:00 GMT

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!

Episode 837 - Add up C5 cells

consult@MrExcel.com (Bill Jelen) — Tue, 2 Sep 2008 13:00:00 GMT

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!

Episode 836 - Pie Chart on a Coin

consult@MrExcel.com (Bill Jelen) — Mon, 1 Sep 2008 13:00:00 GMT

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!

Episode 835 - Transposing Formulas

consult@MrExcel.com (Bill Jelen) — Fri, 29 Aug 2008 13:00:00 GMT

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!

Episode 834 - 3-D Spearing

consult@MrExcel.com (Bill Jelen) — Thu, 28 Aug 2008 13:00:00 GMT

If you need to consolidate multiple worksheets as I discussed in Episode 832, you can skip consolidate and build a spearing or 3-D worksheet reference. Episode 834 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!

Episode 833 - Close Excel

consult@MrExcel.com (Bill Jelen) — Wed, 27 Aug 2008 13:00:00 GMT

Back in the good old Excel, you could click the upper "X" in the top right corner to close Excel. Well...that does not work anymore - both of the "X"s will close the workbook. In Episode 833, I will show you the quick way to close out of Excel. (Maybe you work at a dot-com, and your manager would be upset if you were doing spreadsheets instead of playing solitaire?)

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!

Episode 832 - Consolidate Sheets

consult@MrExcel.com (Bill Jelen) — Tue, 26 Aug 2008 13:00:00 GMT

The Consolidate command is versatile. In Episode 832, see how to use Consolidate to summarize similar data that occurs on multiple worksheets.

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!

Episode 831 - Consolidate

consult@MrExcel.com (Bill Jelen) — Mon, 25 Aug 2008 13:00:00 GMT

During a recent seminar, someone asked me to show the audience how to use the Consolidate command. I launched into the technique shown in Episode 831 - using consolidate to collapse a single range down to one line per customer. In tomorrow's podcast, we will look at alternate uses for consolidate.

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!

Episode 830 - Dates II

consult@MrExcel.com (Bill Jelen) — Fri, 22 Aug 2008 13:00:00 GMT

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!