How I Teach Myself Fancy Spreadsheet Functions for Knit and Crochet Design
If you’re a knit/crochet designer or a tech editor, learning how to teach yourself advanced spreadsheet functions can save you so much time and reduce the frustrating errors that can happen when you try to type in or calculate data manually. Honestly if you have any job where you work with data in a spreadsheet, learning how to identify the functions that will help you work with your specific data more efficiently and effectively is worth it – but since I am primarily a tech editor I will focus this post on using spreadsheets when working with knitting patterns.
Many of us know how to use spreadsheets as, essentially, a fancy calculator – setting up basic calculation formulas to check the number of stitches in a row or the number of repeats in a section, often across multiple sizes. But spreadsheets can do a lot more than just calculate. Two of my favorite non-math functions for knitting pattern editing are:
Split text to columns. If you have a graded pattern where the numbers for each sizes are set up like 1(2,3,4,5,)(6,7,8,9,10), you can copy and paste the entire string into a cell and use split text to columns to separate each number into its own cell, allowing you to check the different sizes without spending ages on data entry (and risking typos).
“Locked” cell references. As you may know, when you copy and paste a standard formula in Excel or Sheets it will automatically adjust the cell references (so if you have B2+B8 in a formula and copy and paste it in column C it automatically changes to C2+C8). So if your stitch gauge is in cell K1, how do you make sure you get B12/K1 and then C12/K1, instead of K1 changing to J1? You “lock” the references by putting the $ symbol in front of both parts of the cell reference (B12/$K$1). Now when you copy and paste the top reference will change, but the bottom number will stay the same.
If you’d like to know more about these and other useful functions for knit/crochet designers, my tech editing colleague Jenna Barron recently wrote a post on her favorite Excel functions at her own blog.
Sometimes when I show a new spreadsheet function like the above to a friend or colleague they’ll say something like “you’re so good at spreadsheets” or “I really need to make time to take a class.” The truth is, I haven’t taken any math courses since high school, and I’ve never taken a business, computer science, or “How to Use Excel” course that would have walked me through some basics. I’ve learned what I needed as I needed it for my various jobs, and taught myself every function I know through a simple 3 step process:
Figure out what you want your spreadsheet to do. For the split text to columns, I already knew a function called “concatenate” existed that allows you to copy text or numbers spread over several columns into one cell. What I wanted was to do the reverse of that – take numbers listed together in one cell and spread them out. For the locked cells, I knew there had to be an easier way to copy and paste a function when you needed one of the cell references to stay constant, I just wasn’t sure what it was called or how to set it up.
Internet search to find the name of the function. This can sometimes take a little trial and error. For split text to columns I started by typing in “deconcatenate” (which seemed like the logical reverse of concatenate). This directed me to an Excel forum where someone asked if that term was a real function and other people told them they were looking for the “split” function. For the locked cells, my search phrase was the somewhat unwieldy “keep one cell reference in a formula the same while copy and pasting Excel.” I advise using Excel in your initial search phrase even if you actually use a different spreadsheet program -- there are more Excel tutorials and forums out there than anything else, so you have a better chance of finding what you need. Once you know the Excel name, you can search “[function name] for Google Sheets (or whatever program you are using)” to find out if it’s the same name or slightly different.
Find a tutorial. Sometimes this step is taken care of in step 2, in that finding the right name is in an article that includes a good tutorial. Other times I find a forum post or an article that mentions the function without fully explaining how to set it up; I then search “[function name] tutorial” to get a step by step set of instructions. I personally prefer written instructions, but there are tons of video tutorials out there for even the most basic functions if that’s how you prefer to learn.
I actually have a “function test” spreadsheet where I can play with new functions if I’m worried I might mess up an edit spreadsheet (then I save my successful tests so I can reference them as needed), or I just go ahead and start using it (locked cells didn’t really need testing, for example). This method is particularly successful because you learn the function when you’re truly interested in using it — you aren’t taking a class and then waiting days/weeks/months to have a chance to practice it.
It’s easy to get overwhelmed by spreadsheet programs and think you have to take an entire course to improve your skills. But really all you need to know is how to identify what functions are most useful to *you* and how to find tutorials for those functions. You can pace yourself and learn one or two functions at a time, get comfortable with them, and then find there’s something else you’d like to automate.
What spreadsheet functions do you like to use for pattern design/editing? What functions do you *want* to learn?