Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Rick from Madison Alabama sends in a cool trick today. You can use your wheel mouse to quickly scroll from ribbon tab to ribbon tab in Excel 2007. Episode 880 shows you how.
Labels: Excel 2007
posted by Bill at 9:00 AM
Excel 2007 offers a new visualization called an Icon Set. I usually hate this feature, because my numbers are right justified and the icons are left justified. However, in Episode 879, Joanna from Columbus, Ohio offers a cool tip to get the numbers and icons next to each other.
Labels: Excel 2007, Formatting
posted by Bill at 9:00 AM
Can you use a function in an Excel calculated field? Well, Bryony from the U.K. says yes you can, and sure enough...it works. Episode 878 shows an example of using a function to create a new field in a pivot cache. Fairly cool.
Labels: calculation, Calculations, Functions, Pivot Table
posted by Bill at 9:00 AM
A mid-day podcast to invite you to a free Excel webinar on Thursday, October 30, 2008 and also to invite you to provide feedback on our new survey.
posted by Bill at 3:00 PM
Seven ways to paste values in Excel. While I talk about many ways to convert formulas to values, Don from Orrville has come up with a method shorter than the other 6 methods I usually discuss. So...drumroll please... Episode 877 with method #7 for doing Paste Values!!!
Labels: Paste Special
posted by Bill at 9:00 AM
Rodney is trying to label a Cartesian grid in Excel. This is a cool idea - Excel is really the world's largest sheet of graph paper, but the labels for the Cartesian grids aren't working well. In Episode 876, I explore the line tool on the drawing toolbar, the textbox tool, and the XY chart. Also a special deal on a book for your middle school students mentioned at the end of the podcast.
Labels: Drawing
posted by Bill at 9:00 AM
Neville from Germany sends in a good suggestion for overcoming pivot formatting problems from Episode 865. By using conditional formatting on the underlying worksheet, Neville comes up with a way to make sure that the total rows are always highlighted in a pivot table. Episode 875 shows you how.
Labels: conditional formatting, Formatting, Pivot Table
posted by Bill at 9:00 AM
After watching Episode 859, Tom from the U.K. pointed out that the problems go away if you turn off in-cell editing. Episode 874 shows the benefits of turning off in-cell editing.
Labels: Editing
posted by Bill at 9:00 AM
Rick asks how to get started with VBA. In Episode 873, I show a horribly formatted file that someone in my Power Excel audience has to deal with daily. A macro seems like a good way to deal with the file. I will show you how to turn on the macro recorder, turn on relative reference, and record your first macro.
Labels: VBA
posted by Bill at 9:00 AM
Janet wonders why her VLOOKUPs are not working in certain cases. If your key field contains a mix of numbers and text that looks like numbers, then the VLOOKUP won't work. Episode 872 shows how to use the TEXT function to intermittently solve the problem and another method to reliably solve the problem.
Labels: custom format, VLOOKUP
posted by Bill at 9:00 AM
Someone tried to record a macro to find the records from a certain date. Since finding dates requires the date format to match exactly, a better way to go would be using Advanced Filter. In Episode 871, I show how to use AdvancedFilter in a macro to extract records from a certain date.
Labels: Advanced Filter, VBA
posted by Bill at 9:00 AM
You could solve yesterday's podcast using the often-cursed-at, but seldom-understood GETPIVOTDATA function. In Episode 870, how to use GETPIVOTDATA to return the grand total and also using =IF(ISBLANK to clean up stray results below the pivot table.
Labels: GetPivotData, ISBLANK, Pivot Table
posted by Bill at 9:00 AM
Richard is building a formula outside of the pivot table that needs to divide by the grand total of the pivot table. In Episode 869, I show two solutions, one of which is an incredibly geeky use of the other type of VLOOKUP.
Labels: formula
posted by Bill at 9:00 AM
Donna from Colorado asks how to clear all of the input cells from a model. In today's Where Is It Wednesday Episode 868, we take a look at how to select only the numeric constants. Also - following up on 858 - Copy as Picture, 860 Resizing Header Logo, 863 Protect Sheet.
Labels: Formatting
posted by Bill at 9:00 AM
If you need to make an exact copy of formulas, you can use the method from episode 866 or this Fill Across Worksheets solution. Episode 867 shows you how.
Labels: formulas
posted by Bill at 9:00 AM
Markus from Germany sends in this tip. Say that you have to make an exact copy of a range of formulas. It is like you need to do a CUT and PASTE, but also leave the original cells where they were. Normally, a Copy and Paste will rewrite the formulas. Using this trick from Markus, you can achieve the desired result. Episode 866 shows you how.
Labels: formulas
posted by Bill at 9:00 AM
Taylor from Orange County California calls in to ask how you can get the formatting of a pivot table to stick? Every time that you refresh a pivot table, the formatting reverts back to a default. The bad news is that you can not make all formatting stick, but Episode 865 will show you a couple of methods to use.
Labels: Pivot Table
posted by Bill at 9:00 AM
Today, a question about the Excel Watch Window. If you make changes to the format of a watched cell, why won't the format in the Watch Window change? Episode 864 shows you how to set up the watch window and how to get the latest formatting changes.
Labels: Watch Window
posted by Bill at 9:00 AM
Where is it Wednesday: Where did they move Tools, Protection, Protect Sheet? In Episode 863, I will show you how to protect only the formula cells in Excel 2007. While this was complicated in Excel 2003, it is just a bit more complicated in Excel 2007 when you can't find the final command, Protect Sheet.
Labels: Protection, WIIW
posted by Bill at 9:00 AM
Dan from Texas asks: is there a way to change the color of cells that are precedents of a particular formula. Use the technique in Episode 862 to quickly complete this task.
Labels: formula
posted by Bill at 9:00 AM
The subtotal command is a fantastic time-saver, but it occasionally behaves erratically. In Episode 861, I will show two situations which can cause the subtotal command to whack out.
Labels: Subtotals
posted by Bill at 9:00 AM
Adding a company logo jpg to your worksheet header. Episode 860 shows how to add a header, and then poses a question about if your image is scaling in size as you add more data.
posted by Bill at 9:00 AM
Its two-for-the-price-of-one-Thursday (sounds like we need a new theme...). Question 1: When editing a long formula, the formula covers up adjacent cells, making it difficult to point to those cells with the mouse. Question 2: When you use Shift+Ctrl+Right+Ctrl+Down to select a large range, the Error icon scrolls off the screen. I'll propose solutions to both of these in Episode 859.
Labels: Editing
posted by Bill at 9:00 AM
In our Where Is it Wednesday podcast, we go looking for a command that most people never would have been able to find in Excel 2003! If you want to send a perfect copy of a formatted spreadsheet in an e-mail, you could use Snag-It or PrtScrn, but by holding down the shift key, you can find a hidden Copy as Picture command in Excel 2003. Episode 858 shows you how.
Labels: Formatting
posted by Bill at 9:00 AM
