Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/177

Click to flip

177 Cards in this Set

  • Front
  • Back
How will you return a cell formatted as a date or time, back to its default or original format?
Both a) and b)
While converting from military time, when would the formula TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)) return an incorrect result?
The contents of cell A1 do not comprise four digits
When you activate a cell that contains a date, the formula bar usually shows the cell contents formatted using the following date format:
None of the answers provided is correct
Which of the following formulas is used to determine whether the date in cell D4 is part of a leap year?
=IF(MONTH(DATE(YEAR(D4),2,29))=2,TRUE,FALSE)
The formula '=NOW()-TODAY()' displays current time as a ___________ without a related date.
serial number
How do you format a valid date serial number as a date ?
None of the options provided is correct
What result does the following formula display? =DOLLAR(5742.999,2)
$5,743.00
For Windows systems, Excel uses the standard 8-bit ANSI character set. This character set consists of ___ characters.
255
What does the following formula return? =SEARCH("m","Big Mama Thornton",1)
5
What does the following formula return? =FIND("m","Big Mama Thornton",1)
7
To find a working date that falls after a number of days (e.g., 12 business days after some date), you typically use this function:
WORKDAY()
In Excel, if one enters a time greater than ten thousand hours, it is interpreted as ________ instead of a time value.
a text string
What does the WEEKDAY function return when you provide a date as an argument?
an integer between 1 and 7
If one attempts to format a serial number that falls outside the range of supported dates, the value displays as __________.
a series of hash (#) marks
In Excel 2010, in order to change the date's default format, one needs to change ___________ settings.
System-wide (i.e., all of Windows)
To display today's date in the format like "Friday April 5, 2013", which formula should you use ?
="Today is "&TEXT(TODAY(),"dddd, mmmm d, yyyy")
A single cell in Excel can hold up to ________________ characters.
32000
Assume cell A1 contains the text Micro and cell A2 contains the text Age, which of the following formulas will return MicroAge?
=A1&A2
The _________ function is used to return a specific number of letters or characters from the start of a text string.
LEFT()
Which function can be used to make crude/simple vertical separators between cells?
REPT()
To avoid printing more pages than necessary in Excel, you can use the following command: ________ .
Set Print Area
Which function returns the periodic interest rate of a loan, given the number of payment periods, the periodic payment amount, and the loan amount?
RATE()
If you would like a consistent header or footer for all your documents, create a ________ file with your headers or footers specified. This template is used as the basis for new workbooks.
Book.xlt
When you print a worksheet, cell gridlines do not usually get printed. If you want your printout to include the gridlines, which of the following options would you choose?
Page Layout (tab) -> Sheet Options (group) -> Gridlines -> Print
Issuing the Print command (in Excel) with a mouse takes three clicks. A slightly more efficient method is to press _____________ and then click the Print button
Ctrl+P
With regards to Depreciation Calculation functions in Excel, which of the following are true about the function VDB()?
Refers to the Variable-declining balance function. Computes the depreciation of an asset for any period (including partial periods) using the double-declining balance method or some other method you specify.
If you need to create several different printed reports from the same Excel workbook, setting up the specific settings for each report can be a tedious job. You can simplify the process by using the ______________ feature that enables you to give names to various views of your worksheet, and you can quickly switch among these named views.
Custom Views
A _____________ data table shows the results of any number of calculations for different values of a single input cell. For example, using a single loan amount, you can calculate a table of monthly payments each for a different interest rate.
one-way
To calculate the cumulative interest paid between any two payment periods, use the ______________ financial function.
CUMIPMT()
Please refer to the attached screen capture. Which of the following formulas will correctly calculate simple interest?
=D5*D6*D7
Please refer to the attached workbook. The ________ and ________fields are in the Rows and values section respectively.
weekday, amount
What function helps ensure that the formula will continue to reference the intended cells if the pivot table layout is changed?
GETPIVOTDATA
How do you copy a Pivot Table to a new worksheet?
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
A calculated field consists of a calculation that involves other _______.
fields
Please refer the attached workbook. What is the total amount of Sales for the California (CA) state in the east region?
4300
Please refer to the attached workbook. What would you do if you want to filter records in the report so that you get information only for the state of AZ?
To filter the report, click the arrow next to Row Labels and check the 'AZ' option from the menu that appears.
A _________ is a graphical data representation of data displayed in a pivot table.
pivot chart
Which of the following is TRUE about Pivot Charts?
Slicers also work with pivot charts
In the attached image, how would you modify the pivot table to include only Joseph and Lawrence from the Middle region and nothing else.
Select the required cells and then right-click, point to Filter, and click Keep Only Selected Items.
To add one or more Slicers to a worksheet, start by selecting any cell in a pivot table and then choose ______________.
Insert (tab) -> Filter (group) -> Slicer. The Insert Slicers dialog box appears.
A Web query contains information about the path to the ___________ and can pull the __________ data directly from that source.
data source, updated
Which one of the following is NOT a formatting option in the Web Query Options dialog box?
XML formatting
How would you describe an OLAP data cube?
A multi-dimensional source of aggregate data, which can be created from a variety of other sources
Which of the following is NOT one of the ways to get External Data in Excel?
From the Library
To create a single file web page, what file format does a user have to select from the Save as Type drop-down list?
Single File Web Page
The HYPERLINK function syntax has two arguments. Which argument has the path and file name to the document to be opened?
Link_location
What is the shortcut key to create hyperlink when you select an object or a cell?
Ctrl+K
Please use the attached workbook as an external data source. The workbook has a table called 'Employee'. When you import data, after selecting all the columns from the table using the query wizard (Data(tab) -> Get External Data (group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that has a value that starts with 'T' for 'City' column.
From the Column to Filter list, select City. In the right panel, select 'begins with' from the first drop-down list and then type 'T' in the second drop-down list box
When you have imported data from an external data source, you can change the properties of the 'external data range' by using the External Data Properties dialog box. How will you open this dialog box?
Data (tab) -> Connections (group) -> Properties
The last step in the Query Wizard, "Finish", lets you do which of the following?
Save a query so that you can reuse it
What is the use of the following data validation formula: =A2>A1 ?
accepts values for cell A2 that are greater than the value of cell A1
Please see the attached workbook. In the Student Rank table, in cell F19, type Rachel instead of Dennis. Why do you receive an error?
Cell F19 can only take text that begins with the letter 'd'.
If you want to find the cells on the worksheet that have data validation, which option would you select?
Home (tab) -> Editing (group) -> Find & Select -> Data Validation
How do you set up a data validation (using the data validation dialog box) that restricts data entry to a time less than a specified time?
Click the Settings tab (in the Data Validation box). In the Allow box, select 'Time'. In the Data box, select 'less than'.
If you want to trace cell relationships for formulas, which of the following commands / options could you use?
None of the options provided is correct
Which of the followings steps are to be followed in order to identify the cell that contains an error?
Formulas -> Formula Auditing -> Error Checking -> Trace Error
#NAME? error occurs under which of the following conditions?
All of the options provided are correct
Which of the following returns a #REF! error value?
The formula contains an invalid cell reference
Which of the followings steps are to be followed in order to display the Go to Special dialog box which is an Excel Auditing Tool?
Home -> Editing -> Find & Select -> Go to Special
The ________ function is one solution to account for for floating point number errors. It also serves to round off values to the desired number of decimal places.
ROUND()
Please see the attached workbook. The worksheet contains a table of student data (starting in row 13) named as "Students". If you were to use a lookup formula to get the exact value for salary field in row 2, which of the following formulas would you use?
=VLOOKUP(C2,Students,4,FALSE)
Which of the following options best describe what the following formula would do? =VLOOKUP(D2,PAGES!A2:B10,2,TRUE)
perform a vlookup in a different worksheet which does not have an exact match
If you write a lookup formula to look up the text 'education', the formula considers which of the following a match?
All answers provided are correct
What form of LOOKUP is used when you have a small list of values and the values remain constant over time?
array form
Please see the attached workbook. What value of tax rate is returned when the entered income is $22,566?
28%
Please see the attached workbook. What formula (in cell B3) would return the tax rate for the income in cell B2?
=HLOOKUP(B2, E1:J3, 3)
Which of the following is NOT one of the VLOOKUP function arguments?
row_index_num
In context of the MATCH() function, which of the following is FALSE?
All of the options are false
When using the MATCH function, the match_type "-1" is best described by which of the following options?
It will find the smallest value that is greater than or equal to the "lookup_value"
When you use the HLOOKUP() function, which of the following parameters represents the row number from which the matching value will be returned?
None of the options provided is correct
Please see the attached workbook. If the formula =MATCH(B2,I4:I7,0) was used in cell B3, what would be the result?
2
What value should be given to the 'match_type' argument in the MATCH function if you want the MATCH function to find the first value exactly equal to lookup_value?
0
HLOOKUP and VLOOKUP are both _________ in Excel
functions
Please see the attached workbook. Is =VLOOKUP(E3,TABLE1,3,FALSE) a valid lookup function when used in cell E4?
No: because the table has only two columns but the function refers to column 3
Which of the following options provides correct syntax for the VLOOKUP function?
VLOOKUP(value_to_be_looked_up, table_array, column_index_num, [range_lookup])
Please see the attached workbook. What will be the grade of Adams if the following formula is used in the cell C2?
=LOOKUP(B2,{45,50,60,70,80,90},{"F","E","D","C","B","A"})
C
Which of the following options provides correct syntax for the LOOKUP function?
LOOKUP(lookup_value,lookup_vector,result_vector)
When you enter "FALSE" for the range_lookup parameter in HLOOKUP, you are telling Excel that you want to find a(n) ________ match
exact
Consider the HLOOKUP function in Excel. If the "range_lookup" argument is set to TRUE, we need to sort the first column of the range in ____________ order
ascending
Please see the attached workbook, Sheet2. Which of the following do Theme effects operate on?
Charts, Shapes
Please see the attached workbook. Currently, some conditional formatting rules are applied to the range A1:A25. Suppose the same formatting is applied to the range B1:B25. Which of the following will be true?
Cell B6 will have a circle shaded white
Please see the attached Excel workbook. Suppose you are on Sheet1 and you have selected cells A5:B5. What happens when you insert a background image through the following sequence of steps? Page Layout (tab) -> Page setup (command group) -> Background?
It adds a background image to the currently active worksheet
Please see the attached workbook, Sheet3. When you print the worksheet with graphic background what happens?
No background is printed, even though the background is displayed onscreen.
Which of the following rule types does not apply to color scale formatting?
Format cells differently based on the font styles in each cell.
If you want to format cells that have values between 50 and 60, which option under 'Highlight Cells Rules (Conditional Formatting)' category would you select?
Between
Highlight Cell Rules and Top/Bottom Rules use a single type of formatting to highlight ________ cell(s) based on the condition or requirement
Specific
Can you format individual characters of text in a cell?
yes
When the value of a cell is changed (after conditional formatting is applied), the cell formatting ______ based on the conditions set.
Is updated
In order to keep track of all rules in a worksheet, we use the ___________ menu command under Conditional Formatting.
Manage Rules
What option in the Table Tools / Design tab removes a table without losing the data in it?
Convert to Range
What of the following is FALSE about filtering a table?
When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.
Which of the following actions should be taken while creating a table to display Table Headers?
Check 'My table has headers'
To add rows or columns within the table, right-click and choose Insert from the shortcut menu. Which of the following commands is NOT displayed on this menu?
Table Column Above
A formula refers to a value in the Total Row of a table. What will happen to the formula result if the option for "Total Row" (from the Excel ribbon: Table Tools / Design tab) is unchecked?
the formula returns an error
You can use a data Form to add, find, and edit rows in a range or table. Is this correct?
Yes, these are all valid form operations
Please see the attached workbook image. What sequence of steps will allow you to format the data into a table?
Select the data, then click "Table" under the "Insert" tab of the Excel Ribbon
A data form provides a convenient means to enter, display and search for data within a table. It also permits you to do the following: ____________
Delete a record/row
Please see the attached Excel workbook image in answering this question. In order to sort the 'Salesperson' column in a descending order, which of the following steps is correct?
Click on any cell containing Salespersons data, from the Ribbon pick the Data tab, and then click on the "Sort Z to A" command button
Different table formatting styles can be previewed by mousing over different options within "Table Styles" (i.e., you can see what the appearance will be like without actually applying a new style). Is this statement accurate?
Yes
Which of the following is TRUE about sorting a table?
Sorting a table re-arranges the rows based on the contents of a particular column.
When a column is sorted within a table, which of the following is true about the drop-down list in the header row?
It displays a different graphic to remind you that the table is sorted by that column
Please refer to the attached Excel workbook. How can you add a dollar ($) symbol to values in a column as seen in the "Grand Total" column?
One way to do this is by going through the "Format Cells" dialog, and selecting "Currency" (with a $ symbol and 2 decimal places) as your formatting option from within the Number tab
Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________.
Table
To gain access to the Table Tools / Design tab in the Excel Ribbon when working with tables, you should do the following:
Click anywhere on the table
Excel uses column headers to create labels for each _____ on the form.
Field
Which of the following is TRUE about sorting a table?
Sorting a table re-arranges the rows based on the contents of a particular column.
A formula refers to a value in the Total Row of a table. What will happen to the formula result if the option for "Total Row" (from the Excel ribbon: Table Tools / Design tab) is unchecked?
The formula returns an error
For this question, please download the attached Excel workbook. Convert the data on Sheet1 into a table. Next answer the following question: If you proceed to remove duplicate values using the "Remove Duplicates" feature in "Table Tools", will Excel remove one of the two table rows?
No, these are not considered duplicates by Excel. To remove the duplicate data you must ensure that the date in the second row is formatted the same way as the date in the first row.
What option in the Table Tools / Design tab removes a table without losing the data in it?
Convert to Range
When a column is sorted within a table, which of the following is true about the drop-down list in the header row?
Correct It displays a different graphic to remind you that the table is sorted by that column
Please refer to the attached Excel workbook. How can you add a dollar ($) symbol to values in a column as seen in the "Grand Total" column?
One way to do this is by going through the "Format Cells" dialog, and selecting "Currency" (with a $ symbol and 2 decimal places) as your formatting option from within the Number tab
Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________.
Table
To gain access to the Table Tools / Design tab in the Excel Ribbon when working with tables, you should do the following:
Click anywhere on the table
Excel uses column headers to create labels for each _____ on the form.
Field
Which of the following is a VALID range name in an Excel workbook?
_2ndyear
Suppose your worksheet has a comment in cell A1 and a comment in cell B5. Your active cell is B1.
Which command can you use to navigate to the comment in cell B5?
Review (tab) -> Comments (command group) -> Next
With a single IF() function, how many actions can you ask Excel to carry out?
2 (i.e., an action if the criteria is true, or an an action if the criteria is false)
In Excel, Shift + Spacebar (keyboard shortcut) is used to _______.
Select a row
The Analysis Toolpak can be activated through which sequence of steps?
Correct File-->Options-->Add ins -->Manage Excel Add-Ins --> Go
In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram?
Input Range and Bin Range
Suppose you have a named range called "myrange" on Sheet1 of your workbook. If you delete the rows or columns that contain the range "myrange", the name (in Name Manager) contains an invalid reference. If you open name manager and examine what "myrange" refers to, you will see the missing cells denoted by ______.
Sheet1!#REF!
The Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following?
#N/A
In the attached spreadsheet "Ch13_sum_reference.xlsx", please use an appropriate formula to compute the total of the Cells C2 through C11 (surrounded by a red border in the worksheet). What is this total?
14787.45
Use the attached spreadsheet "Advanced_aggregation.xlsx" to answer the following question.
Which formula can be used to find: How many songs were sung by singers whose last names contain exactly six letters?
=COUNTIF(B2:B23,"??????")
If you used the Excel data file to generate a histogram, and compared it with the histogram in the image, which of the following options best describes what would you see?
Three of the Histogram bins will have a different count
Which of the following is NOT a type of ANOVA (Analysis of Variance) performed using tools in the Analysis ToolPak?
Single-factor without replication
Which formula can be used to find: How many songs were sung by singers whose last names contain exactly six letters?
=COUNTIF(B2:B23,"??????")
The formula =COUNTIF(range, "*budget*") will count the number of cells in the specified range containing the word "budget" in certain positions within the text. Which of the following best describes the cells counted?
The word "budget" can appear anywhere within the cell.
What does selecting the option "Current region" within the: Go To Special Dialog Box (Home (tab) -> Editing (command group) -> Find & Select) do?
Selects a rectangular range of cells around the active cell
What would you type in the Find and Replace dialog box (Home (tab) -> Editing (command group) -> Find & Select) to find all four-digit entries that begin with 5 and end with 2?
5??2
Which of the following is NOT an advantage of using defined range names to work with ranges in Excel?
Moving to areas of the worksheet is controlled
Please see the attached workbook "Ch4_example.xlsx". What does the small red triangle on the upper right corner of cell B1 denote?
A Comment Exists for that cell
To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________.
Shift+F2
Examine the image provided. What does the middle button (circled in red) in the row of "Paste Value" do?
Pastes values and number formatting
Please select the Excel function which allows us to answer the question: "How many days of stock data do I have?" You may refer to the image in the question.
COUNT()
For a range called Data, what does the function =COUNTIF(Data, TODAY()) return?
number of cells containing the current date
Which of the following shows the correct syntax for referencing cell "A2" in another worksheet?
=A1*Sheet2!A2
What type of cell reference does the formula in E2 use? (refer to the attached image, the formula is highlighted)
absolute
Which of the following error codes indicates that a problem with a value exists? For example, a negative number was specified by the user where a positive number was expected.
#NUM!
What is the result of the formula =2^3 * (,10*10
excel tells you that the formula contains an error
If the formula =ROWS(Asset) * COLUMNS(Asset) that is used to calculate the number of cells in a range named Asset doesn't work, what does it imply?
Correct The range Asset consists of non-contiguous cells
What does the following formula =A1=C12 do?
Compares cell A1 with cell C12. If the cells are identical, the formula returns TRUE; otherwise, it returns FALSE
Please see the attached workbook "Ch4_range_names.xlsx". How many names does the workbook have defined?
2
Which of the following does the Formula Autocomplete feature in Excel take into account?
all of the options provided for this answer are valid
Which of the following function(s) can be used to count error values in a range?
all of the options provided for this answer are valid
If your formula uses named cells or ranges, you can either type the name in place of the address, or choose the name from a list and have Excel insert the name for you automatically.
You can also press the ____ button which displays the Paste Name Dialog box. If no names are defined, pressing this button has no effect.
F3
Examine the image in the question. You can store the results of a formula entered in the formula bar (e.g., =(25*10)/2 ) as an actual number (e.g., 125) in the active cell. What keyboard shortcut do you need to press to do this?
F9
A formula in Excel can consist of which of the following?
all of the options provided for this answer are valid
Functions use arguments in various ways. Fill in the blank: Depending on its purpose, a function may use ___________.
1. no arguments
2. one argument
3. a fixed number of arguments
4. an indeterminate number of arguments
5. optional arguments.
All answers (1, 2, 3, 4,5) are true
The SUM() function does which of the following in Excel?
Allows you to total up numbers in a specified range of cells
Examine the attached Excel file "chapter10_mixed_cell_references.xlsx". When you copy the formula in cell "C3" to "F7", the cell in "F7" contains which of the following formulas?
=$B7*F$2
Which of the following types chart types is a VALID option in the Charts ribbon group on the Insert ribbon tab?
all of the provided options are valid
A bar chart is essentially which of the following chart types rotated 90 degrees clockwise?
column
Which of the following best describes what a Sparkline is? (one of the new features in Excel 2010)
a mini-chart that is displayed in a single cell
Please refer to the figure. Which of the following View ribbon tab options was used to display the horizontal bar between the nonconsecutive rows containing "Astoria, OR" and "Victoria, TX"?
split
Fill in the blanks: A cell in an Excel worksheet is identified by its address, which consists of a column ___ and row ___.
letter, number
Which of the following is NOT a state an Excel workbook can be in?
expanded
Examine the attached workbook (Ch18_19_example.xlsx). If you format a chart element with the font seen in Chart 2, how can you can reset it to its original state ?
Either of the previous options (resetting through the menu or the right-click options work)
Examine the survey results in the attached: "Ch18_Charts_SampleData.xlsx" (in Sheet2). What type of chart would be most suitable to represent the survey response data?
Pie
A single element in a worksheet that can hold a value, some text, or a formula is referred to as which of the following?
cell
Which of the following is the file format of the Excel 97-2003 workbook?
.xls
Which of the following procedures will NOT save (nor give you an option to save) your Excel workbook?
Shift+S
You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks?
passwords are case sensitive
Please refer to the figure. Within the "Number" tab, how many categories are available for displaying numbers?
12
Which of the following is the keyboard shortcut that creates a new blank workbook in Excel?
Ctrl+N
How many worksheets does a new Excel workbook have by default when created?
3
You enter "(500)" into an unformatted cell in an Excel worksheet (without quotation marks). Which of the following will be generated by your entry?
a negative number
Which of the following keyboard shortcuts will add the current time to a cell in an Excel worksheet?
Ctrl + Shift + ;
Calculate the total yearly sales for the West Region and the South Region in cells "F5" and "F7" (shaded in orange). What is the total widget sales for all regions for the year?
$24,914
If you copy the formula in cell "B8" and paste it into cell "D8" (shaded in green), will it produce the correct total sales value for Quarter 3?
yes
After pressing "Alt + H" while in an open Excel worksheet, a number of Key Tips are displayed. Which ribbon tab do these Key Tips relate to?
Home
Please refer to the figure. What Excel element is shown in the red box?
Ribbon
In the Top Salespersons list, which feature can be used to populate the numbers "3," "4," and "5" in cells "A17," "A18," and "A19," respectively?
Autofill
Which of the following formulae must be entered in cell "C8" (shaded in blue) to get the total widget sales for Quarter 2?
=SUM(C4:C7)
Which of the following types of data may NOT be contained in a cell in an Excel worksheet?
objects
Which of the following file formats is NOT available when saving an Excel 2010 workbook?
.xlsq
Which of the following keys on your keyboard can be held to select multiple workbooks when opening an existing workbook?
Crtl