Presenting data in tables and graphs

Offhand I can think of a number of different ways I could present the data from the daily downloaded statistics project I discussed last week.

I could…
…create a table that shows the number of sessions per library per month.
…create a pie chart to quickly show which libraries are using the service the most, and which are using it the least.
…create a line chart to illustrate usage trends over time.
…hard-code the data into a HTML file for a static online presentation.
…create a CSV file that would be accessed by a short PHP script, for a dynamic, easily updated online presentation.

No matter which option you choose, the first step is to compile the data you want to present into a single area. Here’s a breakdown of the steps needed for that. You can see the steps laid out in this example, with one step per worksheet.

Step 1: Extract a list of libraries/teachers/departments/whatever you’re trying to get data about
You can do this using the same technique as getting rid of duplicates, but with a slightly different application. Instead of doing this with the entire data set, you want to start by copying just the column with the libraries/whatever, and pasting it into the sheet you want your final data table in. Then sort that column, and delete all the duplicates in it. You’ll now have a list containing one and only one entry for every item.

Step 2: Set up the headers for a table
You can use the list you just made as the row headers, and the dates as the column headers. In my example I only care about how many chats came in from each library in a given month, so I only list dates with years and months, not days. If you select any of the column headers except the first one you’ll see that I used a quick formula to calculate the dates rather than having to type them all in. For example, in C1 the formula is date(year(b1), month(B1)+1, 1).

Step 3: Fill the table with data
You can do this using either =countifs(), or a combination of =countif() and =concatenate().

In my example I chose to use concatenate and countif(). I started by adding another column (which I named “data_combined”) to the data that compiled the the year (column D), the month (column E), and the library name (column B) using the formula =concatenate(d2,” – “,e2,” – “,b2). You can use any formula you want, but I recommend making a habit of using some kind of symbol that isn’t likely to appear in the data to separate the different parts. It’s not an issue with this arrangement, but if I had library then month then year, and no separator, there’d be no way to tell if Library 1112012 was Library 1 November 2012 or Library 11 January 2012.

Then in the table I entered a formula that combined the year & month (from the column header) and library name (from the row header), and counted how many times that combination occurred in data_combined: =countif(data_combined,concatenate(year(b$1),” – “,month(b$1),” – “,$a2)

Again, you can use any formula you want, but make sure the format of the results matches what you did in the data_combined column. Otherwise your countif will just return zero.

You might notice that I used partially fixed references in the table, but not in the data sheet. That’s because in the data sheet all of the references are just to different columns, not different rows, and it’s only being copied down multiple rows, not across multiple columns. In the table, though, it’ll be copied across multiple columns so the reference to the row headers, column A, has to be $a2 so the column will stay the same. Similarly, it’ll be copied down multiple rows, so the reference to the column headers, row 1, has to be b$1 so the row won’t change.

Step 4: Add any additional columns/rows desired.
This is optional. I added a Total column so it would be easy to see the total traffic from each library, and a Total row to make it easy to see the total traffic each month.

Once that’s done, the remaining steps depend on your end goal. It is, of course, possible to present the data more than one way.
Option: Create a table
This option is easy, since you’ve already put everything in a table — you just need to make it look like one by formatting it. To do this you can use automatic formatting or design your own piece by piece. In the Present_Table worksheet I’ve created an example, listed some of the formatting choices I made, and explained why I made them.

Option: Create a pie chart
For this option you only want to be dealing with one row or column — like one time period for all the libraries, or all of the time periods for one. I got a bit carried away with the examples and ended up making 3 of them, to illustrate different ways you can make a pie chart. You can see them all in the Present_Pie worksheet, along with some comments about the formatting choices I made.

Option: Create a line chart
Line charts can handle multiple lines of data, though it’s also useful for just one.
I made two, and you can see them in the Present_Line worksheet, along with some comments about the formatting choices I made.

That’s it for the basic presentation options I mentioned! Next week I’ll talk in more depth about a neat trick I did using concatenate to make informative legends, and the week after that I’ll cover the online options I mentioned: hard-coded HTML, and preparing a CVS file for a PHP script to use as a database.