PAUSE & PRACTICE: EXCEL 4-1

For this project, you format data as a table, display the total row, and remove duplicate records. You use a structured reference to add a column to the table, apply conditional formatting with data bars, and sort data.

File Needed: PerfectRentals-04.xlsx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: your initials PP E4-1.xlsx

1. Open the PerfectRentals-04 workbook from your student data files and save it as your initials PP E4-1.
2. Click the Rentals sheet tab.
3. Format data as an Excel table.
   1. Select cell A5.
   2. Click the Format as Table button [Home tab, Styles group].
   3. Select White, Table Style Light 1 from the gallery (first row).
   4. Confirm the data range in the Create Table dialog box. Excel assumes the range $A$4:$F$30 because the data is organized with a header row.
   5. Verify that the My table has headers box is selected in the Format As Table dialog box.
   6. Click OK. The AutoFilter arrows display and the rows are banded (filled every other row).
4. Add a total row to a table.
   1. Click cell A5.
   2. Select the Total Row box [Table Design tab, Table Style Options group]. The values in column F are summed.
   3. Click cell B31 and choose Count from the drop-down list. The result is 26.
5. Remove duplicate rows in a table. Notice that rows 16:17 are duplicates of rows 14:15.
   1. Click cell A5.
   2. Click the Remove Duplicates button [Table Design tab, Tools group].
   3. Click Unselect All to deselect all the header names.
   4. Select the Rental Name box and Location boxes. When you look for duplicate data in specific columns, you can speed up search activities (Figure 4-25).
   5. Click OK in the Remove Duplicates dialog box. Two records will be removed.
   6. Click OK in the message box.
   7. Set the width of column F to 12.
6. Apply conditional formatting using data bars.
   1. Select cells F5:F28.
   2. Click the Conditional Formatting button [Home tab, Styles group].
   3. Point to Data Bars to see its menu.
   4. Select Orange Data Bar in the Gradient Fill group (Figure 4-26).

1. 1. Manage conditional formatting rules.
      1. Select cells F5:F28 if necessary and click the Conditional Formatting button [Home tab, Styles group].
      2. Choose Manage Rules.
      3. Click Edit Rule in the Conditional Formatting Rules Manager dialog box.
      4. Click the Color arrow for Fill in the Bar Appearance area.
      5. Choose Gold, Accent 4, Lighter 60% (eighth column, third row).
      6. Click the Color arrow for Border in the Bar Appearance area.
      7. Choose Gold, Accent 4, Lighter 40% (eighth column, fourth row) (Figure 4-27).
      8. Click OK to close the Edit Formatting Rule dialog box.
      9. Click OK to close the Conditional Formatting Rules Manager dialog box.
   2. Use a structured reference in a formula.
      1. Select cell G5 and type = to start a formula.
      2. Click cell F5 to insert a structured reference to the number of days.
      3. Type *225 and press Enter. The formula is calculated down the new column. This is an average rental rate (Figure 4-28).
      4. Select cell G4, type Average $225 and press Enter.
      5. Select cell G4 and click the Wrap Text button [Home tab, Alignment group].
   3. Format results, show the total, and complete formatting.
      1. Select cells G5:G28 and format them as Currency with no decimal places.
      2. Select cell G29 and choose Sum from the drop-down list.
      3. Select cell G5 and deselect the Banded Rows box [Table Design tab, Table Style Options group].
      4. Select cells G5:G28 and apply All Borders.
      5. Select cells A1:G2 and click the Alignment launcher [Home tab, Alignment group].
      6. Choose Center Across Selection from the Horizontal drop-down list.
      7. Apply a Bottom Border to cell G2 (Figure 4-29).
   4. Sort data in a table.
      1. Click the AutoFilter arrow with Location.
      2. Choose Sort A to Z.
      3. Click the AutoFilter arrow with State and choose Sort A to Z.
      4. Select cell A1.
   5. Sort by multiple columns in the Sort dialog box.
      1. Select the Sort&Filter sheet tab.
      2. Select cell A5 and click the Sort button [Data tab, Sort & Filter group].
      3. Click the Sort by arrow and select State.
      4. Confirm that the Sort On choice is Cell Values.
      5. Confirm that the Order is A to Z.
      6. Click Add Level to display the Then by row.

1. Click the Then by arrow and select Bedrooms.
2. Confirm that the Sort On choice is Cell Values.
3. Change the Order to Largest to Smallest.
4. Click Add Level to display another Then by row.
5. Click the second Then by arrow and select Baths.
6. Confirm that the Sort On choice is Cell Values.
7. Change the Order to Largest to Smallest.
8. Click OK (Figure 4-30).
9. Select cell A1.