The Pivot Table

The business of expressing data in a spatial manner is a very fascinating one. My pastel-colored world is definitely an eye-catching venture that can help you make sense of an otherwise bland set of data of infinite rows. (This particular one had almost 2,000 rows in them!)

land use sample map

But before anything can look as pastel-color perfect, you need to first ensure that you have the right type of data. Usually the editing and cleaning up and analysis of data begins in Microsoft Excel or any spreadsheet that you are comfortable with. I tend to prefer Excel and a new favorite feature I have of this program is the Pivot Table function.

Pivot Table, as the name implies, involves manipulating a given table to make it appear in a different way so that you can have more room for new emphasis. A simple table can look like this:

given table

(Sorry for the apples and oranges sample. It’s just for illustration purposes only.)

Suppose that I want to line up the columns in a different way. I can use the pivot table option found on the Insert menu toolbar.

step 1

 

The program will prompt me to highlight the region of interest and then choose whether I want the new table in the same page or in another worksheet: step 2

After that has been settled, a new panel on the right sidebar appears where I can manipulate the fields by dragging and dropping them on the areas below:

step 3

For this particular set, I decided to use this:

step 4 - drag and drop

And voila. There’s my new perspective of the previous table I had for a given.

There are options to tweak this appearance by accessing the field value settings (right click on lower left area to access this menu). I can choose to just display counts, averages, maximum or minimum values, and other things:

step 4a - value field settings

 

Clicking the design tab on top allows me to tweak the appearance a little and decide whether I want to display subtotals, depending on the type of data. For the purposes of fruit tallies, I settled for the simple tabular format:

step 4b - report layout

The pivot table can allow me to make many things out of an otherwise ordinary table.If this function did not exist, I’d probably be able to sort 5,000 rows of data in a whole day. This exercise took only the whole of 20 minutes. Go figure. 😉

I have to thank my boss Engr. Gina Milarion for sitting with me on this one as I play with real-life apples and oranges. 🙂 I keep on using MS Excel daily and I realize the magic tricks it has under its sleeve. As it turns out, it’s not as boring or generic as I initially thought. 🙂