Guided Project 4-1

For this project, you import data to complete the inventory worksheet for WearEver Accessories. You use Flash Fill to enter product codes and sort the data. You also filter data, work with an Excel table, prepare subtotals, build a PivotTable, and use Text functions.

[Student Learning Outcomes 4.1, 4.2, 4.3, 4.4, 4.5, 4.6, 4.7, 4.8]

Files Needed: WearEver-04.xlsx and WearEverText-04.txt (GP-E4-1) (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 4-1.xlsx

Skills Covered in This Project

• Import a text file.
• Convert a table to a range.
• Use Flash Fill to complete data.
• Set conditional formatting.
• Use an AutoFilter.
• Create an Excel table.
• Add a field in a table.
• Use the Subtotal command.
• Create and format a PivotTable.
• Use the LEN function to count characters.

1. Open the WearEver-04 workbook from your student data files and save it as [your initials] Excel 4-1.
2. Import a text file.
   1. Select cell I3 on the Inventory sheet. Imported data will start one row below this location in cell I4.
   2. Click the From Text/CSV button [Data tab, Get & Transform Data group].
   3. Find and select the WearEverText-04.txt file from your student data files in the Import Data window.
   4. Click Import to open the preview window.
   5. Click the Delimiter arrow and choose Tab. The data preview in two columns.
   6. Click the Load arrow and select Load To.
   7. Select Existing worksheet (Figure 4-86).
   8. Verify that cell $I$3 displays as the location and click OK. The supplier name and phone number display in an Excel table.
3. Convert a table to a range and format data.
   1. Close the Queries & Connections pane.
   2. Select cell I3.
   3. Click the Convert to Range button [Table Design tab, Tools group].
   4. Click OK in the message box to remove the query definition (Figure 4-87).
   5. Select cells I3:J3 and clear all [Home tab, Editing group].
   6. Select cells I4:J40 and clear formats [Home tab, Editing group].
   7. Format cells I4:J40 with Trebuchet MS from the Theme Fonts.
   8. Use the Format Painter button to copy formatting from cell H4 to cells I4:J4.
   9. AutoFit columns I:J.
   10. Select cells A1:J2 and click the launcher in the Alignment group [Home tab]. From the Horizontal list, choose Center Across Selection.
4. Use Flash Fill to insert catalog codes.
   1. Insert a column between columns B and C.
   2. Type Catalog Code in cell C4.
   3. Type we001_rhb in cell C5 and press Enter.
   4. Type we002 in cell C6 and press Enter. Excel assumes the pattern and fills the column (Figure 4-88).
   5. AutoFit column C.
5. Apply conditional formatting.
   1. Select cells F5:F40, the quantity in stock.
   2. Click the Conditional Formatting button [Home tab, Styles group].
   3. Click Highlight Cells Rules and select Less Than.
   4. Type 2 in the Format cells that are LESS THAN box.
   5. Click OK to accept the Light Red Fill with Dark Red Text format.
6. Display and use AutoFilter arrows.
   1. Select cell A5 and click the Filter button [Data tab, Sort & Filter group].
   2. Click the AutoFilter arrow for the Supplier column.
   3. Deselect the (Select All) box to remove all the check marks.
   4. Select the Jennifer’s Closet box.
   5. Click OK. The data is filtered to show only records from one supplier (Figure 4-89).
7. Copy the Inventory worksheet to the end and name the copy Markup. Set the tab color to Gold, Accent 4 (eighth column, first row). AutoFit columns as needed.
8. Clear a filter and conditional formatting.
   1. Click the AutoFilter arrow for the Supplier column.
   2. Select Clear Filter from “Supplier.”
   3. Click the Filter button [Data tab, Sort & Filter group] to hide the AutoFilter arrows.
   4. Click the Conditional Formatting button [Home tab, Styles group].
   5. Select Clear Rules and select Clear Rules From Entire Sheet.
9. Create an Excel table.
   1. Select cells A5 on the Markup sheet. The data range has a header row with a blank row above it, and there is a blank row below the data.
   2. Click the Format as Table button [Home tab, Styles group].
   3. Choose Gold, Table Style Medium 5 in the Table Styles gallery.
   4. Confirm the range ($A$4:$K$40) in the Create Table dialog box, that the My table has headers box is checked, and click OK.
   5. Select cells A4:K4 and change the Font Color to Black, Text 1, Lighter 5%.
   6. Select cells A4:K40 and apply All Borders.
   7. Select cell A5 and click the Table Name box [Table Design tab, Properties group].
   8. Type tblMarkup and press Enter.
10. Add a calculated column to a table.
    1. Insert a column at column J.
    2. Type Markup in cell J4 and press Enter.
    3. Type = in cell J5 and click cell I5. The structured reference is @Retail].
    4. Type − for subtraction and click cell H5 (Figure 4-90).
    5. Press Enter to copy the formula.
    6. Press Ctrl+Home.
