Creating Charts With Pivot Table

Visual representation of data appeals a lot more than text representation. So, today we’ll learn the foundation of data representation in excel, the pivot table and charts.

Pivot table collates crude information into readable data and using this data, one can create charts that pictorially represent that data. Let’s dive right into it.

Sample Data

Let’s say I have a sales report of my sales team that shows their name, total sales, and the month in which they have achieved those sales.

sample pivot data

But it’s done at random. From row 2, I know that mike achieved 2,380 in September and in row 12, the same mike has brought 1,282 in February. I also know for sure that if I were to show this report to my boss, I am most likely to lose my job. Because it’s not data, it’s just crude information. This is where pivot table comes in.

  1. Place the cursor anywhere in any of the cells in the table, not outside.

  1. From the top menu go to insert and select pivot table

sample pivot data

  1. Just hit “ok” in the resulting window

pivot dialogue window

  1. You will see that this has created an empty pivot table in a new work sheet

empty pivot table

  1. Take a look at the panel on the right. You will see 3 empty check boxes and the names against them are employee, sales and month which are the names of the columns in our original report in sheet 1. You will also see that in the check boxes below, there are 4 field boxes named: filter, columns, rows and values. Now, let’s start populating information in this blank pivot table.
  2. Click on the “employee” check box and drag it into the “Rows” field box.

employee column field

Now do you see how the table is starting to take shape? We have instantly populated unique names of the employees without any repetitions.

  1. Now let’s drag the “month” check box in to the “columns” field box and the “sales” check box into the “values” field box.

sample pivot data

Now it looks much better than the crude information that we had, right? Let’s perk it up a bit more.

  1. Select the entire table from B5 to N15. Right click on the selection and select format cells.