Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Scott from Australia sent in what should have been a simple lotto question. However, I chose to solve the problem using an array formula. Now, as far as I know, no one has ever tried to explain an array formula in a 2 minute podcast. Episode 454 shows you how.
posted by Bill at 9:00 AM
After Scott’s question for tomorrow’s podcast, I thought it would be cool to see how a pivot table could analyze the most popular lottery numbers. The MegaMillions jackpot in several US states is up to $216 million and today’s podcast will select the most popular numbers. Episode 453 shows you how.
posted by Bill at 9:00 AM
Rupal asks how to return the worksheet name as the result of a formula. Episode 452 shows the obscure function which can be adapted to solve this problem.
posted by Bill at 9:00 AM
Heidi asks a question that is a new twist on an old problem – how to fill in the blank cells in a range with an outline numbering system. Episode 451 shows how to adapt the technique discussed in Learn Excel to solve this problem.
posted by Bill at 9:00 AM
As a follow-up to yesterday’s netcast, Episode 450 shows you how to unhide column A and a cool trick left over from Lotus 1-2-3 where you can see what is in the hidden columns without unhiding them.
posted by Bill at 9:00 AM
Stuart from the UK asks: say that you have columns B through H hidden and want to unhide only one of those columns? Episode 449 will take a look at the process of hiding and unhiding columns. Although I had never tried it before Stuart posed the question, it seems that there is a way to unhide just that one column.
posted by Bill at 9:00 AM
Another popular way for people to lie with charts is to use a cone or pyramid chart. Episode 448 shows why your radar should go up anytime you see someone presenting data using these chart types.
posted by Bill at 9:00 AM
Here is a bizarre problem sent in by a viewer. Many of you will think that you know the answer, but this problem is not as simple as it appears. In a worksheet, any dates are instantly converted to their 5 digit serial number. The actual date is shown in the formula bar, but the worksheet cells show the underlying serial number. Yes – my first thought was that someone had formatted the worksheet using a Number format, but this is not the problem. Hint: this is a simple spreadsheet set up to log information, and there is not a single formula anywhere in the worksheet. Episode 447 solves the mystery.
posted by Bill at 9:00 AM
Watch out when someone tips a pie chart backwards to show a 3-D perspective. They are either unintentionally or intentionally trying to misrepresent the data. Episode 446 shows you how.
posted by Bill at 9:00 AM
A question from Mike in Oklahoma City. Excel is supposed to copy only the visible rows in a dataset where AutoFilter is turned on. However, sometimes this does not work. Episode 445 shows the simple shortcut to make sure that you have only the visible rows.
posted by Bill at 9:00 AM
In a recent charting challenge, Jerome Sullivan sent in an interesting chart where the budget and actual numbers were actually plotted on top of each other. Episode 444 shows the non-intuitive steps required to create this type of chart.
posted by Bill at 9:00 AM
If you have inherited a computer where someone has customized your toolbars, you might want to go back to the default, out-of-the-box set of toolbars. Episode 443 will show you how to reset your standard toolbar, the formatting toolbar, and your worksheet menu bar to undo any rogue customizations.
posted by Bill at 9:00 AM
How to create a combination chart that includes both stacked columns and a line chart. Episode 442 shows you how.
posted by Bill at 9:00 AM
Eric from Shanghai writes in with a question about adding a Paste Formulas icon to the standard toolbar in Excel. While Excel only offers custom buttons for Paste Values and Paste Formats, Episode 441 will help you discover how to get a Paste Formulas icon on your toolbar.
posted by Bill at 9:00 AM
Excel 2007 does offer a number of improvements in the AutoFilter arena. Episode 440 shows the new virtual date filters, such as selecting records from this week, next month, or last quarter.
posted by Bill at 9:00 AM
Jim from Montreal calls in wondering why the AutoFilter dropdowns changed radically in Excel 2007. Episode 439 takes a look at the old AutoFilter, and the new AutoFilter.
posted by Bill at 9:00 AM
Excel 2007 offers an oddity - an icon to do translations between 20 different languages. Episode 438 shows you how.
posted by Bill at 9:00 AM
Did you ever notice the status bar below your spreadsheet? Select some numbers in Excel and the totals will appear in the status bar. In Episode 437, learn how to change the number shown in the status bar, and also the new enhancements in Excel 2007.
posted by Bill at 9:00 AM
Use the Goal Seek function to back into an answer. Episode 436 shows you how.
posted by Bill at 9:00 AM
The Home & Student edition of Office 2007 includes a product that may be new to you - OneNote 2007. Episode 435 takes a quick tour of OneNote.
posted by Bill at 9:00 AM
