You can use the SUMPRODUCT function in Microsoft Excel to help you quickly calculate business expenses per department and then visualize that report data using Excel Pie Charts. That’s what will be demonstrated in this post, along with some use of basic features like Paste-with-Transpose and the SUM function.

Setup Your Table

Below is an example table which shows how taxable business expenses are spread across departments:

Using SUMPRODUCT

You could create a formula at the end of every team-specific column to calculate the total of that column, but that’s inefficient and error prone…especially as you start to add new expense items and teams.

Instead, Microsoft Excel has a function suited perfectly for the requirement: SUMPRODUCT. I’ll show you how to use it for this report, and for additional follow-up, definitely check out the excellent Microsoft Excel in-product documentation.

Simple Pie Charts in Microsoft Excel

When we work with data and charting, we need to decide how we setup our tables and which formulas to use for calculations. Those choices can be based on a variety of factors: are we producing a simple static report deliverable, is the workbook intended to be an interactive instrument, do people need to control slicers, timelines, and adjust input/output to create their own report results, etc. And, of course, the relationship and decision impact goes both ways.

The background for this post was/is based on a simple report I build for a client. I copy and pate the charts I need into a Word document with supporting text, convert to PDF, and then send an e-mail; that’s what the client wants.

So, in this scenario, I use the main table which handles the calculations, and then just do some simple copy-and-paste steps to get data structured as I need for the Microsoft Excel Pie Charts. I also define a basic formula using the Microsoft Excel SUM function to calculate totals by product.

For the report, I build:

• Expense % By Product
• Expense % By Team

Using the Paste – Transpose functionality and a quick  Excel Sum function works out faster (and cheaper) than spending time changing chart series and trying to unnecessarily use PivotTables. I do basically the same thing you see in this video as I do for my client, and clean things up with a few formatting-and-sort options before pasting the pie charts into the final report. Keep it simple and cost effective for your clients…don’t go building a Microsoft Excel expense reporting dashboard with advanced analytics and a custom user interface when your client just need a couple of pie charts in a PDF.

Thanks for reading.

Categories: Business, Microsoft Excel

Tags: Microsoft Excel, Office