Independent Project 4-5

Independent Project 4-5

Boyd Logistics monitors shipments statistics in a workbook. Before formatting the data as an Excel table, you export data as a text file for use in a regulation app. You Flash Fill route codes, build a PivotTable, and create a PivotChart.

[Student Learning Outcomes 4.1, 4.2, 4.6, 4.7, 4.8]

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

Completed Project File Names: [your initials] Excel 4-5.xlsx and [your initials] Excel 4-5Export.txt

Skills Covered in This Project

• Export data as a text file.
• Format data as an Excel table.
• Set conditional formatting with data bars.
• Edit a conditional formatting rule.
• Flash Fill a column in a table.
• Create a PivotTable.
• Format fields in a PivotTable.
• Create and format a PivotChart.

Select cell A1 and save and close the workbook (Figure 4-119).

Open the BoydLogistics-04 workbook from your student data files and save it as [your initials] Excel 4-5.

Export data to a tab-delimited text file.

Insert a new worksheet and name it Export.

Copy cells A4:F32 on the Stats sheet to cell A1 on the Export sheet. It does not matter if not all data is visible.

Click the File tab and choose Export.

Change the file type to a tab-delimited text file.

Save the file as [your initials] Excel 4-5Export in your usual location for saving work and click OK in the message box to save only the active sheet. The sheet is renamed after the data is exported.

Click the File tab and select Save As.

Save and replace [your initials] Excel 4-5 as an Excel Workbook (.xlsx).

Format data as a table with data bars.

Select the Stats sheet tab.

Select cell A4 and format the data as an Excel table using Green, Table Style Medium 21.

Select cells E5:E32 and set conditional formatting to use green data bars with a gradient.

Edit the conditional formatting rule to use Green, Accent 6, Lighter 60% as the gradient color and as the border color (Figure 4-118).

Flash Fill data.

Insert a column at column F.

Type Route Code in cell F4.

Select cell F5 and type min:chi to set the first code.

Type as much of the second code as needed to Flash Fill the column.

Create a PivotTable and PivotChart.

Select cells A4:G32 and click the Quick Analysis button.

Select Tables and choose the PivotTable option to sum miles by contractor.

Rename the sheet PivotTable&Chart.

Select cell B3 and open the Value Field Settings dialog box. Use Total Miles as the custom name and Number format with zero (0) decimal places and a comma separator for values over 1,000.

Add a 3-D Clustered Bar PivotChart to the sheet.

Position the chart object to start in cell D3. Size the chart to reach cell N20.

Delete the legend and the title in the chart.

Select cell B4 in the PivotTable.

Sort the values in ascending order (smallest to largest). The bar chart adjusts so that the longest bar is at the top.

Add a Black, Text 1 outline to the chart object.