Episode 662 - SumProduct

There. I said it. SumProduct. Some of my friends in the U.K. seminars asked why I NEVER talk about SumProduct. And there is a really simple, but really stupid reason for it…. I missed that day in Excel class. SumProduct simply was not in my arsenal of tools, and so I find myself writing complex array formulas when a SumProduct would do the trick. In today’s Episode 662, we take a look at how to convert yesterday’s array formulas to SUMPRODUCT formulas.

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 661 - OR Conditional Sums

Jeff from MA writes with today’s question; creating a conditional sum array formula that uses OR functions instead of AND functions. Episode 661 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 660 - Weeks Ending on Sunday

Paul from Ontario asks how to create a timesheet dropdown showing all of the weeks ending on Sunday. Episode 660 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 659 - Deleting Borders Deletes Gridlines

Greg from Indiana sends in a great mystery today. He removed some borders and somehow the gridlines were removed as well. We’ll take a look at the pain someone must have went through, when the solution is right in the paint bucket dropdown. Episode 659 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 658 - Subtotal Row Formulas

Debbie from Virginia asks about adding new calculations to the subtotal lines in a data set with subtotals. Episode 658 shows how to do this.

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 657 - Find All

Today we take a look at some nifty tricks in the Find dialog box. While I bet you will learn something along the way, the big problem is that we can not effectively select all of the results of Find All across multiple worksheets. So, to Norel from California who asked the question, we don’t have a good answer, but for everyone else, you might find some cool tricks in Episode 657.

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 656 - Negative Charts

Chris from Long Island asks how to conditionally format the negative items in a chart. Episode 656 shows two methods for handling this.

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 655 - Year vs Year

Hiro asks how to build a pivot table to show year over year growth. Episode 655 will show you the cool grouping functionality in pivot tables which make this very easy.

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 654 - The Ribbon is Cool

There. I’ve said it. Let me say it again… “The Ribbon is Cool”. Wow. I haven’t heard anyone actually say that. We don’t adjust well to change and the ribbon is completely new. Yes – it is maddening at first, but they really used the “Customer Experience Improvement Program” data to find those commands that are really popular yet were hidden deep with the Excel 2003 menu. Let’s face it – how many amazing commands in Excel 2003 were hidden deep behind flyout menus and obscure dialog boxes. In Episode 654, I will take a look at some of the improvements in Excel 2007’s ribbon interface.

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 653 - Non-Numeric Managers

Do you have a manager who’s eyes glaze over when presented with a column of numbers? Excel 2007 offers some new amazing data visualizations. Episode 653 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 652 - Pivot 2007

Excel 2007 offers new pivot table functionality. Episode 652 shows you the details.

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 651 - Remove Duplicates

Excel 2007 offers a fantastic new way to remove duplicates from your data set. Episode 651 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 650 - 2007 Filter

In Episode 650, I take a look at the new functionality for Filters 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 649 - Slider Controls

Today, a second answer to Steve’s question about changing numbers in a worksheet by using a graphical control. Today, we use the ActiveX scrollbar control to solve the problem. Episode 649 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!

Episode 648 - Changing Numbers Graphically

Steve asks if he can build an Excel worksheet where people can change numbers by dragging a graphical element. Episode 648 shows you how you can use a chart in previous versions of Excel 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!

Episode 647 - Extending ABC

Excel knows how to extend any series, but it can not extend the letter “A” to “B”, “C”, etc. Episode 647 shows you a couple of tricks 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!

Episode 646 - Exit Excel

In Excel 2007, Microsoft has changed the behaviour of the “X” used to close the application. In Episode 646, I show you how to use the customize button to add a true “Close Application” button to the QAT.

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!

Episode 645 - Fibonacci Sequence

John from New York asks if Excel can be used to create the Fibonacci sequence. Episode 645 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!