Excel Test Answers (Part 1)
·
Excel Test Answers (Part 2)
Excel Test Answers (Part 3)
What formula returns the number of cells in
column A that are greater than 50?
=SUMIF(A:A,">50")
=COUNT(A1:A100)+50
=COUNTIF(A1:A100,">50")
=COUNTIF(A:A,">50")
When is an absolute cell reference used?
When you want a cell reference to stay fixed on a
specific cell.
When you want a cell reference to change when
copied and pasted to other cells
True or False: There is only ONE way to group
a pivot table item.
True
False
The logic for the formula in cell B2:
=IF(A2>5,.2,0) is expressed as:
.2 in cell B2 if the value in cell A2 is greater
than 5; otherwise return 0
0 in cell B2 if the value in cell A2 is greater
than or equal to 5; otherwise return .2
.2 in cell B2 if the value in cell A2 is less than
5; otherwise return 0
What is the keyboard shortcut for moving the
cursor to the first cell in the current worksheet (assuming no frozen panes,
this would be cell A1)?
Ctrl + Shift + Home
Ctrl + Home
Ctrl + End
Ctrl + Shift + End
When working in Page Break Preview, we can do
which of the following?
Only add or remove page breaks
Only change the print area
Change the print area, view exactly where the page
break occurs, add or remove page breaks
Only change the print area and add or remove page
breaks
Only add or remove page breaks and view exactly where
the page break occurs
When a list is filtered (showing only rows
that meet the criteria) and highlighted, the copy command will result in:
No option; Excel will not allow copying of filtered
rows
Only the highlighted cells being copied
A dialog box asking which rows to copy
Both visible and hidden rows being copied
To move around the tabs at the bottom of a
workbook:
Right click on desired tab, choose "move or
copy", choose where to place it in list provided
These are all correct
Click on desired tab and drag to new location
When you widen a column that results in excess
row height, you can eliminate the excess row height by:
All of these
Using the shortcut keys Alt-O,R,A
Double-clicking the bottom of the row number box
Using Home/Cells/Format/Auto Fit Row Height
Attaining data from a cell located in a
different sheet is called what?
referencing
locating
travelling
updating
functioning
To view a cell comment, do which of the
following?
click the comment command on the view menu
click the comment command in the insert menu
position the mouse pointer over the cell
click the display comment command on the window
menu
Formatting a cell in Currency, you can
specify...
none of the above
decimal places
currency symbol
both decimal places and the currency symbol
What is the difference between a workbook and
a worksheet?
They are the same
A workbook contains worksheets
Workbooks are Access databases and worksheets are
Excel
A worksheet contains workbooks
Data displays in the ____ as you type.
status bar
formula bar
insert function box
name box
True or False: If there are duplicate items in
a list, the COUNT function will not include the duplicates in the total count
of items.
True
False
True or False: If there are duplicate items in
a list, the COUNT function will not include the duplicates in the total count
of items.
True
False
In an alphabetical sort, If two cell contents
are identical, Excel sort?
Placing the item that came first in the original
list before the second item
None of these
merging the two cells
What is one easy method to see if your
spreadsheet will print on one page?
Adjust the Zoom to 100%.
Use Page Break Preview.
If it fits onto the screen, it will print on one
page.
Select "landscape" on paper set up.
To create another worksheet that is exactly
the same as the current worksheet you would...
Insert workbook
Insert worksheet
Move or copy
Which function will return the largest value
within the range?
MAX
LN
MID
Excel does not have such a function.
NLARGE
Which of the following cannot be included in a
header/footer?
Page Numbers
Dates
Images
Formulas that calculate
In a bar graph the horizontal axis is the:
Secondary axis
Y-axis
Latitudinal axis
X-axis
Which of the following Excel screen components
CANNOT be turned on or off?
Tool Bar
None of above
Status Bar
Formula Bar
Which worksheet function returns the average
of selected database entries?
DAVERAGE
DAVG
DCOUNTA
DCOUNT
DGET
To create another worksheet that is exactly
the same as the current worksheet you would...
Insert workbook
Insert worksheet
Move or copy
Does delete button remove cell?
YES it removes cell
Deletes all spreadsheets data
NO, it clears cell contents
DELETE has no effect on cells
Deletes entire Row
If the source of sparkline data contains non-numeric
data, they are neglected while plotting the sparklines.
True
False
Excel's data validation feature enables you
to...
request that all edits be sent to you for approval
set up certain rules that dictate what can be
entered into a cell
only accept data with a citation
limit what kinds of graphs a user can create
Which is not a lookup function in Excel?
LOOKUP
#LOOKUP
HLOOKUP
VLOOKUP
Where do you place a MACRO that is supposed to
run automatically after every entry on a worksheet?
Must manually run the Macro after each entry.
In a cell, place a hyperlink to the code on a
webpage.
Must use a Form to do this.
On the worksheet tab, go to "View Code"
and place the Macro there.
A function inside another function is called a
_____ function.
Nested
Round
Text
2nd Level
True or False: It is possible to upload a SWF
file into Excel.
False
True
How do you make sure that a MACRO won't
automatically run on a spreadsheet you've downloaded?
Impossible to block unless you open the spreadsheet
and delete/modify the MACROs
Don't download spreadsheets from the Internet.
Set your Security settings so that Excel will ask
permission first.
Control-Alt-Delete
Which function repeats text a given number of
times?
LOWER
PROPER
REPT
VALUE
TEXT
How do you change all positive numbers in a
sheet to negatives?
Type everything again with a - sign
Add "-" manually in front of each number
Write -1 anywhere in the sheet, copy it, select the
data to change, Paste Special, select Multiply
Select - and Press Enter
Delete the numbers and reenter
Which worksheet function returns the
individual term binomial distribution probability?
BETADIST
AVEDEV
BINOMDIST
AVERAGEA
BETAINV
Pressing CTRL-Home moves the cursor to:
Cell A1
The upper left cell in the active range
The A column within the current row
The upper right cell in the active range
To unhide a sheet and make viewable with other
tabs in the current window:
Press CTRL-U
Right click on any tab, select Unhide, choose the
tab from the list to unhide
Adjust Custom View selection in View menu
In the View menu select Unhide
You have a table of data listing each sales
person in one column and their territory in a second column. Each sales person
is only listed once but there are hundreds of sales people. What function could
you use to quickly display a salesperson's territory by typing in their name?
TEXT
SUBSTITUTE
ISERROR
VLOOKUP
How can you remove borders applied in cells?
Both by choosing "NONE" on the Border tab
of Format cells AND opening the list on Border on the Formatting toolbar, then
choosing "no border"
By going to page set up
Only by opening the list on Border on the
Formatting toolbar, then choosing "no border"
Only by choosing "NONE" on the Border tab
of Format ce
What is the correct forumla to calculate an
average between cells B3 and B6?
=AVERAGE(B3:B6)
=AVERAGE(B3+B4+B5+B6)/4
SUM(B3+B4+B5+B6)/4
=AVE(B3+B4+B5+B6)/4
=AVE(B3:B6)
In Sheet1, column A is January's sales
numbers. In Sheet2, column A is February's sales numbers. Which equation on
Sheet 3 totals both columns, in row 5?
=Sheet1!A5+A5
=A5+A5
=Sheet1!A5+Sheet2!A5
=Sheet1 + Sheet 2
How are columns named?
Metamorphically
Numerically
Alphabetically
Symbolically
What does the equation =TODAY() show?
The current date from Microsoft
The current date on your computer
The Julian date
Error since there has to be something in () for the
equation to work.
What is a circular reference?
An animation tool
A modeling tool
A formula that either directly or indirectly
depends on itself
A cell that points to a drawing object
Always wrong
Your spreadsheet is printing, but the grid
lines aren't showing up. What is most likely the problem?
Change out the printer cartridge.
Go to page set up and set to print as black and
white.
Need to set the cell to bold.
The formatting for grid lines isn't set on the
cell.
If you would like to count the number of cells
within a range that fit a specific criteria, which function would use?
COUNTTHOSEARE
COUNTONLYFIT
NUMFITCRITERIA
IFFIT
COUNTIF()
The cell reference of a range of cells that
starts in cell B1 and goes over to column G and down to row 10 is ….
B1;G10
B1*G10
G1-G10
B1:G10
The Solver in MS Excel is used for what?
To find only optimal maximum solutions to linear
programming problems
To find optimal minimum or maximum solutions to
linear programming problems
To find only optimal minimum solutions to linear
programming problems
What tool allows you to summarize and analyze
data in various categories?
Pie Chart
Copy and Paste
Pivot Table
Square Root Analysis
Which worksheet function converts a time in
the form of text to a serial number?
SECOND
TIMEVALUE
NOW
NETWORKDAYS
TIME
The Freeze Panes function allows the user to:
keep an area of a worksheet visible while you
scroll to another area of the worksheet.
drag an existing formula across multiple cells
within a range by utilizing the following.
add a macro which sets size-specific formatting
within a worksheet.
lock in all existing formulas and formats within
the cells that they currently reside.
In which tab is the page breaks preview
located?
file
help
tools
design
View
Does delete button remove cell?
Deletes all spreadsheets data
YES it removes cell
Deletes entire Row
DELETE has no effect on cells
NO, it clears cell contents
What tool is used to split the window into
different parts of the same worksheet?
Duplicate Box
Seperate Box
Split Box
Title Bar
Remove Box
How do you change the orientation of a
spreadsheet from portrait to landscape?
Page layout > orientation > landscape
Page layout > scale to fit > landscape
Page layout > landscape > orientation
How do you turn data from rows into columns in
Excel?
Copy data and then use Paste Special option with
Transpose box checked.
Regular Copy and Paste Process
Sort Function
You Can't
How do you create Word Art?
On the Page Layout tab, in the Page Setup group,
click WordArt
On the Review tab, in the Text group, click WordArt
On the Page Layout tab, in the Themes group, click
Effects
On the Insert tab, in the Text group, click WordArt
When a formatted number does not fit within a
cell, what does the cell display?
#####
none of these
#DIV/0
#DIV@
Which of the following formulas will Excel NOT
be able to calculate?
=SUM(Sales)-A3
=SUM(A1:A5)-10
=SUM(A1:A5)/(10-10)
=SUM(A1:A5)*.5
What tool allows you to summarize and analyze
data in various categories?
Square root analysis
Pie Chart
Pivot tables
Copy and paste
In Excel a ____ is an instruction in a
spreadsheet to carry out a calculation
Data
Insert
VLOOKUP
Formula
Function
You can change the color of the following in
Excel:
Headers/Footers
Background
Font
Borders
All Listed
You can edit a cell by
Selecting Edit>Edit Cell from the menu
Clicking the formula button
Double clicking the cell to edit it in-place
None of the Above
How do you keep others from changing the
values on a worksheet?
Name the file "Do not change.xls"
Make all values Bold font.
Use "Protect Sheet" and assign a
password.
Use track changes to see what has been changed.
What does this formula do?
=IF(E6=G6;"Nice";"Not so Nice")
Sums up the range E6 to G6 and if the sum is
greater than zero then, the formula returns 'Nice'
Creates borders around cell G6
It changes your font color to Red
Checks if E6 equals G6 and if it does, the result
is true and the formula returns 'Nice'
If $34 is entered into a cell, Excel will
interpret it as:
A reference to the sum of values in the 34th row
A value(number) with currency format
A #NAME? error
Text
True or False: You cannot insert comments for
individual cells.
False
True
In Excel a ____ is an instruction in a
spreadsheet to carry out a calculation
Insert
Data
Function
Formula
VLOOKUP
True of False: You can filter an Excel Pivot
chart so that it shows only the information you want it to show.
True
You can only filter subsets in a Pivot table
Only by creating completely new pivot tables
False
Which would you choose to create a bar
diagram?
Insert, Chart
Format, Chart
Tools, Chart
Edit, Chart
Which keyboard shortcut pastes copied
information?
Ctrl + v
Ctrl + p
Ctrl + c
Ctrl + Shift + v
Shift + v
How to you create a pivot table?
right click> options> pivot table
Insert>pivot table
Identify the error: When entering 325 in a
cell, the result shows up as 3.25.
This is a common glitch in Excel 2007. Upgrade
ASAP.
Fixed Decimal mode is turned on.
"What-if" analysis has converted all
entries for simple compiling.
Rounding has been set to reduce all numbers by 99%.
You can edit a cell by
None of above
Double clicking the cell to edit it in-place
Selecting Edit>Edit Cell from the menu
Clicking the formula button
True or False: Sparklines are a new part of
Excel 2010/2011 and are not available in previous versions.
True
False
To ensure a worksheet will print all on one
page, adjust the settings in the:
Zoom command
Margin Dialog box: Shrink to Fit
Page Layout/Page Setup dialog box
Paper size option
Which solver method will change variable cells
to meet an objective cell?
Cannot be done in Solver
Evolutionary
All Solver Methods
LP Simplex
GRG Nonlinear
You can use the formula pallette to...
enter assumptions data
copy a range of cells
create and edit formula containing functions
format cells containing numbers
True or False: Sparklines are also often
called "micro charts."
True
False
What does this formula do?
=IF(E6=G6;"Nice";"Not so Nice")
Sums up the range E6 to G6 and if the sum is
greater than zero then, the formula returns 'Nice'
Creates borders around cell G6
It changes your font color to Red
Checks if E6 equals G6 and if it does, the result
is true and the formula returns 'Nice'
If you want to highlight a row of numbers by
less than, greater than or equal to, what should you use?
Font
Page Layout
Conditonal Formatting
Fx
Print Preview
How do you ensure that the person using a
spreadsheet can always see the column headings?
Re type the headings every few lines.
Use Freeze Panes or Freeze Top Row.
Reset the view Zoom percentage.
Print to fit on one page.
What is the best formula to use to calculate
12 to the 4th power?
4!12
=12+12+12+12
=12!4
=12^4
=12*12*12*12
True or False: You can download content from
the net directly into Excel.
True
False
Pressing Ctrl - Home moves the cursor to
the A column within the current row
cell A1
the upper left cell in the active range
the upper right cell in the active range
A ________________ consists of a grid made
from columns and rows?
Cell
Spreadsheet
Formula
Label
Right clicking on a cell then selecting insert
from the cell menu allows you to do what?
Add a new cell, row, or column
Nothing
Insert an entirely new workbook
Creates a formula
True or False: A fixed dollar sign appears to
the far left in the cell often with spaces between it and the first digit.
Whereas a floating dollar sign appears immediately to the left of the first
digit with no spaces.
True
False
If there is text in a column and you try to
sort the entire column:
it will not sort the column
sorting function will be disabled
it will keep the text in the same cell and sort the
numeric values
an error message will appear
Which worksheet function returns the F
probability distribution?
SQDEV
QIDIST
TDIST
ZDIST
FDIST
The formula =NOW() displays:
The local computer's actual date and time
The time the file was opened
The time the file was last changed
The time the file was last saved
True or False? A pivot table must have column
headers.
True
False
How many sheets are there in Excel Workbook by
default?
5
4
2
3
The "Merge & Center" button does
what?
Combines the quantities in two cells and centers
the result
Merges cells together that are not physically
attached
Changes the font size on a cell
Merges a cell with text with attached cells and
centers the text over the range of cells
What is the correct formula to calculate the
total of cell B3 and B4?
=TOTAL(B3+B4)
=summary(b3+b4)
=SUM B4+B3
=SUM(B3:B4)
=SUMMARY(B3+B4)
Cells B3 through B121 list several values.
What equation shows the average of those values?
=AVERAGE(B3:B121)
=SUM(B3:B121)
=SUM(B3:B121)/10
=COUNT(B3:B121)
How do you make a chart?
Highlight cells of interest go to File, Cart
Highlight cells of interest go to Insert, Chart
Highlight cells of interest go to Tools, Chart
Highlight cells of interest go to Edit, Chart
Which one of the following formulas cannot be
calculated by Excel?
=SUM(Sales)-A3
=SUM(A1:A5)-10
=SUM(A1:A5)/(10-8)
=SUM(A1:A5)*.5
How do you view the 5th decimal place when
calculating costs?
Cannot view more than 2 decimals on currency.
Convert to text and view.
Increase the decimal to 5 digits.
Set to smaller font size.
How can an interactive chart be created?
With Form Controls, or with Active-X Controls, or
with Custom VBA Userforms
Only with Active-X Controls
Only with Custom VBA Userforms
Only with Form Controls
Only with a third-party add-in
True or False: Once cells are merged in Excel,
they can never be un-merged.
False
True
How do you insert saved images?
Choose Insert/Object
Choose Insert/Picture
Choose Home/Illustrations/Picture
Choose Home/Images
You can sort rows by:
Smallest to largest
Z to A
Oldest to newest
A to Z
All listed are correct
Where do you change the default font for new
workbooks?
File --> Help
File --> Info --> Permissions
File --> Options --> Advanced
File --> Options --> Proofing
File --> Options --> General
Are you able to do spell check in Excel?
Depends on Excel version
Yes, but only if there are no charts in the
workbook.
No
Yes
Cells D3 through Z3 list sales total for
various months. What does the equation "=SUM(D3:Z3)" show?
Average sales for all the months in D3 through Z3
Sales quota for each month.
Sale averages for each month.
Total sales for all the months in D3 through Z3.
If you have an error, how can you find ways to
fix it?
Go to "Data" and click on data tools.
You cannot request assistance.
Left click on the triangle, pause until you see a
question mark, click on the arrow, and scroll to " Help on this
error."
How can you change the date format of a cell
or group of cells?
right click, copy, paste
Select cell or cells, right click, format cells,
date, choose option.
insert, data, choose format
Ctrl + F3
type preferred format into formula bar
What is the proper shortcut for "Refresh
All"?
Shift + A
Ctrl + Alt + F5
Ctrl + A
Ctrl + Shift + A
When you write up a lookup formula to look up
the text "budget", the formula considers any of the following a
match: BUDGET, Budget, or BuDgEt. Is it possible to perform a case-sensitive
lookup?
Yes
No
True or False: To calculate trig functions in
degrees, you must convert them, or Excel will calculate them in radians.
True
False
What is the purpose of the Σ button?
Sorting Data
Totaling a column or row
Recording a macro
Auto-saving
In a large data set with 121,005 rows, You are
on the top row (row 1) of the data set. What is the fastest way to get to row
121,005?
ctrl + D
ctrl + A + delete
ctrl + down arrow
double click down arrow
If you want to set the print area which ribbon
should you use?
PAGE LAYOUT
DATA
REVIEW
HOME
FORMULA
Cell A1 contains the formula =$F$39. Copying
that formula to cell B2 will result in:
=G40
=$F$39
=$A$1
=$F40
A ________________ consists of a grid made
from columns and rows?
Spreadsheet
Label
Formula
Cell
Which of the following formulas is not entered
correctly?
=B1(N1)
=98-B11
=B1/N1
98-B11=
How does one save the active workbook without
altering the file or folder name.
Highlight and drag a cell.
Copy and paste.
Double click the box.
Click save button (on top of home)
What do you use to create a chart?
Data Wizard
Excel Wizard
Pie Wizard
Chart Wizard
What is a logical function?
A function that inserts or calculates dates or
times
A function that works with statements that are
either true or false
A function related to monetary calculations
A ________________ consists of spreadsheets.
column
form
row
workbook
cell
What happens when you double click on a cell?
It is highlighted
It become editable
Nothing
Its contents are deleted
How do you ensure that the person using a
spreadsheet can always see the column headings?
Re type the headings every few lines.
Print to fit on one page.
Use Freeze Panes or Freeze Top Row.
Reset the view Zoom percentage.
How many sheets are there in Excel Workbook by
default?
4
3
2
5
Which symbol is used as a reference operator?
A Colon
A Space
All of these can be used
A Comma
You have two columns summing your account
totals. The first column looks exactly right but the second one is not
displaying the way you want. How do you make the two columns look the same?
Delete both columns. Reytpe values.
Copy the first column. Select the second column.
Paste.
Copy the first column. Select the second column.
Use "Paste Special" to paste "Formats".
Delete second column. Insert new column. Retype
values.
When you insert a row, you have to reset all
the equation references because:
The results will skew otherwise
The columns will resize
The formatting has to change as well
Inserting a row does not require you to reset the
equation references
What will the formula =A1*C1 return?
An error
B1
The sum of A1 and C1
A1 raised to the power of C1
The product of A1 and C1
A pivot table allows you to:
Save data within a cell
Maneuver from one application database such as
Access to another such as Excel
Merge columns and rows together
Create a worksheet with data using a filtering
window where you can include or exclude desired fields of the original table
Import data from MS Word
How do you control which cells are printed?
Highlight the cells you want to print, go to File,
and select Print Area => Set Print Area
Highlight the cells you want to print, go to
Format, select Format Cells, and select the Border option. Then place a border
around the cells you want to print.
Which keyboard shortcut copies cell data?
Ctrl + Shift + c
Shift + c
Ctrl + c
Shift + v
Ctrl + v
Subscribe to:
Posts (Atom)
