Recently I’ve been spending a LOT of time in spreadsheet-template-land, population 1. Sure, it can be repetitive and dull at times, but I also feel creative momentum from tackling the template one cell at a time. These templates use a combination of Conga merge fields (populating a template with data from Salesforce) and SUMIFS functions (populating a template with data from another tab). Truth be told, it’s a LOT of trial and error in projects like this, and sometimes I have a tendency to procrastinate – but I’m finally tackling it! If this sounds like I’m speaking alien, don’t fear! I’m just setting the stage, but there’s more accessible stuff a-comin’!
Well, in addition to trial and error with formulas, I also found myself encountering the same formatting problems again and again. If I drag a formula down (to repeat it), then I messed up my cell shading and formatting. Blargh! Same result when I copy formulas from one cell to another. I got the hang of “paste special” *ding ding ding, tip alert!* which allows me to paste JUST the column width or JUST the values or JUST the formula … or JUST the formatting, but I still found myself needing to paste over mistakes again and again.
This begs the question, why don’t I just wait and do all the formatting at the end? Good question, I said to myself. I probably should have done it that way the whole time. But here I am… I had already downloaded a formatted template from my boss, with all of the shading and borders pre-determined, and now I’m just trying to populate the darn thing with data. Waiting to the end would be too frustrating, as well as too visually incongruous.
Let’s explore the “format painter” tool in Excel, because it’s pretty powerful and it can become a gal’s (or any gender!) best friend if you’re going for a certain style, but you’re still facing some significant copy-and-pasting. You can collow along in the .GIF below, or you can simply use the Copy command (CTRL + C or Apple + C) and then right click to see the “paint special” menu and select “formatting” to move the formatting from one array of cells to another.
Wait a second — this is a tangent, but since I mentioned the “drag down” feature above, I happened to find this .GIF for copying/autofilling data and I had to include it because it just blew my damn mind.
Ok ok, back to seriousness.
When I started this piece, the USA and Iran were teetering terrifyingly close to war. (I took a break from writing to work on some other stuff, but now I’m back and I’ve been turning over these ideas in my head the whole time.) I want to write more about how to format Cell Borders specifically, because I’ve found that to be really useful for budget-to-actual reporting (or plain old budgeting) and lots of us have to tackle those kind of pragmatic spreadsheet projects. But I also want to land with some reflections about borders overall – when they are a useful differentiater and when they cause chaos, competition, and calamity.
Microsoft has some pretty good resources on how to apply borders to your spreadsheet (see documentation here). The long and the short of it is… they have a lot of prebuilt options. So does Google Sheets.
After you select the “border style,” you can additionally change colors and styles of the lines you chose. How many of these have you tried? I find myself using the “Thick Box Border” and the “Bottom Double Border” most frequently. I like how the first one create an offset – some visual emphasis and appeal. I like the second one for the second-to-last row of a table… right above the subtotal or total at the bottom. It provides a visual signal for the sum or difference.
Many of you know that I’m a passionate “format as table” evangelist – I love this tool in Excel (or Google Sheets) because it provides flair and filters – and really, what else could you ask for in a spreadsheet? This very organized femme is satisfied 🙂
But occasionally, you need customization that Tables can’t provide (tables are really best for preserving consistency across your dataset) and borders are best for drawing attention to specific insights/conclusions. I use tables for “raw data” or “data dumps” and borders for data that I’ve manipulated somehow to show conclusions or a finished product.
I guess what I’m trying to say here is… Tables and Borders have different uses to get to a similar end goal – some type of visual design that helps turn data into meaning. Then again, after you go to all that hard work of formatting your borders, it’s so easy to accidentally copy over them with the wrong borders or no borders at all! So, in this blog post, I wanted most to introduce border design options and also show how to easily transfer them from one place to another to save yourself (let’s be honest…) also MYSELF the frustration of re-doing work.
Let’s look at some examples. In the first image, I wanted to show the tremendous variety of border types. In the second image, I want to show how you can make use of borders and tables to style your spreadsheet and make it “easier on the eyes.” Note that in the second image, the cells that have alternating stripes use “Tables” and the cells that have plain borders use “Borders.”
After all of this writing, I finally DID find the holy grail of solutions to my original problem. You can read it for yourself here. Nonetheless, going on this shpatzir in the land of spreadsheet styling has been really fun and productive. I hope you feel the same way!
The more I explore these ideas, the more I feel it is important to reflect on WHEN and WHY to use borders. And because I’m me, of course this has a political dimension. I think borders and differentiation are only natural and can be really helpful. All metrics are NOT equally important. All ethnic groups are NOT homogeneous. Let’s draw some lines, create space, honor broad categories that are genuinely different and deserve separate consideration, agency and autonomy. We need to make distinctions: it’s human nature, it’s helpful to distinguish and prioritize information, we need to know where one insight stops and another one starts. But if they are overused, they become… cluttered, overwhelming, disparate, competitive for attention. The distinctions that we were trying to emphasize become muddy, meaningless, almost arbitrary.
What’s a spreadsheet without borders? (LOL… I guess it’s a .CSV file…) Ok that’s a bad joke. What’s a map without countries? What’s a country without a border? Can we make meaning without relying on these tropes? I think of the travelling I did in Mexico City a few years ago, and the reading I’ve done about borders as fluid, permeable, intercultural zones as opposed to a “line in the sand.” Drawing borders comes with great power, and so does manipulating data and drawing conclusions, especially when we are dealing with information about REAL PEOPLE. I am often trusted with the task of taking raw survey data or fundraising data and coming up with summaries and analysis. I don’t want to equate this with geopolitical conflict, but I do want to draw attention to power overall. I think the cultural and political norms that we absorb absolutely do transfer into how we present data and this seems as good a time as any to question them.
I am reminded of this beautiful poem by Naomi Shihab Nye and the pleading question, “What flag can we wave?” Usually, my answer is “None.” However, with war on the horizon (and endless wars operating all the time), I do have an urge to cling to my sense of place and deep roots in the Delaware River watershed. Making meaning of spreadsheets is part of my personal contribution to our movements to stop the war machine.
Whether you’re adding borders or removing them, I wish you peace and purpose in your spreadsheet journeys.
"A true Arab knows how to catch a fly in his hands," my father would say. And he'd prove it, cupping the buzzer instantly while the host with the swatter stared. In the spring our palms peeled like snakes. True Arabs believed watermelon could heal fifty ways. I changed these to fit the occasion. Years before, a girl knocked, wanted to see the Arab. I said we didn't have one. After that, my father told me who he was, "Shihab"—"shooting star"— a good name, borrowed from the sky. Once I said, "When we die, we give it back?" He said that's what a true Arab would say. Today the headlines clot in my blood. A little Palestinian dangles a truck on the front page. Homeless fig, this tragedy with a terrible root is too big for us. What flag can we wave? I wave the flag of stone and seed, table mat stitched in blue. I call my father, we talk around the news. It is too much for him, neither of his two languages can reach it. I drive into the country to find sheep, cows, to plead with the air: Who calls anyone civilized? Where can the crying heart graze? What does a true Arab do now?