PivotTables can save the day in a budget crisis

This “Office Show” episode features a common dilemma: The budget data you want to analyze in Excel contains so many rows and columns of numbers that it seems like an impossible task to get it all sorted out. Meanwhile, there’s a deadline looming and mounting pressure to figure out how to save money.

This is where PivotTables come in. PivotTables are brilliant at making sense out of walls of numbers, because they do a lot of the work for you. They can automatically organize and summarize your data, so you can immediately see things like what areas of your business are performing the best (and, unfortunately, the worst).

So what situations really call for a PivotTable? Consider creating one when you want to:

  • Make sense of large amounts of data and numbers – A PivotTable helps you see the forest through the trees.
  • Analyze your data in minute detail – Drill down to answer specific business questions. For example, you’ll be able to see which of your products are outselling others or which ones are generating the most revenue.
  • Focus on areas that need your attention – Apply specific filters to see just the data you want to analyze.
  • View your data from different angles – Pivot the data to get the perspective you want, perhaps to compare data from different regions or different products.
  • Present your data in a report – A professional-looking report format will help other people easily understand your data analysis, especially if you use features such as conditional formatting and sparklines. You can even include a PivotChart for visual cues.

We have plenty of resources on creating PivotTables, starting with this blog post and video by PivotTable expert Frederique Klitgaard. A few other helpful links:

Create or delete a PivotTable or PivotChart report

Quick start: Create a PivotTable report

Overview of PivotTable and PivotChart reports

What if you need to connect your Excel workbook to multiple data sources at once? That’s where PowerPivot, a free add-in for Excel, can really help. It also increases Excel’s capacity to deal with hundreds of millions of rows. We’ll have more on PowerPivot in an upcoming post.

 — Doug Kim and Frederique Klitgaard