My Rules for Using Spreadsheets

Aug 13, 2026 08:00 PM - 2 hours ago 3

Next post Previous post

August 1, 2026 astatine 10:29 AM by Dr. Drang

My basal norm is Don’t, but a azygous connection wouldn’t make for overmuch of a blog post.

In what follows, I dream to explicate really I’ve travel to that norm and the exceptions I make to it. I’ve been reasoning astir really I usage and don’t usage spreadsheets rather a spot lately. This introspection was inspired successful portion by Allison Sheridan’s position astatine Macstock (which you tin spot on her site on pinch a mates of other caller spreadsheet posts) and successful portion by my caller usage of Numbers to make quality tables and clean up a array of data.

Let’s commencement by considering what makes spreadsheets truthful attractive. Right disconnected the bat, you’re presented pinch a grid of cells that enactment arsenic information containers. You don’t person to specify these containers, you don’t person to sanction them, you don’t person to initialize them—they’re conscionable there, waiting for you to capable them arsenic you spot fit.

When it comes clip to commencement operating connected this data, you still don’t person to sanction the cells. You conscionable click (or click and drag) to capable successful the usability arguments. The spreadsheet app fills successful the due row/column reference. If you want a reminder of what a compartment is for, you tin type a sanction aliases explanation successful an adjacent cell. Similarly, you don’t person to fig retired the due bid of the operations. The app useful retired the compartment dependency concatenation and recalculates everything, everywhere, each astatine once.

And because you tin group the size, color, border, and font styling of each cell, your spreadsheet tin make nice-looking tables for inserting into your reports, memos, and slideshows.

So a spreadsheet is simply a information store, a logic machine, and a position tool. Are you getting it?

But if spreadsheets are each that, wherever does my Don’t norm travel from? There are galore sources, but I’d person to opportunity I’ve been powerfully influenced by the past 10–15 years of my moving life, during which clip I had to analyse dozens and dozens of information sets, each of which were sent to maine arsenic Excel spreadsheets. The engineering firms that sent maine the spreadsheets had created them not simply arsenic information stores. They included immoderate of their ain study (which typically overlapped somewhat pinch mine), and they formatted the spreadsheets arsenic tables to put into their ain reports. This made my activity harder for a fewer reasons:

Because I had to make judge I understood and agreed pinch their analysis, I had to reappraisal each their formulas. Some of these formulas were complex—nested IF statements are easy to travel successful a accepted programming language, but they’re a messiness successful a spreadsheet. Some were inconsistent—different rows successful the aforesaid array would person different formulas, arsenic if they were written by different group astatine different times aliases adapted from a spreadsheet connected a erstwhile project. Some of them referred to cells that were acold distant and required a batch of scrolling to way down. None of them—not a azygous 1 successful complete a decade—used compartment names to thief make the formulas easier to understand.

The analyzable formulas mentioned supra sometimes—not often, but sometimes—contained mistakes. And sometimes the formulas were correct, but the descriptions successful the header cells were wrong. This meant telephone calls were needed to resoluteness the discrepancies, further slowing the analysis.
It was communal for the information to beryllium divided complete 2 aliases much sheets. I deliberation this was done chiefly to make the tables fresh amended into the different engineers’ reports, which was good for their purposes but not for mine. I had to recombine the information for my analyses. Also, the sheets often had complicated, multiline headers, which meant I couldn’t conscionable export them arsenic CSV files.
Every technologist I worked pinch built their spreadsheets successful a different way. Those who worked for the aforesaid patient didn’t adhere to an “ABC Engineering” location style. Even individual engineers would alteration their spreadsheet styling from 1 task to the next. Basically, each spreadsheet that came successful the doorway was sui generis, and I had to do each the information cleaning by hand. This slowed maine down, not only because I couldn’t trust connected automation for this step, but besides because I had to double- and triple-check my activity to debar copy/paste mistakes.

Fundamentally, this experience—especially Item 1—soured maine connected the usage of spreadsheets for thing ample aliases complex. The engineers I was moving pinch were smart, but their spreadsheets weren’t. My conclusion was that the simplicity of the emblematic click-and-drag method of assembling a spreadsheet encouraged mediocre statement and errors arsenic the spreadsheets grew aliases were adapted to caller data. It’s easy to opportunity “Oh, I would ne'er do that,” but I’m aged capable to cognize that I would do that. I spot the easiness pinch which I tin build spreadsheets pinch today’s apps arsenic a Siren opus that will lead maine onto the rocks.

