Excel can be your best friend when you’re designing a pattern. Knowing the little tips and tricks that it offers can really make your life a whole lot easier.
In addition to the beauty of autofilling calculations across a range of sizes, there are several lesser known functions that you can be incorporating into your design work too.
I shared some of these on Instagram a few weeks ago, and I wanted to give these tips a more permanent home. Plus since you can’t really copy + paste from Instagram I hope this gives you more of an opportunity to make use of the functions I’m sharing.
You can now also get a free Google Sheet with all these functions in it (and a few extra) by signing up for my newsletter at the bottom of this page.
So, without further delay, here are some of my favorite functions that I’m constantly making use of when tech editing knitting patterns.
Concatenate is awesome for transferring numbers out of your spreadsheet and into your pattern document. If you have your bust sizes listed in cells A1-J1, then using this formula will put them all together. =CONCATENATE(A1,” (“, B1,”, “,C1,”, “,D1,”, “,E1,”, “,F1,”) [“, G1,”, “,H1,”, “,I1,”, “,J1,”]” ) This formula then output the contents of these cells as 30 (32, 34, 36, 38, 40) [44, 50, 56, 60]. Then you can just copy and paste right into your pattern.
This is great because it can also cut down on the potential for you accidentally writing the wrong number, thus cutting down on pattern errors and your overall tech editing bill.
You can also go the other way with the SPLIT function. =SPLIT(A3, “( , ) [ ]”). A3 is where my previous CONCATENATE is. The symbols in the quotes are what you want the program to ignore. This is useful for taking things out of your pattern and putting them into your spreadsheet. I’d say this one is probably more useful for tech editors and the concatenate is more useful for designers.
You can rename cells. I always rename the cells that store gauge information. So rather than having to refer to B3 and C3 for stitch and row gauge, I rename them “sts” and “rows”. You rename a cell by going to the toolbar just above and where it says the current cell name (like A1), you can type whatever you want in there.
Rounding to stitch repeat multiples
MROUND is a great function to use when you need to fit your stitch counts around a certain multiple. For example, if I have a stitch pattern that is a multiple of 25 and I want to determine what my cast on number should be, I could use MROUND to make this easier.
In this example, I’m multiplying my 1″ stitch gauge by my bust circumference to determine the number of stitches. This is the first parameter of the function. Then the second parameter, I’ve put 25 because I want it to round the number of stitches to the nearest 25.
Without MROUND, multiplying my bust circumference of 30 with my stitch gauge of 4.75 would result in 142.5, and then I would have to manually round up to 150. But here the function takes care of that step for us.
This can also be useful in rounding measurements. You can use it to round your inches/cm to the nearest .5, for example.
The $ symbol
The dollar sign is your friend. If you want to autofill a formula, but want to refer to a single cell rather than it taking the corresponding cell from a different row or column, use $. So putting $A$1 into your formula will make it always refer to that cell, whereas if you just put A1 and autofill across multiple columns then it would autofill as B1, C1, D1, etc, which you might not necessarily want. I used to use this all the time when referencing gauge before I learned you could rename cells, but there are other times I use it too.
Pasting without formatting
One final thing is pasting without formatting. Using ctrl + v pastes something you’ve copied without formatting. This is useful if you want to copy numbers only. If you just do a normal copy + paste, it’ll copy the formula, whereas this just keeps the final value. Useful if you, like me, end up with messy spreadsheets when all is said and done and you want to copy important numbers and group them all together at the end of the spreadsheet to make it easier to view.