BUS 1350 EXAM 2 QUESTIONS &
VALID COMPLETE SOLUTIONS
How do you create a worksheet group consisting of sheets that are not adjacent in a
workbook - Correct Answer ✔✔ Press
and hold CTRL as you click each sheet tab that you want to add to the worksheet
group.
2. Can a worksheet have only one window - Correct Answer ✔✔ No; a single workbook
can have multiple workbook windows.
3. How do you ungroup a worksheet group that consists of all the sheets in the
workbook - Correct Answer ✔✔ Click the
sheet tab of any worksheet in the group.
4. What is 3-D cell reference to cell C20 in the Monday worksheet - Correct Answer ✔✔
Monday!C20
5. What is the absolute 3-D cell reference to cell C20 in the Monday worksheet - Correct
Answer ✔✔ Monday!$C$20
6. What is the 3-D cell reference to cell C20 in the Monday through Friday worksheet
group - Correct Answer ✔✔ Monday:Friday!C20
7. Write formula that uses the MAX function to calculate the maximum value of cell C20
of the
Monday through Friday worksheet group - Correct Answer ✔✔ =MAX(Monday:Friday!
C20)
8. What is the external reference to cell C20 of the Final Report worksheet located in
the Annual
Statement.xlsx workbook - Correct Answer ✔✔ '[Annual Report.xlsx]Final Report'!C20
9. What is the external reference to cell D10 of the Sunday worksheet located in the
Weekly Report.xslx
workbook that is stored in the C:\Documents\Reports folder - Correct Answer ✔✔ '[C:\
Documents\Reports\Weekly
Report.xlsx]Sunday'!D10
10. When would you paste a copied cell using the Paste Link option - Correct Answer
✔✔ When you want to paste the reference
to the cell and not the cell's value or format
, 11. How do you check the status of a link within the current workbook to determine
whether the link's
source file is accessible and up to date - Correct Answer ✔✔ On the Data tab, in the
Queries & Connections group, click the
Edit Link button to open the Edit Links dialog box, and then view the link's status in the
Status column.
12. How does Excel indicate that a cell contains linked text - Correct Answer ✔✔ The
text is displayed in green and underlined.
13. What does Excel do when a hyperlink is clicked by the user - Correct Answer ✔✔ It
opens an application associated with the
type of link.
1. Why is Report-Date not a valid names range - Correct Answer ✔✔ It contains a dash
in the name.
2. What happens when you select a defined name in the Name box - Correct Answer
✔✔ Excel selects the cell or cell range
referenced by the defined name.
6. What is displayed in the title bar for a workbook created from the EmployeeList.xltx
template file - Correct Answer ✔✔ EmployeeList1
A school gives out grades in the following ranges: F: 0 - < 60; D: 60 - < 70; C: 70 - < 80;
B: 80 - < 90; A: 90 - 100. Create a vertical lookup table for this grade scale with the
range values in the first column and the letter grades in the second.
0F
60 D
70 C 80 B
90 A
What is the function to retrieve the letter grade stored in the second column of the
GradeScale table using a lookup value of 83 in an approximate match lookup?
=VLOOKUP(83, GradeScale, 2)
What is the function to retrieve the value located in the fifth row and second column of
the data in the range A10:G50 - Correct Answer ✔✔ =INDEX(A10:C50, 5, 2)
What is the function to return the index number of the cell within the range E1:E50 that
is equal to "Carter", using an exact match - Correct Answer ✔✔ =MATCH("Carter",
E1:E50, 0)
What are two advantages of index matches over the VLOOKUP and HLOOKUP
functions - Correct Answer ✔✔ You can do lookups in an approximate match lookup
with the values sorted in descending and ascending order. You can do searches based
on any row or column from the lookup table.
VALID COMPLETE SOLUTIONS
How do you create a worksheet group consisting of sheets that are not adjacent in a
workbook - Correct Answer ✔✔ Press
and hold CTRL as you click each sheet tab that you want to add to the worksheet
group.
2. Can a worksheet have only one window - Correct Answer ✔✔ No; a single workbook
can have multiple workbook windows.
3. How do you ungroup a worksheet group that consists of all the sheets in the
workbook - Correct Answer ✔✔ Click the
sheet tab of any worksheet in the group.
4. What is 3-D cell reference to cell C20 in the Monday worksheet - Correct Answer ✔✔
Monday!C20
5. What is the absolute 3-D cell reference to cell C20 in the Monday worksheet - Correct
Answer ✔✔ Monday!$C$20
6. What is the 3-D cell reference to cell C20 in the Monday through Friday worksheet
group - Correct Answer ✔✔ Monday:Friday!C20
7. Write formula that uses the MAX function to calculate the maximum value of cell C20
of the
Monday through Friday worksheet group - Correct Answer ✔✔ =MAX(Monday:Friday!
C20)
8. What is the external reference to cell C20 of the Final Report worksheet located in
the Annual
Statement.xlsx workbook - Correct Answer ✔✔ '[Annual Report.xlsx]Final Report'!C20
9. What is the external reference to cell D10 of the Sunday worksheet located in the
Weekly Report.xslx
workbook that is stored in the C:\Documents\Reports folder - Correct Answer ✔✔ '[C:\
Documents\Reports\Weekly
Report.xlsx]Sunday'!D10
10. When would you paste a copied cell using the Paste Link option - Correct Answer
✔✔ When you want to paste the reference
to the cell and not the cell's value or format
, 11. How do you check the status of a link within the current workbook to determine
whether the link's
source file is accessible and up to date - Correct Answer ✔✔ On the Data tab, in the
Queries & Connections group, click the
Edit Link button to open the Edit Links dialog box, and then view the link's status in the
Status column.
12. How does Excel indicate that a cell contains linked text - Correct Answer ✔✔ The
text is displayed in green and underlined.
13. What does Excel do when a hyperlink is clicked by the user - Correct Answer ✔✔ It
opens an application associated with the
type of link.
1. Why is Report-Date not a valid names range - Correct Answer ✔✔ It contains a dash
in the name.
2. What happens when you select a defined name in the Name box - Correct Answer
✔✔ Excel selects the cell or cell range
referenced by the defined name.
6. What is displayed in the title bar for a workbook created from the EmployeeList.xltx
template file - Correct Answer ✔✔ EmployeeList1
A school gives out grades in the following ranges: F: 0 - < 60; D: 60 - < 70; C: 70 - < 80;
B: 80 - < 90; A: 90 - 100. Create a vertical lookup table for this grade scale with the
range values in the first column and the letter grades in the second.
0F
60 D
70 C 80 B
90 A
What is the function to retrieve the letter grade stored in the second column of the
GradeScale table using a lookup value of 83 in an approximate match lookup?
=VLOOKUP(83, GradeScale, 2)
What is the function to retrieve the value located in the fifth row and second column of
the data in the range A10:G50 - Correct Answer ✔✔ =INDEX(A10:C50, 5, 2)
What is the function to return the index number of the cell within the range E1:E50 that
is equal to "Carter", using an exact match - Correct Answer ✔✔ =MATCH("Carter",
E1:E50, 0)
What are two advantages of index matches over the VLOOKUP and HLOOKUP
functions - Correct Answer ✔✔ You can do lookups in an approximate match lookup
with the values sorted in descending and ascending order. You can do searches based
on any row or column from the lookup table.
