introduction to microcomputers - excel exam
juicywang
excelexam2013.docx
with Microsoft Office 2013
Prepared Exam—Application for GO! with Microsoft Office 2013 Excel Chapters 1-3
Open the workbook in Excel called Excel_Exam2013.xlsx and save it as Lastname_Firstname_Excel_Exam.
Go to the FIRST Worksheet
1. In cells B3:D3 center the column titles. In cell B4 enter 56522.13. In cell C4 enter 64788.42. In cell D4 enter 69125.67.
2. In cell B8 enter a formula to sum the column. Repeat this process for columns C and D. Sum the rows in the range E4:E8.
3. Merge & Center the contents of cells A1:F1. Merge & Center the contents of cell A2:F2. Apply the Title style to both ranges. Apply the Comma Style to B4:E7. Apply the Total Style to B8:E8.
4. Insert a clustered column chart using the data in range A3:D7. Add the Chart Title First Quarter Shoe Sales and apply Chart Style 8 to the chart. Apply the Colorful Color 4 variation of the theme colors. Move the chart to the upper left corner of cell A10.
5. Insert a Line Sparklines for the range B4:D7 in F4:F7. Select the Markers and apply the Sparklines Style Accent 6, Darker 25%.
Go to the SECOND worksheet
6. Total Retail value is determined by multiplying the Quantity by the Retail Price. Enter a formula to calculate Total Retail value in cell F5. Copy the formula into the range F6:F8. In cell F9 sum up the Total Retail value column.
7. In cell G5 enter a formula to calculate the Percent of Total Retail value for Aerobic. Use absolute addressing in your formula. Copy G5 to the range G6:G8. In the cell range G5:G8, apply the Percent Style and increase the decimal to two places.
Go to the THIRD worksheet
8. In cell C11, type 1345 and Flash Fill the column. In cell H11, type Run and Flash Fill the column.
9. In cell F4, sum up the Quantity in Stock column. In cell F5 average the retail price column. In cell F6 find the median retail Price. In cell F7 find the minimum retail price of all shoes (cheapest shoes). In cell F8 find the maximum retail price of all shoes (most expensive shoes). You must use functions.
10. For the range F5:F8, apply the Accounting Number Format.
11. In B8, enter a formula to count the cells in the range G11:G39 that contain the criteria Womens.
12. In cell I11, enter an IF function to display Order if the Quantity in Stock amount is less than 90 and OK if it is not. Fill this formula down the column.
13. For the cell range A11:A39, apply the Custom Format of Bold Italic font with the font color to Green, Accent 6. Also apply the Data Bars Conditional Formatting. Set the Gradient Fill to Green.
14. In any cell in the data below row 12, click Create Table and apply Table Style Light 9.
15. Sort the table by the Retail Price Largest to Smallest. Filter the table to show only Womens shoes.
Go to Sheet1 & Sheet2 worksheets
16. Rename Sheet1 to Center Store and change the tab color to Orange. Rename Sheet2 to West Store and change the tab color to Green, Accent 6.
Go to the SUMMARY worksheet
Go to the FOURTH worksheet
18. Select the ranges A5:A8 and C5:C8 and insert a 3-D Pie chart in a new sheet named Income Chart.
19. Create a new chart title Shoe Sales formatted with the WordArt Styles group Gradient Fill - Gold, Accent 4, Outline - Accent 4 and Font Size 36. Remove the legend from the bottom of the chart and display the data labels of Category Name and Percentage Centered. Bold and italicize the font and set the font size to 20. Add the 3-D format to the data series with the Bevel Circle. Set the width and height of the Bottom and Top Bevel to 512 pt.
20. Set the Material setting to Plastic and add the Shadow effect below the circle. Adjust the angle of the first slice to 250. Set the Running slice to 10% Point Explosion and a gradient fill of Bottom Spotlight – Accent 6. Format the Chart Area with a Gradient fill of Bottom Spotlight – Accent 1 and a Blue – Gray, Text 2, 5 pt Border.
Copyright © 2014 Pearson Education, Inc. Publishing as Prentice Hall Page 2 of 2
