Submitted by: Craig Brody, Microsoft Office Training/Consulting/Programming Instructor, C. Brody Associates

Ever heard of PivotTables? They’re one of Microsoft Excel’s most powerful and popular features. Here’s an example why: Suppose your business sells three different product types (Smartphones, Laptops, and Tablets) to several customers across the country. Over the course of the year, you have diligently recorded every product order, entering each order’s customer, location, order date, product ordered and total sales amount.

It’s now the end of the year and you would like to summarize and analyze the year’s sales quickly without having to manually sum each potential category of sales. Perhaps you’d like a report summarizing sales by customer broken down by quarter? Or quickly change it to a report than totals sales by month for each product? Or maybe even display a pie chart that shows % of sales by location?  Or view all reports together in a “dashboard” style?

Well, PivotTables can quickly do this for you to help analyze your sales, saving you time and helping you to better plan for the next year.

Here is a picture of a PivotTable report that summarizes sales by Quarter, Location and Product in one professional report.

Excel Pivot Newsletter

Here are some quick tips on creating and working with PivotTables:

  1. Easy to create. Just start with a list of transactions and in four clicks you can summarize all your totals. First click – Click in the list you want to summarize. Second Click – click the Insert Tab. Third Click – click Create PivotTable Icon. Fourth Click Pick your categories to sum.
  2. Easy to change. Just drag and drop categories in different places to see a completely new view of your data. Or insert the Slicer option to “slice and dice” your data to produce even smaller snippets of results.
  3. Easy to update. Over time your original list of data will probably change. To update or “refresh” your PivotTable report output based on the changes, just right click a cell in the report and click Refresh.
  4. Easy to create a chart based on the PivotTable. Create your report then click the PivotChart icon and select your chart type. (Pie, Bar, Line etc…)

Next month, I’ll discuss how you can put several PivotTable reports and charts together into a dashboard template within Excel.

C. Brody Associates
Microsoft Office Training/Consulting/Programming
cell: 267-872-7410
email: cbrody@outlook.com
web: www.cbrodyassociates.com
linkedin: https://www.linkedin.com/in/craigbrody