Creating Charts With Pivot Table

Feb 2016

4 mins read

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.

pivot sample data

  1. In the resulting window, make the changes as shown below and hit ok.
    pivot data
  1. It already looks more readable, doesn’t it?
    pivot sample data
  1. Now, let’s make a bar graph out of it. From the top menu, again, go to insert, and select the 2D graph. Again, make sure you have placed the cursor somewhere in the table.
    sample pivot data
  1. It will create a 2D chart based on the data from the pivot table. You can click around and drag the edges and corners to how ever you want to shape it.
    sample pivot data
  2. Looks a little crammed, doesn’t it? However, you can still take control. Use the month and employee drop down menus and customise it as you like. For example, from the month menu I select January. This will plot a bar graph for sales performance of all the employees in January.
    bar graph

Now, if I select all the month and select only Aoki from the employee menu, this will give me Aoki’s performance throughout the year.

bar graph

  1. If you want to find out the best sales man of the year, simply untick the month check box from the pivot table fields.

Thus, we infer from this chart that Irfan is the top performer. Irfan deserves to be given the exemplary performer award.

Looking to sharpen your knowledge on excel trading and investing models? Join our top-rated professional diploma in financial trading and investment today!

Join 12+ million students who already have a head start
Sign up today and get 4 weeks free!
No commitments. Cancel at any time.

Shaw Academy

Shaw Academy has over 100 globally recognised courses across 10 faculties. Sign up now - the first four weeks are free.