Data can come from many different sources and is often available as a delimited text file.

Part 1 – Create your file and Import your data

1. Start by creating your own original new file using Excel 2016 or 2019 with the name Kyler.Attisha_Excel_Lab4.xlsx
   • Files created in older versions of Excel may not earn full credit!
2. Enter the appropriate Excel header and footer elements in the locations below. The only part you should type is your name. 

1. Left Click the link below to create your file. A download link will appear after you click the link and your file has been created. By clicking Create My Data File the button below you agree to follow the guidelines for this assignment.  – Click once

1. Import the data file that you downloaded
   • Use the import wizard use the Load To option to load the data into Sheet 1 cell A3.
     • There are 417 rows of data in this file.
   • Close the Queries & Connections pane
   • 
   • reduce the table so it only includes the data values 
2. Worksheet data entry, the following cells should contain information as follows:
   • cell A1 title contains Attisha Revenue
   • cell G2 contains the text Markup.
   • cell G3 contains the text Order Total.
   • cell H2 contains the value 15% formatted as percent with 0 decimal places.
   • cell H3 contains the text Shipped Total.
3. Change the title font size in cell A1 to 18 points.
4. Merge and Center the title in cell A1 across the data columns A – H.
5. Set the background color of cell A1 to Accent 6 Darker 25% or the closest green color.
6. Set the text color of cell A1 to White and bold
7. Add like breaks between words in all column titles with 2 words by using a line break.
   • Do not use wrap lines.
8. Change all column widths to 20 to verify that the text still wraps correctly.
   • Set columns A, C, D, E, and F to a width of 10 (75 pixels)
   • Set columns B, G, and H to the width of 13 (96 pixels)
9. Bold all column headings in row 3.
10. Align all the column titles vertically at the top and horizontally centered.
11. Use the Convert to Range option to convert the table to a range.
    • Click OK to the message “This will permanently remove the query definition … “
12. Your file should look something like this:
13. Add 3 document properties via the Document Properties panel.
    • Title: Excel Lab 4 Fall 2021
    • Author: Kyler.Attisha
    • Comments: location where you completed the lab i.e Home System, Work Computer
14. Save the file with the name Kyler.Attisha_Excel_Lab4.xlsx

Part 2 – Layout, Format and Enter Formulas – Revenue Worksheet

Modify the worksheet to format and calculate the revenue with a discount.

Important – The formula for Sales Price column must use the correct cell reference type (refer to your Excel text page E2-93).

1. Calculate Order Total in column G for all 416 items.
   • The Order Total is the Quantity times the Unit Price times (1 + Markup).
2. Calculate Shipped Total in column H for all 416 items.
   • The Shipped Total is the Order Total + Quantity times Shipping.
     • Self check 1– Changing the value in cell H2 should cause all Order Total prices to increase. If it doesn’t then you did not use the correct cell reference type in your formula for Order Total.
     • Be sure to set cell H2 back to 15% after checking.
     • Self check 2 – If your Order Total formula for the first row is correct then you should be able to copy it to all the remaining rows in the worksheet and obtain the correct results.
3. Enter formulas at the bottom of the Order Total and Shipped Total columns to calculate the total all items each of those columns and bold the results in those cells.
4. Format as follows:
   • columns, Unit Price, Shipping Cost – set as comma style with 2 decimal places
   • columns Order Total, Shipped Total – set as comma style with 0 decimal places
   • column D – Short Date
   • cell H2 – Percent with 2 decimal places
5. Rename the worksheet tab to Revenue.
6. Change the tab color to Green, Accent 6, Lighter 60%.
7. Freeze panes
   • Column A and Rows 1-3 should remain visible when you scroll either horizontally or vertically
8. Enter formulas at the bottom of the Order Total and Shipped Total columns to calculate the total all items each of those columns and bold the results in those cells.
9. Enter the text Totals in column F next to the cell containing the formula you added in the previous step.
10. Bold the text and totals. Should look similar to this 
11. Add the range name Revenue_Total for the Shipped Total total cell at the bottom of column H.
    • The scope of the range name should be the workbook and not restricted to a worksheet.
    • Check the scope using the Name Manager.
12. Change the worksheet view to the Normal view (if needed).
13. Save the workbook before continuing.

Part 3 – Sorting and Filtering

Filtered Worksheet – In this part of the lab you will be sorting on multiple columns, adding subtotals and then applying filters to the data. Most of this content was covered in chapter 4.

Caution – You must select the correct worksheet area before sorting. If you don’t then you will corrupt the data and end up with multiple subtotals in each category.

1. Copy the Revenue worksheet to a new tab and name it Filtered
2. Change the tab color to Blue, Accent 1, Lighter 60%
3. Change the text in cell A1 to Filtered
4. Change background fill color the cell A1 to Blue, Accent 1, Darker 25%
5. Change background fill color in cells A3:H3 to Blue, Accent 1, Lighter 40%
6. Change background fill color in cells A4:H430 to No Fill
7. Clear the cell contents for the totals and adjacent text at the bottom of the data rows in the Filtered worksheet so only the sales remain. 
8. Add a filters to the worksheet data using the column headings in row 3 of the Filtered worksheet.
9. Using the Sort button on the ribbon, add a Custom Sort and sort the data by the Category Name in ascending order (A to Z)
10. Add a Level for a secondary sort on the Quantity in ascending order (Smallest to Largest)
11. Add Subtotals for Order Total and Shipped Total with each change in Category Name and include Summary below the data.
    • Use the column headings in row 3 for subtotal command if prompted
12. Change the filter to only show the Category Meat/Poultry, Meat/Poultry Total , and Grand Total by selecting those values from the filter list see example
13. Add the range name Filtered_Total for the Grand Total in the Shipped Total column. The scope of the range name should be the workbook and not restricted to a worksheet.

How is this assignment graded?

Upload file as Kyler.Attisha_Excel_Lab4.xlsx as the Excel Lab 4 assignment

Caution – Remove any circular references from your file.
Resubmit – This assignment CAN be resubmitted 1 time to improve your score.

Grading Rubric

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order