4 Ways Spreadsheets are Great Tools for Designers

5

Spreadsheets may seem like the exclusive domain of accountants and other number crunchers, but they can make life easier for designers too. I’m using Google Drive spreadsheets in the examples below. You can create and store them for free with a Google account.

Spreadsheets 101

Before we dive in, let’s review a few spreadsheet basics for absolute beginngers. When you open a spreadsheet, you see a grid of boxes that are called “cells.” Each cell contains a bit of information. The cells are arranged horizontally in “rows” which are numbered, and vertically in “columns,” which are indicated by letters. If you’ve ever worked on an HTML table or made a chart or graph you get the idea. Most spreadsheets can have multiple pages of these grids, called “sheets” The first row of a spreadsheet often has labels, and is then called the “header row.” A group of cells is called a “range.”

Each cell has an address made up of the column letter and the row number ie: G12. The cells can contain words or numbers in various styles such as time or date, currency, and percentage. Cells can also contain formulas. The formulas use numbers or the data in other cells to add, subtract, average and more. In a formula, a cell is referred to by its address, such as G12. These cell formulas are visible in the formula bar, at the top of the screen under the menu bar. The answer to the formula is visible in the cell.

1. Tracking Little Details in Big Jobs

Looking at information in different ways by sorting data is one handy thing spreadsheets can do. You can use this to track progress on jobs and reorganize the information for different purposes. Recently my studio had to create images for a fabric website. We had to add the product ID and one of 15 different ruler graphics to a 100 or so jpg images of fabric patterns. To accomplish this, we needed to know several things: which fabric belonged with which collection; which ruler graphic should be applied; how to find the cropping reference; and where each fabric pattern was in our workflow. So I set up a spreadsheet like this:

We decided it was more efficient to do all the 10” ruler images at once. So I sorted the spreadsheet like this:

Now the production artist could see a continuous list of all the images that needed a 10” ruler graphic.

I could easily sort the data by workflow stage (Lo Res rec’d) so I could quickly copy and paste a list of lo-res images that I still needed and send it to the project manager:

You sort the data in a spreadsheet by selecting all the cells you would like to sort, and selecting “sort range” from the data menu. You’ll get a dialog box asking which column you’d like to use to determine the sort, if the range you selected has a header row, and if you’d like to sort from A to Z or Z to A. (Numbers will be sorted lowest to highest or vice versa).

Caution: Be careful when you select your data range for sorting! If, for example, you miss a couple of columns or rows when you select the range, your data could get out of order.

 

2. Free Productivity-Boosting Forms

You don’t need to be an expert to use spreadsheets. Spreadsheet ninjas offer free pre-made spreadsheet templates for Google docs and Microsoft Excel. They range from personal finance calculators to calorie counters. There are useful ones for designers, including time tracking, web browser testing, and client forms.

When you see a form you like, select “use this template.”

Google will save it to a tab called “Templates I’ve Used”  so you can easily find it again when you search Google templates. You can also save a copy to your Google Drive.

You usually don’t have to know how the formulas work to use them, although I find sometimes I need to make a fix. Spreadsheet formulas look different than the math I was taught in school, but it’s not rocket science. 

1+1=2 would look like this in the formula bar:

=sum(1,1)

You’re instructing the cell to show you:

the answer(=) when you add (sum) one plus one (1,1) 

Adding the contents of the cell at column B row 2 to the contents of the cell at column C row 2 would look like this in the formula bar:

=sum(B2,C2)

3. Make Your Computer Figure it Out

Spreadsheet formulas can do calculations for you, speeding up your work. For instance, when writing CSS for an ebook, I calculated percentages for a fluid multi-column div based on the pixel widths in the layout. Here’s how I did it.

I made a place to input the numbers from the static layout in column B:

• the width of the div in pixels (from the layout);

• the number of columns;

• and the gutter width in pixels (from the layout).

In C2, I put 100, because my whole div is 100%.

First, in cell C4, figure the widths of the gutters in percent by dividing the gutter width by the div width: =divide(B5,B3)

Then in cell E3, string several functions together by nesting them inside each other. Google spreadsheets will perform the inside functions first.

1. Figure out how many gutters there will be (The number of columns minus 1, or minus(B4,1))

2. Then multiply the number of gutters times the % width of the gutters,

3. Subtract the width of the gutters from the 100% and

4. Divide what’s left by the number of columns to get the column width in percent.


Google also offers a good overview of adding formulas to spreadsheets.

4. Keep Project Managers Happy with Pie (Charts)

Spreadsheets can display data as charts and graphs, so for example, you could show hours spent on a job vs. the hours estimated.

Set up a spreadsheet with the parts of the job in rows, plus a header row with dates and total time spent. Include time remaining estimates if that’s helpful.

To get a total of hours spent, select the range of cells in the hours worked column, (in this case, from row 4 to row 16) and choose “sum” from the functions fly-out menu.

Notice that the formula has a colon instead of a comma between the two cell addresses. That means that the function (sum, or addition, is to be done to all the cells from F4 to F16)

You do the same for the forecast of remaining hours column.

Now, subtract the hours spent from your estimate:

Put labels for your data in column A, rows 19 – 21

Put total estimate hours in cell B19

Put =(F17) in cell B20 to place your total hours worked there as well

Put this formula in cell 21B: =minus(B19,B20) to figure hours left

Select the range of cells from A20 diagonally across to B21. Choose Chart from the Insert menu. Choose pie chart from the dialog box.

Click the “Insert” button. You can move the chart within your spreadsheet, add a title, change the colors and the font.

Then to distribute your spreadsheet, select “Download as” and then “PDF document”  or share the file through Google Docs.

These examples really just scratch the surface of what spreadsheets can do for you. For example, here’s an online discussion of how game designers map game play with spreadsheets.

I hope you’ll find spreadsheets useful for your design projects as well.

  • Guest says:

    Mike – Great info as usual! I would be VERY surprised to see a designer send a project manager a pie chart showing how much of the budget is left. It’s a great idea, but somehow just seems unlikely. Although I may do it next time and see what kind of reaction I get!

    Don’t forget that Excel is also extremely useful for organizing text that will be imported via a mail-merge. You can presort before you import and do a lot in Excel that is harder or impossible in InDesign. 

  • mchak says:

    p.s. that last comment was from me – I forgot I wasn’t logged in! I also use Google Docs spreadsheets with clients a lot for keeping track of status of various chapters, articles, ads etc. Better to have them online instead of getting updated versions every 2 days.

  • Susan Neuhaus says:

    Matt,  The pie chart turned out to be a good tool for talking about scope. Good suggestion about the mail merge – how did the InDesign import work? XML tags?  

  • Ginger Wilson says:

    Not only is it good for organizing text for a data merge, but if you’re working on a catalog or “yearbook” page, you can use it to import photos too. This feature has saved me hundreds of hours!

  • Bonnie C says:

    Excellent suggestions for use of such a powerful tool. very creative uses for your jobs and business! Thank for the tips! Well written

  • >