Software

Turn an Excel sheet into graph paper

Tired of erasing holes in your graph paper or having to start over from scratch? Here's an easy way to create your own custom graph paper in Excel.

This past week, I started mapping out new flowerbeds on graph paper. Now, graph paper's cheap, but making changes takes time and sometimes you just have to start over. Unfortunately, I don't have specialized software aimed at garden design, so I thought about what I do have -- I have Excel! With just a little work, I turned an Excel sheet into a modifiable piece of graph paper.

The trick is to square up the cells. The gauge is less important. Like graph paper, a cell can equal anything you want. The hard part is getting the width and height settings to produce a square because there's no easy way to match a cell's height and width settings. You can't just set the row height and column width to the same value because:

  • Excel measures row height in points.
  • Excel considers the current font when calculating column width; a column width of 5 means that the column will display 5 digits, using Normal style.

You could spend a lot of time tweaking the height and width and you could even try holding a ruler up to your screen, but there's an easier way: Format an AutoShape as a square and use it as a guide. First, you need to insert and format an AutoShape as follows:

  1. From the Drawing toolbar, choose Basic Shapes from the AutoShapes drop-down list.

  2. Double-click the rectangle (the first shape in the first row) and Excel will insert a rectangle into the current sheet.
  3. Right-click the rectangle and choose Format AutoShape.
  4. Click the Size tab.
  5. In the Size And Rotate section, enter .25" for both the Height and Width to create a square. If you want a larger square, enter a larger value for both measurements.

  6. Click the Properties tab, select the Don't Move Or Size With Cells option, and click OK.
  7. Back in the sheet, move the rectangle (it's a rectangle object, shaped as a square), to the top-left corner, just over cell A1.

Now, use any method you like to resize both the height and width. Perhaps the easiest way is to drag the header and row cells to the appropriate position as follows:

  1. Select the entire worksheet (or the area you want to resemble graph paper). To select the entire sheet, click the sheet selector -- that's the cell that intersects the row and column headers (in the top-left corner).
  2. Hover the mouse over the right border of column A's header cell.
  3. When the cursor turns into a double-arrow, drag the border until it's flush with the rectangle's right border. Excel will resize the width of all the selected columns, not just column A.
  4. To resize the height, adjust the bottom border of row 1 until it's flush with the bottom of the rectangle. Again, Excel will adjust all the selected rows.

At this point, you have a sheet full of .25-inch cells. Move the AutoShape rectangle at cell A1 or delete it -- you're done with it. If the cells are still a bit too large, set the Zoom property to 50%. Then, start adding the appropriate components by formatting cells and adding AutoShapes. Be sure to add a legend to identify all those components.

As you change your mind, it's easy to reformat cells and delete objects. No more erasing, no more starting over!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

39 comments
SchultzyBeckett
SchultzyBeckett

A  terrific modification  in an  excel worksheet  this.  it is going  to enhance the workability  in the  excel sheet.there is a  real dearth of time with  the  IT professionals .Modifying and  and  enhancing the  excel  working techniques would go a  long  way  to  help them.

Schultzy @ http://www.acalculator.com/

angalaz
angalaz

In Excel 2007, you can change your view to Page Layout and then resize your cells in inches... .25" will equate to a one-quarter inch square.

davekingsb
davekingsb

That's a good idea! Why buy graph paper when you can print your own.?

dhawktx
dhawktx

You can also make knitting graphs this way (knit stitches are wider than tall). However, for your layout needs there's DIA, a free, Open Source alternative based on Visio that is even available as a Portable App (my fave). http://portableapps.com/apps/office/dia_portable

Cornils
Cornils

I've done this before, but it's kind of a pain. Try an online search on 'Graph Paper' to see a lot of online sites for doing the same thing - great for Math teachers, etc.

wwgorman
wwgorman

This is a fine solution for rectangular coordinate plots but many involve two to five cycle logarithmic plots. There are many inexpensive graph paper printing programs that are much more flexible. Even those plotting programs don't cover such things as triangular plots. There are also many other special plots not covered by any program. K & E where are you when we need you?

Mycah Mason
Mycah Mason

This seems way too complicated. Is this because you are using an earlier version than 2007? In 2007 it is super easy: Ctrl+a to highlight all columns and rows > drag a column to say 20 pixels (it shows pixels for me), then drag a row to 20 pixels. Since all columns and rows are highlighted the resizing sets them all to the same value. Then you can use the "Borders" tool "All borders" and "Line color" to add the lines to the paper (black was too dark so I chose grey).

EasonSys
EasonSys

Thanks! Garden here I come!!

medler1
medler1

ASAP Utilities latest version has an option to create square cells of a size designated by the user. You will find the facility hidden away under menu item 19 (Options) > New Tools in Development (english only). If you are an avid reader of this forum but not already using ASAP then you could be a banana. It is FREE and it is WONDERFUL! (PS. I have no connection with ASAP other than as a user)

deICERAY
deICERAY

I didn't get the either/or rationale, but I do have need of a program for re-arranging furniture, so this should help - shouldn't that auto-shape thingy also be able to produce your pieces to be placed on the sheet? Thanks for a nifty tool! p.s. Why do Tech-republic blog replies only work half the time? It errors out and tells me I didn't do it right, but I did...

st_hayden
st_hayden

Creating the graph paper helped one of my users with the floor plans of our office. Thank You

janly
janly

A shortcut to do this is to select the whole spreadsheet as stated in step 1, then resize the columns to 1.89 pts and the rows to 17.25 pts.

NickNielsen
NickNielsen

I just set my row height to .25 and my column width to .25. Now I have graph paper. But I cheated and used OOCalc.

simon.freeman
simon.freeman

See: www.mrexcel.com/tip071.shtml note the width that you enter is a single "normal" character and must be larger that 0.5. You can't use this to get specific square sizes directly in inches or cms.

Pevrill
Pevrill

You can then use conditional formatting to shade - e.g. "g" for grass with a pattern of green, "f" for flowers with an appropriate pattern, etc.

donniedarko
donniedarko

unfortunately when i tried to turn it into 1 cm by 1cm graph paper, the programme keeps changing the size to 1.01cm x 1.01cm. Any way to lock the size of the square?

GreenPirogue
GreenPirogue

It is always nice to see how someone takes Excel. Sure, this is a fairly obscure use of Excel, but it is interesting nonetheless.

Editor's Picks