Note: This is an edited extract from Data Smart: Using Data Science to Transform Information into Insight by John W. Foreman, published by Wiley (RRP £30.99)
This book relies on you having a working knowledge of spreadsheets, and I'm going to assume that you already understand the basics.
If you've never used a formula before in your life, then you've got a slight uphill battle here. I'd recommend going through a For Dummies book or some other intro-level tutorial for Excel before diving into this.
That said, even if you're a seasoned Excel veteran, there's some functionality that'll keep cropping up in this text that you may not have had to use before. It's not diffi cult stuff; just things I've noticed not everyone has used in Excel. You'll be covering a wide variety of little features in this chapter, and the example at this stage might feel a bit disjointed.
But you can learn what you can here, and then, when you encounter it organically later in the book, you can slip back to this chapter as a reference.
As Samuel L. Jackson says in Jurassic Park, "Hold on to your butts!"
Some Sample Data
Imagine you've been terribly unsuccessful in life, and now you're an adult, still living at home, running the concession stand during the basketball games played at your old high school. (I swear this is only semi-autobiographical.)
You have a spreadsheet full of last night's sales, and it looks like Figure 1-1.
Figure 1-1 shows each sale, what the item was, what type of food or drink it was, the price, and the percentage of the sale going toward profit.
Moving Quickly with the Control Button
If you want to peruse the records, you can scroll down the sheet with your scroll wheel, track pad, or down arrow. As you scroll, it's helpful to keep the header row locked at the top of the sheet, so you can remember what each column means. To do that, choose Freeze Panes or Freeze Top Row from the "View" tab on Windows ("Layout" tab on Mac 2011 as shown in Figure 1-2).
To move quickly to the bottom of the sheet to look at how many transactions you have, you can select a value in one of the populated columns and press Ctrl+↓ (Command+↓ on a Mac). You'll zip right to the last populated cell in that column. In this sheet, the final row is 200. Also, note that using Ctrl/Command to jump around the sheet from left to right works much the same.
If you want to take an average of the sales prices for the night, below the price column, column C, you can jot the following formula:
=AVERAGE(C2:C200)
The average is $2.83, so you won't be retiring wealthy anytime soon. Alternatively, you can select the last cell in the column, C200, hold Shift+Ctrl+↑+ to highlight the whole column, and then select the Average calculation from the status bar in the bottom right of the spreadsheet to see the simple summary statistic (see Figure 1-3). On Windows, you'll need to right-click the status bar to select the average if it's not there. On Mac, if your status bar is turned off, click the View menu and select "Status Bar" to turn it on.
Copying Formulas and Data Quickly
Perhaps you'd like to view your profi ts in actual dollars rather than as percentages. You can add a header to column E called "Actual Profit." In E2, you need only to multiply the price and profit columns together to obtain this:
=C2*D2
For beer, it's $2. You don't have to rewrite this formula in every cell in the column. Instead, Excel lets you grab the right-bottom corner of the cell and drag the formula where you like.
