This project covers the following MOS Objectives:
Create Excel tables from cell ranges (EX21-3.1.1)
Sort data by multiple columns (EX21-3.3.2)
Add or remove table rows and columns (EX21-3.2.1)
Convert tables to cell ranges (EX21-3.1.3)
Filter records (EX21-3.3.1)
Filter and Sort Table Data (EX21-3.3)
Apply table styles (EX21-3.1.2)
Insert and configure total rows (EX21-3.2.3)
Configure table style options (EX21-3.2.2)
Steps to complete this project:
Mark the steps as checked when you complete them.
Open the start file EX2021-MOS-Ch3 presentation. If the presentation opens in Protected View, click the Enable Editing button in the Message Bar at the top of the presentation so you can modify it.
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.
Create the customer list table.
Format the data on the Customer List worksheet into a table using Green, Table Style Light 14. The table data should include cells A1:I65. Be sure to include the table headers.
Clean up the customer list table by removing all duplicate rows with identical data in the First Name, Last Name, and Street columns. Excel should find and remove 8 duplicate values, leaving 56 unique values.
Sort the customer list data by Last Name and then by First Name.
Convert the table to a normal range.
Create the sales analysis table.
Go to the Sales Analysis worksheet.
Sort the sales data by the Purchase Date column so the oldest dates appear first.
Apply the Dark Blue, Table Style Dark 6 table style.
Display the total row and show the total (sum) number of sales in the # Items Purchased column and the total (sum) in the Purchase Total column.
Filter the table to show only sales from the city Rocklin with the zip 95663. There should be nine rows that meet the criterial.
Remove the banding from the table.
Create the February Sales table.
Go to the February Sales worksheet.
Sort the table by Zip, and then by City, and then by Purchase Total with the largest purchase totals at the top.
Add a custom filter to the table to show only purchases after or equal to 2/1/23 and before or equal to 2/28/23. There should be six rows that meet that criteria.
Apply the Blue, Table Style Medium 6 table style to the table.
Create the custom filter table.
Go to the Custom Filter worksheet.
Clear the existing filter from the City column.
Create the criteria range by inserting four blank rows above the table header row and then copying the table header row (row 5) to row 1.
Enter filter criteria to show only records where the city is Rocklin and the # of items purchased is less than five and the purchase total is greater than 100 OR where the city is Rocklin and the # of items purchased is greater than five and the purchase total is less than 100.
Filter the data in place. Hint: Use the Advanced Filter feature with cells A1:I3 as the criteria range and cells A5:I69 as the list range. There should be nine rows that meet that criteria.
Add a total row to the table to show the number (count) of records in the City column, the average in the # Items Purchased column, and the average in the Purchase Total column.
Save and close the presentation.
Upload and save your project file.
Submit project for grading.
