Data Matters with Excel®

Activity 1.1

The project in Section 1.1 requires these four steps.

1. Open the software you are using.
2. Enter data for the last three U.S. presidents: name, political party, and gender.
3. Use your software to calculate the proportion of the three presidents who had each name, the proportion in each political party, and the proportion of each gender. Check that the steps you used produced valid answers.
4. Use your software to create a bar chart showing the proportions found in the last step. Check that the steps you used produced valid bar charts.

Here’s how to do each step:

Step 1: Open the software you are using.

Double-click the icon or file name for Excel.

Step 2: Enter data for the last three U.S. presidents: name, political party, and gender.

The middle of the screen looks a little like graph paper. That graph paper is a spreadsheet. Each white rectangle is called a “cell.”

The cells in the top row will hold the names of the variables. Select the top-left cell by clicking on it. Type in the name of your first variable, Name. Press “Tab” to move to the next column. Repeat for the other two variables, Party and Gender.

Select the cell under Name. Type in the name of the first president in your data.

After you have typed in the name, press “Tab” to move to the next column, where you can enter political party. Press “Enter” to move to the next row, where you can enter the name of the second president in your data.

Once all the data are entered, save your data by selecting File, then Save.

Step 3: Use your software to calculate the proportion of the three presidents who had each name, the proportion in each political party, and the proportion of each gender. Check that the steps you used produced valid answers.

Select a cell away from your data. Then select Data, PivotTable and PivotChart Report... . A new dialog box appears, labeled PivotTable and PivotChart Wizard. Click on Next. In the next box, click on the tiny diagonal red arrow. The box changes shape. On the spreadsheet, select the leftmost variable name and drag to the bottom-right cell of the data. A flashing border will now surround your data and variable names. Go back to the PivotTable and PivotChart Wizard box and click on the tiny red down arrow. Then select Next, Layout.

Select the first variable name and drag it to and drop it on ROW. Select that variable name on the right side of the screen again. Drag and drop it on DATA.

Double-click the gray box that appears that has text beginning “Count of.” Click on Options >>. Click on the triangle next to the box labeled Show data as:. Select % of column. Click OK, then OK again, then Finish.

Check the table that appears. It should show you the proportions you’re looking for.

Repeat these steps for the other two variables.

Step 4: Use your software to create a bar chart showing the proportions found in the last step. Check that the steps you used produced valid bar charts.

Repeat the steps above with one change. When the PivotTable and PivotChart Wizard box first appears, click on the white dot next to PivotChart (with PivotTable). When your chart appears, you will be on a new sheet of the Excel workbook. To get back to your data, click on Sheet1 near the bottom of the screen.