MS Excel 2016 Exam 2 Study Guide Report, Fall 2018 - Kaur, Ipinder

This document presents a comprehensive study guide for an MS Excel 2016 exam, completed by a student named Ipinder Kaur on October 21, 2018. The guide includes a detailed report of various tasks and their results, covering a wide range of Excel functionalities. These functionalities include entering formulas with absolute cell references, utilizing arithmetic operators, and employing functions like IF, MAX, PMT, VLOOKUP, and ROUND. It also covers using Quick Analysis tools, creating and editing data bars, working with chart elements, applying conditional formatting, splitting and freezing worksheets, using the AutoFill feature, inserting sparklines, and performing what-if analyses with Goal Seek. Furthermore, the guide demonstrates the creation and manipulation of charts, tables, and pivot tables, including filtering, sorting, and adding calculated fields. The report meticulously details the student's performance on each task, indicating whether the task was completed correctly or incorrectly, providing a valuable resource for exam preparation and skill enhancement in Excel 2016.

Sunday, October 21, 2018
Student: Kaur, Ipinder
Assignment:
Assignment Date: 10/21/2018
Score(%): 80
Performance Tasks
Subject Task ID Task Result Remediation Training
MS Excel 2016 4845 Enter a formula in cell C4 that
divides the value in cell B4 by the
value in cell B12, using an absolute
cell reference to cell B12.
Correct • Exploring Cell References Create a formula using
absolute cell
references.
MS Excel 2016 4847 Enter a formula using arithmetic
operators and parentheses in cell
B10 that adds the grant amounts in
cells B4, B5, B6, and B7, and then
multiplies that result by 12.
Correct • Making Workbooks User-Friendly Create a formula using
order of operations.
MS Excel 2016 4850 Enter a formula in cell F4 using the
IF function that returns a value of
YES if cell E4 is greater than 2, and
a value of NO if not.
Correct • Using Defined Names in Formulas
• Working with Logical Functions
Create a formula using
the IF function.
MS Excel 2016 4851 In cell B12, create a formula using
the MAX function to calculate the
maximum value in the range B4:B9.
Correct • Using Excel Functions Create a formula using
the MAX function.
MS Excel 2016 4853 In cell H6, create a formula using
the PMT function to calculate the
monthly loan payment using interest
rate, and loan period values in cells
B6:F6. Enter the formula with a -
(negative) sign to return a positive
value. Do not specify values for the
optional arguments fv or type.
Correct • Introduction to Financial Functions Create a formula using
the PMT function.
Exam 2 Fall 2018
Launch All Training
Exam Study Guide Report Page 1 of 6
Exam Study Guide

Subject Task ID Task Result Remediation Training
MS Excel 2016 4854 In cell B10, use the Quick Analysis
tool to create a formula that uses
the SUM function to total the values
in the range B4:B9.
Incorrect • Summarizing Data with the Quick
Analysis Tool
Create a formula using
the Quick Analysis Tool.
MS Excel 2016 4857 In cell E5, create a formula using
the VLOOKUP function to determine
the dog type for teen Anna Morante
based on the Program Code listed in
cell D5. Use range G4:H7 as the
lookup table and the dog type listed
in column 2 of the lookup table. Do
not enter a value for the optional
range_lookup argument.
Correct • Using LOOKUP Functions
• Using Lookup Functions
Create a formula using
the VLOOKUP function.
MS Excel 2016 4956 Add Solid Fill Orange Data Bars to
range C4:C10.
Correct • Creating Data Bars Create a Data Bars
conditional formatting
rule.
MS Excel 2016 4959 Add a chart title chart element to
the Bar chart at the default location.
Correct • Working with Chart Elements Add a chart element.
MS Excel 2016 4961 On the Bar chart, add the title
Funding Organization to the
vertical axis and the title Dollars to
the horizontal axis.
Correct • Creating a Combination Chart Add axis titles to a
chart.
MS Excel 2016 4963 Split the worksheet into panes at cell
G1.
Correct • Splitting the Worksheet Window into
Panes
Split a worksheet into
panes.
MS Excel 2016 4964 Freeze column A and rows 1 through
3 in the worksheet.
Correct • Freezing Rows and Columns Freeze columns and
rows.
MS Excel 2016 4969 Apply chart Style 13 to the chart. Correct • Working with Chart Elements Apply a chart style.
MS Excel 2016 4985 Use the fill handle to copy the
formula in cell B11 to cells C11 and
D11.
Correct • AutoFilling Formulas and Data Use the Fill handle to
copy a formula.
MS Excel 2016 4987 Insert a Line type sparkline in cell E4
using the Data Range B4:D4.
Correct • Creating Sparklines Insert a sparkline.
MS Excel 2016 4993 Use Goal Seek to calculate the
changing value in cell B12 that will
result in a set value in cell B19 of $0
(zero).
Correct • Performing What-If Analyses Use Goal Seek.
Exam Study Guide Report Page 2 of 6
Exam Study Guide

