Learn Excel from Bill Jelen with this daily 2 minute video podcast.
How to join text from two columns using the concatenation character. Episode 412 shows you how to do concatenation, plus how to convert upper case to proper case on the fly.
posted by Bill at 9:00 AM
Do you notice that in Excel 97-2003, if you have a really long formula in row 1, when you select a cell, the formula bar obscures the value in the cell? This is now fixed in Excel 2007. Episode 411 shows you how.
posted by Bill at 9:00 AM
Sometimes, you want to enter a formula where one reference doesn't change when you copy the formula down. Episode 410 will show you the shortcut for creating absolute references.
posted by Bill at 9:00 AM
Learn how to use a simple English-language query in order to discover new functions. Episode 409 will show you how to find a function to calculate a monthly car payment. You can also use the Goal Seek function to immediately back into an answer.
posted by Bill at 9:00 AM
How to use Excel as a word processor. This episode again covers the Edit - Fill - Justify command, but also shows off the cool ability to create a two column text box in Excel 2007. Episode 408 shows you how.
posted by Bill at 9:00 AM
Use the freeform tool to make a 3-D version of your company logo. Episode 407 shows you how.
posted by Bill at 9:00 AM
New in Excel 2007 - you can sort by cell color! Today's episode takes a look at the new Sort dialog in Excel 2007. You can sort by more than three levels, and also choose to sort by font color, cell color, or icon. Episode 406 shows you how.
posted by Bill at 9:00 AM
Although Excel offers 100+ built-in AutoShapes, you can use the various handles on the shape itself, or the tools on the drawing toolbar to morph those shapes into thousands of different shapes. Episode 405 shows you how.
posted by Bill at 9:00 AM
Have you ever needed to create many similar autoshapes? You can set the default color, line style, etc and then use an autoshape mode to crank out numerous autoshapes. Episode 404 shows you how.
posted by Bill at 9:00 AM
Actually - there are three different file formats in Excel 2007. Episode 403 describes the differences and shows you how you can use a zip utility to take a look inside the new file format.
posted by Bill at 9:00 AM
When a number is too large to fit in the width of a column, Excel will display pound signs. Episode 402 shows a unique way to select all of the cells that are currently displaying as pound signs. This trick would seem to be useful for quickly formatting all of the cells that match some certain condition.
posted by Bill at 9:00 AM
Excel 2007 sports a massive grid. Take a tour of cell XFD1084576 to get a sense of just how big the new grid really is. Also: how to unlock the extra rows when you open your legacy workbooks in Excel. Episode 401 shows you how.
posted by Bill at 9:00 AM
It is a real pain to try to trace precedents that are on other worksheets or in other workbooks. Episode 400 shows some formula auditing tricks, including a trick to see all of the precedents on other worksheets. This is our 300th episode!
posted by Bill at 9:00 AM
There are a couple of obscure functions in the analysis toolpak that will allow you to calculate how many workdays have elapsed. Enter a hire date and today's date, and you can calculate the number of workdays. This function even allows for you to enter a range of company holidays which are factored into the calculation. Episode 399 shows you how.
posted by Bill at 9:00 AM
The only toolbar that is easy to customize in Excel 2007 is the QAT or Quick Access Toolbar. Episode 398 shows you how to customize the QAT with your favorite icons.
posted by Bill at 9:00 AM
A much better way of showing leading zeroes. After episode 392, several listeners called in with a better better way to show leading zeroes for any number of characters. Episode 397 shows you how.
posted by Bill at 9:00 AM
A look at Excel 2007 data bars and color scale features. These new features allow you to quickly see the largest/smallest values in a sea of numbers. Episode 396 shows you how.
posted by Bill at 9:00 AM
In today's episode a caller asks how to split names that are in one cell into first name in column A and last name in column B. Episode 395 shows how to do this trick, but then the annoying gotcha that will happen for the rest of the day.
posted by Bill at 9:00 AM
Today's tip comes from Dave in Columbus, Indiana. Dave was at my Power Excel seminar last week and passed along a better way to invoke the Paste Special Values command. In the process of showing Dave's trick, I also talk about joining text using the concatenation operator, changing text from upper case to proper case, copying a formula by double-clicking the fill handle, using Ctrl+z to undo. Episode 394 shows you how.
posted by Bill at 9:00 AM
