For the Love of the Ampersand

Designers!

Do you use Excel to grade your patterns? Do you love the ease with which you can drag formulas across columns and convert inches to centimeters in a snap?

But do you also struggle with the dark side - do you hate having to type out the results from each cell into one tidy line that most publications require for print? You know the ones - they vary, but they're usually of the same theme. Parentheses and commas, brackets and colons, spaces, no spaces, two spaces. Your fingers flub on the keys and you command knitters to work 1x1 rib across thousands of stitches, or you end up missing out instructions for some sizes entirely and leave a tech editor puzzling to fill in the blanks. Most of all, it just eats into your time.

But don't worry - I have you covered! And it's easy. It just takes some getting used to.

I know not everyone wants to marry Excel and have its babies like I do, so I've broken it into teeny tiny steps. If you're impatient and want to get the formula already, just skip down to the bottom.

So...

First: Open your spreadsheet. Mine looks like this.

Click to look closer.

Of course, yours may look quite different. Don't worry; the basics are the same. As long as you know the names of the cells you want to put into a list of instructions for grading, that's all you need.

Second: Choose the numbers you want to place in a list.
For the first list, I'm using the row of numbers highlighted here:

These are cells C6, D6, E6, F6, G6, H6, and, finally, I6.

I've used my own standard method of presenting instructions, with parentheses and each number in the parentheses separated by a comma and a space. You can change this to suit your style, which I'll show you how to do in a minute.

Third: Find the column where you would like to put your list. I usually choose the first empty column in a row of numbers I want to list. Click on it.

Fourth: This is where things to start to look like a string of incomprehensible symbols and numbers, but stick with me. 

Type your equals sign. This lets Excel know that it needs to do some work.

=

Next (and this is the important bit), add a parenthesis. This is not part of your list, but just lets Excel know that you're grouping the following numbers. So that cell will now look like this.

=(

Appropriately.

Anyway, this is where you'll actually start to use your precious calculations from your spreadsheet. So select the cell that holds the first number you want to list:

and follow it with an ampersand (&). Mine is shift-7 on my keyboard. It took me a while to find it. The "&" will not show up on the final list of numbers; this is just what connects a string of numbers and characters. If you leave it out, Excel's brain will explode.

Fifth: You've listed your first number! Congratulations. Now, you'll want to add whatever character separates your numbers. In my case, the first number is separated from the second number by a space and parenthesis. To add a character that isn't from a cell, you will have to place it in quotes. After the ampersand, I will add " (". So in my cell, I will now have:

=(C6&" ("

What do we need after this? Why, another ampersand, of course!

=(C6&" ("&

Tip: Depending on the finickiness of your Excel, if a cell number is black instead of blue, purple, yellow, etc., it might mean it hasn't been inputted properly. You've probably forgotten an ampersand.

Now you just continue adding your numbers, not forgetting the ampersands, nor the quotes around those spaces and commas. By the time I get to I6, my formula bar will look like this:

=(C6&" ("&D6&", "&E6&", "&F6&", "&G6&", "&H6&", "&I6&

It's a bit confusing to look at at first, but once you get the hang of it, you start to see the pattern and copying and pasting &", "& becomes your friend.

Now we just need to close her up. But don't forget your ending parenthesis. The first one, I mean.

 =(C6&" ("&D6&", "&E6&", "&F6&", "&G6&", "&H6&", "&I6&")"

It's in quotes, so it will appear in the resulting text. That's the closing parenthesis that you'll want at the end of your list of numbers.

Now you just need to add one more parenthesis to close the string of commands:

  =(C6&" ("&D6&", "&E6&", "&F6&", "&G6&", "&H6&", "&I6&")")

Press enter...

 And voila! A tidy list of your graded numbers, just like you wanted. Messed up? Don't worry, just go back and fiddle with the string in the formula bar.

So that's it. Even better, once you've typed it up once, you can use it through your entire spreadsheet. Just click on the cell that contains your list and copy it (don't click and drag to highlight the contents of the cell, as that will copy the resulting numbers rather than the  formula) and paste it into the next row down or any of the rows below (if you move it over to a different column, you'll need to adjust your cell numbers in the list to keep it accurate).

Need to use colons instead of commas? No problem:

   =(C6&" ("&D6&": "&E6&": "&F6&": "&G6&": "&H6&": "&I6&")")

What about brackets?

 =(C6&" ["&D6&", "&E6&", "&F6&", "&G6&", "&H6&", "&I6&"]")

"But they don't want me to use spaces!"

   =(C6&" ("&D6&","&E6&","&F6&","&G6&","&H6&","&I6&")")

That should cover it! 

Questions? Let me know in the comments. Otherwise, happy Excel-ing!

P.S. In the comments, Teresa pointed out you could dedicate a cell to whatever punctuation you need (say, C3 for the comma, and refer to it as a static cell: $C$3), and use that so you don't have to change the lists throughout the pattern in case you need to change the formatting. It wouldn't need to go into quotes in this case.

This could be especially helpful if you originally published with a magazine but want to self-publish later. Good tip - thanks Teresa!