MrExcel Excel Videos

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!

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!

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!

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!

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!

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!

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!

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!

Adjust the ranges used by the icon sets. Episode 924 will show you how. This video is the 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!