Monday, January 25, 2010

How to Make a Knitting Chart in Excel (Part 2 - Drawing Your Chart)

Welcome to the second article on how to make a knitting chart in Excel. Part 1 looked at setting up the spreadsheet so you would have a working grid. Now that you're set up and ready to go, this article will take you through the drawing process. Part 3 will give instructions on how to save and share your chart with the world.

Remember: to see a larger version of each screen shot click on the thumbnail.

I'm using Microsoft Excel 2003 on a PC, so the location of some of the menus and tools may vary depending on your version. The general technique should work just fine for you. These instructions will give you a square grid. Keep in mind that knit stitches are not perfect squares.)

The knitting chart you created in Part 1.

The Formatting Toolbar

The formatting toolbar may already be part of the toolbar you can see already. The buttons that will be most useful for your chart design are found on the default right hand side of the toolbar.
  • Familiarize yourself with the Formatting Toolbar. If this is not visible immediately, you can open it by going to Viwe --> Toolbars --> Formatting. (See the check by the formatting bar.)

    How to find the Formatting Toolbar
  • There are two parts of the toolbar that you need to draw your chart. "Fill Color" and "Borders".
  • If you plan on making a lace chart (not covered in this How To Article) you will need to find what symbols correspond to K2tog etc instead of Colors.

Fill Color will be your color pallet for your chart

Borders will help you provide a grid that is visible when you print your chart. Although you can currently see a grid, if you try looking at your work with "Print Preview" this grid is invisible.

Drawing your Chart
  1. Choosing your color Pallet. By reading the above formatting toolbar section, you should know how to find the Borders Toolbar. We will start my making a grid the that should be large enough to accommodate our project. Select (highlight) the region you would like to make a grid with.

  2. In the Borders Tool Menu, select "All Borders" (The symbol looks like a mini grid.)

  3. Now it's time to choose your color pallet using the "Fill Color" Button off of the toolbar. When drawing your chart, you could highlight each grid field you would like to change color, then go to fill color and select the color you want. This would quickly become tedious. I prefer to make a mini color pallet in the corner of my workspace, and use Copy and Paste to make my chart.

    See the mini color pallet in the top right section of the grid with 4 colors. Now you can copy and paste from these colors, rather than go into the Fill Color menu each time you want to make a color selection.

  4. Draw your chart! Copy the color you want in you "palette", select a cell, and paste to change the color. Continue until you have drawn the outline or design of your choice.

  5. Even if you have copied one lavender square, if you highlight 4 empty cells before you paste, all of them will turn lavender. (This can make filling in the a shape much easier.)

    Select multiple cells (left) and paste the color into all of them (right)

Save your work often!
Even with saving your file frequently (I will save each time I pause, but I am neurotic about saving), it is possible to erase your hard work accidentally. Or it can be hard to undo some modifications you made to your chart, and you would like to go back to a previous version. I like to save my in progress charts by copying them to a lower area of the same excel document.

If you copy the chart (in rows 11-16) and paste it below the work-in-progress grid (I pasted at rows 49-58), then you have a copy you can work on, but a previous version saved below. This way, if you become unhappy with your progress and would like to go back to an earlier version, you can!

