Learn Excel from Bill Jelen with this daily 2 minute video podcast.
In Episode 1137, a cool way to remove all hyperlinks from a spreadsheet.
posted by Bill at 9:00 AM
Mary from Louisiana passes along a cool tip for applying AutoFilter dropdowns to a subset of your records. Episode 1136 shows you how.
Labels: AutoFilter, Filters, shortcuts
posted by Bill at 9:00 AM
Priscilla from Plano Texas offers up yet another way to have the Fill Handle automatically extend 1 to 1, 2, 3. Episode 1111 shows you how.
Labels: shortcuts
posted by Bill at 9:00 AM
Bob Umlas from the Excel Magic book passed on another amazing tip to me at the Microsoft Summit. If you need to add a total to data that was subtotaled using SUM formulas, you can do it in 1 double-click. Episode 967 shows you how.
Labels: shortcuts
posted by Bill at 9:00 AM
You might already know these, but I recently was shown a few cool shortcuts for inserting rows, columns, or cells. Episode 765 will show you how.
Labels: shortcuts
posted by Bill at 9:00 AM
Jim from California suggests that I take a look at the new Excel 2007 Alt shortcut keys. They are pretty cool, with one minor drawback. Episode 696 takes a look.
Labels: Alt Keys, Excel 2007, shortcuts
posted by Bill at 9:00 AM
If you need to copy the *exact* formula from the cell above, there is an amazing shortcut key that will copy this formula down one row. I mean...the exact formula...not a formula with different references, but the exact formula. Episode 692 shows you how.
Labels: shortcuts
posted by Bill at 9:00 AM
Justin asks how to hide columns in Excel 2007. Finding the command on the 2007 ribbon is a little tough, but Episode 689 shows a way to invoke any Excel 2003 command in Excel 2007.
Labels: Excel 2007, shortcuts
posted by Bill at 9:00 AM
Today, Dr Fill Handle provides a faster way to use the Fill Handle. Episode 595 shows you how.
Labels: shortcuts
posted by Bill at 9:00 AM
Bill from Addison TX sent in a great tip to allow you to open Excel with Ctrl+Alt+X, Notepad with Ctrl+Alt+N. Nominally, this makes the technique in Episode 578 faster, but in reality it will make everything you do in Windows much faster. Episode 589 shows you how.
Labels: shortcuts
posted by Bill at 9:00 AM
One of my Greatest Hits – a fast way to copy a formula down to all rows. Episode 579 shows you how.
Labels: shortcuts
posted by Bill at 9:00 AM
A quick trick showing how you can make a formula point to a new range without typing the range. Episode 575 shows you how.
posted by Bill at 9:00 AM
My favorite trick is to double click the fill handle to copy a formula. But, this requires the column to the left to have no blanks cells. Today, Suzanne from Chicago gives us a great trick of how to overcome a sparse column to the left of the formula. Episode 545 shows you how.
posted by Bill at 9:00 AM
In yesterday’s podcast, I showed how to rearrange one column. But – what if you need to rearrange all of the columns? Episode 544 shows how to use a little known sorting trick to sort the columns back to the original sequence.
Labels: Column Widths, Data, shortcuts, Sorting
posted by Bill at 9:00 AM
Mary from Chicago asked how to rearrange columns in a dataset. In Episode 543, learn how to cut and Insert Cut Cells to rearrange the columns.
Labels: shortcuts
posted by Bill at 9:00 AM
Jonathan writes in to mention Ctrl+; enters today’s date. In Episode 542, I run through several of my favorite shortcut keys, including Show Formulas, Visible Cells Only, and more.
Labels: dates, shortcut keys, shortcuts, visible cells
posted by Bill at 9:00 AM
Today’s podcast shows a better way to do data entry. Rather than having to change the Move Selection After Enter Direction, you can use the Tab and Enter keys to quickly navigate through a data entry range. Episode 501 shows you how.
Labels: shortcuts
posted by Bill at 9:00 AM
Do you hate the Zoom control on the standard toolbar? If you have a wheel mouse, there is a much faster way to zoom in and zoom out. Episode 496 shows you how.
Labels: shortcuts
posted by Bill at 9:00 AM
Today’s guest podcaster is Curt Frye, author of the Step by Step series for Excel. Curt shows off some cool fill handle tricks. Episode 471 shows you how.
Labels: shortcuts
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.
Labels: AutoFilter, shortcuts, visible cells
posted by Bill at 9:00 AM
If you work Monday through Friday, Microsoft has a great secret shortcut for filling all of the weekdays in your spreadsheet. Episode 431 shows you how.
posted by Bill at 9:00 AM
Use the Fill Handle to extend a series. Episode 429 shows you how.
Labels: shortcuts
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.
Labels: Alt Keys, keyboard shortcuts, shortcuts
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.
Labels: Concatenation, keyboard shortcuts, Paste Special, shortcuts
posted by Bill at 9:00 AM
Once you have customized a chart, add the chart to your library of user defined charts. You can then apply all of that chart formatting to a new chart with a single keystroke! Episode 372 shows you how.
Labels: chart, custom format, Customizing, shortcuts
posted by Bill at 9:00 AM
There is a secret icon that will let you select all of the items on the drawing toolbar. Episode 363 shows you how.
Labels: drawing toolbar, shortcuts
posted by Bill at 9:00 AM
Sure, you probably know that the F4 key can be used to create absolute references in a formula. When you are not in formula entry mode, the F4 key also becomes a shortcut key for the last command issued. Episode 353 shows you how.
Labels: keyboard shortcuts, shortcuts
posted by Bill at 9:00 AM
If you have a lot of numeric data entry to complete, use the tips in this podcast to keep your fingers on the numeric keypad. Stop using the arrow, tab, and home keys using this simple trick. Episode 352 shows you how.
Labels: keyboard shortcuts, shortcuts
posted by Bill at 9:00 AM
What if you had to total 168 different fields using the Subtotal command? The maddening Subtotal box only lets you choose three fields before scrolling to see three more fields. In Episode 351, learn the cool way to quickly add the subtotals.
posted by Bill at 9:00 AM
