Skills Test Answers - Excel

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