# If and Lookup Functions Charts Template

Document Sample

```					Microsoft® Office Excel® 2007:
Level 2
Lesson 1: Calculating Data with Advanced
Formulas
Topic 1-A: Manage Cell and Range Names

How to Manage Cell and Range Names

1-1        Multiple Choice
(Q)      Which Ribbon tab needs to be selected to name a range of cells within your worksheet?
A.   Home
B.   Insert
C.   Page Layout
D.   Formulas
E.   Review

1-2        Performance Based
(Q)      Calculate the total sales for the West Coast and display it in cell B12.

1-3        Multiple Choice
After creating a named range of cells in your worksheet, you decided you didn't need them
(Q)
named. Which steps would you take to delete the named range?
A.   Right-click and choose Delete.
Click the Formulas tab and within the Defined Names group, select Name A Range, select
B.
Delete, and click OK.
Click the Formulas tab and within the Defined Names group, select Name Manager, select the
C.
name range, click Delete, and click OK.
Click the Insert tab and within the Defined Names group, select Name A Range, select Delete,
D.
and click OK.
Topic 1-B: Calculate Data Across Worksheets

How to Calculate Data Across Worksheets

1-1        Multiple Choice
When adding the sums of several cells from different worksheets, which Ribbon tab would you
(Q)
need to select?
A.   Home
B.   Insert
C.   Formulas
D.   Page Layout
E.   View

1-2        Multiple Response
(Q)      How would you add the average from cells on different worksheets?
A.   Add the formula in the Formula Bar.
B.   Click the Function Wizard and select a formula.
C.   Right-click and choose Add Formula.
D.   Right-click and choose Format Cells.
E.   Click the Function Wizard in the Formula Bar area and select a formula.

Topic 1-C: Use Specialized Functions

How to Use Specialized Functions

1-1        Multiple Choice
(Q)      On the Formulas tab, where is the Date & Time drop-down menu located?
A.   Function Library
B.   Named Cells
C.   Formula Auditing
D.   Calculation

1-2        Performance Based
Using the 360-day method, calculate the number of days in the first quarter of the year, and
(Q)
display this in cell B7. Use \$B\$4 to determine the Start Date and B5 to determine the End Date.
1-3        Multiple Response
(Q)      Which of the following would be an appropriate formula to type in to make text a specific case?
A.   =LOWER()
B.   =UPPER()
C.   TEXT()
D.   =EXACT()

1-4        Multiple Response
In the Formula Bar, if you typed in =SUM(), how would you add the function arguments for this
(Q)
formula?
A.   Right-click and choose Expand Formula Bar.
B.   Type the specific cell coordinates inside the parentheses.
Click the Function Wizard button in the Function Arguments popup window and select the cells
C.
for the SUM field.
D.   Right-click and choose Function Arguments, and the select the cells for the SUM field.

Topic 1-D: Analyze Data with Logical and Lookup Functions

How to Analyze Data with Logical and Lookup Functions

1-1        Multiple Choice
(Q)      Which of the following is the formula for looking up data with the HLookup function?
A.   HLOOKUP (LookupValue, TableArray, RowIndexNumber)
B.   HLOOKUP (LookupValue, TableArray, ColumnIndexNumber)
C.   VLOOKUP (LookupValue, TableArray, RowIndexNumber)
D.   VLOOKUP (LookupValue, TableArray, ColumnIndexNumber)

1-2        Multiple Choice
(Q)      Which of the following best describes Lookup and Reference functions?
A.   A function that looks up data in the first worksheet in the workbook.
A function that finds data in another location based on data in the current worksheet. This
B.
information can then be displayed in a cell or used in a calculation.
C.   A function that references another function to provide data.
D.   A function that returns a row of data based on input parameters.
Lesson 2: Organizing Worksheet and Table Data
Topic 2-A: Create and Modify Tables

How to Create and Modify Tables

2-1        Multiple Choice
(Q)      What Ribbon tab would you select to add a table to your worksheet?
A.   Home
B.   Insert
C.   Page Layout
E.   Data

2-2        Multiple Response
(Q)      What would you click to add a table to your worksheet?
A.   Format As Table
B.   Table
C.   PivotTable
D.   Column
E.   Cell Styles

2-3        Performance Based
(Q)      Delete the last two rows in the table, and calculate the totals row.

2-4        Multiple Choice
(Q)      To delete a header row from your table, what is the first step?
A.   Check the Header Row check box.
B.   Uncheck the Header Row check box.
C.   Select the table.
D.   Right-click and choose Delete Header Row.
2-5        Multiple Response
If you select a cell within your worksheet, which options could you select from the Insert drop-
(Q)
down list?
A.   Insert Table Rows Above
B.   Insert Cells
C.   Insert Sheet Rows
D.   Insert Sheet Columns
E.   Insert Table Columns To The Left

Topic 2-B: Format Tables

How to Format Tables

2-1        Multiple Choice
After adding a table to a worksheet , which of the following are two Ribbon tabs that contain a
(Q)
function to format the style of a table?
A.   Home and Insert
B.   Home and the Table Tools Design Contextual tab
C.   Insert and the Table Tools Design Contextual tab
D.   Insert and Page Layout
E.   Insert and View

2-2        Performance Based
(Q)      Modify the table by adding banded columns.

2-3        Multiple Response
(Q)      Which of the following are steps that can be taken to customize the style of a table?
Click the Home tab, click Styles, click Format As Table, select New Table Style, and then select
A.
different formatting options.
Click the Page Layout tab, click Format As Table, select New Table Style, and then select
B.
different formatting options.
Click the Insert tab, click Format As Table, select New Table Style, and then select different
C.
formatting options.
Click the Design tab and in the Table Styles group, click More, and select New Table Style, and
D.
then select different formatting options.
Click the Insert tab, and in the Table Styles group, click More, and select New Table Style, and
E.
then select different formatting options.
2-4        Multiple Response
After creating a custom table style for your worksheet, you decide you don't like it and want to
(Q)      remove the custom table style. Which of the following are steps to remove the custom table
style?
A.   Right-click and choose Delete Custom Table Style.
Click Home, click Styles, click Format As Table, and then right-click the custom table style and
B.
choose Delete.
Click Design and in the Table Styles group, click More, and then right-click the custom table style
C.
and choose Delete.
D.   Click Insert, click Format As Table, and then right-click the custom table style and choose Delete.

Topic 2-C: Sort or Filter Worksheet or Table Data

How to Sort or Filter Worksheet or Table Data

2-1        Multiple Choice
(Q)      On which Ribbon tab is the option to sort located?
A.   Home
B.   Data
C.   Insert
D.   View
E.   Review

2-2        Multiple Choice
(Q)      How would you remove the filter on a column of your worksheet?
A.   Right-click and choose Delete.
B.   Right-click and choose Clear Contents.
C.   Click the drop-down menu within the header column and choose Clear Filter.
D.   Select the column and press Delete.

2-3        Multiple Choice
If you need to add a filter to a column of numbers and you want all the numbers that go up 30 at
(Q)
the top of the column, what would you choose?
A.   Filter->Number Filters->Less Than Or Equal To
B.   Filter->Number Filters->Equals
C.   Filter->Number Filters->Greater Than
D.   Filter->Number Filters->Greater Than Or Equal To
2-4        Performance Based
(Q)      Sort the transactions table in Newest to Oldest order based on the Date field.

Topic 2-D: Calculate Data in a Table or Worksheet

How to Calculate Data in a Table or Worksheet

2-1        Multiple Choice
After clicking the Function Wizard, which category from the drop-down menu would you need to
(Q)
choose to add the DSUM function?
A.   Date & Time
B.   Math & Trig
C.   Text
D.   Database
E.   Logical

2-2        Multiple Choice
(Q)      Within the Data tab of the Ribbon, which group contains the Subtotal function?
A.   Get External Data
B.   Sort & Filter
C.   Data Tools
D.   Outline

2-3        Multiple Response
Which of the following are required fields within the Function Arguments dialog box for the DSUM
(Q)
function?
A.   Database
B.   Field
C.   Year
D.   Number
E.   Criteria
2-4        Multiple Response
(Q)      In the Use function field of the Subtotal dialog box, what are the different options to choose from?
A.   Text
B.   Sum
C.   Degrees
D.   Count
E.   Product

Lesson 3: Presenting Data Using Charts
Topic 3-A: Create a Chart

How to Create a Chart

3-1        Multiple Choice
(Q)      On which tab of the Ribbon would you click to find the option to add a chart?
A.   Insert
B.   Home
C.   Page Layout
D.   Review

3-2        Multiple Response
(Q)      Which of the following are different types of Column charts that can be added to a worksheet?
A.   2-D Column
B.   Cone
C.   3-D Pie
E.   Pyramid

3-3        Performance Based
(Q)      Create a 3-D Column chart and add the chart title as Annual Sales Data.
Topic 3-B: Modify Charts

How to Modify Charts

3-1        Multiple Choice
(Q)      To center the chart title, which Ribbon tab would you need to click?
A.   Chart Tools Design
B.   Chart Tools Layout
C.   Chart Tools Format
D.   Insert
E.   Page Layout

3-2        Multiple Response
After you have created a chart for your worksheet, which of the following are options you can
(Q)      select from to change in your chart from within the Labels group on the Chart Tools Layout
Contextual tab?
A.   Chart Title
B.   Axes
C.   Legend
D.   Data Labels
E.   Gridlines

3-3        Performance Based
(Q)      Move the legend to the top-right without overlapping the chart.

Topic 3-C: Format Charts

How to Format Charts

3-1        Multiple Choice
To apply specific styles to the text within the chart that has been created in a worksheet, what tab
(Q)
on the Ribbon would you need to select?
A.   Chart Tools Design
B.   Chart Tools Layout
C.   Chart Tools Format
D.   Page Layout
E.   Insert
3-2        Multiple Response
(Q)      Which of the following are options within the WordArt Styles group?
A.   Shape Outline
B.   Text Outline
C.   Text Fill
D.   Shape Effects
E.   Size

Lesson 4: Analyzing Data Using PivotTables and
PivotCharts
Topic 4-A: Create a PivotTable Report

How to Create a PivotTable Report

4-1        Multiple Choice
(Q)      Which Ribbon tab would you need to click to find the PivotTable drop-down list?
A.   Home
B.   Insert
C.   Page Layout
D.   Review

4-2        Multiple Choice
(Q)      Which Ribbon tab would you need to click to Group your selection for the PivotTable?
A.   Insert
B.   PivotTable Tools Option
C.   PivotTable Tools Design
D.   PivotTable Tools Analyze

4-3        Multiple Choice
(Q)      To customize your calculations in a PivotTable, what dialog box should you be working from?
A.   Edit Data Source
B.   Format Chart Area
C.   Data Field Settings
D.   Format Data Labels
4-4        Performance Based
Change the custom calculations for the month of May to calculate the average sales, rename it
(Q)
as "Average of Sales in May", and change the number format to currency.

Topic 4-B: Analyze Data Using PivotCharts

How to Analyze Data Using PivotCharts

4-1        Multiple Choice
To modify the PivotTable Report by adding grand totals on the rows only, which Ribbon tab
(Q)
would you need to select to modify these changes?
A.   Insert
B.   Page Layout
C.   PivotTable Tools Options
D.   PivotTable Tools Design

4-2        Multiple Response
In the Create PivotTable With PivotChart dialog box displayed, how would you specify the range
(Q)
of PivotChart you are trying to create?
A.   Select the area of your worksheet that has the data.
B.   In the Table/Range field, type in the range of the table.
C.   Click New Worksheet.
D.   Click Existing Worksheet.

Lesson 5: Inserting Graphic Objects
Topic 5-A: Insert and Modify Pictures and ClipArt

How to Insert and Modify Pictures or ClipArt

5-1        Multiple Choice
(Q)      Which of the Ribbon tabs contains the Illustrations group?
A.   Home
B.   Insert
C.   Page Layout
D.   View
5-2        Multiple Choice
(Q)      The Picture Effects drop-down menu is in which group on the Picture Tools Format tab?
A.   Picture Tools
B.   Picture Styles
C.   Arrange
D.   Size

5-3        Multiple Choice
In your worksheet, you created a meeting agenda and you want to add an image from your
(Q)
desktop. What steps would you take to add the image?
Click Insert and click Clip Art, and then in the Search For field, type the image name, click Go,
A.
and then select the image.
Click Page Layout and click Clip Art, and then in the Search For field, type the image name, click
B.
Go, and then select the image.
Click Page Layout and click Picture, and then in the Search For field, type the image name, click
C.
Go, and then select the image.
Click Insert and click Picture. From the Look In drop-down list, navigate to locate the saved
D.
image, select the image, and click Insert.

5-4        Performance Based
(Q)      From the Clip Art pane, search for "World" and then insert the globe clip art to your worksheet.

Topic 5-B: Draw and Modify Shapes

How to Draw and Modify Shapes

5-1        Multiple Choice
(Q)      To insert pre-designed shapes in your worksheet, which Ribbon tab would you need to click?
A.   Home
B.   Insert
C.   Page Layout
E.   View
5-2        Multiple Response
(Q)      How do you get the shape to appear on your worksheet after the shape has been selected?
A.   Double-click the shape in the Ribbon.
B.   Double-click the worksheet.
C.   Click the worksheet and click and drag.
D.   Right-click and choose Paste.

5-3        Performance Based
(Q)      Insert an isosceles triangle shape in cell C4.

Topic 5-C: Illustrate Workflow Using SmartArt Graphics

How to Illustrate Workflow Using SmartArt Graphics

5-1        Multiple Choice
(Q)      To add smart art to your worksheet, which group on the Insert tab would you need be within?
A.   Shapes
B.   Tables
C.   Illustrations
D.   Charts
E.   Text

5-2        Multiple Response
(Q)      Which of the following are groups on the SmartArt Tools Design tab?
A.   Create Graphic
B.   Shape Styles
C.   Shapes
D.   Layouts
E.   SmartArt Styles
Topic 5-D: Layer and Group Graphic Objects

How to Layer and Group Graphic Objects

5-1        Multiple Choice
On the Picture Tools Format tab, which group has the options to send a picture back or to the
(Q)
front?
A.   Picture Tools
B.   Picture Styles
C.   Arrange
D.   Size

5-2        Multiple Response
There are two picture images on a worksheet, and the front image needs to move behind the
(Q)
back image. How would you do this?
First, select the back image. To move it to the front, right-click, and from the shortcut menu,
A.
choose Bring to Front, and then click Bring Forward.
First, select the front image. To move it to the back, on the Picture Tools Format contextual tab in
B.
the Arrange group, select the Send To Back drop-down list, and then click Send Backward.
First, select the front image. To move it to the back, on the Picture Tools Format contextual tab in
C.
the Arrange group, select the Send To Back drop-down list, and then click Send To Back.
First, select the front image. To move it to the back, right-click, and from the shortcut menu,
D.
choose Bring To Front, and then click Bring Forward.
First, select the bottom image. To move it to the front, on the Picture Tools Format contextual tab
E.
in the Arrange group, select the Send To Back drop-down menu, and then click Send Backward.

5-3        Multiple Choice
(Q)      To group together images, how many images do you need selected to group?
A.   One or less
B.   Only two
C.   Only one
D.   Two or more

5-4        Multiple Choice
In which group of the Picture Tools Format tab is the drop-down menu located with the options to
(Q)
group or ungroup images?
A.   Picture Tools
B.   Picture Styles
C.   Arrange
D.   Size
Lesson 6: Customizing and Enhancing
Workbooks and the Excel Environment
Topic 6-A: Customize the Excel Environment

How to Customize the Excel Environment

6-1        Multiple Choice
To change the color scheme for your Excel user interface, what button would you select on the
(Q)
main screen?
A.   Insert button
B.   Office Button
C.   Format button
D.   Font Color button
E.   Fill Color button

6-2        Multiple Response
Which are options that can be changed when navigating to the Office Button->Excel options-
(Q)
>Popular options?
A.   ScreenTip Style
B.   Maximum Iterations
C.   Color Scheme
D.   Include This Many Sheets
E.   Maximum Change

6-3        Performance Based
(Q)      Enable the live preview of your document.
Topic 6-B: Customize Workbooks

How to Customize Workbooks

6-1        Multiple Choice
If you had to add a new comment to your worksheet , what tab would you select to find the
(Q)
comment section?
A.   Insert
B.   Page Layout
C.   Data
D.   View
E.   Review

6-2        Multiple Response
Your manager reviewed your worksheet and left a comment within it, and you want to respond to
(Q)
his comment. What steps would you take to respond within the comment?
Click the cell within the worksheet, click the Insert tab, click Edit Comment, and type within the
A.
comment cell.
B.   Click the Review tab, click Edit Comment, and type within the comment cell.
Click the cell within the worksheet that has the edit, right-click and select Edit Comment, and type
C.
within the comment cell.
Click the Review tab, click the cell within the worksheet that has the edit, click Edit Comment, and
D.
type within the comment cell.
Click the Page Layout tab, click the cell within the worksheet that has the edit, click Edit
E.
Comment, and type within the comment cell.

6-3        Multiple Choice
(Q)      On which tab of the ribbon would you click to add a hyperlink?
A.   Insert
B.   Page Layout
C.   Formulas
D.   Review
E.   View
6-4        Multiple Response
(Q)      Within the hyperlink pop-up box, which of the following are linked to objects?
A.   Existing File or Web Page
B.   Create New Document
D.   Text To Display

6-5        Multiple Choice
When customizing your worksheet which section would you select to create the watermark
(Q)
effect?
A.   Size and rotate
B.   Scale
C.   Crop from
D.   Image Control

Topic 6-C: Manage Themes

How to Manage Themes

6-1        Multiple Choice
(Q)      On the Page Layout tab of the Ribbon, which group would you need to be in to change the font?
A.   Sheet Options
B.   Arrange
C.   Scale to Fit
D.   Page Setup
E.   Themes

6-2        Multiple Choice
In order to customize the Theme font color for your worksheet, which drop-down menu would you
(Q)
need to work from?
A.   Themes
B.   Theme Colors
C.   Theme Fonts
D.   Theme Effects
6-3        Multiple Response
(Q)      When customizing the Theme fonts for a worksheet, which two settings need to be set?
C.   Text/Background 1 - Dark
D.   Body Font
E.   Theme Colors

Topic 6-D: Create and Use Templates

How to Create and Use Templates

6-1        Multiple Choice
(Q)      What would you select to create a new workbook?
A.   Office Button
C.   Insert
D.   Page Layout
E.   View

6-2        Multiple Choice
Which of the following would be accurate steps to take to create a new worksheet using an
(Q)
existing template already created by Microsoft?
A.   Click the Office Button, select Install Templates, and select the appropriate worksheet template.
B.   Click Insert, select Install Templates, and select the appropriate worksheet template.
Click the Office Button, select Microsoft Office Online, and select the appropriate worksheet
C.
template.
D.   Click the Office Button, select Featuring, and select the appropriate worksheet template.

6-3        Performance Based