Colorwork with Google Sheets

I’m very much a “computer person.” As a (former) database developer, I reach for databases and spreadsheets when I need to organize and manipulate data — and sometimes to do oddball problem solving. A couple of people have asked me about the color changes I made to the original Riddari yoke, so I’ve decided to write a post about how I used Google Sheets to help me change the chart and pick colors.


Setting Up the Sheet

Resize columns

After opening a new Sheet, I begin by resizing the columns to match the height of each row. To do this, select all of the columns. On a Mac, you would do this by clicking on the first column header, which contains a letter, and holding down the Shift key while clicking on the last column header. Then right click on any selected column header to reveal a menu, which includes the option “Resize columns.” If you haven’t changed the row size, you can set the column width to 21 pixels.

Next, figure out how many columns are in the chart. In the case of the Riddari chart, the pattern chart is 16 columns — two repeats — wide. However, since I was using Google Sheets to help me visualize color combinations, I decided to make my spreadsheet 32 columns wide. To insert additional columns via the Google Sheets menu, go to Insert > Column right. (Or left. It doesn’t matter at this point.)

Format > Conditional formatting...
Once all of your columns are present, select all of them (on a Mac, ⌘A) and click Format > Conditional formatting… from the Sheets menu.

Setting Up the Chart

Conditional Format RulesClicking on Format > Conditional formatting… will open the Conditional format rules menu on the right side of your window. I use conditional formatting to setup the chart in a way that I can easily manipulate the colors later. I do this by creating rules based on exact values entered in the spreadsheet cells. If the cell contains the value “1,” then be this color. If the cell contains the value “2,” then be that color. In order to achieve a solid colored box, I set the text color and fill color to be the same. This hides the numeric value. Empty cells can be left empty, although I tend to put a hyphen in the empty cells to distinguish them from white stitches.

This is where I begin to play with the existing chart, and this is also where using a computer can be much faster and efficient than knitting a swatch. I knew that I was going to change the way that the Riddari colors are knit — not just the colors themselves, but also how the colors were deployed across all of the design elements. I needed something to help me quickly visualize those changes.

Visualizing Design Elements

I break down the chart into design elements by assigning a color and conditional formatting rule to each individual section that I might want to control the color of. So, even though the original chart establishes one color for the downward-pointing arrow elements shown above, I created three rules for each part in order to have flexibility assigning colors to them later. In the middle iteration of the chart pictured above, I assigned the same color to two separate elements, or conditional formatting rules. In the iteration on the right, I decided to set the rules so that they would be different colors.

Colorwork with Google Sheets

Above, I’ve shown one of my Riddari charts with the conditional formatting menu, as well as what that spreadsheet would look like without the conditional formatting. This gives you a sense of what it looks like “under the hood.”

Changing the Elements

Duplicate

Once the chart is broken down into design elements — each with its own rule for conditional formatting — I begin making real changes to the sheet. I generally use my first sheet to duplicate the original pattern as closely as possible. Once I’ve established a base sheet, I duplicate it and edit the text and fill colors associated with each rule.

It’s impossible to have the on-screen colors match yarn colors, but I try to be mindful of how the yarn colors contrast with each other. This is all guesswork, and, if you’re a smart knitter (I am not), you’ll swatch your final spreadsheet chart to confirm that the colors actually look right next to each other.

Three Versions of Riddari

By simply changing the conditional formatting rules, I was able to quickly visualized six color arrangements.

The Final Chart

Riddari (Chart #6)

In addition to color changes, I also eliminated rows from my final chart. I have a smallish frame, and I knew that the yoke as originally charted would be too deep for my body. So once I settled on colors, I began deleting rows. I did it based on instinct, and there were several times that I deleted a row only to undo it a second later, because that row’s absence from the chart didn’t look right.

As I was reducing the depth of the yoke, I also decided to bring Chart 1 (body and sleeves) into the yoke chart, so that the hem and cuff motif is also around the collar.

One-Off Formatting

Finally, I added the symbols for the decreases onto the chart in approximately the same places they’re found on the original chart. Note that this will cause the conditional formatting in those cells to disappear (since “/” is not an exact match to your formatting rules). For those few “broken” cells, I simply use one-off formatting: bold, readable text with a fill color that matches the conditional formatting rule for that cell.


This method for picking yarn colors and tweaking charts is probably not for everyone. For me, it’s a very natural and efficient way of visualizing the design elements without actually committing anything to fabric. I like to think of this process as “pre-swatching.”

There are definitely some upfront costs, because you need to setup the conditional formatting and transfer the chart to the spreadsheet, but it goes pretty quickly for me. And remember: the usual spreadsheet shortcuts for duplicating a row (or copy-pasting) also work in Google Sheets.

If you have any questions, feel free to leave a comment. And if you also use a computer to pre-swatch, leave a comment and tell me about it. I love to hear about spreadsheet-knitting geekery!