mis 112 final module 5-8 PS

which of the following is one of the conditional formatting rules? -highlight cell rules -color scales -data bars -all the other options are correct

D. all the other options are correct

In Excel, the set of worksheet functions related to dates can be accessed via: Formulas (tab) -> Function Library (command group) -> Date & Time Home (tab) -> Number (command group) -> Number Format -> Date Functions Pressing: Ctrl + ; (semicolon) Home (tab) -> Number (command group) -> Number Format -> Special

Formulas (tab) -> Function Library (command group) -> Date & Time

What function helps ensure that the formula will continue to reference the intended cells if the pivot table layout is changed? GETPIVOTDATA PUTPIVOTDATA EXTENDPIVOTDATA REFPIVOTDATA

GETPIVOTDATA

Suppose you deposit $100 at the beginning of each month (for 12 months) into an account that pays 3.5 percent annual interest compounded monthly. Which formula calculates the future value of your series of deposits? =FV(3.5%/12,12,-100,,1) =FV(3.5%/12,12,100,,1) =FV(3.5%,1,-100,,1) None of the above

=FV(3.5%/12,12,-100,,1)

If you want to use the HYPERLINK function to jump to a cell E2 in the same workbook and display "Hello" as the link text, which of the following functions would you use? Assume that the workbook name is Example.xlsx. =HYPERLINK("[Example.xlsx]E2", "Hello") =HYPERLINK("[Example1]E2", "Hello") =HYPERLINK("[Example.xlsx]E12", "Hello") =HYPERLINK("[Example.xlsx]E2", "")

=HYPERLINK("[Example.xlsx]E2", "Hello")

Which Excel function returns the serial number of current date and time? =TODAY() =NOW() =DAY() =DATE()

=NOW()

To get rid of ALL blank spaces in a string (e.g., in "Jo hn Doe "), which Excel function will you use? =NOBLANKS() =SUBSTITUTE() =PROPER() =TRIM()

=subsitute()

Which of the following is the correct output of the formula '=TRIM(" A B C D ")'? A B C D ABCD a b c d None of the other options are correct

A B C D

Which of the following is TRUE for Pivot Tables? A Pivot table can be sorted by any column selected in the Pivot table field list The main drawback of Pivot tables is that you cannot sum up values (e.g., Sales for a region) The main difference between Pivot tables and regular Tables is that Pivot tables allow more columns Pivot tables are a new feature in Excel 2016

A Pivot table can be sorted by any column selected in the Pivot table field list

Which of the following statements is FALSE? You can create a pivot table and a pivot chart using the same data Slicers can be used with Pivot Charts All Excel charting features are available in a pivot chart A pivot chart is always based on a pivot table, i.e., changes to the pivot chart must be made by changing the underlying table

A pivot chart is always based on a pivot table, i.e., changes to the pivot chart must be made by changing the underlying table

How do you group Pivot Table items manually? After creating the pivot table, select the items to be grouped and then choose Analyze (tab) under PivotTable Tools and select Group (group) -> Group Selection After creating the pivot table, select the items to be grouped and then choose Design (tab) under PivotTable Tools and select Group (group) -> Group Selection Select columns in the pivot table and right click on them and choose the option to "Cluster Group" None of the answers provided is valid

After creating the pivot table, select the items to be grouped and then choose Analyze (tab) under PivotTable Tools and select Group (group) -> Group Selection

The formula: =CHAR(CODE("C")) returns which of the following? 67 100 C an Error

C

How do you set up a data validation (using the data validation dialog box) that restricts data entry to a text of a specified length? Click the Settings tab (in the Data Validation box). In the Allow box, select 'date'. In the Data box, select 'greater than'. Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length. Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between'. Home (tab) -> Editing (group) -> Find & Select -> Data Validation

Click the Settings tab (in the Data Validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length.

What steps would you take if you wanted to adjust the outline of a rectangle shape that has already been inserted into your worksheet? 1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Outline group, change the settings. 1) Click the shape. 2) Click Home (tab) --> Font (group) --> Fill Color 1) Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Styles group, select a different outline. 1) Click the shape. 2) Hold shift while pressing the up/down arrow keys to increase/decrease the size of the outline

Click the shape. 2) Click (Drawing Tools) Format. 3) In the Shape Styles group, select a different outline.

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the entry in cell A1 is an odd number? =ISODD(A1) =ISNOTEVEN(A1) =ODD(A1) All the other options are correct