Continue to Part 3 (How to share your chart)
Go Back to Part 1 (Setting up your chart)


  1. U r awesome! Infinite thanks for excellent tutorials!!!

  2. Just a note to help if you want to change colors in an Excel pattern. Instead of rehighlighting all of the cells of a color to change them, you can run a macro that will do it for you, You have to know the number assigned to the color you are starting with and the color you want to change to, then make an Excel macro with the following:

    For x = 1 To 16
    For y = 1 To 26

    ActiveSheet.Cells(x, y).Select

    If Selection.Interior.ColorIndex = 5

    Then Selection.Interior.ColorIndex = 3

    Next y

    Next x

    When I ran this macro this went through all of the cells of my 16x26 pattern (A1-Z16) and changed any blue cell to red. Just be careful if you are trying to invert the colors of a pattern, you'll have to choose an interim color for one set of cells so you don't wind up with a block of a single color, and you can't undo the work of a macro.

  3. I'm confused... you said to keep in mind that knit stitches are not perfect squares, but your chart is made up of squares that are 12 pixels by 12 pixels. Won't the proportions be off when you go to knit your design?

  4. In this tutorial, I do make the squares 12 pixel x 12 pixels, so yes, the proportions will be off. The dimensions of your knit stitches will vary from many conditions, so it is hard to know how much to vary them. Most of the time they are wider than they are tall, but this is something best determined by making a swatch.

    In many of my charts now, I have them 12 pixels wide x 9 pixels high. Sometimes it works to make things square, other times it doesn't.

    Good luck!

  5. I LOVE this tutorial, thank you so much for posting it :)

  6. I am making Christmas stockings and was just about to draw out a chart and hadn't even though of using Excel. Thanks for the great idea, the pattern turned out perfect.

    1. This is exactly how I design my stockings, too. I'm glad that you found the tutorial helpful!

  7. In excel you can adjust the row heights and column widths to reflect your gauge swatch. For garter stitch, the stitches are almost exactly twice as wide as they are tall, and for stockinette stitch the row height is about 70-75% as tall as the stitches are wide (but check your swatch). After adjusting, the patterned grid on the screen will look like the knit result.

    Also, conditional formatting allows quick adjustment of the color schemes. Conditional formatting is on the Style menu, and on my screen, that's on the main command menu. Choose a letter to correspond to each color you want to use. Make a conditional formatting rule that says, for example, that if the cell contains or is equal to r, make the cell red (you might have to type "r" into the wizard with the quotation marks around it). If you don't want the r's to show in the pattern, in the conditional formatting wizard you can format the font to be the same color as the fill color (you'll still be able to see the letter in the formula bar when you click on the cell). Then type the letter r in each cell you want to be red. If you want to change the color scheme, you can either (1)change the rule, or (2)highlight the pattern block, and use control-h to change all the r's to g's for example (assuming you set up g to result in green fill). Make sure you don't highlight the color chart you set up in a corner, though. Changing the formatting rule is faster and more efficient computing-wise than substituting out the letters. The computer can be slow if your pattern is big and you want to exchange the letters in each cell.

    brooklineknitter (ravelry)

    1. Thanks for these tips!

      I do frequently adjust the height and width of my cells to accomodate for my gauge. I never knew about the conidtional formatting, and that is a GREAT helpful tip!

  8. Have you found any practical limits to the size of the charts -- in other words, how many cells by how many can you have before the resulting Excel file with its pattern filled in gets to be humongous? I tried to generate charts with Excel way back when, for sampler type cross stitch patterns, and the Excel files got to be very large rather quickly as I filled in cells to make the pattern. I was working along the lines of 50 or 100 by 200 cells -- is that practical with the current version of Excel? I wasn't very familiar with Excel at the time (I'm probably a little better now) and it took me a long time to get it set up, and I probably had not found nearly the easiest way to fill in the blocks. So I'm curious if you have encountered any chart sizes that make the job slow, or result in huge files. Thanks! I enjoyed reading the blog, and am going to try Excel again.

    1. 50 x 200 shouldn't be a problem. I try to keep things that are able to print on a single piece of paper (which 200 cells isn't) but it is still possible to create. I have a chart that is 255 wide x 126 deep and there is space in the file for me to create more. This isn't one project, but me comparing my different stocking designs side by side - so it should work for you. Good luck!

  9. If you want to print your entire grid to one page, you can do that, regardless of the size. Select the entire grid, then set that as the print area, and select "fit to page" in print options. Check your print preview first. You may need to change between portrait and landscape to maintain read ability, though after a certain point that will be lost if you are adamant about keeping it to one page.