Excel Test Answers (Part 2)

·


Excel Test Answers (Part 3)


 

A cell with a numerical value has a green corner. What does this mean?
Result of a formula
Formatting error
Number stored as text
Cell has a comment


To remove extra space from a cell, use the formula:
TRIM(cellNumber)
DELSPC(cellName)
REMOVE(cellLetter)
TRIM(cellName)
DEL(cellName)


What is the shortcut to launch the Visual Basic editor?
Alt + Shift + F7
Home + F5
Alt + F11
Ctrl + Shift + F11
Ctrl + F1


The value of cell C3 is 23 and the cell has the name Days; the formula =Days is entered into cell D3; does Excel return an error?
The formula should be =textvalue(Days)
The formula should be ="Days"
The formula should be =$Days
It will not return an error, the formula is correct and will return the value of 23


How do you generate random numbers 1-100?
=NUM1-100
=RANDBETWEEN(1,100)
=RAND:1,100
:RAND (1,100)
+RAND:1,100


Which worksheet function returns the most common value in a data set?
MEDIAN
MINIMUM
MODE
MEAN
MAXIMUM


Which worksheet function returns the average of its arguments, including numbers, text, and logical values?
AVERAGEA
BETAINV
AVEDEV
AVERAGE
BETADIST


What is the most elegant way to change the Tab Color for all the Sheets in a document?
Right-click on any Sheet Tab and choose "Select All Sheets" then right-click on any Sheet Tab again and choose "Tab Color"
Right-click on any Sheet Tab and choose "Select All Sheets"
Click on any Sheet Tab and choose "Select All Sheets" then click on any Sheet Tab again and choose "Tab Color"
Right-click on any Sheet Tab and choose "Tab Color/Apply to All Cells"


If A1 contains the text "Red" =OFFSET(A1,0,0) will return
0
Red
#REF
#Name?


Identify the one function in the following list that does not require the use of any argument(s):
SUM
IF
COUNT
RAND
COUNTA


What Tab do you use to create a background Graphic?
Insert > Background
Page Layout > Background
Insert > Illustrations
Page Layout > Themes
Insert > WordArt


Can you insert images from ClipArt into Excel?
No
Yes


Which Function should be used to count all the cells which are not empty?
=CountA()
=CountIF()
=CountIFS()
All of the Above
=Count()


How would you determine how many blanks are in the range of cells between A2 and A35?
=IFBLANKCOUNT(A2:A35)
=COUNT("A2:A35")
=COUNTIFBLANK(A2:A35)
=COUNTIFBLANK A2:A35
=COUNTBLANK(A2:A35)


What does COUNTA () function do?
counts cells containing numbers
counts non-empty cells
counts cells containing letters
counts empty cells


True or False: The value in a cell that contains the formula =RAND() only changes when the file is resaved.
True
False


Why will Excel return an error for the formula =HLOOKUP(A1,B2:E6,6,0)?
There is no row "0"
A1 should be entered as $A$1
The column of cells (sixth) to evaluate is outside of the lookup range
There is no exact match


If you choose Find & Replace All when the cursor is in one cell, it will result in:
Replacement only in that cell
The command replacement of the entire worksheet
None of these
Replacement only in that column.


What does the keyboard shortcut F12 do?
Displays the Paste Name dialog box.
Inserts a new worksheet.
Displays the Save As dialog box.
Opens the Microsoft Visual Basic For Applications Editor.
Maximizes or restores the selected workbook window.


Under what tab can the Scenario Manager be found?
Home
Data
Insert
Formulas
View


How would you ensure that B1 divided by A1 always equals zero when the value of A1 is zero?
=IFERROR(B1/A1, 0)
=IFERROR(A1/B1, 0)
=IF(A1/B1=Error, 0, A1/B1)
=B1/A1
=IF(B1/A1=Error, 0, B1/A1)


Column A contains first names and Column B contains last names. What formula would output in the format "Last, First" in cell C7?
=B7 & ", " & A7
=A7 ", " B7
=A7 & ", " & B7
=B7 ", " A7


The key board short-cut to insert TODAY'S DATE in a cell is:
Ctrl + 0 (zero)
Ctrl + ;
Ctrl + V
Ctrl + D


Which error message will appear if you enter the following wrong function in a cell? =CONCATINATE(A1,A2)
#VALUE!
#NUM!
#NULL!
#NUL!
#NAME?


