Learn Excel from Bill Jelen with this daily 2 minute video podcast.
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.
Labels: Pivot Table
posted by Bill at 9:00 AM
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.
Labels: printing
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
Labels: Sorting
posted by Bill at 9:00 AM
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.
Labels: Filter
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
Seiichi from Japan asks how to fill in blank cells in column A with the value above the blank. Episode 847 shows you how.
Labels: formula, Go To Special
posted by Bill at 9:00 AM
Creating your first pivot table in Excel 2007.
Labels: Excel 2007, Pivot Table
posted by Bill at 9:00 AM
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.
Labels: charting, Excel 2007
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
Labels: Excel 2007, Ribbon
posted by Bill at 9:00 AM
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.
Labels: array formula, Offset, SUMIF
posted by Bill at 9:00 AM
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.
Labels: array formula
posted by Bill at 9:00 AM
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.
Labels: Pivot Table
posted by Bill at 9:00 AM
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.
Labels: Filter by Selection, VBA
posted by Bill at 9:00 AM
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.
Labels: Editing, Excel 2007
posted by Bill at 9:00 AM
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!
posted by Bill at 9:00 AM
This was an intriguing question...how can you set up a pie chart in Excel that appears to divide a dollar coin into wedges?
Labels: charting
posted by Bill at 9:00 AM
