Data Cleaning and Basic Spreadsheet Skills

This course will become read-only in the near future. Tell us at community.p2pu.org if that is a problem.

Task Navigation 

Synthesize Your Data, and Begin to Put it to Work


Write your first formulas!

 

Let's dive in to working with the data!
 
The first thing we might want to do on the Sales page is to calculate the total revenue gained from each sale, which is simply the the number of units sold times the revenue per unit. Click to enter cell G6, type:
=E6*F6
and press enter. You've just written your first formula! The formula will disappear and the cell will display the revenue gained from the first sale, $609.90. Now copy the formula (highlight cell G6 by clicking on it, then do Ctrl-C or right-click and select Copy), highlight the rest of the column (from cell G7 to G32), and paste (Ctrl-V or right-click and select Paste).
 
Now that we've got all the individual sales' revenues, one of the most sensible questions to ask is what the total sales revenue was in 2011. Scroll down to row 34 and select cell G34. We're going to enter a formula in this cell that will return the total value of all the sales in the column above it. The best way to do this is using the SUM formula. In cell G34, enter the formula 
	=SUM(G7:G32)
and press enter. Again, the formula should disappear and be replaced by the total sales figure we were looking for, $18,989.77.
 
A few notes about formulas:
  • Remember to always start formulas with the equals sign, so that the spreadsheet knows you're entering a formula rather than trying to edit the value of the cell.
  • The formula's result will change when its inputs change. What if a member of the sales team comes to you to say that they made a mistake: the June sale to customer D32 was actually for 20 units and not 25. Find the errant cell, make the change, and watch the change populate into the Revenue column and to the Total figure you just calculated.
  • You may have guessed that the letter-number codes in the formulas you've been writing were references to the cells with those coordinates. That's true! And in the case of the Total formula, the colon between the two cell references means the formula is referring to the entire range between (and including) them, instead of just to the two individual cells (which would be denoted by changing the colon to a comma). Try to write the formulas again, but instead of typing the cell locations directly, select the cells you wish to refer to with your mouse. In the case of the Total Revenue formula, you can just click the first cell, then type the "*", then click the second cell. In the case of the Total formula, simpley click the top cell, drag the cursor down to the bottom one, and let go. You can do the same thing by navigating with the keyboard's arrow keys, and using the Shift key when necessary. Can you figure out how?

Task Discussion


  • Gareth Glynn said:

    I completed all the tasks but couldn't work out how to use arrows keys and tabbing - instead of the mouse - to execute calculations. I kept getting errors and couldn't see how to resolve them

    on June 18, 2012, 4:47 a.m.

    Patrick said:

    Hi Gareth, on mac CMD / will get you an overview of the keyboard shortcuts. You can find this shortcut in the Help section of the menu bar (last item). That should help you on your way.

    Good luck,

    Patrick 

    on June 18, 2012, 6:11 p.m. in reply to Gareth Glynn

    Gareth Glynn said:

    Patrick

    Many thanks for this; I've been using Macs for 20+ years but haven't yet seriously explored using keyboard instead of mouse. Of couse, if/when i'm no longer able to use a mouse I'll have to!

    Right now I'm still trying to get more adept at using a touch screen smartfone ... there's more and more to learn – if only there was more time!

    best regards

    Gareth

    on June 19, 2012, 4:24 a.m. in reply to Patrick