How Do Those Accountants Do It? A Guide on Using Excel PivotCharts for Compelling Legal Training

Colorful charts make for compelling presentations.

Corporate counsel should take a lesson from the accountants, and use Microsoft Excel PivotTables and PivotCharts to create beautiful and effective charts to transform boring legal training.  

Contrary to popular belief, Excel is not just for financial data. PivotCharts help you make graphs for any type of list or spreadsheet you can imagine. 

PivotCharts? Sounds complicated. 

Don't fret. You don't need to ask Doug in accounting for a tutorial.

Corporate Counsel Training Tips has you covered. 

Using a made-up corporate policy catalog spreadsheet as a guide, I'll walk you through how to create PivotTables and PivotCharts to WOW your audience.

(seriously, you will need to WOW them to keep eyes open during legal training presentations on the topic of corporate policies...)

Easy Instructions: 

Step 1 - Create the Spreadsheet

Excel makes it easy to setup a simple spreadsheet. Just organize the information you want to present into columns and rows:

As you can see, there aren't any numbers in the spreadsheet.

Lesson - you don't need to be an accounting wiz to make cool charts. 

Step 2 - Create the PivotTable

A. Go to the top left corner of the screen and click on the "Insert" tab (yellow arrow): 

B. Click on "PivotTable" on the far left side:

C. A PivotTable screen will pop up and ask you to "Select a table or range."

D. Select the entire data set you wish to use. For this example, we will select everything. Click on cell A1 and drag your cursor to cell F15. 

E. Next, click on the "Location" bar on the Create PivotTable pop-up window (yellow arrow) to select where you want the PivotTable to be located on the spreadsheet:

F. Select a cell located below your spreadsheet or wherever there is room. E.g., cell A17.

G. Click "OK" to create your PivotTable.

H. Next you will choose the fields you use to create the table - i.e., what information do you want to show to your audience?

Select fields to add to columns, rows and "Values".

For spreadsheets that don't contain numbers, adding a field to the Values section will simply count the number of times that a certain "Row Label" or "Column Label" appears.

E.g., if I want to determine how many policies are subject to annual v. semi-annual v. quarterly training, I would select "How Often are Employees Trained on Policy?" and place it into the "Row" section. Then select "Policy Name" and place it in the "Values" section: 

Excel will generate a chart that looks like this: 

I. If you want to add more data points, select more fields in the PivotTable field list, and add to Columns or Rows as it makes sense.

E.g., select "Department" and place it in "Column Labels" to get a chart that looks like this: 

 

Now your table shows how often certain training occurs for policies organized by department - HR has 3 policies that are trained on annually, IT has 1 policy...

Time to make the chart!

Step 3 - Create the Pivot Chart

 A. Click anywhere on the PivotTable - this will pull up the PivotTable Tools tab. 

B. Under the Options tab at the top of the screen (yellow arrow), select the PivotChart button (green arrow).

C. Time to select your chart type (bar, pie, plot, etc.). Once the options pop up, we will select a standard bar chart for our example. 

D. Click "OK" once you have selected your chart type, and Excel will automatically generate the chart: 

E. This chart doesn't look all that great. You'll want to customize your PivotChart using the PivotChart Tools options highlighted in green at the top of your screen. 

Here's a brief explanation of each tab:

Design

The design tab allows you to use template colors and chart layouts. Selecting one of the options will automatically convert the chart accordingly. I often use the chart layout templates to present the data in a more interesting way.

Layout

The layout tab allows you to create descriptions and titles for the chart, axis and other data. It's important to use these features to make the chart easy to read and interpret. 

Format:

The format tab allows you to tweak color, font and borders for the chart. The template "Shape Styles" make it very easy to create colorful charts and clean borders. 

After some tweaking with the PivotChart Tools, we arrive at the final product:

This simple chart details how many policies are subject to annual, quarterly and semi-annual training for each business unit. 

Now, insert the chart into your legal training presentation, and you are all set!

Looks much better than using bullet points in PowerPoint!

NOTE: This example is pretty simple. You can create extremely complex PivotTables and PivotCharts which simultaneously take into account dozens of factors. I've done this before for a number of presentations. Excel makes it easy.