This week I’m working on a client’s sales plans for the upcoming fiscal year. They forecast sales per month by product and customer, and we use some pretty complicated formulas to sort things out. Of course, anywhere that it makes sense to use a pivot table, I create one. It’s a great way to summarize [...]

Are you too old to compete in the Olympics? Maybe you’re not as bendy as those 16-year-old figure skaters, but there might be other sports with athletes about your age.
Athlete bios are posted on the Vancouver 2010 Winter Games website, and I compiled that data, then created a few Excel pivot tables, to analyze the [...]

After you update the source data for a pivot table, and refresh the table, some of the old data might still appear in the pivot table drop downs. For example, you changed a product name from Whole Wheat to Whole Grain, and now both names show up in the pivot table’s Product drop down.

Prevent Old [...]

Occasionally, a football game appears on the television at my house. I’m talking about real football, with burly men in tight pants, not that other kind of football, with wiry men in shorts.
This weekend, one of the games was between teams from Arizona and New Orleans. While admiring the strategies of the two teams, [...]

Have you tried Microsoft PowerPivot for Excel 2010 (formerly Gemini)? It’s a powerful data analysis add-in for Excel, and is part of the Office 2010 Beta. If you haven’t downloaded the Beta, you can test PowerPivot in the hands-on Virtual Lab.
That’s where I tested PowerPivot last weekend, and hit a few snags, but was impressed [...]

In an Excel file, you might record the time that employee work on specific projects. For example, an employee worked on Project A for 8 hours Monday, 8 hours Tuesday, 6 hours Wednesday and 3 hours Thursday, for a total of 25 hours.
If you create a pivot table from the time data, and show the [...]

A common pivot table question is “How can I create a pivot table from data that’s on separate sheets in my workbook?
Sometime people have a workbook set up with a separate sheet for each region, or for each salesperson. Eventually, they want to pull all the data together, and create a summary report in a [...]

In a pivot table, you might want to see all the orders that were shipped on a specific date. To do that, you’d move the Ship Date field to the Page area, and select a date from the drop down list.

Sometimes though, you’d like to show the orders shipped in a date range, instead [...]

If you select a cell in a pivot table and click the Chart Wizard button on the toolbar, a default pivot chart is created, on a new chart sheet. Sometimes you might want to step through the Chart Wizard, so you can set the options as you create the pivot chart.
Use the Chart Wizard
If you [...]

When you add a field to the pivot table Values area, it’s automatically given a custom name, such as Sum of Units.

You might want to change the custom name to Units, so it’s easier to read and makes the column narrower.
However, if you select the cell and type Units, you’ll get an [...]