Power point, Excel , Word

FINAL EXAM - Excel

Note: If the function was not covered in class or in the textbook, it should not be part of your answer. DO NOT go to the internet for solutions. Insert the following table into Sheet1 of a new Excel Workbook, keeping the cell references consistent.

1. Copy the following table into a new Excel spreadsheet. Be sure to keep the cell data the same.

A B C

1 Order No. Customer Olive Oil Gallons Purchased

2 Ord-001 Walmart 2185

3 Ord-002 Winn Dixie 1982

4 Ord-003 SE Grocers 442

5 Ord-004 Aldi 1755

6 Ord-005 Sedanos 748

7 Ord-006 Piggly Wiggly

928

8 Ord-007 Target 825

9 Ord-008 Publix 1212

10

11 Pricing

12 Price for 500 gallons and below 25.74

13 Price for 1000 gallons and below 22.05

14 Price for more than 1000 gallons 19.51

2. Save the workbook as yourname_Q2 3. Place a box around cells A11:B14 4. Color the background to these cells

a. You choose the color 5. Add 2 rows at the top of the worksheet 6. At A1 enter the title: Goya Corporation

CALCULATIONS 7. Add a column after Olive Oil Gallons Purchased 8. Add the column title: Order Total at cell D3 9. Add the column title: Distinction at cell E3 10. Find the order totals for each Order

a. Use an IF function and ABSOLUTE cell reference to determine the Total based on the quantity purchased

b. Use the values in cells B14:B16 c. Be sure to copy the same formula to ALL of the cells in the column

11. Center the title (A1) across the table 12. Change the Font, Font Size and Color of your Title

a. You Choose 13. Center and Bold the column titles 14. Use the AutoFit Option to fix the column widths 15. Change the font, font size and color of table cells

a. You choose 16. Format the table to your liking. 17. Adjust column widths again

18. Change the name of the worksheet to Olive Oil a. Color the tab RED

19. Change the page orientation to Landscape 20. Be sure to format your numbers appropriately CONDITIONAL FORMAT 21. Use conditional format on the cells that have a quantity of 1375 gallons and greater

a. format them with a Blue Background and Bold White letters GRAPH 22. Create a Column Chart showing the Customer and Order Total 23. Move the Graph to a new worksheet

a. Call the worksheet Sales Totals i. color the tab RED

b. ADD axis titles c. ADD data labels d. Add a title for the chart

FINISHING TOUCHES 24. From the tab with the table, add your name and student ID number in the header 25. Re-Order the tabs so the chart is the 2nd worksheet

26.Change the Document Properties a. Place your Name b. Subject: CGS1030-SumB

c. TAGS – minimum of 3, separated by commas i. Initials are not key words

ii. Excel, final and exercise are also NOT keywords.