11. Copy the Markup worksheet to the end and name the copy Subtotals. Set the tab color to Black, Text 1 (second column).
12. Prepare for the Subtotal command.
    1. Select cell A5 on the Subtotals sheet. The records are sorted by the “Product ID” column.
    2. Click the Filter arrow for Supplier and sort in ascending order. The Product ID order is maintained within each supplier group.
    3. Click the Data tab. Note that the Subtotal button [Outline group] is grayed out because you cannot use the command in a table.
    4. Click the Convert to Range button [Table Design tab, Tools group] and click Yes. The formatting is now inconsistent.
    5. Select cells A4:L40 and click the arrow with the Fill Color button [Home tab, Font group].
    6. Choose No Fill.
13. Complete the Subtotal command.
    1. Select cell A5 and click the Subtotal button [Data tab, Outline group].
    2. Click the At each change in arrow and choose Supplier.
    3. Click the Use function arrow and verify or choose Count.
    4. Check the Supplier box in the Add subtotal to area.
    5. Deselect any other selected fields in the Add subtotal to list (Figure 4-91).
    6. Click OK. A subtotal row displays at the bottom of each group and shows how many products are available from each supplier.
    7. Format the labels in cell J17, J26, and J43:J44 as right-aligned (Figure 4-92).
14. Create a PivotTable.
    1. Click the Markup sheet tab and select cell A5.
    2. Click the PivotTable button [Insert tab, Tables group].
    3. Verify that the Table/Range is tblMarkup in the Create PivotTable dialog box and that the New Worksheet button is selected.
    4. Click OK. A blank PivotTable layout displays on a new sheet.
    5. Name the sheet PivotTable.
    6. Size and position the PivotTable Fields pane as needed.
15. Add fields in a PivotTable.
    1. Select the Product box in the PivotTable Fields pane to place it in the Rows area.
    2. Select the Quantity box to place it in the Values area.
    3. Drag the Supplier field name into the Columns area.
16. Format a PivotTable.
    1. Click the More button in the PivotTable Styles group [Design tab].
    2. Select Light Orange, Pivot Style Medium 7.
    3. Select the Banded Rows box [Design tab, PivotTable Style Options group].
    4. Select the Banded Columns box [Design tab, PivotTable Style Options group]
    5. Select cells A3:E4 and change the Font Color to Black, Text 1, Lighter 5%.
    6. Format cells A3:E4 as bold.
    7. Click the Field Headers button [PivotTable Analyze tab, Show group] to hide the filter arrows (Figure 4-93).
17. Use the LEN function to count characters.
    1. Select the CatalogPrep sheet tab and click cell D5.
    2. Click the Text button [Formulas tab, Function Library group] and select LEN.
    3. Select cell B5 for the Text argument.
    4. Click OK. The first result is 19.
    5. Copy the formula in cell D5 to cell E5.
    6. Copy cells D5:E5 to reach row 40.
    7. Select cell F5.
    8. Build a formula to add cells D5, E5, and 2 as specified in the text box.
    9. Copy the formula in cell F5 to reach row 40.
18. Save and close the workbook (Figure 4-94).