Subject Task ID Task Result Remediation Training
MS Excel 2016 5062 Use the AutoFill feature to fill the
range A4:A15 with the names of the
months, in chronological order,
starting with January in cell A4.
Correct • AutoFilling Formulas and Data Enter a series of values
using Autofill.
MS Excel 2016 5208 In cell B5, create a formula using
the ROUND function that rounds the
value in cell G19 to an integer, with
0 (zero) decimal places.
Correct • Using Excel Functions Create a formula using
the ROUND function.
MS Excel 2016 5316 Insert a 2-D Line chart in the
worksheet based on range A3:E7.
Correct • Creating a Line Chart Insert a Line chart.
MS Excel 2016 5325 For range B4:B8, define 100000 as
the maximum data bars value. Do
not use a currency symbol when
entering the value.
Correct • Creating Data Bars Edit a Data Bar
conditional formatting
rule.
MS Excel 2016 5329 In the Stacked Column chart, define
range B3:E3 as the Horizontal Axis
Label values. Use the mouse to
enter the range in the Axis Labels
text box.
Incorrect • Editing a Chart Data Source Change the Horizontal
Axis Label values used
in a chart.
MS Excel 2016 5504 Add a total row to the Financials
table and display totals, using the
Sum function, for columns B and D.
Incorrect • Using the Total Row to Calculate
Summary Statistics
Add a total row to a
table.
MS Excel 2016 5506 Remove duplicate records from all
columns of the Grants table.
Incorrect • Maintaining Data in an Excel Table Delete duplicate
records from a table.
MS Excel 2016 5507 Insert a table, with headers, using
cells A3:H9 as the table range.
Incorrect • Creating an Excel Table Insert a table.
MS Excel 2016 5509 Filter the Matchups table to display
only records with a value of Level 1
in the Level field.
Correct • Filtering Data Filter a table based on
a single field.
MS Excel 2016 5510 Using a custom AutoFilter, filter the
Matchups table to display only those
records whose Level value is greater
than 3.
Correct • Using Advanced Filters
• Filtering Data
Filter a table based on
a custom AutoFilter.
MS Excel 2016 5511 Apply Table Style Medium 14
(Medium section, row 2, last option)
to the table.
Correct • Creating an Excel Table Apply a table style.
Exam Study Guide Report Page 3 of 6
Exam Study Guide

Subject Task ID Task Result Remediation Training
MS Excel 2016 5512 Sort the Matchups table first on the
Breed field in ascending (A to Z)
alphabetical order, and then on the
Level field in descending (Largest to
Smallest) numerical order.
Incorrect • Sorting Data Sort a table based on a
custom sort order.
MS Excel 2016 5513 Insert subtotals, using the Sum
function, for each category in the
Level field, sorted Largest to
Smallest, in range A3:H13. The
subtotals should add the values in
the Weeks Completed field for each
Level category, and then include a
grand total.
Incorrect • Inserting Subtotals Insert subtotals.
MS Excel 2016 5514 Add a new record to the end of the
Matchups table, entering the
following data: type Joe in cell A13,
type Harrison in cell B13, type
Tyler in cell C13, type Pug in cell
D13, and type Pet in cell E13. Press
ENTER when done.
Correct • Maintaining Data in an Excel Table Add a record to the end
of a table.
MS Excel 2016 5515 Convert the Matchups table in range
A3:H13 to a range.
Correct • Inserting Subtotals Convert a table to a
range.
MS Excel 2016 5516 Delete the table row with the record
starting in cell A8. Do not delete any
worksheet data outside of the table.
Correct • Maintaining Data in an Excel Table Delete a row from a
table.
MS Excel 2016 5535 Refresh the data in the Matchups
PivotTable located in the PivotTable
worksheet.
Correct • Refreshing a PivotTable Refresh PivotTable
data.
MS Excel 2016 5537 Use the Level slicer to filter the
PivotTable to display only data for
Level 2 and Level 3.
Incorrect • Filtering a PivotTable Filter data in a
PivotTable using a
Slicer.
MS Excel 2016 5538 Create a calculated field in the
Expenses PivotTable, naming the
field Q1, that totals the values in the
January, February, and March fields.
Correct • Creating a Recommended PivotTable Add a calculated field
to a PivotTable.
MS Excel 2016 5539 Add a slicer to the Matchups
PivotTable to filter the data by Level.
Correct • Filtering a PivotTable Insert a slicer into a
PivotTable.
Exam Study Guide Report Page 4 of 6
Exam Study Guide

