Lab 10: Creating formulas in Excel

Introduction

This lab is an introduction to writing formulas and using functions in Microsoft Excel. In this lab you will be using Excel to manipulate and analyze data from the 2000 Census. All of the data used in this lab is from the US Census Bureau's web site at www.census.gov. These numbers are the populations counts for the state of Washington; they are grouped by age.

For an introduction to Excel and to spreadsheets in general, see Chapter 13.

1. Open the Lab 10 worksheet

Click on the lab11 link below to download the Excel worksheet. Click on the link, save the file to your Desktop, or somewhere else that will be easy to find later. Double-click on the Desktop icon to open it in Excel, or you can start Excel, click the Office button, and open the file on your Desktop.

lab10_data.xls


A spreadsheet is a table that stores data and calculations. Each "box" in the spreadsheet is a cell. The cells are each in a row and a column. The rows are numbered (1,2,3,...). The columns are lettered, (A,B,C,...). We can refer to a cell by its column letter and row number. For example, the cell with the words "Age" is cell A1.

2. Formatting a spreadsheet

Right now the spreadsheet just looks like a jumble of numbers. We can make the spreadsheet easier to read by changing the appearance of the cells.

  • The spreadsheet contains US Census data for the state of Washington. The data is broken down by age group and gender. Currently it is difficult to tell what the categories are and where the data starts. Let's make the column titles bold so they will be easier to spot.

  • Click in cell A1 and drag the mouse to the right so that the top three boxes (A1, B1, and C1) are selected. Press CTRL+B to make the text in these cells bold.

    Extra Notes:
  • The numbers in this sheet are still somewhat difficult to read. We can fix this by telling Excel to display them with commas (e.g. 1,234 instead of 1234). By default, Excel just prints the numbers without commas or dollar signs. If your numbers were in units of time or money, you can tell Excel to format them appropriately (e.g. 14:00, 2pm, or $14).

  • Important: Each cell has at least two parts: (1) the cell's content (e.g. 1234) and (2) the cell's presentation (e.g. US currency, time, real numbers, italicized, bolded, Times New Roman, etc...). It is important for you to remember that these two both affect how a cell is displayed.

  • Now we'll add the commas to change the presentation. Click and drag so that all of the values in columns B and C are highlighted.

    Then go to Number box of the Home tab, select the Comma Style button, then click the Decrease Decimal button twice. Now, commas have been inserted between the thousands and hundreds place. This does not change the value of the cell, just how it is displayed.

    screenshot
  • 3. Using functions

    Now we will use a function to calculate the total number of males and females recorded by the census. We'll use one of Excel's built-in functions to calculate the total of each column. To do this we will write a formula to put inside one of the cells.

    Important: In addition to a cell's content (e.g. 1234) and presentation (e.g. US currency, time, real numbers, etc...). A cell can have a formula instead of a number. So a cell can have three parts: (1) A formula (e.g. =1+2+3), (2) a value (e.g. 6), and (3) a number format (e.g. $6).

    We're going to put the total sum of the values in column B into cell B21. The SUM function is used to compute the sum of a range of cells. Once you've entered the formula correctly, Excel will display the result of the SUM function. These next instructions will show you how to use the SUM function.

    You must start by typing = to indicate that you are typing in a function. Otherwise, Excel will not evaluate your input as a formula.

    The sum function takes the form =SUM(cell1:cell2), where cell1 is the first cell in the range and cell2 is the last cell in the range. All of the cells between cell1 and cell2 are calculated in the function. You can type in the names of cell1 and cell2 to indicate the range. Or, you can select the range with the mouse while you type in the formula. We will try it with the mouse.

  • Select cell B21.
  • In cell B21, type in the following text: =SUM( . Next, with your mouse, highlight all of the numbered cells in column B. Notice that Excel automatically calculates the range based on your highlight.
    screenshot
  • Now, type a closing parenthesis, ). B21 should now contain the text =SUM(B2:B20)

  • Press Enter to insert the formula into the cell. The formula will be replaced by the calculated sum of column B. However, if you click on the cell, the function will be shown in the function box above the spreadsheet even though the data is shown in the cell. You can also edit the formula by double-clicking the cell.

    screenshot

  • Repeat the steps above to find the total size of the female population and place it in cell C21.
    Extra Note: You can access the functions another way by clicking on the Formulas tab or by selecting Insert Function button from the Function Library box.
    screenshot
    Excel has a large number of built-in functions that perform many different operations. Feel free to go in and explore them. You can find a list of all the available functions here. In this pop-up window, the drop down menu lists the categories of functions. The functions themselves are displayed in a list. You can click on a function to see a description of what it does and how to use it.
    screenshot
  • Let's label this new data: Put the words TOTAL in cell A21. Make this cell bold also (Ctrl-B).

    4. Mathematical functions and Filling

    Excel can also handle regular ("infix") arithmetic statements. For example, instead of SUM(1,2) you could write 1+2. You can use + to add numbers, - to subtract, * to multiply, and / to divide just as you did in JavaScript. Excel uses the standard order-of-operations for math, so make sure you indicate mathematical precedence by using parentheses. For example, 1+2*3 is different than (1+2)*3.

    Now let's add up the female and male populations to get the total for each age group. We will place this total in column D.

  • Click on cell D2. In it, we will put the total number of people under age 5.

  • Use the + operator in our function instead of the SUM function (although SUM would work, too).

  • Once again, enter = to indicate we are using a function. Then click cell B2 to add this cell to our formula. Type a + sign and then click on cell C2. The formula should appear as =B2+C2 (you can also enter this purely with the keyboard)

  • Press Enter to make the total population under age 5 appear in this cell.

  • We've now created a formula to calculate the number of people within a single age group. It would be nice to have totals for each age group, but we rather not type the formula each time. Fortunately, Excel provides a shortcut for creating sets of similar formulas based on another formula. We will use this feature to fill in the rest of column D with group totals.

  • Click on cell D2 and drag down to D20 to highlight the cells you wish to copy your formula into.

  • Go to Editing box, select the Fill button, and select Down to fill the selected cells with their correct formula.
    screenshot
    Click on each cell to see how excel filled each one. Note that this feature is very handy. This feature looks at your formula B2+C2 and changes the number 2 for each new line that it fills, changing it to match the newly filled box. So, B2+C2 becomes B3+C3, B4+C4 , B5+C5, and so on... This might allow mistakes (maybe you really wanted it to change like this: B1+C1, B1+C2, B1+C3, B1+C4.. where B1 stayed absolute or fixed). Remember to check that the cells were filled out with the right formula.
  • Extra, advanced stuff: If you want more control over references in formulas, click on the Help button, go to Formula and Name Basics. Visit the section on relative and absolute references to figure out how to control exactly how Excel automatically fills new cells.

  • Finally, put the text Totals (in bold) for cell D1.

    5. Copying and pasting formulas

    You can also copy and paste formulas (e.g. =SUM(B2,C2) or B2+C2, instead of the value: 192,241) in Excel. Copying and pasting acts like the fill operation we performed above: The formulas in the original cells make references. In the new cell, these references are rewritten relative to the original cell. (e.g. If the original cell was adding the two cells to the left of it, the new cell will add the two cells to the left of the new cell.)

    Notice that we have not yet calculated the GRAND total population of Washington State in 2000. Let's do so now and put that into D21 using copy and paste.
  • Click on cell D2 again. Copy the formula with the Copy button in the Clipboard box of the Home tab, or CTRL-C.

  • Now click on cell D21 and paste the formula with the Paste button in the Clipboard box of the Home tab, or CTRL-V The total population should now be in D21. Also, notice that the formula for D21 is different than the formula for C21 and B21.  The cell might display "#####" if the number is too large to fit in the cell. Don't worry about this for now.
    The rules that Excel uses to copy and paste formulas can initially seem confusing. The best way to get a sense for these rules is to experiment with copying and pasting formulas. Go ahead and try it out until you have a good sense of how it works.
    Sometimes you just want to copy the value of the cell and not the formula. One way to do this is to click the area below the Paste button, select Paste Values, to just paste the calculated value. Paste Special is a powerful way to control what gets pasted, you conduct complicated types of pastes using this function.

    6. Automatic updating of formula results

    When you change data in cells that are used in the formula, the formula is automatically recomputed and the displayed result is adjusted accordingly.

    Let's say we found out the census data for the 20 to 24 age group was incorrect and that there were actually only 189,370 females in that age group. We would want to change the spreadsheet and have all of the totals reflect those changes.

  • Click on cell C6, the count for females aged 20 to 24, and type in the number 189370.

  • Watch cell D6 as you change C6. The value in D6 will change as you update C6, and so will the grand total at the bottom of the sheet. This is a huge advantage to using formulas in Excel. You don't have to go back through your data and update everything that depends on the value of C6. Excel does it for you!

    7. Boolean operators and the IF function

    Excel doesn't just operate on numbers. It can also perform logical operations using Boolean values (true and false).

    Suppose you are asking: Does a particular age group have more males or females? and Which gender has the greater population? You could do this arithmetically: Subtract the number of females from the number of males, and use numbers to represent the difference. But this could be unclear. It would be much simpler if you had a column for the prominent gender: its cells displaying "males" if there were more males or "females" if there were more females.

    The IF function allows you perform a logical evaluation of some expression and to perform an action based on the result of the expression. You can tell Excel what to display if the expression is true and what to display if the answer is false. This is similar to the if statements we encountered in Javascript.

    The if function uses the following syntax:
    =IF(logical expression, result if the expression is true, result if the expression is false)