Learn Excel from Bill Jelen with this daily 2 minute video podcast.
After adding Subtotals, Excel will append the word " Total" to the field being subtotaled. In Episode 791, we take a look at how to use a formula to remove the totals so that the data can be used as a lookup table.
Labels: formula
posted by Bill at 9:00 AM
Kathy asks how to type numbers in a diagonally split cell. Episode 790 shows you how.
Labels: Formatting
posted by Bill at 9:00 AM
Rather than use a custom view, Episode 789 shows how to create a 3-line macro to unhide all worksheets.
posted by Bill at 9:00 AM
You can hide 20 worksheets in a single command, but it is very tedious to unhide all worksheets. Episode 788 shows how to use a custom view to unhide all worksheets.
posted by Bill at 9:00 AM
A question from a seminar... how can you use the consolidate command to subtract one data set from another data set? Episode 787 shows you the workaround.
Labels: consolidate, Data, Paste Special, subtract
posted by Bill at 9:00 AM
An introduction to using Data Consolidate to add two data sets. Episode 786 introduces the concept. Tomorrows episode will throw in an interesting twist.
Labels: consolidate, Data
posted by Bill at 9:00 AM
Episode 322 talked about adding pop-up pictures to a cell. Devin asks if there is a way to add many pictures at once. In Episode 785, I use some macro code from page 764 in the book to quickly add pop-up pictures to many cells.
posted by Bill at 9:00 AM
Tom asks how to do a VLOOKUP with a wildcard. This interesting question has many possible approaches to solve the problem and it will become the June/July challenge of the month. I encourage you to enter our contest to win cool prizes. Watch Episode 784 to see how!
Labels: VLOOKUP
posted by Bill at 9:00 AM
Back in Episode 767, the pod dealt with the problem of using =IF(ISBLANK(A2),"",A2) to hide zero values in the results table. Today, Rene and Denis both write in with a far simpler solution. Episode 783 shows you how.
Labels: charting, Interactive Chart
posted by Bill at 9:00 AM
Hamilton sends in a question about parsing data that has leader......lines between the columns. This ends up being trickier than you might think. Episode 782 shows you how I approached the problem.
Labels: Data
posted by Bill at 9:00 AM
I keep hearing a common misconception that the lookup table in the VLOOKUP has to be sorted. In many cases, this is not true. Episode 781 looks at the optional fourth parameter of VLOOKUP.
Labels: VLOOKUP
posted by Bill at 9:00 AM
Do you have the world's greatest Personal.xls and want to share it with others in your company? Watch Episode 780 for the steps necessary to distribute the personal macro workbook.
Labels: Personal Macro Workbook, VBA
posted by Bill at 9:00 AM
Column I is sparsely filled in with product codes. You would like a macro to copy only the non-blank cells to a new column. It turns out that the macro recorder can actually reliably perform this task. Episode 779 will show you how.
Labels: VBA
posted by Bill at 9:00 AM
One of my favorite tricks is to fill a textbox with the result of a formula in the worksheet. When you try this as the title in an embedded chart, you need to change the formula to point back to the original worksheet. Episode 778 shows you how.
posted by Bill at 9:00 AM
To enhance the chart from Episode 776, I'll use option buttons on the worksheet from the Forms toolbar. Episode 777 will show you how.
Labels: forms, option buttons
posted by Bill at 9:00 AM
Lucy asks how to make a chart interactively change from red to yellow to green based on an external selection. In today's Episode 776, we take a look at the basics of setting up such a chart. Tomorrow's episode will take a look at the Options button on the Form toolbar to enhance the chart.
Labels: charting, Interactive Chart
posted by Bill at 9:00 AM
In Excel 2003, there is a cool button lurking in the Forms toolbar. This button gives you one click access to show/hide gridlines. I am not a big fan of hiding gridlines, but when it is only one click away, I find myself actually doing so. Also, another tip about using PgDn and PgUp to move between worksheets or between screenfuls of columns. Episode 775 shows you how.
Labels: gridline
posted by Bill at 9:00 AM
After recording a macro in yesterday's podcast, I take a look at the recorded code to look for instances where the macro recorder hard coded the number of records. With just a few tweaks, we have a working macro to automate the import and conversion of data from one system to another. Episode 774 shows you how.
Labels: Macro
posted by Bill at 9:00 AM
Pau asks how to take a CSV file from one website and convert it for importing to Google Calendar. In Episode 773, I turn on the macro recorder and attempt to fix the file.
posted by Bill at 9:00 AM
You are printing a multi-sheet workbook which is mostly portrait. In the middle, one worksheet needs to be landscape. You would like the page number to appear at the bottom of the portrait page like all of the other page numbers in the document. Episode 772 will show you the steps required to make this happen.
Labels: page numbers
posted by Bill at 9:00 AM
Telling red cells from green cells in Excel is difficult for those suffering from color blindness. In Episode 771, I take a look at a macro that will show the text name of the color in the status bar for any selected cell.
Labels: VBA
posted by Bill at 9:00 AM