Subject Task ID Task Result Remediation Training
MS Excel 2016 5541 Add the Breed field to the Matchups
PivotTable in the COLUMNS area of
the PivotTable Fields task pane.
Correct • NP MS Excel 2016 Comp Add a field to the
COLUMNS area of the
PivotTable Fields task
pane.
MS Excel 2016 5542 Apply the Pivot Style Medium 21
style to the Matchups PivotTable
(Medium section, row 3, last option).
Correct • Creating a PivotTable Apply a style to a
PivotTable.
MS Excel 2016 5543 Change the layout of the Matchups
PivotTable to the Outline layout.
Correct • Creating a PivotTable Change the layout of a
PivotTable.
MS Excel 2016 5545 Use the PivotTable command on the
Insert tab to insert a PivotTable in a
new worksheet using the Matchups
table as the source range.
Correct • Creating a PivotTable Insert a PivotTable.
MS Excel 2016 5548 In the PivotTable, change the
number format of the Sum of
February field to the Accounting
number format with zero decimal
places.
Incorrect • Creating a PivotTable Change the number
format of a PivotTable
VALUE field.
MS Excel 2016 5550 Use the Recommended PivotTables
command to insert a blank
PivotTable in a new worksheet using
the Matchups table as the source
range.
Incorrect • Creating a Recommended PivotTable Insert a Recommended
PivotTable.
MS Excel 2016 5551 Add the Level field to the FILTERS
area of the PivotTable Fields task
pane.
Correct • Filtering a PivotTable Add a field to the
FILTERS area of the
PivotTable Fields task
pane.
MS Excel 2016 5792 Add the Type field to the ROWS
area of the PivotTable Fields task
pane.
Correct • Creating a PivotTable Add a field to the
ROWS area of the
PivotTable Fields task
pane.
MS Excel 2016 6018 Insert a Clustered Column
PivotChart in the current worksheet.
Correct • Creating a PivotChart Insert a PivotChart.
MS Excel 2016 6019 Filter the PivotChart on the Title
field to display only records with a
value of Dog Trainer.
Correct • Creating a PivotChart Filter a PivotChart.
Exam Study Guide Report Page 5 of 6
Exam Study Guide

Subject Task ID Task Result Remediation Training
MS Excel 2016 6045 Resize the slicer so the top-left
corner is in cell D3 and the bottom-
right corner is in cell F10
(approximately 1.5" high and 1.6"
wide).
Correct • Filtering a PivotTable Resize a slicer.
MS Excel 2016 6051 Move the slicer so that the upper-
left corner is in cell D3.
Correct • NP MS Excel 2016 Comp Move a slicer on a
worksheet.
MS Excel 2016 6415 In cell E17, enter a formula using
the MEDIAN function to calculate
the median value in range E4:E14.
Correct • Using Excel Functions Create a formula using
the MEDIAN function.
MS Excel 2016 6418 Apply the Currency format with 0
(zero) decimal places to the Bar
chart data labels, and then close the
task pane.
Correct • Working with Chart Elements Edit the number format
of data labels in a
chart.
Exam Study Guide Report Page 6 of 6
Exam Study Guide