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.
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.
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.
- Place the cursor anywhere in any of the cells in the table, not outside.
- From the top menu go to insert and select pivot table
- Just hit “ok” in the resulting window
- You will see that this has created an empty pivot table in a new work sheet
- 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.
- Click on the “employee” check box and drag it into the “Rows” field box.
Now do you see how the table is starting to take shape? We have instantly populated unique names of the employees without any repetitions.
- Now let’s drag the “month” check box in to the “columns” field box and the “sales” check box into the “values” field box.
Now it looks much better than the crude information that we had, right? Let’s perk it up a bit more.
- Select the entire table from B5 to N15. Right click on the selection and select format cells.
- In the resulting window, make the changes as shown below and hit ok.
- It already looks more readable, doesn’t it?
- 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.
- 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.
- 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.
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.
- 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!