Excel_INTRO_CAP_Sales_AS_Instructions
.docx
keyboard_arrow_up
School
Bucks County Community College *
*We aren’t endorsed by this school
Course
100
Subject
Information Systems
Date
Jan 9, 2024
Type
docx
Pages
4
Uploaded by brexo3
Grader - Instructions
Excel 2019 Project
Excel_INTRO_CAP_Sales_AS
Project Description:
In this project, you will unhide a worksheet, work with grouped worksheets, insert and rename worksheets, insert summary, logical, date, statistical and date functions, and refer to cells in other worksheets. Additionally, you will apply conditional formatting, create and modify charts, and create, sort, and filter Excel tables.
Steps to Perform:
Step
Instructions
Points Possible
1
Start Excel. Download and open the file named Student_Excel_Intro_CAP_Sales_AS.xlsx
. Save the file as Last_First_Excel_Intro_CAP_Sales_AS
.
0
2
Group the worksheets. Adjust the column width of B:F to 9. Center and
Wrap the text in the range B3:F3.
3
3
With the four sheets grouped, in the Year Total row, insert a function that will sum Years 1 - 4. Insert a function in the Yearly Total column to
total each row through Year Total.
4
4
With the four sheets grouped, apply the Currency [0] cell style, to the range B4:F4 and B17:F17. Apply the Total cell style to the Year Total results. Save the file.
3
5
With the four sheets grouped, insert a function to calculate the average
for each year in the range B19:E19. In the range B20:E20, insert a function to calculate the highest sales for each year. Ungroup the worksheets.
8
6
Insert a new worksheet. Rename the worksheet tab Summary and then change the color of the worksheet tab to Orange, Accent 1. Move the Summary sheet to the left of the Downtown worksheet tab.
2
7
On the Downtown worksheet, copy the range A1:F3. Paste the copied range to the Summary worksheet in the range A1:F3, keeping source column widths.
2
8
On the Summary sheet, in cell A2, replace the existing text with Yearly
Sales
; in cell A3, replace the existing text with Location
; in cell A4, type Downtown
; in cell A5, type Midtown
; in cell A6, type Seaside
; in cell A7, type TownCenter
; and in cell A8 type Total Sales
.
2
9
On the Summary sheet, in cell B4, create a formula that will display the
total from cell B17 on the Downtown worksheet. In cell B5, create a formula that will display the value from cell B17 on the Midtown worksheet. In cell B6, create a formula that will display the value from 5
Created On: 04/07/2021
1
SK19_XL_INTRO_GRADER_CAP_AS - Sales 1.2
Grader - Instructions
Excel 2019 Project
Step
Instructions
Points Possible
cell B17 on the Seaside worksheet. In cell B7, create a formula that will display the value from cell B17 on the Town Center worksheet. AutoFill the range B4:B7 to the right through column F. If necessary, AutoFit column F. Save the file.
10
On the Summary sheet, insert a function in the Total Sales row to sum each column. Apply the Total cell style to the Total Sales results. Apply
the Comma [0] cell style, to the range B5:F7. If necessary, AutoFit columns to fit all data.
4
11
On the Summary sheet, in cell H2 type 75,000 apply Align Center and Currency with 0 decimal places. Type Bonus in H3. In cell H4, enter an IF function that will display the value 1500 if the value in cell F4 is greater than the value in H2. Otherwise, the function will return the value 500
. In the function, use an absolute cell reference to H2. Apply Currency with 0 decimal places to the result in cell H4 and Align Center. AutoFill cell H4 down through cell H7.
4
12
On the Summary sheet, in the range G4:G7 insert Column Sparklines to compare the Year data for each location. Apply the Sparkline Style Colorful #6 (last option in the thumbnails). Note, depending on the version of Office you are using, the style may be named Brown, Sparkline Style Colorful #6. Show the High point for each Sparkline. Save the file.
3
13
Unhide the Stock worksheet. Group the worksheets. Make cell A3 the active cell. Find any occurrence of TownCenter
and replace it with Town Center
In the grouped worksheets correct any spelling errors, ignoring the spelling of the wines. Ungroup the worksheets.
2
14
On the Summary sheet, in cell A10, insert the TODAY function. Apply the date format March 14, 2012.
3
15
On the Summary sheet, in B4:E7 apply the Gradient Fill Orange Data Bar conditional formatting.
3
16
On the Summary sheet, create a 3-D Clustered Bar chart using the range A3:E7. Move the chart to a new sheet and rename the sheet Sales Chart Change the layout to Layout 8. Switch the Row and Column data. Change the chart title to Sales by Location
. Change the
vertical axis title to Location and the horizontal axis title to Sales Save
the file.
9
17
On the Summary sheet, insert a 3-D Pie Chart using the range A3:E3 and A8:E8. Move the chart so that the upper left corner of the chart is 5
Created On: 04/07/2021
2
SK19_XL_INTRO_GRADER_CAP_AS - Sales 1.2
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help