1 Open the file Student_Excel_6E_Hires.xlsx downloaded with this project. 2 On the first worksheet, select the data in t
Posted: Fri May 20, 2022 5:08 pm
1
Open the
file Student_Excel_6E_Hires.xlsx downloaded with
this project.
2
On the first worksheet, select the data in the range B4:F10, and
then use the Quick Analysis tool to insert Sparklines using the
Line format. Place the sparklines in the range adjacent to the Year
5 column, show the High Point and Last Point. Apply a sparkline
style using the first sparkline style in the first row.
(Mac users: Use the Insert Line Sparkline command on the Insert
tab.)
3
Type Trend in the cell above the
sparklines.
4
Using the data for the years and for each location (not the
totals) and the Recommended Charts command, create a Clustered
Column chart on a separate chart sheet named Hires
by Location Chart
5
Display the Change Chart Type dialog box. On the All Charts tab,
if necessary, on the left click Column, and then on the right, at
the top of the dialog box, click 3-D Clustered Column. Click
OK.
Mac users: Instead of the Change Chart Type dialog box, use Change
Chart Type button on the ribbon.
6
Apply Chart Style 5 to the chart, and then add a chart title
above the chart with the
text Job Hires by
Location Set the title’s font size to 28.
7
Format the Chart Area with a solid fill—in the first column,
click the third color. Format the Plot Area with a solid fill two
shades darker—in the first column, click the fifth color.
8
Format the floor and the side wall with a solid color—in the
next to last column, click the last color. Set the transparency for
both at 60%. Add the chart element Axis Title to the vertical axis
with the text Number of Attendees
Hired and change the font size to 16. Click outside
of the chart to deselect it.
9
On the Job Hires by Location worksheet, using the data for
Dallas including the years , insert a Line with
Markers line chart. Resize the chart to position the upper left
corner within cell A13 and the lower right corner within cell
G26.
10
Change the chart title to Successful Job Hires in
Dallas and set the title’s font size to 16. Format
the Vertical (Value) Axis so that the Minimum value is 100 and the
Major unit is 20
11
Add a Linear Trendline. Format the trendline by applying a
color—in the sixth column, click the last color. Set the Width of
the line to 2 pt. Click cell A1.
12
Display the List Chart worksheet. In cell A1,
type Three Largest Communities We Serve
Merge and center this title across the range A1:G1 and apply the
Title cell style.
13
Insert a SmartArt graphic using the Vertical Box List. In the
three boxes, type, in
order, Dallas, Fort
Worth, and Carrollton
14
Resize the graphic to position the upper left corner in cell A3
and the lower right corner in cell G16. Apply the Inset style and
change the colors to Colorful Range – Accent Colors 4 to 5. Click
cell A1 to deselect the graphic. Display the Funnel Chart
worksheet. By using the data in the range A3:B5, insert a funnel
chart. Apply the third Chart Style. As the Chart Title,
type Year 5 Candidate Hires. Display the
Format Axis pane, and then click the Fill & Line icon. Format
the Line using a solid line and then in the color gallery, in the
sixth column, click the last color. Set the line Width to 2 pt and
then close the pane.
15
Display the Format Chart Area pane, and then apply a 2 pt Solid
line border using same color as the axis line. Close the pane. Drag
the upper left corner of the chart into cell A6, positioned
slightly below the G in Granted. Click cell A1 to deselect the
chart. Display the Hires by Location Chart sheet. From the Page
Layout tab, use the Dialog Box Launcher to display the Page Setup
dialog box. Insert a Custom Footer with the File Name in the Left
section.
16
Display the Job Hires by Location sheet. Hold down CONTROL and
select the remaining two worksheets to group the three sheets.
Insert a Custom Footer with the File Name in the Left section, and
then center the sheets horizontally.
17
Click the Hires by Location Chart sheet tab to make it the
active worksheet and to ungroup the worksheets. Ensure the
worksheets are in this order: Hires by Location Chart, Job Hires by
Location, List Chart, and Funnel Chart. Save and close the file,
and then submit for grading.
Open the
file Student_Excel_6E_Hires.xlsx downloaded with
this project.
2
On the first worksheet, select the data in the range B4:F10, and
then use the Quick Analysis tool to insert Sparklines using the
Line format. Place the sparklines in the range adjacent to the Year
5 column, show the High Point and Last Point. Apply a sparkline
style using the first sparkline style in the first row.
(Mac users: Use the Insert Line Sparkline command on the Insert
tab.)
3
Type Trend in the cell above the
sparklines.
4
Using the data for the years and for each location (not the
totals) and the Recommended Charts command, create a Clustered
Column chart on a separate chart sheet named Hires
by Location Chart
5
Display the Change Chart Type dialog box. On the All Charts tab,
if necessary, on the left click Column, and then on the right, at
the top of the dialog box, click 3-D Clustered Column. Click
OK.
Mac users: Instead of the Change Chart Type dialog box, use Change
Chart Type button on the ribbon.
6
Apply Chart Style 5 to the chart, and then add a chart title
above the chart with the
text Job Hires by
Location Set the title’s font size to 28.
7
Format the Chart Area with a solid fill—in the first column,
click the third color. Format the Plot Area with a solid fill two
shades darker—in the first column, click the fifth color.
8
Format the floor and the side wall with a solid color—in the
next to last column, click the last color. Set the transparency for
both at 60%. Add the chart element Axis Title to the vertical axis
with the text Number of Attendees
Hired and change the font size to 16. Click outside
of the chart to deselect it.
9
On the Job Hires by Location worksheet, using the data for
Dallas including the years , insert a Line with
Markers line chart. Resize the chart to position the upper left
corner within cell A13 and the lower right corner within cell
G26.
10
Change the chart title to Successful Job Hires in
Dallas and set the title’s font size to 16. Format
the Vertical (Value) Axis so that the Minimum value is 100 and the
Major unit is 20
11
Add a Linear Trendline. Format the trendline by applying a
color—in the sixth column, click the last color. Set the Width of
the line to 2 pt. Click cell A1.
12
Display the List Chart worksheet. In cell A1,
type Three Largest Communities We Serve
Merge and center this title across the range A1:G1 and apply the
Title cell style.
13
Insert a SmartArt graphic using the Vertical Box List. In the
three boxes, type, in
order, Dallas, Fort
Worth, and Carrollton
14
Resize the graphic to position the upper left corner in cell A3
and the lower right corner in cell G16. Apply the Inset style and
change the colors to Colorful Range – Accent Colors 4 to 5. Click
cell A1 to deselect the graphic. Display the Funnel Chart
worksheet. By using the data in the range A3:B5, insert a funnel
chart. Apply the third Chart Style. As the Chart Title,
type Year 5 Candidate Hires. Display the
Format Axis pane, and then click the Fill & Line icon. Format
the Line using a solid line and then in the color gallery, in the
sixth column, click the last color. Set the line Width to 2 pt and
then close the pane.
15
Display the Format Chart Area pane, and then apply a 2 pt Solid
line border using same color as the axis line. Close the pane. Drag
the upper left corner of the chart into cell A6, positioned
slightly below the G in Granted. Click cell A1 to deselect the
chart. Display the Hires by Location Chart sheet. From the Page
Layout tab, use the Dialog Box Launcher to display the Page Setup
dialog box. Insert a Custom Footer with the File Name in the Left
section.
16
Display the Job Hires by Location sheet. Hold down CONTROL and
select the remaining two worksheets to group the three sheets.
Insert a Custom Footer with the File Name in the Left section, and
then center the sheets horizontally.
17
Click the Hires by Location Chart sheet tab to make it the
active worksheet and to ungroup the worksheets. Ensure the
worksheets are in this order: Hires by Location Chart, Job Hires by
Location, List Chart, and Funnel Chart. Save and close the file,
and then submit for grading.