Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Jackie from Chicago calls in with today's question - how can you find the date at the end of the month from a column of dates? Episode 287 shows you two ways to solve this problem.
posted by Bill at 9:00 AM
If you need to change the numeric format in your pivot table reports, don't use the formatting toolbar! Instead, follow the steps from Episode 286. As a bonus, this podcast also shows the secret number format for displaying numbers in thousands.
posted by Bill at 9:00 AM
While pivot tables are incredibly powerful, they generally look absolutely horrible. Rather than continuously formatting the pivot table after every change, you can use the Pivot Table AutoFormats on the pivot table toolbar. Episode 285 shows you how.
posted by Bill at 9:00 AM
If you are trying to plan inventory in the warehouse or material scheduling at the manufacturing plant, you will want to create an order leadtime report. Episode 284 shows how to use a pivot table with two dates grouped up to months.
posted by Bill at 9:00 AM
Kevin from Chicago called to ask how to remove leading and trailing spaces from a column, while keeping the spaces between words. Episode 283 shows you how to solve this problem.
posted by Bill at 9:00 AM
What if you need to group your daily dates up to weeks? There is not a weekly option in the Group dialog but, Episode 282 shows you how.
posted by Bill at 9:00 AM
If your pivot table shows data by days, it is just a couple of clicks to transform the pivot table into a monthly report. Episode 281 shows you how. As a bonus - a new tip that is not in the book - how to create a year-over-year comparison report by month.
posted by Bill at 9:00 AM
Pivot Tables look horrible when you try to add two fields to the pivot table data area. Episode 280 will show you the four different ways to display pivot tables with two data fields.
posted by Bill at 9:00 AM
Yes - you can have multiple fields in the data area of a pivot table - fields like quantity, revenue, and cost. There are some subtle changes to moving fields, removing fields, and sequencing fields. Episode 279 will show you how.
posted by Bill at 9:00 AM
All of the pivot tables lately have been showing the total of the data fields. It is easy to show Min, Max, Average, Count, Standard Deviation or Variance - anything except Median. Episode 278 will show you how to create a pivot table with averages.
posted by Bill at 9:00 AM
Skip the whole wizard and create a pivot table in just a few clicks. Episode 277 shows you how.
posted by Bill at 9:00 AM
There are many difficult methods for producing a list of the unique values in a list. Episode 276 will show you the wickedly easy way to get the unique list of customers in 3 clicks.
posted by Bill at 9:00 AM
Use Page Fields in a pivot table to create an ad-hoc reporting tool. Episode 275 shows you how.
posted by Bill at 9:00 AM
What if you need to produce a report for each region or each customer? Pivot Tables can help. Episode 274 shows you how.
posted by Bill at 9:00 AM
Another hidden option allows you to have the pivot table calculate the top 5, 10, or 12 customers. Watch Episode 273 for cool Excel Pivot Table options to produce a report of the best (or worst) customers.
posted by Bill at 9:00 AM
Why are pivot tables always sorted by customer alphabetically? Wouldn't it be cool if you could get the largest customers first? Learn how using the completely hidden AutoSort option in Excel Pivot Tables. Episode 272 reveals the secret.
posted by Bill at 9:00 AM
In Excel 2003 and earlier, you only have 255 columns to work with. You quickly run into limitations when you try to build a wider pivot table. Even if you hoped to group the dates up to months (more about that on June 22), you will still need to make the field longer than wide. Episode 271 walks you through this.
posted by Bill at 9:00 AM
Once you have three or more fields in a pivot table in Excel, you have several options on how to display the data. In Episode 270, I'll show you how to use the Show and Hide buttons to quickly expand a field.
posted by Bill at 9:00 AM
How to add or remove fields to a pivot table in Excel. Episode 269 shows you how.
posted by Bill at 9:00 AM
If you pull out your copy of John M. Nelson's The Elements of Spreadsheet Style, he tells you that you should never leave blank cells in the middle of a range. Fill the blank cells with zeroes. This is spreadsheet style 101. Unfortunately, Microsoft breaks this rule in many pivot tables. Episode 268 shows you the hidden setting to fill those blanks with zeroes.
posted by Bill at 9:00 AM
Pivot tables don't automatically update. Episode 267 will explain why and how to solve this.
posted by Bill at 9:00 AM
They say that every data set has errors. Once you are able to summarize those 50,000 rows to a 5 line summary, people start spotting all kinds of errors. Wait a second! We never sold that product to this customer! You can double click to view the detail behind any pivot table cell. Episode 266 shows you how.
posted by Bill at 9:00 AM
