Learn Excel from Bill Jelen with this daily 2 minute video podcast.
It's another Excel 2007 Thursday. I'll take a look at how your old Excel 2003 shortcut keys continue to work in Excel 2007. Episode 393 shows you how.
posted by Bill at 9:00 AM
Have you ever tried to enter an ID number that starts with zeroes? Excel automatically removes the leading zeroes, making it impossible for your VLOOKUPs to work. Rather than resorting to using a Zip Code format, try one of the tricks in Episode 392 to make Excel keep the leading zeroes.
posted by Bill at 9:00 AM
Starting on Tuesdays and Thursdays, I will be taking a look at the new features in Excel 2007. Today, an introduction to the ribbon and the options to minimize the ribbon. Episode 391 shows you the details.
posted by Bill at 9:00 AM
Jim from Montreal calls in today to ask if there is any way to change the grey background lurking behind your workbooks in Excel. While there is a setting that will adjust this background, it also annoyingly changes many other items as well. Episode 390 shows you how.
posted by Bill at 9:00 AM
Add visual interest to your reports by adding a colorful title. You aren't limited to the 30 styles in the initial WordArt gallery. Episode 389 shows you how to use WordArt to create eye-catching titles for your reports.
posted by Bill at 9:00 PM
Create an Org Chart in Excel. The org chart tool is hidden behind an obscure icon on the drawing toolbar. The details of using the tool are a bit complicated. Episode 388 shows you how to create an org chart.
posted by Bill at 9:00 AM
Sometimes when you have a difficult formula like the one in Episode 386, you end up creating the formula in several steps. Once you have the formula working, but spread out over several columns, use the technique in this episode to bring everything back together into one megaformula. Episode 387 shows you how.
posted by Bill at 9:00 AM
Ken from Chicago calls with a tough question - how can Excel calculate the 2nd Tuesday of November for a series of years. The solution involves a series of obscure Excel functions. Episode 386 shows you how. If you have a question for the Netcast, call 1-866-581-0221 and leave your message on the recording.
posted by Bill at 9:00 AM
Create a target diagram in Excel, using the business diagramming tools on the Drawing Toolbar. Episode 385 shows you how.
posted by Bill at 9:00 AM
Someone posed this question in a recent Power Excel seminar - he had to format cells with a strange format, and had to do this repeatedly. Learn how to record a simple macro to automate this task. Episode 384 shows you how.
posted by Bill at 9:00 AM
Setting up data validation lists, where the second list is dependent upon the answer given in the first dropdown box. Episode 383 shows you how.
posted by Bill at 9:00 AM
Add a tooltip to each cell in your worksheet that you expect someone to enter a value. This provides great context-sensitive help as someone tries to use your worksheet. Episode 382 shows you how.
posted by Bill at 9:00 AM
Sometimes you want to have cell validation, but you need a way for the person using the spreadsheet to override the validation list, especially in the case where there is a brand new value that you have not yet added to the list. Episode 381 shows you how to have the Validation dropdown ease up.
posted by Bill at 9:00 AM
When you set up a validation dropdown, Excel won't let someone enter an invalid value. Add some text to guide the person using the spreadsheet. Episode 380 shows you how.
posted by Bill at 9:00 AM
Yesterday's netcast showed how to set up data validation to create a dropdown for a cell. The one problem is that someone might inadvertently delete a row from your list of values. Technically, Microsoft says that the validation list can not be on another worksheet, but Episode 379 shows you the workaround.
posted by Bill at 9:00 AM
If you are creating a worksheet for someone else to fill out, why not provide dropdowns where the person can choose the valid values from a dropdown list? It is easy to do. It makes life easier for the person filling out the form. It makes sure the data that comes back to you is correct. Episode 378 shows you how.
posted by Bill at 9:00 AM
In Excel 2002+, try to create a formula next to your pivot table. When you copy this formula down to other cells, the formula keeps pointing at the original cells! Is this a bug? No, it's a feature! Well, whatever it is, in Episode 377 I will show you how to stop the functionality so that things continue to work like they did back in Excel 2000.
posted by Bill at 9:00 AM
When you have created automatic subtotals and want to format the subtotals rows, you will probably be visiting the Edit - Goto - Special - Visible Cells only setting. However, by customizing your toolbar, you can select the subtotals using a single button click. Episode 376 shows you how. Also - see how to move buttons on a toolbar without displaying the customize dialog.
posted by Bill at 9:00 PM
Today's netcast talks about an easy-to-create reporting tool - one that allows you to show the totals for any subset of a dataset. Watch Episode 375 for the complete details.
posted by Bill at 9:00 AM
If you need to create a chart where each series contains both small and large numbers, it will be difficult to see the detail for the smaller numbers. Allow the chart to show detail of both the small and large numbers by using a logarithmic scale. Episode 374 shows you how.
posted by Bill at 9:00 AM