What are the two syntaxes for the AGGREGATE function?
REFERENCE, COUNTA
AVERAGE, ARRAY
FUNCTION, COUNTA
REFERENCE, ARRAY


What does a “Green Triangle” in the top-left corner of a cell indicate?
The cell is empty.
The cell contains a comment.
Error checking options.
The cell contains a smart tag.


Which Windows Keyboard Shortcut switches between worksheet tabs, from right-to-left.
CTRL+PgDn
CTRL+SHIFT+DOWN ARROW
CTRL+PgUp
CTRL+SHIFT+UP ARROW


True or False: The print area cannot be set to discontinuous ranges.
True
False


Transparent objects will appear in all versions of Excel, no matter what the year, true or false?
False
True


Cell A1 has this information "Current Value: $10.00" Which function extracts "Value:" from "Current Value: $10.00"?
Left()
Right()
Mid()
None of the above.


A formula to show the date for the previous Sunday could be:
=TODAY()-(TODAY()-1,3)
=TODAY()-MOD(NOW()-1,3)
=TODAY()-MOD(TODAY()-1,7)
=TODAY()-MOD(TODAY()-1,3)
=TODAY()-MOD(NOW()-1,7)


What is the function of DEGREES?
To convert a numeric string into temperature.
To return a numeric expression as a degree string.
To convert radians into degrees.
To convert numbers values in to degrees.


If you copy a cell and move to another area of the worksheet and first want to insert a row before pasting, the copied data is erased from the clipboard.
False
True


How to make the same data appear in cell A3 on every worksheet?
It's only possible if you have 3 sheets or less.
Right-click on any Worksheet Tab and choose Select All Sheets. Then type the data in cell A3 and go to the following worksheet to annul the All Sheets selection.
Right-click on any Worksheet Tab and choose Select All Sheets. Then type the data in cell A3, right-click the cell, and choose Clear All Sheets Selection.
All of these.


Up to how many IF statements can be nested in a single IF function?
64
10
48
24
12


Which worksheet function extracts from a database a single record that matches the specified criteria?
DSTDEVP
DCOUNT
DMIN
DCOUNTA
DGET


If you want to make a text box transparent, you should:
Right click the text box > Options > Transparent
Left click the text box > Home tab > Fill > No Fill
Right click the text box > Format Shape > Fill > No Fill
Not possible with a text box; only Word Art allows this option


Which of these characters is not allowed as part of a tab name?
> 
?
;
"
< 


Rounding errors can occur when you use _____ in a formula.
Only multiplication and division
Only multiplication
Multiplication, division, or exponentiation
None of these
Only exponentiation


What's the recalculate shortcut?
F5
Shift
F9
F4
Tab


Which worksheet function returns a value along a linear trend?
FORECAST
FINV
FTEST
FISHER
FDIST


The formula EXACT("Fish","fish") returns:
TRUE
FALSE
#NAME?
None of these


What is the function to display only the current date?
DATE()
NOW()
CURRENT()
TODAY()


You can replace a formula with its _____ so it remains constant.
Results
Function
Total Value


What does the 1 represent in this formula: =Now()+1
an error
one hour
one minute
one day


What do you call the chart that shows the proportions of how one or more data elements relate to another data element?
Pie Chart
Column Chart
XY Chart
Line Chart


What combination of keys do you need to use for an array formula to work?
No key combination is required, an array formula can be entered in the same way as any other formula
Ctrl+Shift+A
Ctrl+a
Ctrl+Shift+Enter




Which of the following is not a predefined replacement for the [format] portion of the following expression? FORMAT (expression, [format])
Scientific
Text
Currency
Fixed





Array formulas in Excel work with...
only one data value
one or more data values
none of these
more than one data value


Double clicking the currently-selected tab on The Ribbon will do what?
Open customize window
Automatically format worksheet
Collapse the ribbon
Pull up dialog box
Add another ribbon menu


If A1 contains the text "Red" and B2 contains "Blue" then =OFFSET(A1,1,1) returns
Red
Blue
#NAME?
#REF


How do you close a excel program using keyboard shortcut?
ctrl+alt+w
ctrl+w
alt+w
shift+w
shift+ctrl+w


