Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Steve wants to know how to sort pictures with the associated data. There is an obscure setting that controls if pictures sort with the cells or not. Episode 940 shows you how.
posted by Bill at 9:00 AM
Create a running total within months in a pivot table. Episode 939 shows you how.
Labels: Pivot Table, running total
posted by Bill at 9:00 AM
If you like my videos, check out the YouTube channel EXCELISFUN. Mike Gel Girvin has an amazing collection of enthusiastic Excel Videos on YouTube. Also in Episode 938 - an improvement for episode 933: =MOD(ROW(),2) is equivalent to =MOD(ROW(A2),2)=1.
Labels: conditional formatting, ExcelIsFun, Formatting
posted by Bill at 9:00 AM
I've talked about making the list for your data validation automatically grow with the list. In the past, I've used a macro or the OFFSET formula, but today Morne sends in a beautiful solution for either Excel 2003 or Excel 2007 to build a dynamic validation list. Episode 937 shows you how.
Labels: List, Table, Validation
posted by Bill at 9:00 AM
Pablo sends in a great question: why can't you copy a range of cells and paste only the total of those cells in a new cell? Microsoft should really add this functionality, but in the meantime, 2 lines of macro code solve the problem. Episode 936 shows you how.
Labels: VBA
posted by Bill at 9:00 AM
In case 54x20 formats is not enough for you, you can design your own table format. Episode 935 shows you how.
Labels: Formatting, Table
posted by Bill at 9:00 AM
The every-other-row formatting becomes simple in Excel 2007 using the Table functionality. Choose from 54x20 formats in seconds. Episode 934 shows you how.
Labels: Formatting, Greenbar, Table
posted by Bill at 9:00 AM
Excel 2003 used to offer an obscure conditional formatting option called Formula Is. It is still obscure in Excel 2007. Episode 933 will show you the ROW, MOD functions and a dynamic solution to formatting every other row.
Labels: conditional formatting, Formatting
posted by Bill at 9:00 AM
By the time I answered Lisa's question from yesterday, she had already formatted her spreadsheet, using a clever method that I would not have thought of. Episode 932 will show you how to format every other row using the fill handle.
Labels: Formatting, Greenbar
posted by Bill at 9:00 AM
Lisa asks how to shade every other row in the worksheet. While there are several solutions, Episode 931 shows you how to use the old AutoFormat dialog to solve this problem.
Labels: Formatting, Greenbar
posted by Bill at 9:00 AM
It is frustrating that commands in Excel 2007 are spread out amongst 7 different ribbon tabs. Episode 930 will show you how to add your favorite commands to the QAT - Quick Access Toolbar so that you can have one-click access to these commands.
Labels: Excel 2007, QAT
posted by Bill at 9:00 AM
Yet another solution to matching data from two different worksheets...the Data Consolidate command. Episode 929 shows you how.
Labels: consolidate, Data
posted by Bill at 9:00 AM
Another solution to matching data on two worksheets is the Multiple Consolidation Ranges option in the pivot table. Unfortunately, it is much harder to find in Excel 2007. Episode 928 shows you how.
Labels: Pivot Table
posted by Bill at 9:00 AM
Match two worksheets using a pivot table. We continue on this week with Jim's question about matching two worksheets with a common column. Rather than using VLOOKUP, you can use a pivot table to do the comparison. Episode 927 shows you how.
Labels: matching, Pivot Table
posted by Bill at 9:00 AM
Jim has two lists that he wants to match, but some records are missing from one list or the other. There are many ways to solve this. Episode 926 will show the classic double-lookup method.
posted by Bill at 9:00 AM
Apply icons only to the numbers that qualify for a red X. It is possible, but tricky. Episode 925 will show you how.
Labels: Excel 2007, Icon Sets
posted by Bill at 9:00 AM
Adjust the ranges used by the icon sets. Episode 924 will show you how.
Labels: Excel 2007, Icon Sets
posted by Bill at 9:00 AM
The one problem with icon sets is that they are always left-justified in the cell. Episode 923 will show you why this is a problem and how to overcome it.
Labels: Excel 2007, Icon Sets
posted by Bill at 9:00 AM
Add an icon set to a range in Excel 2007. Episode 922 shows you how.
Labels: Icon Sets
posted by Bill at 9:00 AM
Change the default number of worksheets in a new workbook using Excel 2007. Episode 921 shows you how.
Labels: Excel 2007
posted by Bill at 9:00 AM
As you switch to Excel 2007 and try to find things on the new Ribbon, look for the tiny dialog launcher icons to take you back to the old Excel 2003 dialogs. Episode 920 shows you how.
Labels: Excel 2007
posted by Bill at 9:00 AM
