We want to calculate several things based upon our current data. First, we would like to see the total self-reported confidence level for our respondents in two areas: office-based applications and communications applications. Also, we would like to determine the average confidence level by application.
Before we can begin to enter our new formulas, we will need to add headings to the right of our existing data set.
Step1. To navigate to the first additional cell for the new heading,
Click cell K1
Step2. To enter the new heading, type:
Office Software Total Enter
Step3. To navigate to the next column heading,
Click cell L1
Step4. To enter the next heading in cell L1, type:
Communications Apps Total Enter
Step5. Widen columns as necessary.
Step6. To activate the cell K2, if necessary,
Click cell K2
Step7. To begin the formula, in cell K2, type:
Step8. To enter the Sum function,:
Press & Drag E2:G2
9. To complete the function entry, press:
Step10. To activate cell L2,
Step11. To begin to enter the function in cell L2, type:
Step12. To designate the proper cells for the communication applications (Gmail, Skype and Facebook),
Press & Drag H2:J2
Step13. To complete the function entry, press:
It turns out that we have one more entry to make that we forgot to include. We will add it to the top of the data. The technique for inserting rows is very similar to inserting columns. We want to add one row of responses to the top of the worksheet, so we will select only the row at the top of the worksheet, but still below the headings. The new row will then appear above the selected row.
Step1. To select the first row, in the row heading selection area,
Point to the row 2 header
2. To select the row,
Step3. To insert a new row,
Right-Click the selection, Click Insert
NOTE for MacOS Users: To insert a new row, press: Control key+Click the selection,Click Insert.
Step4. Use the table below to enter the forgotten data, noting that this participant did not have an additional comment, so we'll leave D2 empty:
Step5. Save the workbook.
Using the Total Row and Fill Features of Tables
We can continue to learn more about our participant responses by calculating an average of confidence levels for each application. We will place our averages at the bottom of the data. As mentioned earlier, the table feature in Excel has a Total row for this kind of task.
Step1. To begin to use more Table features, if necessary,
Clickany cell in the table
Step2. To reveal the Table Tools, on the Ribbon,
Click the Design command tab under Table Tools
NOTE for MacOS Users: To reveal the Table Tools, on the Ribbon, Click theTable command tab.
Step3. To activate the Total Row feature, on the Ribbon in the Table Style Options group,
Click the Total Row check box
Step4. To activate the last Total row cell,
Click the last cell in the last column on the right
Step5. To enter the Average function,
Click, Click Average
Step6. To display some of the formatting features, on the Ribbon,
Click Home command tab
Step7. To adjust the number format to the desired format, on the Ribbon, on the Home tab, in the Number group,
Clickrepeatedly, until only two decimal places are visible
Step8. To copy the functions to the remaining columns, with the auto-fill cursor,
Press & Drag the fill handle in the Total Row cells 7 columns to the left (ending under the Excel column)
Step9. To activate our label cell,
Click the cell in the Total Row under Comments
Step10. To enter the label, type:
Averages Tab key
Using Table Features to Analyze a Data Set
As mentioned earlier, one of the features of a table is its filters. By combining the filters with the Total Row we can learn more about our data. In our example, we would like to see the average levels for the top-ten family income respondents.
Step1. Scroll to the top of the table.
Step2. To begin to filter the household income, in the header row,
Click, Point to Number Filters
NOTE for MacOS Users: Under the Filter section, Click "Choose One" drop-down option.
Step3. To choose the top ten reported incomes,
Click Top 10...
Step4. To accept the default and filter the list of household incomes,
Step5. To see the top ten incomes,
NOTE: There are more than ten entries due to several respondents reporting the same income levels.
Step6. To clear the filter for Household Income,
Click, Clear Filter from "Household Income"
NOTE for MacOS Users: To clear the filter for Household Income, Click Clear Filter and close the dialog box.
Similarly to the chart we created in the previous workbook, we need to add alternative text to this table.
Step1. To see more menu options for the table,
Right-Click any cell in the table, Point Table
NOTE for MacOS Users: To see more menu options for the table, press: Control key+Click any cell in the table, Point Table.
Step2. To activate the alternative text dialog box,
Click Alternative Text...
Step3. Enter an appropriate Title and Description in the corresponding fields.
Step4. To complete the alternate text entry,
Step5. Save the workbook.