ISODD(A1)

Which of the following is one of the ways to create a Pivot Chart? Insert (tab) -> Charts (group) -> PivotChart Insert (tab) -> Sparklines (group) -> PivotChart Insert (tab) -> Tables (group) -> PivotChart Insert (tab) -> Illustrations (group) -> Shapes -> PivotChart

Insert (tab) -> Charts (group) -> PivotChart

To add one or more Slicers to a worksheet, start by selecting any cell in a pivot table and then choose ______________. Insert (tab) -> Filters (group) -> Slicer. The Insert Slicers dialog box appears. Insert (tab) -> Slicer (group) -> Filters. The Insert Slicers dialog box appears. Right Click on it. The Insert Slicers menu option can be selected. All of the answers provided are correct

Insert (tab) -> Filters (group) -> Slicer. The Insert Slicers dialog box appears.

How do you remove a filter in the PivotTable Field List? Move the cursor over the filter icon next to the field name you want to remove the filter from. Click the arrow that appears, and then choose the Clear Filter From option. At the top of the window, on the Ribbon, click the Analyze tab under PivotTable Tools. In the Actions group, click Clear, and then click: Clear Filters. Right Click on a cell and select: Remove Filter from PivotTable Field List. Use the Grand Totals feature from the Pivot Tables "Design" tab.

Move the cursor over the filter icon next to the field name you want to remove the filter from. Click the arrow that appears, and then choose the Clear Filter From option.

How can you define a Print Area after selecting the required cells on the worksheet? On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Custom Print Area. On the Page Layout tab, in the Page Setup group, click Print Area, and then click Clear Print Area. Press the keyboard shortcut of: Ctrl+!

On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area.

To calculate the present value of an investment, which is the total amount that a series of future payments is worth now, the function used is: PMT() PV() NPER() PRESFUTUREPAY()

PV()

What is the general formula to calculate compound interest (i.e., the future value of an investment that includes both Principal and interest)? Principal * (1 + periodic rate) ^ number of periods Principal * (1 + periodic rate) ^ 10 Principal * (1 + 2) ^ number of periods Principal * (periodic rate) ^ number of periods

Principal * (1 + periodic rate) ^ number of periods

What is the syntax for the RATE() function? RATE(nper,[fv],[type],[guess]) RATE(nper,pmt,pv,[fv],[type],[guess]) RATE(rate,nper,pmt,pv,[fv],[type]) RATE(nper,pmt,pv,[guess])

RATE(nper,pmt,pv,[fv],[type],[guess])

Which of the following options can be used to quickly remove data validation for a cell? Select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). On the Settings tab, click Clear All. Right click on the cell and choose delete Right click on the cell and choose erase Select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). Select Input Message tab and click on OK.

Select the cell and open the Data Validation dialog box (Data (tab) -> Data Tools (group)). On the Settings tab, click Clear All.

With respect to Sparklines, what is FALSE? The Create Sparklines dialog box does NOT allow users to specify a different sheet for the Data Range. A Sparkline's data source can be changed. A Sparkline can be deleted. A set of Sparklines can be ungrouped.

The Create Sparklines dialog box does NOT allow users to specify a different sheet for the Data Range.

What is an advantage of using formulas instead of Autofill to create a series of dates? (e.g., for a work-week schedule) When the first date is changed, the others update automatically When the first date is changed, the others are not affected A formula cannot be used to create a series of dates Autofill uses a formula, so these two options are equivalent

When the first date is changed, the others update automatically

In Step 2 of the Query Wizard, you specify which data rows should be included in your query output. How is the criteria entered? a criterion for each column you drag and drop the rows you want you use a checkbox to pick criteria criteria are automatically copied from cell $A$1 of your current workbook sheet

a criterion for each column

A pivot chart is which of the following? A bar chart that can pivot to a pie chart form with a single click A graphical representation of a data summarized in a pivot table A chart that is joined to a pivot table in a 4-way link A chart that cannot be used with a slicer

a graphical representation of a data summarized in a pivot table

To create a single file web page, what file format does a user have to select from the Save as Type drop-down list? XML data Single File Web Page Web Page Excel Binary Workbook

a single file web page

HLOOKUP and VLOOKUP are both _________ in Excel a.functions b.formatting options c.interest payment calculation functions d.style and theme related options

