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
How do you close a formula?
(
%
"
)
,
Clipart can be found under what menu?
Data
Formulas
Insert
File
Review
In order to change page margin, which menu would you choose to select "Margins"?
Insert
Page Layout
View
Data
Review
True or False: Frequency is an array function.
False
True
A pivot table allows you to:
Import data from MS Word
Create a
worksheet with data using a filtering window where you can include or exclude
desired fields of the original table
Maneuver from one application database such as
Access to another such as Excel
Merge columns and rows together
Save data within a cell
Is SmartArt available in Excel?
No, it is only available in Word.
Yes
No, it is only available in PowerPoint.
True or False: You can save any Excel document as a web page.
False
True
How do you change text's alignment within a cell?
Select the cell
and click the appropriate alignment button in the alignment group on the Home
tab
Select auto-fit in the cell
You can not align an individual cell
Use the commands in the font group on the Home tab
Which is a type of operator?
All of these
Reference
Text Concatenation
Arithmetic
Comparison
You can embed Tables from Excel in:
Neither
Both
Power Point
Word
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 second column. Insert new column. Retype
values.
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".
What is a cell?
The intersection
of a column and row.
A header.
A series of rows.
A series of columns.
The total of two numbers.
In order to multiply items in Excel, you would use which symbol?
@
/
*
!
#
Pressing CTRL-Home moves the cursor to:
moved down
cell a1
Data can be arranged in a worksheet in an easy to understand manner by
Applying Styles
Changing fonts
Auto Formatting
All of these
Changing decimals
How would you rename the tabs at the bottom of the spreadsheet?
Both ways: right
click on tab and type new name or double click tab and type new name.
Left click tab and type new name.
Single click on column header and type new name.
lookup _value argument is the value that is searched for in the first column of the table array
True
False
You can change the color of the following in Excel:
Background
Borders
All Listed
Font
Headers/Footers
How could one delete an embedded object?
Select it
Press Shift + Delete
Press Alt + Delete
Select it, press
Delete
Double-click it
After typing data or a formula into a cell and hitting ENTER, the default action is:
Stay in the cell
Move down one
cell
Move up one cell
Move Right one cell
A collection of worksheets contained within a single file is called a ____ .
Range
Book
Workarea
Workbook
Cell
What does an asterisk signify in a formula?
Square Root
Divide
Add
Subtract
Multiply
Can Excel open two documents with the same file name simultaneously?
Yes
No
What is a column on a worksheet?
A consecutive group of cells within a worksheet.
A vertical group
of cells within a worksheet.
A diagonal group of cells within a worksheet.
A horizontal group of cells within a worksheet.
A lateral group of cells within a worksheet.
What do you click on to navigate between worksheets in a workbook?
Tab
Files
Rows
Cell
If a set of numbers has an even number of items, the MEDIAN function will return:
An error
The average of
the two numbers in the middle of the set
The higher of the two numbers in the middle of the
set
The lower of the two numbers in the middle of the
set
The Remove Duplicates command:
Opens a dialog box asking to delete the duplicates
in the existing range or copy to a new range
Copies and pastes the list without duplicates in
the right-adjoining column
Removes
duplicates from the range that is highlighted based on selected criteria
Copies and pastes the list without duplicates in a
new worksheet
The Doughnut chart type
Compares
relative values of different categories to the whole. Similar to the pie chart
except that it can display multiple sets of data.
Compares values from different categories. Values
are indicated by the height of the columns
Compares three sets of values in a
three-dimensional chart
Compares a collection of values from several
different data sets
The Remove Duplicates command:
Copies and pastes the list without duplicates in a
new worksheet
Opens a dialog box asking to delete the duplicates
in the existing range or copy to a new range
Removes
duplicates from the range that is highlighted based on selected criteria
Copies and pastes the list without duplicates in
the right-adjoining column
How do you create a reminder to follow up on a document?
Data/Validation/Input Message
Tools/Scenarios/Add/Merge
Tools/Flag for
Follow Up/Enter Date & Time
Insert/New Comment
True or False: The value in a cell that contains the formula =RAND() changes every time any change is made to the workbook.
True
False
You can sort rows by:
Z to A
Oldest to Newest
A to Z
Smallest to Largest
All listed are
correct
A function inside another function is called a _____ function.
Nested
Round
Sum
Text
Combined
When entering formulas and functions in Excel, the symbols *, /, +, and - are called:
Operators
Divisors
Signs
Changers
Constants
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.
Which function copies data from a row into a column or vice versa?
Hyperlink
Rows
Index
Transpose
The process of identifying specific rows and columns so that certain columns and rows are always visible on the screen is called?
Freezing
Fixing
Holding
Selecting
Attaching
Spell check can be found in the _______ menu
Data
Review
File
Formula
Insert
Which one is NOT a column chart type?
Clustered cylinder
Doughnut
Stacked column
Stacked pyramid
What happens when you double click on a cell?
It is highlighted
It become
editable
Nothing
True or False: You can convert an existing shape to a Freeform Shape.
False
True
What tool allows you to summarize and analyze data in various categories?
copy and paste
Pivot tables
Square root analysis
Pie chart
Where can you see the contents of an active cell?
View bar
Formula bar
Tool bar
Screenshot bar
Page Preview bar
What is the formula used to multiply cell A1 by cell C1?
=A1XC1
=A1*C1
=A1+AC,times
= A*C,1
= A1/AC
What is the home position in an Excel worksheet?
H1
B3
A0
H
A1
The formula =4>6 returns:
FALSE
2
TRUE
-2
True or False: The formula =((A2+B5)*5% is valid.
False
True
In the formula =sum(B1:B5), what does the ":" represent?
Last Number
All numbers between
(if applicable)
Several Numbers
And
First Number
To insert a character that is not part of an alphabetic font sent (such as copyright ©) use the:
Character set in WORD and copy into the worksheet
=@(C) function
Insert/Symbol
commands
Draw command to create it
What does Live Preview allow the user to do?
View distant parts of a worksheet at the same time
Store frequently used features
See how a new
font, font size, table size, or cell size will look on selected data before it
applies
Control the magnification of the screen
Can Excel spreadsheets contain images?
Yes
A special add-on must be installed
They can be inserted only with Macros
No, Excel files cannot have images
Only when the Workbook is imported from another
file format
It is normal practice that numbers are treated as decimals when entering into a cell, but is it possible to enter a fraction (eg 3/4) into a cell in Excel?
Yes
No
True or False: It is possible to access Outlook, Word or other Microsoft products through Excel.
False
True
How do you translate a time into a decimal value.
=TIMEVALUE
Ctrl T
Look into the date and time function
=Time
How do you change text's alignment within a cell?
Use the commands in the font group on the Home tab
You can not align an individual cell
Select the cell
and click the appropriate alignment button in the alignment group on the Home
tab
Select auto-fit in the cell
How do you make sure that a formula's result will always fit into the cell?
Set the cell's
format to "Shrink to fit".
Use Zoom to see the cell.
Make the cell very large.
Set the font size to 8 or less.
Formulas in excel start with?
(
-
=
%
When the string 12345678910 is contained in Cell B3, which formula below returns the last 5 digits of the string?
=LEFT(B3,7)
=RIGHT(B3,5)
=RIGHT(B3,7)
=LEFT(B3,5,7)
True or False: When you record a macro in Excel, you are actually creating a VBA program.
False
True
True or False: You can create a connection to a SQL Server Analysis Services Cube in Excel.
False
True
Can you add borders to cells without having a border for the workbook?
Yes, individual
cells or a collection of them can have borders.
No, this function is only available in Word.
No, cells cannot have borders, they can only be
added to the entire workbook.
True or False: A sparkline usually shows trend information.
False
True
The functions AVERAGEIF and SUMIF both use cell values that match specified criteria.
False
True
True or False: It is possible to sort information both vertically (by columns) and horizontally (by rows)
True
False
True or False: A Name can be applied to a range of non-contiguous cells.
False
True
What does the function FV stand for?
Future Variety
Present Value
Current Value
Future Value
How many conditions can be set for an individual cell's conditional formatting?
0
3
2
Any of these
1
True or false? The Quick Access Toolbar (QAT) is useful for adding popular commands.
True
False
True or False: Excel will recognize patterns in data and automatically enter formulas where the patterns continue.
False.
True.
What is one way to get a new workbook?
File, New
Escape Key
Format, Row
Edit, Cut
File, Save
True or False: You can protect a single worksheet and leave the others with full access.
False
True
Where can you insert a text box in Excel?
Only in designated columns
Anywhere
Only in designated rows
In cells that do not currently contain any data or
text
Only in columns with red arrows in the corner
Where do you find the picture icon?
The review tab.
The data tab.
The home tab.
The insert tab.
The formula tab.
Can you print only a selected part of your workbook?
Yes, you can
control what part of the workbook is printed.
No, you cannot select parts of a workbook, only the
number of pages to print.
True or False: Excel has trigonometry functions built into it.
False
True
True or False: You can add new commands to the QAT.
False
True
True or False: A pivot table is a "dynamic summary report generated from a database."
False
True
Double-clicking column separators will do what?
Create and additional column in between the two
columns
Delete both columns
Adjust number alignment
Automatically
adjust column width
Hide Columns
It is possible to password protect content of selected cells so that cannot be modified
True
False
What type of chart would you create in order to present how much each component comprise the whole?
Pie chart
Bar chart
Bubble chart
Ratio and proportion chart
Scatter plot
The best way to automate a complex, repetitive task within a worksheet, is to create:
a page break.
a macro.
a pivot table.
a bar chart.
a hyperlink.
Each location in an Excel spreadsheet is called a:
Tab
Cube
Cell
Square
Can the Match & Index Functions be combined together to perform a lookup?
Yes
No
You can convert your Excel document into a Google doc.
True
False
Each Excel file is called a workbook because
It will have text in it
It can be modified
You have to work hard to create it
It can contain
many sheets including worksheets and chart sheets
The standard name of each cell is based on its ___________ and __________
Row and column
Age and sequence
Level and priority
Height and width
Identify all the arguments and the correct order of arguments needed to execute the IF() function.
logic test
logic test, value if false, value if true
logic test,
value if true, value if false
logic test, value if false
Can a macro save a file?
No
Yes
If you place the cursor in cell A2 which contains text and press the keystrokes CTRL-B / CTRL-I / CTRL-U the result will be:
Text is copied up to cells B1 and I1
Cell A2 and all cells under it are made bold and
italic
All cells in worksheet are Book Antiqua font, in
italics and underlined
Text in cell A2
is bold, in italics and underlined
Which of the following is a "Lookup and Reference" function?
Iferror
VLookup
Average
Sumif
Sum
What does the function DATE(year, month, day) do?
Extracts the day of the month from the date value
Extracts the year number from the date value
Adds the number of days in between dates
Creates a date
value for the date represented by the year, month, and day arguements
Which of the following is NOT a way to align a cell?
Align Right
Align Reverse
Align Center
Align Left
Is it possible to sort by the color of a cell in the most recent version of excel?
No
Yes
True or false: To change column width in MS Excel, put cursor on the right side of the cell in that column, in the first row. Click and hold and drag to desired width.
False
True
An array range that includes both rows and columns is known as____?
A
two-dimensional array.
A single-dimensional array.
All array ranges must have rows and columns.
Currency figures in Excel can be changed from US Dollars to the:
Yuan
None Listed
Rupee
All Listed
Euro
How can you remove borders applied in cells?
Choose None on Border tab of Format cells
Both of these
Open the list on Border tool in Formatting toolbar
then choose first tool (no border)
How do you create a table on excel?
insert>table
data>table
How can you set 0.5 inch left indentation for a cell in Excel?
This is impossible.
This is possible, but the answer is not listed
here.
Indentation can
be set from Format Cells box.
You can specify indentation only if you turn the
rulers on.
The indentation can be specified only when
printing.
Each Excel file is called a workbook because
It can be modified
It can contain
many sheets including worksheets and chart sheets
It will have text in it
You have to work hard to create it
True or False: It is possible to have a folder of templates in Excel to make it easier to access them.
True
False
In a Excel spreadsheet, It's possible to select multiple non-adjacent ranges of cells.
True. We select
the first block of cells and then we use the CTRL key + selecting the other
blocks .
True. We select the first block of cells and then
we use the SHIFT key + selecting the other blocks.
False. It's not possible to select multiple non adjacent
rages of cells, in Excel.
You want to lock rows 1:2 and column A in place as you scroll through a large spreadsheet. Which command should you use?
Freeze Top Row
View Side by Side
Freeze Panes
Arrange
Split
If you see a small red triangle in the top right corner of a cell, it means there is something 'attached' to the cell. What is it?
A comment
A macro
A hyperlink
A formula
In an alphabetical sort, if two cell contents are identical, Excel sorts by:
Adding a " ~ " to the end of the second
item to make it unique
Placing the item
that came first in the original list before the second item
None of these
Merging the two cells
What would you use to only view the rows in a spreadsheet that meet the criteria you want?
Auto Filter
Goal Seek
Find
Auto Complete
In the Find and Replace function it is NOT possible to replace what you've searched for with an alternative at the same time.
False
True
The definition of a macro is:
a recording of
commands that can be played back at any time.
an anchor which holds that value of a referenced
component unchanged.
the prevention of unauthorized use.
a feature that allows the user to see how a new
font or font size will look before actually applied.
What kind of formatting allows you to create a set of rules to format cells based on a cell value or another cell?
Output Formatting
Conditional
Formatting
Input Formatting
Text Formatting
Table Formatting
Which language is used to create macros in Excel?
Visual Basic for
Applications
Java
Visual C++
C#
C
Which worksheet function returns the minimum value in a list of arguments?
MIN
MINA
MINARG
MINVAL
MINIMUM
What is a function of data markers?
You can change
the position of a data marker and automatically change the data point value in
the worksheet
You can change a data print value and automatically
turn it into a chart
You can automatically apply formatting to a data
series
What is the correct syntax for typing a function into a cell?
Function name, argument, equals sign
Equals sign,
function name, arguments
Function name, equals sign, arguments
Equals sign, function name, result
In an Excel worksheet, the active cell is indicated by:
a dotted border
a blinking border
a thick black
border
None of these
What does letter H stands for in HLOOKUP formula?
Hidden
Horizontal
Highest
Header value
However
Which worksheet function returns covariance, the average of the products of paired deviations?
COVAR
FREQUENCY
COUNT
DEVSQ
CHITEST
Can macros close currently open worksheets?
No
Yes
What is the purpose of the BIN2HEX function?
To multiply the BIN by 2.
To compare binary and hexadecimal numbers.
To convert a
binary number to a hexadecimal number.
To convert a hexadecimal number to a binary number.
Which worksheet function returns the one-tailed probability of the chi-squared distribution?
CHIDIST
AVERAGE
BINOMDIST
BETAINV
BETADIST
How are rows named?
Alphabatically
Metamorphically
Symbolically
Numerically
What would the following formula do? =Sheet1!D9+Sheet1!E9
This would select D9 and E9 and add the two
together from sheet2
This forumla doesn't do anything.
This would select D9 and E9 and create an average
from sheet2
This would
select D9 and E9 and add the two together from sheet1
Which types of charts can excel produce?
Only line graphs
Bar charts and line graphs only
Line graphs and pie charts only
Bar charts, line
graphs and pie charts
Can Excel spreadsheets contain images?
No, Excel files cannot have images
A special add-on must be installed
Yes
They can be inserted only with Macros
Only when the Workbook is imported from another
file format
What is the short cut for saving a file in Excel?
Shift + S
Ctrl + F
Command + K
Shift + C
Ctrl + S
True or False: You can create a two-input data table.
True
False
True or False: Formulas can be edited across multiple worksheets at the same time.
True
False
Is there a way to share your spreadsheet and allow someone to change it?
Yes
Only by email
No
Which types of charts can excel produce?
Line graphs
Line graphs and pie charts only
Bar charts and line graphs only
Bar charts, line
graphs and pie charts
What does the "V" in "VLOOKUP" stand for?
Verified
Vitric
Validated
Valkyrie
Vertical
In order to multiply items in Excel, you would use which symbol?
&
/
#
!
*
Data can be arranged in ascending or descending order by using?
Sort command
from Data menu
Sort command from Table menu
Sort command from Tools menu
None of these
What would the function "=Large(DataRange, 2)" return?
The longest string in the DataRange
The second
largest value in the DataRange
The number with the highest exponent in the DataRange
Which function searches for a value in a specified table array?
=search()
=insert()
=searchfile()
=browse()
=vlookup()
Data can be arranged in ascending or descending order by using:
Sort Command from Table Menu
Sort Command from File
Sort Command from Tools Menu
Sort Command
from Data Menu
Sort Command from Formatting Menu
True or False: You can customize your toolbars and menus.
False
True
What-If analysis functionality in Excel includes:
Data Tables
Scenario Manager
Goal Seek
All of these
True or False: Charts from Excel can be pasted into Power Point.
False
True
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
Both visible and hidden rows being copied
Only the
highlighted cells being copied
A dialog box asking which rows to copy
Under which tab would you access Visual Basic?
Data
Formulas
Insert
Home
Developer
Which of the following is not a term of MS-Excel?
Cells
Columns
Rows
Document
The difference between centering text in a cell and centering a number is:
The text center command is part of the font
selection
A number must be centered with the
"Accounting" format option
A number must always be right justified and cannot
be centered
There is no
difference
Can you save a .xlsx file as a .xls file?
Yes
No
What can be protected from accidental modification?
Only contents
Contents and objects
Contents,
objects, and scenarios
Only objects
Objects and scenarios
a formula is always started with what symbol?
-
=
*
$
+
Does conditional formatting override the formatting set in a cell?
Yes - if the
condition(s) are met
Depends on the user settings.
No - the manually set format will override the
conditional formatting
No - unless a MACRO is used.
The formula ISERROR(A1) returns TRUE if A1 is :
Any of these
#REF!
#VALUE!
#N/A
Which is an INCORRECT formula for sum of cells A1 to A5?
=sum(a1+a2+a3+a4+a5)
=sum(a1:a5)
=a1+a2+a3+a4+a5
=add(a1:a5)
True or False: The formula =((A2+B5)*5%) is valid.
False
True
What's the function for calculating variance?
VAR
VARI
It does not exist
STANDARDDEVROOT
VARIANCE
When multiplying A1 and B1, where A1=4.33 and B1=6.111, what is the formula to achieve an answer rounded to the closest tenth?
A1*B1
=ROUNDA1
=(ROUND)A1*B1
=ROUND(A1*B1,1)
=RND(A1/B1)
The function =VLOOKUP("Widget",D4:E9,2,False) is correct format
True
False
To speed up the calculations of a data table on a worksheet,
Automatic option is not available
You can create a running balance formula in Tools
menu Formula auditing Mode
You can change
the Calculation options to automatically recalculate the worksheet.
How do you make a long sentence fit into the column size?
Sentence Wrap
Text Edit
Shrink To Fit
Wrap Text
Increase the size of the column
How do you copy paste only a number from a cell and not the formula that goes with it?
Edit, copy, control + P
Edit, copy, desired location, paste, format
Edit, copy,
desired location, edit, special paste, values
Control + shift + V
Edit, copy, paste
Cell A1 contains the number 3, Cell A2 contains the number 4. If Cell A3 contains the formula =IF(A1>A2, "true", "false") what solution will Excel return?
#name?
#NUM
true
false
N/A
Excel can retrieve external data from which of the following sources?
All of these
SQL Server
XML Data Import
Microsoft Query
Data Connection Wizard
How can you replicate only the formatting of a graph onto other graphs in your workbook?
Copy, Past Special, All
This function does not exist
Copy, Paste
Copy, Paste
Special, Format
Format Painter
The following functions are listed when the office button is selected:
Print
Save as
Publish
all of these are
functions
Close
A user can sort a maximum of...
7 columns
10 columns
5 columns
3 columns
Infinite columns
How does one change the font style of the selected cell to *bold*.
(Ctrl+B)
click twice
Which worksheet function returns the correlation coefficient between two data sets?
CHIINV
CHIDIST
CORREL
CHITEST
COREL
Applying a $ to a cell reference in a formula:
Turns on wrap text in that cell.
Converts the result of the formula to text.
Applies currency formatting to the result of the
formula.
Keeps the
referenced component unchanged as you copy this formula to other locations.
Which Excel feature allows a user to split apart values in a text string into separate fields?
Consolidate
Remove Duplicates
Text to Columns
What-If Analysis
What is the operator for concatenating two text strings together?
&
!
^
+
#
True or False: When you write a formula in Excel, the references in the formula are limited to that worksheet only.
False
True
In Excel 2010, you can open the Highlight Changes dialog box by choosing Track Changes from which menu?
Edit
Format
Review
Insert
What is the function used for removing all non-printable characters from a string in Excel?
CLEAN()
DEL
DEL()
CLEAN
What "Insert" option should you use for a PDF?
Chart
Picture
Object
Spreadsheet
Which formula correctly references cell A1 on Sheet2?
=Sheet2:A1
=Sheet2!A1
=Sheet2A1
=Sheet2-A1
=Sheet2|A1
Which worksheet function Estimates the standard deviation based on a sample of selected database entries?
DSTDEVP
DCOUNT
DCOUNTA
DGET
DSTDEV
What character is used in a formula cell reference so that it can be copied elswhere but still refer to the same cell or cells?
*
$
/
&
In the formula, which symbol specifies the fixed columns or rows?
*
&
$
%
What does the Excel function Lookup & Reference do?
Provide statistical analyses of a set of data
Return logical values
Look up and
return data matching a set of specified conditions from a range
Analyze engineering problems
Which command locates values in a worksheet?
Save
Search
Print
Locate
Find
To delete an embedded object,
select the object by clicking it
double click the object
select it and
then press the delete key
Can you create Macros on excel?
true
false
What is one way to get a new workbook?
Edit, Cut
Format, Row
Escape key
File, New
File, Save
Which function provides similar functionality to using the & symbol in a formula?
=COMBIN
=CORREL
=COUPPCD
=CONCATENATE
=CUMIMPT
True or False: The SUMIF function adds values in a range that cannot be defined by the user.
True
False
Which of the following formats can you decide to apply in the AutoFormat dialog box?
All of these
Border format
Font format
Number format
Which is NOT a method to calculate A1 to the power of B1
=POWER(A1, B1)
=SQRT(A1,B1)
=A1^B1
Concatenation of text can be done using
Exclamation (!)
Apostrophe (‘)
Question mark (?)
Semi-colon (;)
Ampersand
(&)
Which character always appears in formulas with external references?
^
*
&
!
The accounting style shows negative numbers in
quotes
italics
strikethrough font
parentheses
bold
Is it possible to determine the day of the week for a particular date?
Yes, Excel
offers several ways of doing so.
No, this will result to computer error.
No, Excel does not have this capability
What is the correct syntax of the worksheet function “RANK”?
RANK(known_y's,known_x's)
RANK(x,mean,standard_dev)
RANK(number,ref,order)
RANK(probability,mean,standard_dev)
RANK(known_y's,known_x's,new_x's,const)
The Quick Access toolbar does which of the following?
Displays the contents of the currently active cell
Displays the name of the currently active cell
Marks the currently active cell or range
Stores shortcuts
to frequently used features
Which of the following is correct syntax for typing a function into a cell?
ABS(A2-A4)
=Covar()
=IF(A2>0,1,0)
All of these
(B46-C46)^2=
Which formula can add the all numeric values in range of cells, ignoring those which are not numeric, and place the result in a different cell?
Count
Function
Sum
Adding
Average
Using formulas, how could I combine text resulting in "Check # 1234 paid 02/15/2013 - pending cash application"? Cell B4 refers to the date.
=TEXT(Check # 1234 paid, ADD(02/15/2013)," -
pending cash application"END)
=+VALUE("Check
#",IF(1234),DATE(02/15/2013) =("- pending cash application")
=SUMALL("Check #
",VALUE("1234"),"paid",DATE(B4,"mm/dd/yy"),"
- pending cash application")
=CONCATENATE("Check
# 1234 paid ",TEXT(B4,"mm/dd/yy")," - pending cash
application")
True or False: Excel has the capability to sort horizontally from left to right or right to left.
True
False
What is the fastest way to retrieve more than one comment?
Ctrl + F2
Click on
"Review" and scroll down to "show all comment."
Click the cells one by one to read comments.
Not sure, I would go to the "Help'' tab.
Hold Shift and double-click any cell with a
comment.
Which of the following is an absolute cell reference?
A1
%A%1
$1$A
#A#1
$A$1
If I want to unlock some cells after protecting the spreadsheet, what should I do?
First, select
the cells. Then, remove the check before the LOCKED option under the PROTECTION
tab of the FORMAT CELLS menu.
First, select cells. Then, right click and select
the Unlock option.
First, select cells. Then, go to the REVIEW menu
and select PROTECT SHEET.
First, select cells. Then, Under the Insert menu,
select Unlock.
A red triangle at the top right corner of a cell indicates what?
There is an error in the cell
The cell can’t accept a formula
The cell is connected to another cell
There is a
comment associated with the cell
The font color of the text in cell is red
Imported data appears entirely in column A. How do you separate the data into individual columns?
Data Validation
Text to Column
Unwrap text
Ungroup
Data
Which character denotes a text entry?
'
@
#
()
$
Column A contains an apartment number, column B is the street number, and column C contains the street name. I would like all these values to be in the same cell; what function could I use to accomplish this?
Join Cells
Not possible in Excel
Concatenate
Create String
What does the Mod function do in an equation?
Returns divisor
Returns the
remainder
Returns zero
Returns divider
A formula with brackets such as {sum((C2:C1)*(D2:D11))} is called:
The geometric mean
A statistical function
A pivot formula
An array formula
A formula in Excel must begin with the operator:
=
/
@
--
Where can I find formulas in excel without having to type it?
Formulas tab in
toolbar --> search for formula
Data tab in toolbar-->Formulas
Home tab in toolbar-->Formulas
When a cell containing a formula is copied and then pasted into a new cell, Excel:
Only adjusts absolute cell references
Does nothing
Erases the original copy of the formula
Doesn't adjust relative cell references
Edits cell
references in the newly copied formula
If cell A1 contains the value "32.96" and you format the cell to show only 1 decimal place, which value is displayed?
32.9
None of These
33.0
32.0
32.960
Which is NOT a function?
DMV
SUM
PMT
AVERAGE
MAX
What does a formula usually start with?
/
$
=
-
+
When a formula references cells that are affected by the same formula, what type of error results?
Text error.
Boundary error.
#DIV/0!
A circular
reference.
True or False: There are three worksheets with every new workbook. You can change that automatic number if you want to.
True
False
True or False: A Name can be applied to a single cell or a range of cells.
False
True
Which group under the Home tab contains the "Clear" command?
Font
Editing
Alignment
Format
Tables
Which group under the Home tab contains the "Clear" command
Editing
Tables
Format
Font
Alignment
To get all rows and columns of data to print on one sheet, you can adjust the page breaks in Page Break Preview or:
Select File, Print, Print Entire Workbook
Select File,
Print, Fit Sheet on One Page
Select File, Print, Print Selection
Select File, Print, Fit All Columns on One Page
What can help format cell contents according to predetermined conditions?
Cell color
Format
None of these options
Conditional
formatting
All of these options
The name box does which of the following?
marks the currently active cell
displays the contents of the currently active cell
stores shortcuts and information about a workbook
displays the
name of the currently active cell
Which formula will extract the first name out of a cell(A2) with first and last names in it like "David Hawley".
=LEFT(A2,FIND("
",A2))
=Collect(A2," ")
=DEL(A2," ")
=RIGHT(A2,FIND(" ",A2))
A "frequency distribution" is a summary table that shows the frequency of each value in a ___?
Range
Workbook
Cell
Row
Column
To compare B14 and C12 and return the value of C12 if it is less than the value of B14, write =IF(____, B14, C12)
C12>B14
B15
B14>C12
B19
If A1=6 and B1=4, then what is =A1&B1?
10
2
1.5
24
64
Which of the following functions returns the greatest numeric value?
MAXIMUM
MAX
MAXNUM
MAXVAL
MAXIMUMVAL
Cell A1 contains "Chang", and cell A2 contains "Pie". What is the result of the formula: =CONCATENATE(A1," ","and"," ",A2)
Change, Pie
Chang Pie
Chang and Pie
Chang & Pie
Pie, Chang
The date algorithm attributes the number 1 to January 1st of which year?
1990
1900
1980
2000
Which of the following is a concatenating operator?
!
#
&
All are concatenating operators
'
If I enter the text =A2&B2 into a new cell what action is performed?
An error is produced.
Cells A2 & B2 are added together in the new
cell if they are both numerical / date fields or concatenated if text.
Cells A2 &
B2 are concatenated in the new cell.
Cells A2 & B2 are added together in the new
cell if they are both numerical / date fields or an error is produced for text.
To control the display of the numbers along the horizontal axis of a line graph, adjust the:
Maximum value of the Y axis
Scale of the x
axis
Range of the source data
Scale of the y axis
What is the effect of adding an apostrophe before the equals sign in the cell entry =(98-42)^0.5?
Excel enters the value of the result rather than
the formula
That cell will require recalculation (press F9) to
update
The result is #VALUE!
The result is centered when viewed in the cell
Excel treats it
as a text entry and displays the formula
How would you find the absolute value of A1?
=A(A1)
=A1
=ABSOLUTE(A1)
=AB(A1)
=ABS(A1)
Which of the following ways does NOT allow you to import audio into Excel?
Retrieve from
email
Browse from file
Audio Browser
Which of the following is NOT an Excel file format?
.xlsx
.xlv
.xlt
.xls
How do you create a cell featuring a drop down list of values?
Using Data Validation and "any value" as
validation criteria
Using List Box (form controls) from the Developer
tab
Using Data
Validation and "list" as validation criteria
Simply creating a name range
When a cell is entered in a formula without using the $ symbol, what type of reference occurs?
Verification Reference
Relative
Reference
Cross Reference
Mixed Reference
Absolute Reference
What would you enter first in a cell to show formula text?
(ft)
question mark (?)
colon (:)
apostrophe (')
Column A lists a long string (example:A3= 1234Smith1740.434.21) where the first four digits are the employee number. What formula in B3 will extract only the employee number from the string in cell A3?
=IF(A3="Ted","1234","")
=VALUE(LEFT(A3,4))
=RIGHT(A3,4)
=VALUE(TEXT(A3,4))
When you create a "Table" in excel, does Excel automatically give it and each of its cells within it a Named Range?
Yes
No
Which of the following formulas shows the correct syntax of a VLOOKUP() formula in the 3rd column?
=VLOOKUP(Data_Range,B14,3,FALSE)
=VLOOKUP(B14,Data_Range,3,FALSE)
=VLOOKUP(B14,Data_Range,FALSE)
=VLOOKUP(B14,Data_Range)
The function DAY(date)
Displays the current date
Extracts the day
of the month from the date value
Extracts the year number from the date value
Displays the current date and time
Cell A1 contains the string " Tom Jones" (two blank spaces before the name Tom). To remove the two blank spaces, the formula is:
=TRIM(A1)
=LEFT(A1,2)
=CLEAN(A1)
=EXACT(A1)
What does the VALUE function do?
Counts the number of cells in a range of cells
Converts a
string that represents a number to a number
Adds the cells specified by a certain criteria or
condition
Adds all the numbers in a range of cells
Which worksheet function estimates standard deviation based on a sample?
STANDDEV
STDEV
STANDARD
STDSAMPLE
STDEVIATION
The SUMIF function is very useful for single-criterion sum formulas. The SUMIF function takes three arguments NOT including:
Point
Range
Sum_range
Criteria
Which formula will provide the same result as the following: =CONCATENATE("Smarter","er")
="Smarter"+"er"
=COMBINE("Smarter","er")
=JOIN("Smarter","er")
="Smarter"&"er"
You can use a template on Excel by going to...
Insert > Smart Art > then picking a template
File > New
> then picking a template.
Page Layout > Background > then picking a
template
Page Layout > Themes > then picking a
template
Excel doesn't have this option.
The two types of cell references are:
Basic and Absolute
Figurative and Numeric
Type 1 and Type 2
Numeric and Data
Relative and
Absolute
What function would you use to extract a portion of a text string from the center of the string?
trim
search
text
mid
find
Does changing the number of displayed decimals change the value Excel is storing?
Yes - changing the decimals will cause the number
stored to round to the nearest decimal.
No - the number
remains the same no matter how it is displayed.
Yes - Unless you set your cell to "Auto
fit"
No - Unless you zoom first.
To show a "-" instead of a zero in cells with zero value, you must adjust the settings in what way?
View / 'Show/Hide' menu / Suppress zeros
Requires a macro
Font Settings / Suppress Zeros
Set the number
system to "accounting"
In the formula =VLOOKUP(a2,c2:d100,2,false) the final argument indicates:
Find all numbers ending in zero
Find the closest match
Find an exact
match
Return the first column
Suppose cell A1 has value "1" and cell B1 has value "2". After merging cell A1 and B1, what will be the value of Cell A1?
1
3
2
#NUM
12
When using the PERMUT (number,number_chose) function, what syntax error will occur if the number_chosen is non-numeric?
#N/A
#NAME?
#NUM!
#VALUE!
What is a shortcut to minimize or maximize the ribbon?
CTRL+ALT+DEL
CTRL+ F1
SHFT+DEL+ALT
TAB+DEL
CTRL+SHFT
Which worksheet function returns the k-th smallest value in a data set?
COUNT
LOGEST
SMALL
MINA
RANK.AVG
Cell A1 has the number "3" in it. Cell A2 has the value "5" in it. If you merge these two cells together, what is left in the merged cell?
Merge is not allowed betweens cells that each
contain a value.
Merging deletes the values in both cells, so the
cell will be blank.
8
5
3
The Format Painter can be deactivated by:
Selecting the "Clear Format" function.
Pressing ESC on
the keyboard or clicking the "Format Painter" button again.
Clicking on "Undo".
Double clicking on the "Format Painter"
button.
A ____ is a predefined set of colors, fonts, chart styles, cell styles, and fill effects that can be applied to an entire workbook.
Format painter
Theme
Format
Layout
If A1=7 and B1=7 what will the formula "=A1=B1" return?
TRUE
14
7
It will be an error
FALSE
When editing a formula, what shortcut will toggle the absolute reference setting for a cell address?
F4
Ctrl + Esc
F2
Alt + F9
F5
Which of the following types of files preserves cell properties, formulas, graphics, and other formatting?
None of these
.txt
All of these
.slk
.csv
The transpose action in Excel is found in:
An add-in
TRANSP() formula
A format option
Paste Special
Command
When entering a formula, what key or combination of keys can be hit to quickly make a cell an absolute reference?
Control + G
F8
F4
F6
Control + J
What is the default alignment for the number in a cell?
bottom alignment
top alignment
center alignment
left alignment
right alignment
Which of following is NOT one of Excel’s what-if functions?
Auto Outline
Goal seek
Scenario manager
Solver
Which worksheet function returns the rank of a number in a list of numbers?
RANKN
RANKLIST
RANKL
NRANK
RANK
What happens to a "relative" cell when it is copied to a new cell?
Nothing
It returns an error message
####
It's reformatted
It's relatively
modified
Which formula allows you to find a desired result when you don't know the input value that will return it?
PIVOTTABLES
OPTIMIZER
FIND and REPLACE
GOALSEEK
Which function returns the Student's t-distribution?
DISTIRBT
TDISTRIB
TDIST
STUDENT
Of the following, which is the quickest way to copy the format of one cell onto another?
Select both cells and click the Synchronize Formats
button.
Right click on the cell you want to copy the format
from and drag it to the cell you want to copy the format to.
Select the cell you want to copy the format from
and select Edit > Copy Format. Then click on the new cell and select Edit
> Paste Format.
Select the cell
you want to copy the format from, choose the Format Painter, and click on the
new cell.
Go to Format > Cells..., check the Copy Format
From button and enter the cell you want to copy the format from.
In the following formula, where is the Table Array located? =VLOOKUP(A5:A19,[RegionalSales]FY2012!$D$2:$J$31,7,FALSE)
Cell Range A5:A19
Regional Sales Worksheet, Cell Range A5:A19
FY2012 Workbook, Regional Sales Worksheet, Cells
D2:J31
Regional Sales
Workbook, FY2012 Worksheet, Cells D2:J31
What does the function FV stand for?
Future Value
Fraction Value
Financial Value
Fully Verified
Find Version
Which function calculates your monthly mortage payment?
NPER (number of periods)
PV (present value)
PMT (payments)
All of these
What conclusion could you reach if Cell B13 contained the text Apple and the formula =LEN(B13) returned 6?
There are 6 spaces after the word.
There is an
extra space somewhere in the text.
There is an error in the formula. The correct
result should be 4 as the LEN() function does not count capital letters.
Cell B13 is formatted as a number.
True or False: Converting a table back to a range will maintain the table style used
False
True
Each column in Excel has a corresponding number. To extract this number you can use the following formula:
countcolumn()
colnumber(column letter)
colcount()
column()
Which is not a type of Cell Format?
Fraction
Text
Digit
Time
Date
How do you debug a formula by looking at each part of the formula individually?
Impossible in Excel
Conditional Formatting
Formula Auditing > Error Check
Data Validation
Formula Auditing
> Evaluate Formula
When the formula bar is active, you can see:
(none of these)
(all of these)
the edit formula button
the cancel button
the enter button
What does the function PV do?
Post Value
Returns the
present value of an investment
Past Value
Percentage of Variance
Which worksheet function counts all cells with numerical and text values?
COREL
COUNTA
CHITEST
COUNT
DEVSQ
What is the correct formula to reflect the logic "If cell A1="Pass" and cell B1="Pass" then cell C1 should equal "Pass", otherwise C1 should equal 'Fail' "?
=IF(OR(A1="Pass",B1="Pass"),"Pass","Fail")
=(A1="Pass" and B1="Pass", then
"Pass", else "Fail")
=IF(A1="Pass" AND
B1="Pass"),"Pass","Fail")
=IF(AND(A1="Pass",B1="Pass"),"Pass","Fail")
Using IF(), to test if a number in cell D2 was between 10 and 1000 the correct function would be:
=IF(10>D2,D2<1000,TRUE)
=IF(10<D2<1000,TRUE)
=IF(AND(D2>10,D2<1000),TRUE)
=IF(10<D2,D2>1000,TRUE)
What function would you use if you want to find the 3rd largest value in a set of data?
LARGE
PERCENTILE.INC
MAXA
TREND
DEVSQ
Which feature allows you to display multiple copies of your worksheet in side-by-side windows?
Sections
Panes
Subsheets
Freeze
Views
If A1 contains the text "Red" then =IF(A1="Blue",1,"")
TRUE
0
1
Blank
FALSE
Which worksheet function counts the number of cells containing data (either numbers or labels) in a selected range?
COUNTA
DEVSQ
CHITEST
COUNT
COREL
How many arguments exist in the IF function?
2
3
4
5
The formula QUARTILE(a1:a100,1) returns:
75% Percentile Value of the A1:A100 range
25% Percentile
Value of the A1:A100 range
1% Percentile Value of the A1:A100 range
100% Percentile Value of the A1:A100 range
The difference between the COUNT function and the COUNTA function is:
COUNT only
recognizes cells in the range that contain values; COUNTA will also include
cells that are text
COUNT recognizes numbers and text; COUNTA only
recognizes cells that contain text
COUNT only recognizes numbers; COUNTA only
recognizes cells that contain text
They are interchangeable and return the same value
What is the main difference between a sumif equation and a sumifs equation?
sumifs equations allow only one criteria. Sumif
equations allow multiple criteria.
sumifs equations allow multiple sum_range columns.
Sumif equations allow only one sum_range column.
sumifs equations
allow multiple criteria. Sumif equations allow only one criteria.
sumifs equations do not exist. Sumif equations do
exist.
Which worksheet function returns the k-th largest value in a data set?
LARGEK
KLARGE
LARGE
HIGHEST
LARGEST
How many workbooks can a user maintain open simultaneously?
This is a user-customizable feature
45 workbooks, except Excel 2008 for Mac which
accepts only 40
Limited only by
system resources and available memory
A maximum of 40 workbooks or 250 worksheets
How do you create a Pivot Chart?
Click
"Insert Pivot Chart" on Insert tab
Click "Charts" under "Other"
tab and select "Pivot Chart"
Click Insert Tab and choose "Pivot
Table", then save as "Pivot Chart" in File Menu
Right-click dataset and click "Edit"
In Excel 2010, to protect a worksheet, choose Protection and then Protect Sheet from which menu?
Format
Review
Data
Edit
The common Illustrations possible under the Insert tab include all of these, EXCEPT?
Clip Art
Shapes
SmartArt
Picture
WebArt
A green flag will appear on the upper right-hand side of a cell when it contains:
A number with leading or trailing spaces
A number
formatted as text or preceded by an apostrophe
An entry obtained from a formula
An ignored circular reference
What function will display current date and time in MS Excel?
B. Today ()
C. now ()
A. date ()
D. time ()
Given range of cells A1:A100, select the formula that would return the 3rd lowest value from the selected range.
=FLOOR(A1:A100,3,TRUE)
=FLOOR(A1:A100,3,FALSE)
=SMALL(A1:A100;3)
=MIN(A1:A100;3)
Cell A1 contains the text string 'X489Smith1740'. To determine the number of characters in the text string, use the formula:
=COUNT(A1)
=CHAR(A1)
=LEN(A1)
=LENGTH(A1)
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