(If you’re getting fresh to constitute to maine astir the Reinhart/Rogoff paper, you tin relax. It is the premier illustration of simple spreadsheet errors—errors that 2 Harvard professors would surely ne'er make—and it led to a batch of suffering done unnecessary authorities austerity policies. And if you’re now getting fresh to constitute to maine astir really Reinhart and Rogoff’s errors don’t negate the basal truth of their conclusions, you tin conscionable fuck off.)

The convenience of having the information and the study logic successful the aforesaid archive becomes a problem erstwhile you person to use that logic to respective datasets, particularly erstwhile they disagree successful size. Spreadsheet templates are awesome erstwhile the information let you make respective spreadsheets pinch the nonstop aforesaid layout, but the information I thin to woody pinch don’t fresh that rigid pattern. If I’m doing, for example, analyses and land of respective clip series, those bid seldom widen complete the aforesaid magnitude of clip and the aforesaid number of information points. It’s acold easier to woody pinch these size differences erstwhile the logic is successful a program, separated from the data.

Another problem pinch spreadsheets is that the magnitude of information they tin incorporate is much constricted than erstwhile you usage different information study workflows. The size limits connected spreadsheets are, admittedly, rather large, but successful an era of Big Data “quite large” whitethorn not beryllium large enough. In her Macstock talk, Allison shows really she ran into that problem pinch the information group of US babe names. Let’s return a detour to talk astir handling that data.

One of the ways you tin download the babe sanction dataset is arsenic a zipped postulation of CSV files. Each record successful the postulation is associated pinch 1 twelvemonth and has a sanction for illustration yob1960.txt. The contents look for illustration this:

Mary,F,51472 Susan,F,39208 Linda,F,37316 Karen,F,36378 Donna,F,34138 [etc]

where the first point is the name, the 2nd is the activity astatine birth, and the 3rd is the number of babies fixed that sanction successful that year. The lines are ordered first by activity and past by number. If you concatenate each the files, you’ll find location are 2,181,032 entries. As Allison recovered out, this won’t fresh into an Excel spreadsheet, arsenic Excel is constricted to 1,048,576 rows. That’s the very computery number 220 aliases 10242. The limit successful Numbers is the little computery but much quality 1,000,000 rows.

Allison sewage astir the size problem by… er… cheating. She eliminated the little celebrated names to get the database to fresh into Excel, and past demonstrated immoderate pivot array stuff. You tin spot it starting astatine 1:13:50 in the video.

I decided to do thing akin to her activity but without the cheating. First, I concatenated each the individual files into 1 large CSV record that besides included a section for the year. That was done done these ammunition commands:

echo 'Year,Name,Sex,Count' > all-years.csv for f successful yob*.txt; do y=${f:3:4} sed -e "s/\r$//;s/^/$y,/" $f >> all-years.csv done

The twelvemonth is extracted from the record sanction done substring description and past added to the opening of each statement via sed. The original files are successful Windows format pinch CRLF statement endings, truthful the sed bid besides deletes the CR characters. The upshot of each this is simply a record (with Unix statement endings) named all-years.csv that looks for illustration this:

Year,Name,Sex,Count 1880,Mary,F,7065 1880,Anna,F,2604 1880,Emma,F,2003 1880,Elizabeth,F,1939 1880,Minnie,F,1746 [etc]

(Yes, moreover though the information group is said to person travel from Social Security registrations, it starts successful 1880, decades earlier the Social Security Act. I can’t explicate that. Nor tin I explicate really Minnie was erstwhile the 5th astir celebrated girls’ name.)

I’m going to usage Python and Pandas to extract the 5 astir celebrated girls’ names from 2001 done 2025 (the past twelvemonth successful the dataset). Here’s the commencement of a elemental interactive Python convention that does it:

>>> import pandas arsenic pd >>> df = pd.read_csv('all-years.csv') >>> cols = ['Name', 'Count']

This sounds the CSV record into a dataframe and defines the columns of the dataframe that we want to see successful our output. The >>> astatine the opening of each statement is the interactive Python prompt. Here’s really we get the database of names we’re willing in:

>>> df[(df.Sex=='F') & (df.Year>2000)][cols].groupby('Name')\ ... .sum().sort_values('Count', ascending=False)[:5] Count Name Emma 449576 Olivia 423613 Isabella 381577 Sophia 368619 Emily 353077

The ... indicates a continuation input line. Everything aft that is output.

Reading done the command, we spot that we’re

getting the subset of information consisting of girls calved aft 2000;
limiting the output to the Name and Count fields;
grouping the output by Name;
summing the Counts for each Name;
sorting the results by Count successful descending order; and
limiting the output to the apical 5 names.

That’s evidently a agelong command, but you tin spot really it’s constructed successful a logical fashion.

If we want to comparison these to the celebrated girls’ names from a period earlier, the bid is very similar:

>>> df[(df.Sex=='F') & (df.Year>1900) & (df.Year<=1925)][cols].groupby('Name')\ ... .sum().sort_values('Count', ascending=False)[:5] Count Name Mary 1056333 Helen 505522 Dorothy 475151 Margaret 402317 Ruth 364923

My woman and I had awesome aunts pinch immoderate of these names.

If you’re a database maven, you admit the Pandas groupby function arsenic a transcript of the SQL GROUP BY construct. Let’s redo this successful an interactive convention pinch SQLite. We commencement by importing the information from the CSV file:

sqlite> .mode csv sqlite> .import all-years.csv names sqlite> .mode columns

The mode is group to csv successful bid to import the data, past group backmost to columns to make the output look the measurement we want.

Now we get the apical 5 girls’ names from the 21st period and show them successful descending order:

sqlite> prime Name, sum(Count) from names ...> wherever Sex is "F" and Year > 2000 ...> group by Name bid by sum(Count) desc limit 5; Name sum(Count) -------- ---------- Emma 449576 Olivia 423613 Isabella 381577 Sophia 368619 Emily 353077

SQL is surely much English-like, but you tin spot the parallels betwixt it and Pandas. Now for the early 20th century:

sqlite> prime Name, sum(Count) from names ...> wherever Sex is "F" and Year > 1900 and Year <= 1925 ...> group by Name bid by sum(Count) desc limit 5; Name sum(Count) -------- ---------- Mary 1056333 Helen 505522 Dorothy 475151 Margaret 402317 Ruth 364923

Allison does akin things pinch her truncated Excel record utilizing pivot tables. I dislike the sanction “pivot table,” because I deliberation it’s an obscure word for the elemental operations of grouping and summarizing. For immoderate reason, my feelings connected this don’t matter, and pivot tables are present to stay. Pandas has moreover added a pivot_table function to placate group who’ve travel complete from Excel. Under the hood, pivot_table calls groupby.

Well, that was benignant of a agelong detour, and I forgive you if you’ve forgotten wherever we were. I had conscionable gone done a database of things that made maine leery of utilizing spreadsheets—why my first norm of utilizing spreadsheets is Don’t.

But I let for exceptions. My 2 main exceptions are:

When the problem is some mini capable to spot connected the surface pinch almost nary scrolling and the operations are elemental capable to beryllium easy understood without counting commas and parentheses. That was what I did for my sum of cubes quality tables. The formulas consisted chiefly of subtractions, pinch immoderate powerfulness and section operations present and there. Only the simultaneous equations solution successful the little correct progressive existent usability calls, and location were nary nested calls.
When I’m utilizing the spreadsheet arsenic a measurement position for editing information earlier passing it along. I did that successful the baseball squad progress station to edit down the ample and unwieldy season results tables from Baseball Reference. It was accelerated and easy to prime the array successful Safari, paste it into Numbers, and past delete the columns and rows I didn’t need. But I did it this measurement only because this was a one-off project. If I were fixed the occupation of making advancement charts for each 30 teams each time of the season, I’d ne'er do it by manus for illustration that. I’d usage the Pandas read_html function to propulsion the HTML array into a dataframe and a assortment of drop commands to pare it down.

I utilized to usage spreadsheets for information entry, too, but not anymore. It was erstwhile the only reliable measurement to put a array of numbers recovered successful a book into physics form. But OCR has gotten truthful overmuch better, I can’t retrieve the past clip I did this.

I cognize location are tons of group who emotion utilizing spreadsheets. They’ve spent a batch of clip learning the ins and outs and don’t want to move to different tool. That’s fine. This station was astir my rules, not anyone else’s. I’m not saying good, accurate, analyzable activity can’t beryllium done successful spreadsheets. It conscionable won’t beryllium done by me.

Next post Previous post