Use One Huge Table in Excel

Sometimes we at Distilled go a little far out with our posts about statistics and data and all that, but not all of that is applicable to everyday work. Not all tasks are so complicated—some are fairly cut and dry, like figuring out which landing pages have received the most traffic over the last year. That’s straightforward, right? At some level this is clerical work—but that’s all the more reason to get it over with as quickly as possible.

Often we can slow ourselves down by organizing data in inefficient ways. These methods might seem smart or fast at the time, and yet they can often make it more difficult to get what we really need out of our data set. Maybe you are trying to figure out which page received the most traffic over the last year—and this is what your Excel file looks like:

 

 

If your spreadsheet looks like this, you’re gonna have a bad time.

Don’t separate sheets by date

The problem with organizing data into separate sheets based on month (or week for that matter) is that if you want to calculate any data that uses information from multiple months, you’re going to need to get the data all into one place. One way or another, those numbers have to come together—but right now they’re on separate sheets.

What I’d suggest is a sheet that looks something like this drastically oversimplified example:

 

 

So instead of separating this data into different sheets, we just have a field that specifies a month. Each unique keyword can appear in multiple rows corresponding to each month. There is no question that this results in a less human readable table, but the reality is that no one reads a table with 1,000,000 or even 1,000 lines by going through it line by line. Human readability isn’t a concern here.

So why do I think this method of organization is so superior? 

It’s all about the pivot

To see the benefit of this organization, it will help to illustrate the end point. We’re trying to get at the total number of visits for a certain keyword across multiple months, right? The fastest way to do that is going to be a pivot table. If you’re not familiar, a pivot table allows you to summarize data in a table.

This is our pivot table—and look, the data we want is there! Poorly labeled, but there nonetheless:

 

 

This is something that can not be done without all of the initial data existing on the same sheet—a pivot table has to reference one and only one table. And, as an added bonus, we can just as easily reverse out pivot table so that the various months appear as row labels and the sum of traffic is calculated for each month instead of each keyword. Neat!

Keep up with the data

It may be that you get reports like this at regular intervals—maybe you don’t have control of the format of the data. That’s no reason to let things pile up and have your spreadsheet out of control. It may be that you have to cut and copy data from each report into one larger table. Do so.

It’s worth the time and effort, and if you do it on a regular basis, instead of going back and slogging through the task of copying all the data at the end of the year, it will seem much more manageable. You may, of course, need to add a column for the time period the data represents. Be careful when autofilling such data because Excel will often assume you want to increment dates by day instead of just copying the same value over and over:

 

 

Final thoughts

If you keep all of these points in mind you’ll probably wind up with fewer, larger tables—and more importantly fewer, smaller headaches. If you’d like to increase your Excel proficiency further, our Mike Pantoliano has written a well received guide to Excel for SEO, which I highly recommend.

A potential problem with this methodology is that Excel only supports ~1,000,000 lines per sheet. If you have more than 1,000,000 lines I’d suggest that you belong in a database program like Access instead of Excel, but that’s another post entirely.

Are there any other basic rules you follow to make sure your Excel workflow stays efficient?

Benjamin Estes

Benjamin Estes

Benjamin is a senior consultant who joined Distilled in 2010. Having earned a BA in Mass Media, his intention is to continue studying the ways in which people interact with media and apply those lessons to his consulting. Ben-h264 // Born and...   read more

Get blog posts via email

13 Comments

  1. This is by far my favourite title for a blog post in recent memory.

    reply >
  2. I totally agree. For people who use Google Docs, the limit on rows per sheet is much much lower than 1,000,000 so this won't be good advice. But if you are using a desktop spreadheet program, I totally agree!

    reply >
    • Benjamin Estes

      There are definitely different considerations in that case—but I've always seen Gdocs as a different use case entirely. More about sharing and presenting and collaborating for me.

      Thanks for the feedback :)

  3. I think using Fusion Tables ( form Google ) can help here too.

    reply >
    • Benjamin Estes

      Wow, I wasn't familiar with that. Might have to play around...

      Of course the title of the Fusion tables page is "Two tables are better than one", so I'll have to get past that first :P

  4. I recently discovered the value of the pivot and it has been so helpful. My workbooks used to be full of sheets; Way, way, way too many sheets to handle. Now I'm pivoting and life is much better.

    I suppose the difference is just a little prior planning and deciding how to organize your table for the data that you want to see in the end.

    reply >
  5. I have faced this situation where I had to manage monthly data for 12 publications in my report. To add to the complexity, I had to provide same report to sales, marketing, editorial and IT team. We found it useful to keep separate tabs for each of them which had very detailed report on keywords, geography, sources etc. and offer one last tab consolidating all of the information in one sheet across the year for sales team and higher management.

    This helped us have one report for each of its recipient instead of creating separate type of reports for sales department, editorial department and marketing department. Those who were interested in overview throughout year could jump directly to the last sheet and grab all the data in one glance and team such as editorial and IT could visit the respective sheets for detailed data where they could get information they wanted.

    reply >
  6. Great post Benjamin, I've been caught out by doing exactly that with months on different sheets. One of my main rules to keep my workflow efficient, well actually to keep my workflow safe is to use copies when I'm trying to rework any of the code.

    I've had some horrible moments where a "quick adjustment" has resulted in a mess of undos and redos and that awful feeling when you realise you've butchered the xls you've been carefully building over the past 8 months.

    reply >
  7. If Ronseal did blog titles, they'd probably do the best blog titles in the world!

    Nice blog though, Benjamin - I'm currently going through Excel for SEO as it's about time I honed my Excel skills to ninja level!

    Totally agree on the above points re. Gdocs. So restrictive... time Microsoft sorted this out and did a full online version of Excel. It might even tempt me to open up my Hotmail account again!

    reply >
  8. Thanks for the tips Benjamin. I've been trying to brush up on my Excel skills, so this was a good one for me. I'm just discovering the magic of pivot tables and really enjoying them.

    I agree with others who are saying that Gdocs are restrictive, not to mention cumbersome and finicky. I can think of a lot of people who would be overjoyed if they could use an online version of Excel.

    reply >
  9. Hi,

    Excellent tips for Excel. I have been trying to improve my excel skills but i have not much into it. Thanks for your explanation.

    Thank you

    Rick

    reply >
  10. Some really great ideas in there Benjamin. People will find their lives much easier if you start with one enormous table of doom which only you see, which is then sliced 'n' diced in a PivotTable for wider consumption. That's definitely the way to go.

    Just a couple of tips: you can bring together more than one data source into the PivotTable, known as 'consolidation'. This feature was always there in Excel 2003, and then mysteriously disappeared for 2007 + 2010. However the dialogue is still in there: press ALT, D, P in that order to resurrect the old options box, and that will allow you to pull together multiple worksheets etc. in one giganto-mash-up PivotTable.

    If throwing around gargantuan chunks of data is really your thing, consider downloading PowerPivot, a free add-in for Excel that extends its capacities enormously.

    If Gdocs could seriously replicate PivotTables it would make Microsoft really worry, but I think the online version MS has coming next year will keep them at the top of the spreadsheet tree a little while longer.

    For more PivotTable hints, tips and training please visit Best STL Excel Training London. Full disclosure: I work for these guys, providing training on Excel and many more applications besides.

    reply >
  11. Hey ,

    Excellent stuff. I guess these are the right methods to use Excel for huge table. I have been searching a lot and at last, you have post it. :)

    Thank you

    reply >

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>