a. functions

Which of the following options provides correct syntax for the VLOOKUP function? a.VLookup_value, table_array, col_index_num, range_lookup) b.VLOOKUP(lookup_value, table_array, row_index_num, range_lookup) c.Either of the two previous choices will work d. None of the options provided is valid

a.VLookup_value, table_array, col_index_num, range_lookup)

Which of the following BEST applies to the INDEX function? a.You can use it with the MATCH() function to do reverse lookups when the data is not sorted (e.g., to find the ProductID for a product named "Chocolate Cake", assuming product names are not sorted) b.It usually returns error codes: #ERR1 - #ERR5 c.It returns the relative position of an item in an array (e.g., "4") when a match is found d.It cannot be used with the MATCH function

a.You can use it with the MATCH() function to do reverse lookups when the data is not sorted (e.g., to find the ProductID for a product named "Chocolate Cake", assuming product names are not sorted)

An Excel formula may return which of the following errors? #REF! #DIV/0! #VALUE! All of the above

all of the above

Which of the following is TRUE about a Watermark in Excel? A watermark is an image (or text) that appears on each printed page. A watermark can be a faint company logo, or a word such as DRAFT. Excel doesn t have an official command to print a watermark, but you can add a watermark by inserting a picture in the page header or footer. All of the above statements are TRUE.

all of the above statements are true

Which of the following is TRUE about Pivot tables? You cannot add new rows or columns to the Pivot table You cannot change any of the calculated values You cannot enter formulas within the pivot table All of the answers provided are TRUE

all of the answers provided ae true

Which of the following options will return Year2016? All the options are correct =SUBSTITUTE("Year 2016", " ","") =REPLACE("Year 2016",5,1,"") =REPLACE("Year 2016",SEARCH(" ", "Year 2016"),1,"")

all the options are correct

A pivot table and a pivot chart are joined in a two-way link, if you make structural or filtering changes to one, the other _______. also changes remains unchanged shows a sparkline graphic to highlight the change can be eliminated to avoid duplicates

also changes

in Excel, a parameter query is a query that prompts for ________ every time it is run. an input a password an image a fingerprint

an input

to make data bars easier to view, we can ________ the numerical values in the cell a. query b. hide c. automatically shrink d. use bold fonts for

b. hide

Assume the "range_lookup" parameter in an HLOOKUP function is FALSE. If an exact match is not found, what value is returned? a. #REF! b.Excel returns a "No value was found" error message c.#N/A d.An approximate match is returned

c.#N/A

Which of the following provides correct syntax for the MATCH function? a.MATCH(column-name) b.MATCH(lookup_value,lookup_vector,result_vector) c.MATCH(lookup_value,lookup_array,match_type) d.MATCH(lookup_value,match_type)

c.MATCH(lookup_value,lookup_array,match_type)

When customizing a sparkline, which of the following is NOT one of the options? Change the width or height of a cell. Insert a Sparkline into merged cells. Change the Sparkline type. Convert the Sparkline into a table.

convert the sparkline into a table

What is the purpose of the function TIME()? Converts the input hours, minutes & seconds to a serial number Returns the current time Returns the serial number of the current date and time Converts a time in the form of text string (e.g., "18:45") to a serial number

converts the input hours, minutes, and seconds to a serial number

Which key combination can a user press to toggle between formula view and normal view within Excel? Shift-Tab Shift+` Ctrl+` Ctrl-Tab

ctrl+'

Which of the following statements about Trace Precedents are correct? a. It can help point out if a formula has been applied to a wrong cell range b. You can access Trace Precedents on the Formula Auditing toolbar c. By clicking Trace Precedents again, you can identify the next level of cells that provide data to the active cell d. All of the options provided are correct

d. all of the options provided are correct

If you are trying to display trends with a Sparkline, you may have to work with irregular time periods in the underlying data. To properly do so, you can click on the Group (command group), then click on "Axis", and choose _________ to format the shape of the chart. Date Axis Type Date Value Type Character Axis Type Find and Replace

data axis type

When a header or footer section is activated, you can insert any of several element codes from the Header & Footer Elements group. Each button inserts a code into the selected section. What is the purpose of the following code: &[Date] ? Displays the current date Displays the date when the workbook was created Displays the date when the workbook was last modified None of the above

