Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Use the LEFT function to return just the left-most three characters from a column of values. Episode 157 will show you how.
posted by Bill at 9:00 AM
Use Excel to calculate text. In this podcast, learn how to join a first name in column A with a last name in column B. Also use a function to convert upper case text to either lower or proper case. Finally, learn how to convert live formulas to values in order to be able to delete the original columns. Episode 156 will show you how.
posted by Bill at 9:00 AM
An introduction to using the IF function to change the calculation based on a condition in your data. Today's podcast shows how to use the IF function to calculate sales in excess of quota. The podcast also shows a slightly less-intuitive, but faster version of the formula using the MAX function. Watch Episode 155 for all of the details.
posted by Bill at 9:00 AM
Use the Add option on the PasteSpecial dialog to add cells from one range to another range. Episode 154 will show you how.
posted by Bill at 9:00 AM
The quick sum feature discussed in episode 152 has more options. Right-click to get an average, min, max, or count of the cells. Episode 153 will show you how.
posted by Bill at 9:00 AM
When your manager calls on the phone and starts drilling you for answers, use the QuickSum feature to sum ranges without entering any formulas. Learn about this cool feature which has been lurking at the bottom of your worksheet for years. Also learn about the Gotchas that can cause problems with the feature. Episode 152 explains it all.
posted by Bill at 9:00 AM
A reader has a similar formula in thousands of cells on dozens of worksheets and needs to regularly change the formula. This should be a perfect time to use the Named Formula option, but you will discover that this seemingly simple task is incredibly hard. It takes 4+ minutes for Bill to walk you through a maze of functions such as INDIRECT, ROW, COLUMN, and ADDRESS just to set up a simple named formula to take the COS of a cell. Episode 151 shows you how.
posted by Bill at 9:00 AM
Part II of Natural Language Formulas shows you how to create a Natural Language Formula that refers to the current row and can be copied to all rows of your dataset. Episode 150 shows you how.
posted by Bill at 9:00 AM
Natural Language Formulas - Create formulas using labels or headings instead of cell addresses.
posted by Bill at 9:00 AM
While many know that you can assign a Name to a cell or a range of cells, you can also assign a constant value to a name. Episode 148 explains why you might want to do this.
posted by Bill at 9:00 AM
Create easier to understand formulas with named ranges. Episode 147 will show you how.
posted by Bill at 9:00 AM
Learn Excel's R1C1 referencing to understand formula copying from cell to cell. Episode 146 will show you how.
posted by Bill at 9:00 AM
Use a shortcut to simplify dollar sign entries in Relative, Absolute and Mixed Reference formulas. Episode 145 will show you how.
posted by Bill at 9:00 AM
Create formulas using relative, absolute, and mixed references in Excel. Episode 144 will show you how.
posted by Bill at 9:00 AM
Sometimes you need a formula to always point to a particular row but allow the column to change. Or, you might need it to point to a particular column but allow the row to change. This is called Mixed References. Episode 143 will show you both varieties.
posted by Bill at 9:00 AM
How to have a cell reference in a formula always point to a particular cell. The concept of relative, absolute and mixed references is the most important concept in Excel. If you've never put a dollar sign in a formula, podcast Episode 142 is a must-watch.
posted by Bill at 9:00 AM
Have Excel copy a formula to other cells using relative references. Episode 141 will show you how.
posted by Bill at 9:00 AM
Have Excel find numbers stored as text. Episode 140 will show you how.
posted by Bill at 9:00 AM
Learn how to insert special symbols into your Excel worksheet. Episode 139 will show you how.
posted by Bill at 9:00 AM
Have Excel talk to you! Excel will read back data from cells as you proofread from original document. Episode 138 will show you how.
posted by Bill at 9:00 AM
Let the Fill Across Worksheets command copy data from current worksheet to any other selected worksheets in your workbook. Episode 137 will show you how.
posted by Bill at 9:00 AM
Track Changes is a fantastic feature in Word. It is disappointing in Excel. Episode 136 explores how this feature works and the limitations.
posted by Bill at 9:00 AM
