Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Subtotals worked great all week, but today we encounter a gotcha - for some reason, the subtotals are COUNTING our revenue cells instead of summing them. Episode 242 reveals while Excel does this on somedays and not on others.
posted by Bill at 9:00 AM
Today, I am appearing at the IMA Continuing Education Workshop in Kent Ohio. There will be a few hundred serious accountants in that room with me today, and most of them will know this trick. Today, we have a spreadsheet where someone added manual subtotals to the worksheet. Rather than building a formula to point just to the totals, there is a cool trick that uses a bit of algebra to solve the problem. Episode 241 will show you how.
posted by Bill at 9:00 AM
After yesterday's podcast, I am sure you tried to copy the manager view to a new file and found out that it did not work. There is an obscure method for copying just this view. Episode 240 shows you the way in the book plus a bonus method that is much faster.
posted by Bill at 9:00 AM
After using the Subtotals function, look for three new buttons called the Group & Outline buttons. Use these tiny buttons to produce quick reports for your manager. Episode 239 will show you how.
posted by Bill at 9:00 AM
An AMAZING trick for adding subtotals to a dataset. Episode 238 shows you how.
posted by Bill at 9:00 AM
Use the Advanced Filter with a criteria. Episode 237 will show you how.
posted by Bill at 9:00 AM
Use the Advanced Filter to generate a unique list of values from a dataset. Episode 236 will show you how.
posted by Bill at 9:00 AM
Use the AutoFilter feature to quickly find records. Episode 235 shows you how to combine filters, use the Top 10 feature or the Custom feature to quickly find certain records.
posted by Bill at 9:00 AM
Sort data into a custom sequence to match the convention used in your company. This can apply to regions, product lines, etc. If your VP of sales wants the East region sorted before the Central region, you can do this with the technique in Episode 234.
posted by Bill at 9:00 AM
The Sort dialog only allows a three level sort. Episode 233 will show you how to sort a dataset by more than 3 levels.
posted by Bill at 9:00 AM
How to sort data in Excel. Episode 232 shows you how.
posted by Bill at 9:00 AM
If you need a long heading in a single cell, use the Alt+Enter control to gain explicit control over word wrap. Episode 231 shows you how.
posted by Bill at 9:00 AM
Set up your data for flawless use of items on the Data Menu. Episode 230 shows you how.
posted by Bill at 9:00 AM
How to protect only the formula cells in your worksheet. Epiosde 229 shows you how.
posted by Bill at 9:00 AM
Rather than play the higher/lower game with Excel to try to find the input value to get to your desired result, use the Goal Seek function to instantly back into an answer. Episode 228 will show you how.
posted by Bill at 9:00 AM
In Lake Wobegon, they say that all of the students are above average. Today, we will use the COUNTIF function with a clever twist. The 2nd parameter is constructed by concatanating an operator with the result of another function to see how many students really are above average. Episode 227 will show you how.
posted by Bill at 9:00 AM
Once someone learns SumIf they soon want to know how to use SumIf with two conditions. Unfortunately, you can not do it with a SumIf. Episode 226 shows you how to use the boolean logic from yesterday's episode to solve the problem.
posted by Bill at 9:00 AM
Use an old boolean logic trick to eliminate the IF function. Today's Episode 225 will lay the groundwork for the super powerful formula in tomorrow's podcast.
posted by Bill at 9:00 AM
How to use Logical Formulas, along with the AND and OR functions to figure out if the sales reps should get a bonus. Episode 224 will reveal everything about these boolean logic facts.
posted by Bill at 9:00 AM
Learn about super-powerful amazing formulas that you can access if you know the secret keystroke combination! Episode 223 reveals all.
posted by Bill at 9:00 AM
