MIS 2223 Lecture Notes - Lecture 99: Microsoft Excel, Sparkline, Filename Extension
30 Apr 2018
School
Department
Course
Professor
Shelly Cashman Excel 2016 | Module 3: SAM Project 1a
Pick Up Motors
WORKING WITH LARGE WORKSHEETS, CHARTING, AND WHAT-IF ANALYSIS
GETTING STARTED
• Open the file SC_EX16_3a_FirstLastName_1.xlsx, available for download
from the SAM website.
• Save the file as SC_EX16_3a_FirstLastName_2.xlsx by changing the “1” to
a “2”.
o If you do not see the .xlsx file extension in the Save As dialog box, do
not type it. The program will add the file extension for you automatically.
• With the file SC_EX16_3a_FirstLastName_2.xlsx still open, ensure that
your first and last name is displayed in cell B6 of the Documentation worksheet.
o If cell B6 does not display your name, delete the file and download a new
copy from the SAM website.
PROJECT STEPS
1. Charlie is a salesman at Pick Up Motors car dealership. He is performing a
detailed analysis of his sales for the first half of 2018, including representing his
sales and commissions with graphs.
Switch to the 2018 Jan-Jun Sales worksheet, and then remove the panes from
the worksheet. (Hint: Deselect the Split option from the View tab.)
2. Freeze rows 1 and 2. (Hint: Select cell A3 before freezing panes.)
3. Change the width of column A to 31.00. (Hint: Do not use AutoFit.)
4. Select the range B4:D4 and, using the Fill Handle, fill the range E4:G4 with
consecutive months.
5. In cell H5, create a Column Sparkline based on the data in the range B5:G5.
Copy the Sparkline you created in cell H5 to the range H6:H9.
6. Using the Format Painter, copy the formatting from the range G5:G9 to the
range H5:H9.
7. Copy the contents of the range B4:G4 to the range B12:G12.
8. Apply the Percentage number format with zero decimal places to the range
B13:G13 and the nonadjacent cell B33.
9. Select the merged range A15:A27, and then rotate the cell contents to 90
degrees (Hint: The text should read from bottom to top).
10. In the stacked column chart (with the title Monthly Sales per Vehicle Type), add
Month as the primary horizontal axis title and Sales Amount as the primary
vertical axis title.
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
Working with large worksheets, charting, and what-if analysis. Open the file sc_ex16_3a_firstlastname_1. xlsx, available for download from the sam website. Save the file as sc_ex16_3a_firstlastname_2. xlsx by changing the 1 to a 2 . If you do not see the . xlsx file extension in the save as dialog box, do not type it. The program will add the file extension for you automatically. With the file sc_ex16_3a_firstlastname_2. xlsx still open, ensure that your first and last name is displayed in cell b6 of the documentation worksheet. If cell b6 does not display your name, delete the file and download a new copy from the sam website. Charlie is a salesman at pick up motors car dealership. He is performing a detailed analysis of his sales for the first half of 2018, including representing his sales and commissions with graphs. Switch to the 2018 jan-jun sales worksheet, and then remove the panes from the worksheet. (hint: deselect the split option from the view tab. )
