Learn Excel from Bill Jelen with this daily 2 minute video podcast.
George sends in a question – How can COUNTIF be made to look for cells that match a pattern? Episode 497 shows the long workaround to this problem.
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.
posted by Bill at 9:00 AM
In yesterday’s podcast, I proposed a formula to solve Jonathan’s dilemma of analyzing information by hourly buckets. An easier solution is to use the Group by Hour feature in a pivot table. Episode 495 shows you how.
posted by Bill at 9:00 AM
Jonathan from California has a column with date & time information, but would like to strip out just the times to do an analysis by hour. Episode 494 shows you two formulas that can be used to analyze the data by hour.
posted by Bill at 9:00 AM
In just about every pivot table tutorial, the writer tells you to start with a nice clean transactional data set. No blank rows, no blank columns, and no months going across the columns. David from Pennsylvania writes in to ask what to do if your dataset already is in a table – with headings in the rows and columns. In Episode 493 I borrow a trick from Mike Alexander to solve this problem and make your dataset appropriate for use in a pivot table.
posted by Bill at 9:00 AM
Bob from Dallas writes in with a question. Bob has created a chart in Excel and needs to copy the chart to a slide in PowerPoint. For those of you who never leave Excel, PowerPoint is a tiny little Add-In that Microsoft created to help Excellers when they need to make presentations. Although there are 7 ways to accomplish this task, Episode 492 takes a look at the best way to get your Excel charts on to a slide in PowerPoint.
posted by Bill at 9:00 AM
Mike writes in with a question that is way too hard for a Friday, and way too hard considering it is dawn in Maui. Mike has what sounds like a simple task – a checkbook register in Excel, but needs to create a tricky validation that will allow either an integer check number or one of a handful of values such as ATM, EFT, etc. Episode 491 shows you how to set up data validation to handle this problem.
posted by Bill at 9:00 AM
Mudit asks, is there any way to sum just the bold cells in a range? This problem is hard to solve in Excel, but easy if you use a tiny user defined function in VBA. In Episode 490, learn how to find free user defined functions on the internet and paste them into your Excel workbook.
posted by Bill at 9:00 AM
Viewer George sends in a column of dates that refuses to be sorted. George says that he already tried converting the text dates to dates using the text to columns trick. In Episode 489, we’ll take a look at two methods to tell if your dates are really dates and how to convert them to real dates.
posted by Bill at 9:00 AM
Bill Gates asked the question: “The ribbon is fine, but there will be a classic mode, right?”. The Office interface team convinced Bill that this was not the way to go. And, unfortunately, learning the new ribbon is the #1 complaint about Office 2007. Well, the folks at www.addintools.com did for you what Microsoft would not do, they brought the classic Excel 2003 menu bar, plus the standard and formatting toolbars back to Excel 2007. The add-in is perfect for someone who is trying to learn Excel 2007 in a pain-free manner and is now available at www.mrexcel.com. If you need a new feature, you can use the new ribbon tabs. But, if you need to use an item off the old menu, just stay right on the new Menu tab and find the menu exactly where you expect it would be. Episode 488 shows you how.
posted by Bill at 9:00 AM
To finish up the recent discussion of VLOOKUP, INDEX, MATCH, and OFFSET, today’s episode talks about the strange situation where the data that you want is located to the left of the key field in the lookup table. Episode 487 will show you how to use VLOOKUP and MATCH to solve this problem.
posted by Bill at 9:00 AM
In today’s podcast, we have hundreds of rows of VLOOKUP functions. Learn some tricks to make the entry of the third argument easier using a hidden row or the COLUMN function. Ultimately, though, after several iterations of VLOOKUP solutions, Episode 486 finally gets to the point of replacing VLOOKUP with MATCH and INDEX functions.
posted by Bill at 9:00 AM
These two functions allow you to dynamically point to a cell or a range. While they are often useless on their own, they can be used as arguments to another function. Episode 485 introduces these two functions.
posted by Bill at 9:00 AM
The VLOOKUP function is one of the most versatile functions in Excel – you can use VLOOKUP to add item descriptions to a database containing item numbers. Episode 484 shows you how VLOOKUP works. This is the first in a series of podcasts to compare VLOOKUP with INDEX and MATCH.
posted by Bill at 9:00 AM
There is one week left to enter the current challenge of the month. In Episode 483, see this month’s challenge is how to calculate a blended commission rate. Take a look at the problem, send in your best solution, and we’ll discuss the solutions in a podcast next week.
posted by Bill at 9:00 AM
Today, Scott W sends in a better way to hide and unhide columns. After watching podcast 449, Scott sent in a suggestion that is much better than the typical Format Column Hide and Format Column Unhide methods. Episode 482 shows you how.
posted by Bill at 9:00 AM
If you have a CSV file, you will always lose any leading zeroes when you open the file in Excel. This causes problems with zip codes and social security numbers. Episode 481 shows the non-Excel method for solving this problem.
posted by Bill at 9:00 AM
After importing data, your numbers and dates might be stored as text. While newer versions of Excel allow you to solve the numbers stored as text easily, the exclamation point dropdown never appears for the dates stored as text. Episode 480 takes a look at several methods for solving the problem.
posted by Bill at 9:00 AM
Jason sends in our question today. He has a single column of cells that contain 5 bits of information in each cell. There aren’t any good delimiters and the fields are not fixed width. To extract the necessary information from the cell, Episode 479 shows how to combine MID, FIND, and FIND to locate the important data.
posted by Bill at 9:00 AM
Today, an interview with Andrew Engwirda, author of the JMT utilities for Excel. Andrew is an Excel MVP from Australia. Episode 478 explains how one of the utilities works.
posted by Bill at 9:00 AM
Play ball! In honor of baseball’s opening day, learn how to display a number in fractions, such as the Innings Pitched column in Cy Young’s lifetime stats. Episode 477 shows you how.
posted by Bill at 9:00 AM
