Spreadsheet Tricks For Grading – Excel and Google Sheets!

by | Feb 11, 2022 | knitdesign

In my past life I was a technical trainer. I wrote training manuals and taught computer classes to all of the employees of my company. I worked for a newspaper so sometimes I was teaching old timers how to use a mouse and sometimes I was teaching administrative assistants all the ins and outs of Excel. That was nearly 20 years ago, but I can still usually make Excel do anything I want it to do. Which comes in handy for grading knitwear!

Today I’m going to show you a few simple tricks that will help you get started and luckily these tricks will work in both Excel and it’s most popular free counterpart Google Sheets!. First off let’s name a cell!

cell names

You see right there? My 5.5 sts per inch number. If I click on that cell you can see circled in red the cell reference – it’s cell B1. But when I’m working with that number over and over again I want to give it an easy name I can remember and refer to (instead of typing $B$1 – the dollar signs tell it to always go to that specific cell). And when I’m dragging formulas across a row, that name will stay nice and static for me. So I’m gonna name that cell spi. To do that, all you do is click where it says B1, type in spi and hit enter. Now that name is ready for use in formulas.

string concatenation

Next up we are gonna do a simple concatenation formula. You know how in your pattern we have to list pattern sizes and stitch counts like this:

32 (42, 52, 62)” bust

You can set up a simple formula to fix that formatting. And you can use the plus sign symbol you get when you hover over the bottom righthand corner of a cell to drag it down the columns to use it for all your numbers. Or just copy and paste the formula if that’s easier for you. It starts with an equal sign and then everything you want to have written together you use an ampersand to connect. If you want specific text (and not say a cell reference) you have to put that in quotes. So my formula looks like

=B3&” (“&C3&”, “&D3”&”, “&E3&”)”

See how the text including the spaces after commas are all in quotes. Otherwise there are no spaces. And when you do this in excel it highlights things in colors for you so you can see what you are doing.

You end up with a nice finished list that looks like this:

multiple rounding

Next up we can do some mround-ing – this is the multiple round function. We want our bust sizes shown in inches and centimeters right? And the centimeter conversion is inches times 2.54…

But then you get a wonky number that is not an easy multiple.

The formula has two parts in the parentheses – the number or calculation you are rounding, a comma, and then the multiple you want it rounded to. I want a multiple of 0.5 here so that’s what I put after the comma. Excel is really nice about guiding you through formulas if you don’t remember what all the arguments are! So my formula looks like this:


Always remember to close your parentheses. That is what will let Excel know your formula is done. And then you have a perfectly rounded set of numbers after you drag that formula over. And if you bring the concatenation formula down you are ready to plug numbers right into your pattern!

putting it all together

Next we are going to use that cell reference. Remember when we named cell B1 as spi? Now we can easily use that to figure out a goal for the number of stitches we want for the front of this sweater. I’ve done a simple formula below where I take the bust inches, divide them by two to get the front bust inches, and then multiple that by my spi which is 5.5 sts per inch.


But of course life is never perfect and I need my front to be a multiple of 6 stitches for my pattern. So now I need to see how those goal numbers compare to some actual numbers I can use. To do that I use the mround function once again. It’s not just for fractions of numbers. I do the formula using 6 as my rounding factor to see how the goal numbers I have will work with that stitch count.


And you can see the numbers work pretty well. I will have to do some tweaking and allow for selvedge and all that, but these few simple tricks can make your Excel grading so much easier


  1. Mary Beth

    I never knew about naming a cell. Thanks!

  2. Gila

    very clever hints and formulas, thank you!


Submit a Comment

Your email address will not be published. Required fields are marked *

Recent Posts



Pin It on Pinterest

Skip to content