USING MICROSOFT EXCEL 2013

Independent Project 4-4

Independent Project 4-4
Eller Software Services has received updated client information with sales numbers. You import the data into the worksheet, sort and filter it, and apply conditional formatting. You also format the data as an Excel table and create a PivotTable.

Skills Covered in This Project













Import a text file.
Sort data
Use an AutoFilter.
Filter data by cell color.
Copy, name, and move a worksheet.
Use the Subtotal command.

Apply Conditional Formatting.
Clear filters and conditional formatting.
Create an Excel table.
Create a PivotTable.
Protect a worksheet with a password.

IMPORTANT: Download the resource file(s) needed for this project from the Resources link. Be sure to extract the file(s) after downloading the resources zipped folder. Please visit SIMnet Instant Help for step-by-step instructions. Step 1
Download
start file

1.

Open the EllerSales-04 start file. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the workbook so you can modify it.

2.
3.

Import the ClientInfo-04.txt file in cell A4. The text file is tab-delimited.

4.

Unhide row 10. Fix the phone number in cell C12.

5.

Set Conditional Formatting to show cells I5:I13 with Yellow Fill with Dark Yellow Text for values that are less than 1500.

6.

Download
Resources

The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.

Sort the data first by Product/Service in A to Z order and next by Client Name in A to Z order
(Figure 4-98).

4-98 Rows sorted by "Product/Service" and then by "Client Name"

7.

Display the AutoFilter buttons and filter the Gross Sales data by color to show only those