In part 1 we logged into Google Spreadsheets and got things set up. Now let’s get this thing to do some work for us.
Creating the Formula
We’re going to teach this worksheet to multiply the price of a plant by the number of plants we want to give us the total price. The end result will make us gasp and reconsider if we really want that many. In following posts we will also teach it to add up all the total prices to give us the order subtotal. Then we’ll look at other ways to use spreadsheets.
Click here to get back to the Google Docs and Spreadsheets Main Menu. (Bookmark it now if you haven’t already.) You should see something similar to this:
Okay, you won’t see the red circle. “Demo” is what I called the workbook we saved last time. I don’t know what you called yours, but click on the name of your workbook to open it. There are two columns that we want to show up as money, so we have to tell the spreadsheet that. First click on column E, “Price per Package,” to select that column (red arrow, Figure 2). Then click on the down-arrow by the word Format and select the currency format from the list (green arrow, Figure 2). Do the same thing for column F, “Total Price.”
Then click on the Formula tab (Figure 3). Now, creating a formula in a spreadsheet works the opposite of the way you’d expect. You start with the end result and work backwards. So the first thing we’re going to do is select the cell where we want the answer to be. That’s the box right under the words “Total Price,” F2. Then we click the word Product, because that’s the kind of result we want (Figure 4). Next we click the cells of the two things we want to multiply, “Number of packages” (B2) and “Price per package: (E2). It should look like Figure 5. In spreadsheets, as in adding machines (perhaps I date myself), multiplication is symbolized with an asterisk (*). So you need to insert an asterisk between the B2 and the E2. Finally, add the closing parenthesis.Press the Tab key and you’re done (Figure 6).
But wait a sec. You’re not going to order just one kind of plant, are you? We’ve got to make this formula work for all the rest of the rows. Right-click in that cell with the zero dollar amount and choose Copy(Figure 7). Hover your mouse over the cell directly below the one that you put the formula into. Then click and hold down the left mouse button, and drag it down however many rows you think you’ll need. Right-click and choose Paste (Figure 8 ). Now each of those cells will be the product of the B column and the E column of the corresponding row.
Enter Data
I bet you never thought we’d get to this point. Actually, it doesn’t take that long to set up once you know what you’re doing, and now we’re going to watch this spreadsheet work for us. Go through your catalog page by page. Enter information in a cell, then tab to the next. The math is done for you. If you don’t like the total price, go back and lower the quantity. If you decide you don’t want a certain plant, change the quantity to zero, but don’t delete the row. It’s useful to know what you thought about buying but didn’t. I save my spreadsheets and sometimes I order stuff that I decided not to get in a previous year. Other times, it’s just good for a laugh: I wanted that?
In the next post we’ll learn how to find the grand total. To see how far we’ve come today, click here.
Kathy, this is good info. I also use spreadsheets at work, but if I didn’t, I don’t know where I would have learned to do so to any great extent.
Great service you are providing. My 2nd order of seeds has arrived, so it is time to set up this year’s spreadshet and see what else I need!
(I’ve also used a spreadsheet to “map” out my garden. I set up the cells to be the same height and width so it looks like graph paper, then do a “color fill” to show where each raised bed is, then type in what I’m planting in each bed. I probably haven’t described it very well, but it is a useful way to plan something like a vegetable garden)
For those who are already familiar with spreadsheets, all one has to do is make the suggestion and a spreadsheet user can take the ball and run with it. My tutorial will be overkill for them. But for those who have never used a spreadsheet, this will help them see the light.
I myself have never used spreadsheets much, so when I made my first one two years ago, I had to rely on the skills of my DH, a bona fide Excel power user. I’m still not skilled enough to provide templates equal to what Jenn referenced in her comment in the previous post, where the user enters in one date, and a multitude of calculations are done for you.
I agree with Zoey. Spreadsheets rock! I ditched my checkbook register and use a spreadsheet. I found a template on the Microsoft site (I use Excel) that had a formula for a running balance. Then there’s my monthly and annual budget spreadsheets and my seed purchase spreadsheets.
Kathy,
What a great idea to teach your readers how to make a spreadsheet.
I use Excel for everything. I even created a spreadsheet to figure the calories burned and the pounds lost when I was working out.
It’s a skill they can use for many things other than gardening.