displays the current date

What would happen if you used a formula to perform arithmetic operations on dates, but one of the dates was typed in a format that Excel does not recognize as a date? Excel will display a message telling you to reformat the date Excel will display an error value Excel will automatically reformat the date into the correct format None of the options provided is correct

excel will display an error value

You can create Sparklines for rows of data that you add later by using the _________ on an adjacent cell that contains a sparkline. Format Painter Fill handle Spray Can Smart Art

fill handle

The HYPERLINK function syntax has two arguments. Which argument has the jump text or numeric value that is displayed in the cell? Friendly_name Link_location Name File

friendly_name

When (or why) does Excel draw arrows to indicate an error source? For finding dependencies between two images pasted in different worksheets In Tracing Error Values Error-checking the sheet Background Because Functions Must Have Arrows

in tracing error values

To apply specific formatting to a sparkline, use the Sparkline Color or the ___________ commands. Sparkline Editor Sparkline Style Marker Color Sparkline Developer

market color

By default, Page Breaks __________ displayed in the Normal view of worksheet. are exclusively may or may not be are always None of the above

may or may not be

Which of the following is NOT one of the four quick margin settings for adjusting margins? Normal Wide Narrow No margins

no margins

______ cells are by default disregarded when data validation rules are created. These cells can be included in the validation rule by unchecking the: "Ignore blank" checkbox. Unchecked All Checked None of the options provided is valid

non of the options provided is valid

By pressing the spacebar, the contents of a cell seem to erase because pressing the spacebar inserts a / an _______ space character visible special non visible none of the options provided is correct

non visible

Which function is used to calculate depreciation in Excel using the straight line method? DB() DDB() SYD() None of the above

none of the above

In a Pivot Table, the names of the fields for the report come from ________. an associated pivot chart the worksheet names both A & B none of the answers provided is correct

none of the answers provided is correct

Which of the following is FALSE about Pivot Table Slicers? Slicers can be moved and resized. The effects of filtering by a particular slicer can be removed. To use a Slicer to filter data in a Pivot Table, you can click one of the slicer value buttons. None of the answers provided is false

none of the answers provided is false

How do you copy a Pivot table (including its design format) to a new worksheet? None of the answers provided is valid select Insert (tab) -> Tables (group) -> Pivot Table select Insert (tab) -> Copy Pivot table. Then select a new worksheet and press Ctrl +V select the entire table and choose Home (tab) -> Clipboard (group) -> Copy (or, press Ctrl+C). Then select a new worksheet and choose Home (tab) -> Clipboard (group) -> Paste -> Paste Values

none of the anwers provided is valid

Which of the following is NOT a summary function for Pivot tables? StdDev Varp Var Regression

regression

In order to delete information using Find and Replace in Excel, enter the text to be deleted in the ________ field and leave the Replace With field _____. Find What, empty Replace With, empty Find What, with a comma Find What, with the keyword "delete"

replace with, empty

______________ replaces part of text string that appears in a specific location within a string. Hint: This function can be used when you know the character location of the text to be replaced. REPLACE() SEARCH_AND_REPLACE() FINDREPLACE() SEARCHREPLACE()

replace()

While using web queries Rich text formatting is typically used if we wish to ______. preserve cell formulas reproduce web page data in a format close to that of the original return none of the formatting return hyperlink formatting

reproduce web page data in a format close to that of the original

While formatting Shapes in Excel, which command is used to change the overall style of a Shape? Change style WordArt Styles group Stylesheet Shape Styles

shape styles

Unlike charts on an Excel worksheet, _________ are not objects. Instead these are tiny charts in the background of a cell. Sparklines Trendlines SmallCharts Inline Charts

sparklines

Which of the following file formats does NOT use a bitmap representation or format? (Bitmap images usually look good at their orginal size but lose clarity if you increase the size) SVG BMP PNG TIFF

svg

When importing a text file into Excel, the _____________ helps specify how the data will be imported. Text Import Wizard External Data Wizard CSV Wizard Text Preview Panel

text import wizard

When working with data returned from a Query Wizard, refreshing a query occasionally produces undesired results. If this occurs, you can use the _____ button to reverse the change. In other words to "un-refresh" the data. Unrefresh Cycle Undo Cancel

undo

A calculated field must reside in the ________ area of the pivot table. Column Values Rows Report Filter

values