EXCEL CERTIFICATION STUDY 105
QUESTIONS WITH COMPLETE
SOLUTIONS!!
1 of 105
Definition
1. On the Data tab, in the Get & Transform Data group, click
From Text/CSV.(Hint: Comma-Delimited files and Tab-Delimited
files are types of Text files.)
2.In the Import Data pop-up window, browse to the
GMetrixTemplates folder.
3.Select the NewFeeds.txt file and click the Import button.
4. In the NewFeed.txt pop-up window, configure the following: File
Origin: accept the default - 1252: Western European
(Windows)Delimiter: TabData Type Detection: accept the default -
Base on first 200 rows
,5.Click the dropdown to the right of the Load button and select
Load To...
6.In the Import Data pop-up window, configure the following:Select
how you want to view this data in your workbook: TableWhere do
you want to put the data? New Worksheet
7.Click OK.
Give this one a try later!
Insert Page 1 of ? page numbering
Remove the conditional formatting
in the header of the Q1 Sales
from the table on on the Feed
worksheet. When finished, return to
Inventory worksheet.
Normal view.
Insert a Footer that displays today's Import NewFeeds.txt located
date on the right and then return in the GMetrixTemplates
to Normal view. folder as a table on a new
worksheet.
Don't know?
, 2 of 105
Definition
1. On the Qtr 2 worksheet, select cell range A4:E9
2.On the Home tab, in the Styles group, click Format as Table
to open the gallery.
3. Under the Medium section, click Orange, Table Style Medium 18.
4. In the Format As Table pop-up window, do the following:
a.Confirm the data field contains =$A$4:$E$9.
b. Confirm the My table has headers box is enabled.
c. Click OK.
Give this one a try later!
On the Qtr 2 worksheet,
convert cell range A4:E9 to a On the Charts worksheet, add an
table with headers. Apply ALT TEXT title Established Sports to
Orange, Table Style Medium the pie chart containing Football,
18. Baseball, and Basketball data.
On the Costs worksheet, repeat the Insert Page 1 of ? page numbering
rows containing the company in the header of the Q1 Sales
name and column headings so worksheet. When finished, return to
they Normal view.
appear on all printed pages.
Don't know?
3 of 105
Definition
, 1. On the Rentals worksheet, select the heading row, (A2:F2).
2.On the Home tab, in the Alignment group, click Wrap Text.
Give this one a try later!
On the Rentals worksheet,
configure the heading row On the Parts worksheet, Use Autofill
in the table so that entries to copy the formula in cell E4 to
wider than the column calculate the Total for January
wrap to through March for each employee.
multiple lines.
On the Costs worksheet, repeat the
On the Feed Inventory worksheet,
rows containing the company
remove the hyperlink
name and column headings so
functionality, but leave the text
they
in cell C27.
appear on all printed pages.
Don't know?
4 of 105
Definition
1. On the Sales by Club worksheet, hold down the CTRL key on
your keyboard and select all the blue cells in the Total Cookies
Sold
column, which are cell range I17:I20, I24:I25, and I36:I37.
2.With these cells selected, on the Formulas tab, in the
Defined Names group, click Name Manager.
3.On the Name Manager pop-up window, click the New... button.
4. In the New Name pop-up window, enter the Name: Blazing_Bikes.
5.Verify that the Refers To: field contains, ='Sales by
Club'!$I$17:$I$20,'Sales by Club'!$I$24:$I$25,'Sales by
Club'!$I$36:$I$37 If needed you can widen the New Name Window
to fully view the Refers To: field
QUESTIONS WITH COMPLETE
SOLUTIONS!!
1 of 105
Definition
1. On the Data tab, in the Get & Transform Data group, click
From Text/CSV.(Hint: Comma-Delimited files and Tab-Delimited
files are types of Text files.)
2.In the Import Data pop-up window, browse to the
GMetrixTemplates folder.
3.Select the NewFeeds.txt file and click the Import button.
4. In the NewFeed.txt pop-up window, configure the following: File
Origin: accept the default - 1252: Western European
(Windows)Delimiter: TabData Type Detection: accept the default -
Base on first 200 rows
,5.Click the dropdown to the right of the Load button and select
Load To...
6.In the Import Data pop-up window, configure the following:Select
how you want to view this data in your workbook: TableWhere do
you want to put the data? New Worksheet
7.Click OK.
Give this one a try later!
Insert Page 1 of ? page numbering
Remove the conditional formatting
in the header of the Q1 Sales
from the table on on the Feed
worksheet. When finished, return to
Inventory worksheet.
Normal view.
Insert a Footer that displays today's Import NewFeeds.txt located
date on the right and then return in the GMetrixTemplates
to Normal view. folder as a table on a new
worksheet.
Don't know?
, 2 of 105
Definition
1. On the Qtr 2 worksheet, select cell range A4:E9
2.On the Home tab, in the Styles group, click Format as Table
to open the gallery.
3. Under the Medium section, click Orange, Table Style Medium 18.
4. In the Format As Table pop-up window, do the following:
a.Confirm the data field contains =$A$4:$E$9.
b. Confirm the My table has headers box is enabled.
c. Click OK.
Give this one a try later!
On the Qtr 2 worksheet,
convert cell range A4:E9 to a On the Charts worksheet, add an
table with headers. Apply ALT TEXT title Established Sports to
Orange, Table Style Medium the pie chart containing Football,
18. Baseball, and Basketball data.
On the Costs worksheet, repeat the Insert Page 1 of ? page numbering
rows containing the company in the header of the Q1 Sales
name and column headings so worksheet. When finished, return to
they Normal view.
appear on all printed pages.
Don't know?
3 of 105
Definition
, 1. On the Rentals worksheet, select the heading row, (A2:F2).
2.On the Home tab, in the Alignment group, click Wrap Text.
Give this one a try later!
On the Rentals worksheet,
configure the heading row On the Parts worksheet, Use Autofill
in the table so that entries to copy the formula in cell E4 to
wider than the column calculate the Total for January
wrap to through March for each employee.
multiple lines.
On the Costs worksheet, repeat the
On the Feed Inventory worksheet,
rows containing the company
remove the hyperlink
name and column headings so
functionality, but leave the text
they
in cell C27.
appear on all printed pages.
Don't know?
4 of 105
Definition
1. On the Sales by Club worksheet, hold down the CTRL key on
your keyboard and select all the blue cells in the Total Cookies
Sold
column, which are cell range I17:I20, I24:I25, and I36:I37.
2.With these cells selected, on the Formulas tab, in the
Defined Names group, click Name Manager.
3.On the Name Manager pop-up window, click the New... button.
4. In the New Name pop-up window, enter the Name: Blazing_Bikes.
5.Verify that the Refers To: field contains, ='Sales by
Club'!$I$17:$I$20,'Sales by Club'!$I$24:$I$25,'Sales by
Club'!$I$36:$I$37 If needed you can widen the New Name Window
to fully view the Refers To: field
