2008-05-01

Beauty in a spreadsheet


There is beauty in a spreadsheet.

Much of my job involves working with people. I don't spend all day buried in numbers. But sometimes that is exactly what I need.

With a spreadsheet, there are right and wrong answers. When you provide the correct input, with the correct formulae, you get the correct output. When you are done, it's either right or it's wrong. And sometimes you can learn from the answer.

A deep, hours-long dive into a spreadsheet can be a welcome bath in the sea of black and white. When I hit that state of flow I feel invigorated. It's been like this for several years.

I'm a fairly competent Excel user at this point. I can work with charts. I can make the things look pretty. I can even code my own macros (not that I've had to do that lately).

Even after all these years, though, I still don't "get" pivot tables.

I can sometimes make them work through trial and error, but despite the exercises I've done, the help files I've been through, and the books I've read, I still can't conceptually grasp pivot tables. I'm starting to accept that I never will. I will always remain an "intermediate" Excel user.

(Can you guess what I've been working with tonight?)

6 comments:

Unknown said...

Thanks for dropping by!

Anonymous said...

I made a pivot table for someone a few months ago and I am sure I would have to re-teach myself how to do it should I need to use one ever again!

Anonymous said...

Great blog!

BoBo said...

Actually..once you get the hang of the pivot tables and getting the data set up properly..they are very powerful and a great way to organize large amounts of data. Keep pluggin away..you'll be glad you did!

Anonymous said...

I love pivot tables. Just keep trying with small sets of sample data until you feel comfortable with them. Once you get the hang of it, they will change the way you look at your data, and make Excel even more cool than it already is.

SB
data nerd

Anonymous said...

I use pivot tables all the time both in excel and in SQL. They are pretty powerful tools.

Think of their organization a bit like you choose what to display in a chart, only a pivot table is going to take rows of data and turn them into columns.

You choose those elements that should be what to group on (dates, districts, people are normal choices)in the order of widest to narrowest groups, then the elements to summarize (sales $, inventory units sold, commissions) and how they should be summarized (count, sum, average) go in the data section of the wizard.

Most likely you won't use every column of your base data and it's best that you don't even try because excel will barf, but get one or two biggies and go from there.

The really neat thing about excel pivot tables is that if someone needs to see the underlying detail, all they have to do is click on that total figure and it will open a new worksheet with that detail of that number only on it.

Just like charts, pivot tables can be eyeopeners, but they do create "weight" in your spreadsheet, so you may have issues trying to email it to someone.