Get A Job In One Hour Or Less!

Well, okay, maybe not, but there's no denying the fact that PivotTable experience is a sought-after skill in today's job market. In fact, the demand for PivotTable people has gone up even as demand for just plain Excel folks has gone down (source: Pivot Table Guy).

It isn't that hard to learn how to use PivotTables, either. If you have an Excel spreadsheet with rows and columns, you already have the source data necessary to generate a PivotTable. The table I was working with had 6 columns: Date, Location, Product, Channel, Quantity, and Revenue. To generate the PivotTable, select a cell anywhere in your table, go to Data > PivotTable and PivotChart Report (Insert > PivotTable in Excel 2007), and complete the wizard. This will create an empty PivotTable. Into the PivotTable you can drag and drop fields to create the final product. My table used Location for its rows, Date for its columns, and Revenue for its comparison data.

The main limitation of PivotTables is they only take up to 255 column values. That means that if you have sales figures for every day of the year, you can't display all of them at once. To fix that, right-click on the appropriate field (in this case, Date), select Group, and choose how you want to group the data. When dealing with dates, you can group by years, quarters, months, or days.

Want more detail than just revenue by location and date? Just drag and drop another field into the appropriate section. I added Channel to the columns, which breaks out the revenue by channel and then by date (which I've grouped by quarter to avoid exceeding the 255-column limit). Excel automatically calculates totals on all rows and columns, as well.

That's not quite good enough for me. I'm the sort of person that color-codes everything. That's where upgrading to Office 2007 finally has a benefit. There are the obvious formatting options (Home > Format as Table), which make the headers, footers, and totals different colors. In addition to that, however, you can add data bars to cells. Data bars are horizontal shadings in cells that show relative quantities according to the range of cells you select, allowing you to gauge at a glance which cells have high or low values. To add data bars, select the cells you want to compare to each other (all first-quarter sales by location, for example), go to Home > Conditional Formatting > Data Bars, and select the color you want. There are menu options (More Rules) to allow you to fine-tune the formatting. Voila, we have color.

Quote of the Day:
'Cause I get a thousand hugs
From ten thousand lightning bugs
As they try to teach me how to dance.
A foxtrot above my head,
A sock-hop beneath my bed,
A disco ball is just hanging by a thread...
--Fireflies, Owl City