Learn Excel from Bill Jelen with this daily 2 minute video podcast.
There is an annoying limitation when you try to insert new rows in a pivot table. Episode 265 shows you the limitation and walks through how to solve it.
posted by Bill at 9:00 AM
How to change an existing pivot table. Episode 264 shows you how.
posted by Bill at 9:00 AM
You have 50,000 rows of transactional data and you need to produce a 3 line summary. Use a pivot table to solve this problem with just a few clicks. Episode 263 will walk you through creating your first pivot table.
posted by Bill at 9:00 AM
In today's dataset, someone tried to make a report look nice by putting the customer number and customer name only once for every group of records. This looks fine, but it is impossible to use in any sort of data analysis. Episode 262 will show you the formulas you can use to copy the customer information down to new columns.
posted by Bill at 9:00 AM
Today, we deal with the most annoying dataset - each logical record is spread out over more than one physical row. It is impossible to analyze this data unless you transform it into one row per record. In Episode 261, Bill shows you how to solve this problem.
posted by Bill at 9:00 AM
In today's podcast, you want to assign all of the same customers an identical group number. Episode 260 shows you how. This technique, as well as the one from podcast 259 will be put together to solve a bigger problem in episode 261.
posted by Bill at 9:00 AM
In today's podcast, you want to assign a sequential number to each customer's invoice, but start over again at 1 when you get to the next customer. Episode 259 will show you how.
posted by Bill at 9:00 AM
The last trick for consolidation - how to see who appears in one of several lists. Episode 258 will show you how.
posted by Bill at 9:00 AM
In today's podcast, I have a single list, but customers appear multiple times in the list. Learn how to use the consolidate feature to combine the duplicate customers and total the revenue with the duplicate records. Episode 257 shows you how.
posted by Bill at 9:00 AM
In today's podcast, we have two lists of customer data and need to add them together. But, as luck would have it, the customers in the first list don't exactly line up with the customers in the second list. Episode 256 will show you the obscure data feature for combining these two lists.
posted by Bill at 9:00 AM
For every accountant who has ever tried to produce a Profit & Loss report using the page setup headers, you probably wondered why you always needed to spell out the word "and". Episode 255 reveals the secret to using an ampersand in the custom header.
posted by Bill at 9:00 AM
Using the replace dialog gets tricky when you need to remove just an asterisk or a question mark. Reporting from the New York City Public Library, Bill reveals the problem and the completely intuitive work-around in Episode 254.
posted by Bill at 9:00 AM
Time to raise everyone's rates by 2%. An obscure feature in the Paste Special dialog lets us do that with ease. Episode 253 shows you this trick and more.
posted by Bill at 9:00 AM
Two cool ways to get rid of all the blank cells in a range. Episode 252 will show you how.
posted by Bill at 9:00 AM
Today's podcast shows an old Lotus 1-2-3 trick that lets you perform dozens of what-if analyses in a single function. Episode 251 shows you how.
posted by Bill at 9:00 AM
How can you insert both automatic subtotals and automatic averages on the same row? While the subtotals feature won't normally allow this, Episode 250 shows you how.
posted by Bill at 9:00 AM
This is a really easy request, but is incredibly hard to do. Episode 249 will show you two different methods. If this is something you need to do, plan on hitting the replay button because it is by far the most complicated tip I've shown in the 249 episodes.
posted by Bill at 9:00 AM
Make your subtotals pop out at the reader. I'll use an obscure trick to format just the subtotal rows in Episode 248.
posted by Bill at 9:00 AM
When you need to subtotal by two different fields, it is important to add the subtotals in the right order. Today's episode shows what can happen if you do the subtotals in the wrong sequence. Episode 247 shows you how.
posted by Bill at 9:00 AM
NateO and Starl join me in Toronto to discuss the age old question regarding GPF's: to send the error report or not? The camera angles were horrible today - so you hear us and see half of us on Episode 246.
posted by Bill at 9:00 AM
Today's episode discusses an obscure trick to add customer names to the subtotal lines when you've subtotaled by account number. Episode 245 will show you how.
posted by Bill at 9:00 AM
Sometimes, you might want your automatic subtotals at the top of each section. Episode 244 will show you how to remove the existing totals and put them back at the top of each section.
posted by Bill at 9:00 AM
Using the automatic subtotals is a huge timesaver, but it is a little tedious when you have 12 columns to add up. Episode 244 walks you through how to slowly add the columns to subtotal, one group at a time.
posted by Bill at 9:00 AM