Which formula should be used to find the row "Steven" appears in and return his phone number? (Sheet contains Names in Column A and Phone Numbers in Column B)
=VLOOKUP("Steven",A:B,1,FALSE)
=VLOOKUP("Steven",A:B,2,TRUE)
=VLOOKUP(A:B,"Steven",2)
=VLOOKUP("Steven",A:B,2)
=VLOOKUP("Steven",A:B,2,FALSE)


What does CTRL(CMD)+D do?
Deletes the active cell
Opens the Font dialog box
Navigate to the Data ribbon
Fill down


A custom list gives you the ability to:
create column or row headings within a table.
create a drop down list within your workbook.
Enter the first word in the list and use the autofill handle to complete the list.
There is no such thing as a custom list in Excel.


When you use the Freeze Panes command, Excel freezes all of the columns and rows:
above and to the left of the selected cell.
below and to the left of the selected cell.
above and to the right of the selected cell.
that are currently selected.
below and to the right of the selected cell.


True or False: Zero values are not plotted using sparklines.
False
True


Which Windows Keyboard Shortcut unhides any hidden rows within the selection.
CTRL+SHIFT+~
CTRL+SHIFT+&
CTRL+SHIFT+(
CTRL+SHIFT_


Can you set 1-inch left indentation for a cell in Excel?
Indentation can be set from Format Cells dialog box
Excel does not have indentation feature
You can specifiy indentation only if you turn the rulers off
You can specify indentation only if you turn the rulers on
The indentation can be specified only when printing


The formula QUARTILE(a1:a100,4) returns:
Max(A:1:A100)
The maximum value of the range
All of these
100% Percentile Value of the A1:A100 range
The highest number in the list


Under which ribbon item can you find protection options?
Security
Review
Data
Edit
View


Where do you enable/disable the Developer Tab?
File --> Options --> Customize Ribbon
File --> Options --> Add-ins
File --> Options --> General
File --> Options --> Advanced
File --> Options --> Proofing


A Sparkline is basically______________ set that allows you to quickly and easily spot trends at a glance.
a little chart displayed in a cell representing your selected data
A chart displaying the selected data
a chart displayed either in the same worksheet or a selected destination, representing your data.


If you want to freeze the top row and first column which cell must you highlight before clicking Freeze Panes?
A1:B2
B1
B2
A1


You are given an Excel 2007 spreadsheet with two columns of data. Cells A1:A1000 have dates and cells B1:B1000 have counts corresponding to those dates. You are asked to find the sum of all of the counts for dates between 01-Jul-2012 and 31-Dec-2012 (inclusive), what is the correct formula:
=sum($A$1:$A$1000,if($B$1:$B$1000 between 01-Jul-2012 and <=31-Dec-2012))
=sum(if(and($B$1:$B$1000 >= DATEVALUE("01-Jul-2012"), $B$1:$B$1000 <=DATEVALUE("31-Dec-2012")),$A$1:$A$1000,0))
=SUMIFS($B$1:$B$1000,$A$1:$A$1000,">=01-Jul-2012",$A$1:$A$1000,"<=31-Dec-2012")
=SUMIF($A$1:$A$1000,">=01-Jul-2012 and <=31-Dec-2012",$B$1:$B$1000)
=COUNTIFS($A$1:$A$1000,">=01-Jul-2012",$A$1:$A$1000,"<=31-Dec-2012")


Cell A1 has text in the format YYYYMMDD. How do you convert this to a valid Excel date?
=DateText(A1)
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
=CONVERT(A1,"YYYYMMDD",Date)
=DATEVALUE(A1)
=MID(A1,5,2) & "/" & RIGHT(A1,2) & "/" & LEFT(A1,4)


Which function lets you reference a cell or range (that hasn't been named) using text values?
INDIRECT
REFTEXT
REFERENCE
INDEX
VALUE


What will be the result of this formula =MOD (40, 0) ?
#VALUE
#DIV/0!
0
Blank
40


All of the following are recognized file extensions in Excel except:
.htm
.csv
.xlsb
.pdf
.xlsbb


How can you cause text to display vertically in a cell?
Choose 180 Degrees in Orientation of Format Cells box
Choose Center Across Selection from Horizontal combo box in Format Cells box
Choose Distributed from the Vertical drop down list of Format Cells box
Choose Vertical on Text alignment in Format Cells box
Choose 90 Degrees in Orientation of Format Cells box


How do you add chart gridlines?
Right click, add gridlines
Select chart, click Gridlines button in Axes group on Chart Tools Layout tab, select option
Left click on chart, click on format, click on add gridlines
Click on chart, click on format, select the option


What is the maximum number of columns allowable in an Excel worksheet?
65536
16,384
256
512


Cell B1 contains the text "A1". What function do you enter in cell C1 that refers to B1 and as a result shows the value in cell A1?
Search()
Substitute()
Exact()
Indirect()
Replace()


True or False: The visual basic editor can be used even when Excel is not running
False
True


You have entered data in a cell B2. You want cells B3:B16 to have the same data. What is the fastest way?
Select B3, enter first character of data, press enter. Select B4, enter first character of data, press enter...up to B16.
Select B2, Copy. Select B3, paste. Select B4, paste... up to B16.
CTRL-D? or CTRL+D?
Highlight B2:B16 and press CTRL+D.
Select B2, Copy. Select B3:B16, Paste.


One of the major differences between the inStr function in VBA and the Excel Search function is that...
Search can never be called in a VBA module, while inStr can.
inStr will not search a whole string, while Search always searches a whole string.
inStr returns 0 if the value is not found, while the Search function produces an error.
inStr value can never be printed to a cell, while the Search function always prints to a cell.


What function converts a text string into a cell reference?
=Text()
=Indirect()
=Reference()
=Convert()
=AddressT()


Which worksheet function returns the skewness of a distribution?
SKEWNESS
SKEW
SKEWDISTRIB
SKEWDIST
SKEWNESSDIST


What VLOOKUP Function provides the multicolumn range or name of the range or date table to be searched?
Table_array
range_lookup
Lookup_value
Col_index_num


To convert the date 12/14/2014 in cell A1 to text in the format of YYYYMMDD, which is a valid option?
=DateText(A1)
=TEXT(A1,"YYYYMMDD")
=RIGHT(A1,4) & LEFT(A1,2) & MID(A1,5,2)
=TEXT(DATEVALUE(A1),"YYYYMMDD")
=DATE(YEAR(A1),MONTH(A1),DAY(A1))


What is the correct answer: A1 value = 10 A2 value = 0 =IFERROR(A1/COUNT(A1:A2),"Err")
10
Err
5


Which of these choices is NOT one of the four function arguments in a VLOOKUP?
Lookup_value
Table_array
Table_lookup
Range_lookup
Col_index_num


what is the function key for creating a chart for a given table?
F4
F10
F11
F1


Which is false regarding Conditional Formatting?
We can delete any condition from Conditional Formatting if it is not required
We can set conditions to look for BOLD and apply ITALICS on cells
We can apply FONT, BORDER, and PATTERN formats that meet specified conditions
We can add more than one condition to check


Cell A1 contains the string "tom jones". To transform the contents to "Tom Jones" the formula is:
=NAME(A1)
=UPPER(A1)
=PROPER(A1)
=CAPS(A1)


What keyboard shortcut will create a chart of the data in the current range in a separate Chart sheet?
Alt + F11
Ctrl + Shift + F11
Ctrl + F11
F11
Alt + Shift + F11


What is the correct term for separating contents from one cell into two by a common character?
Delimited
Text to Columns
Fixed Width
VLOOKUP
Seperated


What function returns information about the formatting, location or contents of a cell?
None of these are correct.
=Cell()
=Verify()
=Audit()
=Sheet()


Sometimes, data that you're charting may be missing one or more data points. Excel offers three ways to handle the missing data. All of the following are ways to handle missing data EXCEPT:
Connect with line: Missing data is interpolated
Gaps: Missing data is simply ignored
Zero: Missing data is treated as zero
Text: Missing data is bridged with words


What is the formula to find the first day of the following month?
=(LASTDAY(NOW)+DAY()+1)
All of them
=EOMONTH(TODAY(),0)+1
=EOMONTH()+1
=((NOW)+1)


Cell A1 contains the formula =$F39. Copying that formula to cell B2 will result in:
=$F39
=$F40
=F40
=G40


How do you create a range for a sum function that anchors itself on A1, but will increase in range downwards as you drag and drop the formula?
Sum($A1:A2)
Sum(A1:$A$2)
Sum(A$1:A$2)
Sum(A1:A2)
Sum(A$1:A2)


Which of the following formulas exists in excel, but does not appear on the formula pallet?
=COUNTA
=DATEDIF
=SERIESSUM
=YEAR


Excel Test Answers (Part 1)



Subscribe to: Posts (Atom)