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.

37 comments
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.

waltjohnson35
waltjohnson35

Look back about six responses to "Size ... using pixels. This also works for Excel 2003.

waltjohnson35
waltjohnson35

Select the entire worksheet or space you require. When you size a column or row by dragging the cell border, it also shows the size in PIXELS. Set both the row and column to the same number of pixels. The printout may not be EXACTLY square but for most non technical work it is good enough.

mkeziah
mkeziah

yes...For instance...use the scale you develop say 1/4" = 1ft of what ever...to draw out the furniture ...thwen move the furniture around within the confines of the drawn room. Works great !! I love this idea...I do a lot of room designs...I used to draw items to scale, cut them ouot and lay them on a piece of graph paper...no more..I will use this method!!

unellen
unellen

Nice to print graph paper, but using excel or some such program like it makes it easier to print out AFTER the changes have all been made. I just checked on office 2007 and it looks a bit different, and it gives you pixels as well as sizes, so I am thinking it may be easier once I figure it out.

doug.cronshaw@baesystems
doug.cronshaw@baesystems

... and you may be able to adjust the resolution by increasing the Print Quality density on your Excel page set-up. (There again, you may be frustrated in such an attempt by a poor quality printer.)

name holdout
name holdout

I used to do this for my kids when they were in grade school and needed graph paper. My oldest daughter is 30 now.

donna122
donna122

I've been using excel as a space planning tool for many years. I created a graph paper grid by making the cells square. It's not to scale, but close enough for planning. And it's shareable (ok I know that's not a word LOL). Just not sure why you'd need your method. Maybe I'm missing something...

evan.simpson
evan.simpson

The square deforms to a rectangle when printing.

drnspot
drnspot

I thought I was the only one who used Excel this way, in lieu of buying a CAD-type program. I've planned gardens, new office spaces, and even a home addition (zoning approved!) using this method.

B3_Nick
B3_Nick

Now - if only somebody could come up with a macro to do this to generate log paper for Electronic applications!

dan
dan

I use the pixel sizing in Excel 2003 to get square cells. Also, if you have Excel, you likely have Power Point. You can lay down a grid in Power Point and also snap shapes to the grid. When my office was moving to a new location, we drew up our new offices in Power Point and moved the virtual furniture around to see what would fit.

john.schauer
john.schauer

I too am using Office 2007, and I am just not getting it. Of course, in O2k7, the ribbons and menus (to me) are significantly different than the older versions of Office. does anyone have any hints for this on O2K7? Thx.

donniedarko
donniedarko

this is absolutely nothing to do with printer type or quality of printer. I have excel 97. I am able to create boxes of exactly the correct dimensions up to 0.99 cm . When I try and create a box 1 cm x 1cm, the app changes the dimensions to 1.01cm x 1.01cm. The app adds an extra 0.01 to every set of dimensions that I create above 1cm square. I have tried everything (that I can think of) to solve this unusual issue.

jcvalley2
jcvalley2

Go to print preview, page setup. Hit the Sheet tab. Select Print Grid lines check box. You should get printed graph paper now.

mjbarden_z
mjbarden_z

This can easily be done with an excel chart. Simply enter some data values in a worksheet, generate a chart sheet from the data, select the data series on the chart and set markers to "None" to hide it, delete the legend. Display major & minor gridlines and set the axes to logarithmic scale. You can adjust the number of log cycles in the axis options by fixing the min-max values. If you just want blank paper, turn of the display of axis labels.

elongp
elongp

You used to add steps for Office 2007 in your tips. Please start including them again. I can figure it out on the tabs, etc, but it's nice not to have to dig. Keep up the good work!

hjertet
hjertet

Brilliant idea for the use of Excel!!! I had no problem following the procedure. Have you tried to choose Insert on The Ribbon, then Figures and Rectangle and so on... It worked for me

Editor's Picks