Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Excel does not deal well with negative times. What if you are tracking vacation time and some employees go into a negative balance? Excel can’t display negative times. However, with the trick in Episode 561, you can coerce Excel into displaying negative times.
posted by Bill at 9:00 AM
How can you figure out a date that is a certain number or workdays BEFORE a date? On the last episode of The Lab with Leo Laporte, I showed how to calculate a date 45 workdays after a certain date. Today’s question is how to calculate the date 5 days before the end of the month. Episode 560 shows you how to calculate this value.
posted by Bill at 9:00 AM
Erik points out that the best way to find the last day of this month is to ask for the zeroth day of next month. Episode 559 shows you how.
posted by Bill at 9:00 AM
Udo from Dresden Germany sends in a tip for a faster way of rearranging columns in a worksheet. Udo’s tip also works to rearrange rows, ranges and so on. Episode 558 shows you how.
posted by Bill at 9:00 AM
Do you have a spreadsheet where the calculation time is so long that you routinely use Manual Calculation mode? If so, John sends in a great tip for how to calculate only cells in a selected range. Episode 557 shows you how.
posted by Bill at 9:00 AM
Rivky writes in to ask how to do multiple consolidation ranges in Excel 2007 pivot tables. This feature has been on the pivot table wizard for the last 10 years, but it is not at all obvious in Excel 2007. In Episode 556, you will learn how to find this and all of the other commands hidden in Excel 2007.
posted by Bill at 9:00 AM
Need to convert a report or a chart to display in thousands or millions? Episode 555 shows two different methods to achieve this result.
posted by Bill at 9:00 AM
Building on yesterday’s podcast, Episode 554 shows how to make full use of the custom number formatting zones to add specific words to a balance due column.
posted by Bill at 9:00 AM
In today’s podcast, a custom number format that shows positive values and zeros, but hides the negative values. Episode 553 shows you how.
posted by Bill at 9:00 AM
In today’s podcast, Episode 552, I use the IF function to hide values that are negative. This is a formula-based solution. In tomorrow’s podcast, a different way to solve the problem using number formatting.
posted by Bill at 9:00 AM
Van writes in with a suggestion about podcast 539. In that podcast, we were trying to calculate which customers had their birthday coming up soon. Van suggests using conditional formatting and the TODAY() function to flag the customers to whom you need to send birthday cards. Episode 551 shows you how.
posted by Bill at 9:00 AM
Way back in Episode 499, I talked about adding the SpellNumber custom function to a spreadsheet. But, Gary wrote in with a problem – how can I simply add this to Excel so it opens on all of my workbooks? In Episode 550, we take a look at creating your own Excel Add-In to solve this problem.
posted by Bill at 9:00 AM
Simon from the UK sends in a question. He has a range of negative numbers that he needs to convert to positive values. Episode 549 shows you two methods for converting the values to positive.
posted by Bill at 9:00 AM
If you navigate your worksheet rows by dragging your scrollbar slider, in some Excel worksheets, the scrollbar slider becomes incredibly small. In Episode 548, I show my one trick for solving this problem, but I expect that there are other solutions.
posted by Bill at 9:00 AM
Today, Bob calls in with a question. He has an Excel spreadsheet where someone managed to cross out only part of a cell. For example, someone crossed out the original due date and then keyed a new due date in the same cell and the new date does not have the strikethrough formatting applied. Episode 547 shows you how.
posted by Bill at 9:00 AM
Today’s problem involves a complex data set with headings throughout the data. We want to add new columns to the dataset, but it is incredibly tedious to copy the new column headings throughout the report. Episode 546 shows you how to use a combination of AutoFilter, and Visible Cells Only to solve the problem.
posted by Bill at 9:00 AM
My favorite trick is to double click the fill handle to copy a formula. But, this requires the column to the left to have no blanks cells. Today, Suzanne from Chicago gives us a great trick of how to overcome a sparse column to the left of the formula. Episode 545 shows you how.
posted by Bill at 9:00 AM
In yesterday’s podcast, I showed how to rearrange one column. But – what if you need to rearrange all of the columns? Episode 544 shows how to use a little known sorting trick to sort the columns back to the original sequence.
posted by Bill at 9:00 AM
Mary from Chicago asked how to rearrange columns in a dataset. In Episode 543, learn how to cut and Insert Cut Cells to rearrange the columns.
posted by Bill at 9:00 AM
Jonathan writes in to mention Ctrl+; enters today’s date. In Episode 542, I run through several of my favorite shortcut keys, including Show Formulas, Visible Cells Only, and more.
posted by Bill at 9:00 AM
Daan Sprunken (who was a runner up in one of the last challenges) writes in to mention that there is a better way to find the last Saturday of the month, as we discussed in podcast Episode 524. Daan points out that the MOD function would have made that formula easier – and he is right. Not enough people think of MOD, so in Episode 541, I take a look at several different MOD examples.
posted by Bill at 9:00 AM
