Learn Excel from Bill Jelen with this daily 2 minute video podcast.
George has a dataset today where a serial number contains 3 groups of digits in a single column. George needs to be able to sort by the middle group of digits. Episode 519 shows you how.
posted by Bill at 9:00 AM
Richard sends in todays question: How do you copy a formula horizontally when it’s reading from a vertical list? Episode 518 shows the easy, but unintuitive solution.
posted by Bill at 9:00 AM
Rod calls in a question today; how to use conditional formatting to highlight all the track athletes from a particular school. This requires the conditional format to look for the school name within a cell. This is very easy in Excel 2007 but very difficult in Excel 2003. Episode 517 discusses the functions required to make it work.
Labels: conditional formatting, Find, ISERROR
posted by Bill at 9:00 AM
In honor of Memorial Day in the United States, a question sent in from a naval aviator who has to enter a series of time values. Using a tiny bit of VBA code, we can eliminate the need to type the colon in the middle of the time value. Even if you don’t have to enter times all day, the techniques in Episode 516 can ease the redundant data entry tasks.
Labels: Event Handler, Time, VBA
posted by Bill at 9:00 AM
Last Thursday, we pondered how to get rid of the annoying Paste Options icon after using the fill handle. In today’s podcast, suggestions from viewers Bob and Tom solve the problem. Episode 515 shows you how.
posted by Bill at 9:00 AM
Why does the automatic subtotals command sometimes choose to Sum and sometimes choose to Count? Episode 514 shows you why Excel seems to arbitrarily count or sum.
Labels: Subtotals
posted by Bill at 9:00 AM
Joe writes in with a faster way to get an entire workbook into Group mode. This is a fast way to make changes to all of the sheets in a workbook. Episode 513 shows you how.
posted by Bill at 9:00 AM
Ricardo sends in a tricky formula question for today’s podcast. How can you calculate a moving average of all sales in the last 90 days, particularly when your data does not include one row for every day? In Episode 512, I discuss the really convoluted solution in Excel 2003 and the only slightly convoluted solution in Excel 2007.
Labels: COUNTIF, formula, moving average, SUMIF
posted by Bill at 9:00 AM
While yesterday’s podcast talked about using the Fill Handle, there are times when there are methods faster than using the fill handle repeatedly. Episode 511 shows how to fill in a whole column of blank cells in one process.
Labels: Blank Cells, Blanks, Go To Special
posted by Bill at 9:00 AM
When you drag the fill handle, Excel offers an extremely useful AutoFill Options dropdown. Well, it is extremely useful UNLESS you really want to get rid of the dropdown so you can see the value in the next row, then the dropdown is really annoying! In Episode 510, you will learn how to control whether Excel will copy or fill the series so that you can turn this annoying icon off.
posted by Bill at 9:00 AM
Christina asks how to make sure that the students in her class stay within a tiny range of the spreadsheet. Episode 509 discusses two methods to solve this problem.
posted by Bill at 9:00 PM
If you need to spice up a column in your report, Episode 508 will teach you how to add a dash to a column. Viewer George writes in to say that his vendor needs the orders transmitted with a dash in the middle of the part number, but George’s data does not have the dash. Learn how to use MID and LEFT and concatenation to put a dash in the middle.
posted by Bill at 9:00 AM
Your crazy software exported a file where the date column has the not-so-useful values like “Jan”, “Feb”, and “Dec” in a column. Episode 507 looks at a function to convert those values to real dates.
Labels: dates
posted by Bill at 9:00 AM
|
0 comments
Christina asks how to create a gradebook in Excel. Episode 506 shows off how to create absolute references and also the obscure TRUE version of the VLOOKUP function. Everyone – teachers and non-teachers should check out the VLOOKUP in this episode.
Labels: VLOOKUP
posted by Bill at 9:00 AM
Thanks to many viewers who wrote in after Episode 497, there is a much easier way to count the number of cells that contain a certain substring. In Episode 505, learn how to use the wildcard options in COUNTIF and AutoFilter.
Labels: COUNTIF
posted by Bill at 9:00 AM
If you use the shapes on the drawing toolbar such as the rectangle, oval, and arrow, you will discover that the shapes do not stay connected when you move them. Rather than using the arrow, Episode 504 shows you how to connect the shapes using a connector that will always stay attached to the shapes.
Labels: drawing toolbar, Shapes
posted by Bill at 9:00 AM
Say that you have a pivot table with region and product in the row area. If you would like a blank row between each region, or even a page break between each region, change the Layout properties for the field. Episode 503 shows you how.
Labels: Pivot Table
posted by Bill at 9:00 AM
There is a cool but subtle way to select all of the subtotal rows in your pivot table in order to format them with a different color. Episode 502 shows you how. This tip came from an audience member at the 33rd annual Meonske Accounting Conference at Kent State. Thanks to Dr. Norm for inviting me back as a speaker at this excellent conference.
Labels: color, Pivot Table, Subtotals
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
As we hit this milestone, you can win one of a treasure trove of prizes, including a video iPod, great books from QUE, and more. There are over 40 prizes in all. Also in Episode 500, I draw the winner for the March/April challenge of the month, using a combination of Advanced Filter, INDEX, and RANDBETWEEN. I will use a similar method to award the prizes from our podcast #500 giveaway.
Labels: Advanced Filter, INDEX, RandBetween
posted by Bill at 9:00 AM
George sends in a question – how can you convert a number to words? For example, how can you write $1234.56 as One Thousand Two Hundred Thirty Four dollars and Fifty Six cents. Although many utility packs offer this functionality, the VBA code is actually available for free from Microsoft. Episode 499 shows you how to add the code to your workbook. Be sure to stop back tomorrow for episode #500, when we will give away a treasure trove of prizes.
Labels: VBA
posted by Bill at 9:00 AM
In today’s netcast, a question about how to create a running total and a running average. Episode 498 discusses how to solve both of these formulas.
Labels: running total
posted by Bill at 9:00 AM
