Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Several people wrote in with a 15-second method to add horizontal subtotals - much faster than the method that Mike and Bill discussed in episode 990. Episode 1001 shows you how to add horizontal subtotals.
Labels: Subtotals
posted by Bill at 9:00 AM
Mike from ExcelIsFun throws down this week's challenge: adding horizontal subtotals. Bill and Mike duel it out in Episode 990.
Labels: Subtotals
posted by Bill at 9:00 AM
The subtotal command is a fantastic time-saver, but it occasionally behaves erratically. In Episode 861, I will show two situations which can cause the subtotal command to whack out.
Labels: Subtotals
posted by Bill at 9:00 AM
Travis has a spreadsheet where you can quickly hide and unhide detail rows using the tiny "1" and "2" buttons. In Episode 799, I will show how to add these Group and Outline symbols to your worksheet.
posted by Bill at 9:00 AM
Another way to solve yesterday's problem is to use the =SUBSTITUTE function instead of LEFT and LEN. Episode 792 will show you how.
posted by Bill at 9:00 AM
A combination of six cool tricks allow you to take detailed transactional data and produce a chart of the top five customers in about 12 clicks. Episode 762 shows you how.
Labels: Data wrangling, Subtotals, visible cells
posted by Bill at 9:00 AM
Kerry asks: after adding automatic subtotals, how can you copy only the subtotal rows? Episode 731 will show 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
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
When you have created automatic subtotals and want to format the subtotals rows, you will probably be visiting the Edit - Goto - Special - Visible Cells only setting. However, by customizing your toolbar, you can select the subtotals using a single button click. Episode 376 shows you how. Also - see how to move buttons on a toolbar without displaying the customize dialog.
Labels: Customizing, GoTo Special, Subtotals, toolbar
posted by Bill at 9:00 PM
What if you had to total 168 different fields using the Subtotal command? The maddening Subtotal box only lets you choose three fields before scrolling to see three more fields. In Episode 351, learn the cool way to quickly add the subtotals.
posted by Bill at 9:00 AM
