Excel Test Answers (Part 1)


Excel Test Answers (Part 2)


Excel Test Answers (Part 3)

 


What formula returns the number of cells in column A that are greater than 50?
=SUMIF(A:A,">50")
=COUNT(A1:A100)+50
=COUNTIF(A1:A100,">50")
=COUNTIF(A:A,">50")


When is an absolute cell reference used?
When you want a cell reference to stay fixed on a specific cell.
When you want a cell reference to change when copied and pasted to other cells


True or False: There is only ONE way to group a pivot table item.
True
False


The logic for the formula in cell B2: =IF(A2>5,.2,0) is expressed as:
.2 in cell B2 if the value in cell A2 is greater than 5; otherwise return 0
0 in cell B2 if the value in cell A2 is greater than or equal to 5; otherwise return .2
.2 in cell B2 if the value in cell A2 is less than 5; otherwise return 0


What is the keyboard shortcut for moving the cursor to the first cell in the current worksheet (assuming no frozen panes, this would be cell A1)?
Ctrl + Shift + Home
Ctrl + Home
Ctrl + End
Ctrl + Shift + End


When working in Page Break Preview, we can do which of the following?
Only add or remove page breaks
Only change the print area
Change the print area, view exactly where the page break occurs, add or remove page breaks
Only change the print area and add or remove page breaks
Only add or remove page breaks and view exactly where the page break occurs


When a list is filtered (showing only rows that meet the criteria) and highlighted, the copy command will result in:
No option; Excel will not allow copying of filtered rows
Only the highlighted cells being copied
A dialog box asking which rows to copy
Both visible and hidden rows being copied


To move around the tabs at the bottom of a workbook:
Right click on desired tab, choose "move or copy", choose where to place it in list provided
These are all correct
Click on desired tab and drag to new location


When you widen a column that results in excess row height, you can eliminate the excess row height by:
All of these
Using the shortcut keys Alt-O,R,A
Double-clicking the bottom of the row number box
Using Home/Cells/Format/Auto Fit Row Height


Attaining data from a cell located in a different sheet is called what?
referencing
locating
travelling
updating
functioning


To view a cell comment, do which of the following?
click the comment command on the view menu
click the comment command in the insert menu
position the mouse pointer over the cell
click the display comment command on the window menu


Formatting a cell in Currency, you can specify...
none of the above
decimal places
currency symbol
both decimal places and the currency symbol


What is the difference between a workbook and a worksheet?
They are the same
A workbook contains worksheets
Workbooks are Access databases and worksheets are Excel
A worksheet contains workbooks


Data displays in the ____ as you type.
status bar
formula bar
insert function box
name box


True or False: If there are duplicate items in a list, the COUNT function will not include the duplicates in the total count of items.
True
False


True or False: If there are duplicate items in a list, the COUNT function will not include the duplicates in the total count of items.
True
False


In an alphabetical sort, If two cell contents are identical, Excel sort?
Placing the item that came first in the original list before the second item
None of these
merging the two cells


What is one easy method to see if your spreadsheet will print on one page?
Adjust the Zoom to 100%.
Use Page Break Preview.
If it fits onto the screen, it will print on one page.
Select "landscape" on paper set up.


To create another worksheet that is exactly the same as the current worksheet you would...
Insert workbook
Insert worksheet
Move or copy


Which function will return the largest value within the range?
MAX
LN
MID
Excel does not have such a function.
NLARGE


Which of the following cannot be included in a header/footer?
Page Numbers
Dates
Images
Formulas that calculate


In a bar graph the horizontal axis is the:
Secondary axis
Y-axis
Latitudinal axis
X-axis


Which of the following Excel screen components CANNOT be turned on or off?
Tool Bar
None of above
Status Bar
Formula Bar


Which worksheet function returns the average of selected database entries?
DAVERAGE
DAVG
DCOUNTA
DCOUNT
DGET


To create another worksheet that is exactly the same as the current worksheet you would...
Insert workbook
Insert worksheet
Move or copy


Does delete button remove cell?
YES it removes cell
Deletes all spreadsheets data
NO, it clears cell contents
DELETE has no effect on cells
Deletes entire Row


If the source of sparkline data contains non-numeric data, they are neglected while plotting the sparklines.
True
False


Excel's data validation feature enables you to...
request that all edits be sent to you for approval
set up certain rules that dictate what can be entered into a cell
only accept data with a citation
limit what kinds of graphs a user can create


Which is not a lookup function in Excel?
LOOKUP
#LOOKUP
HLOOKUP
VLOOKUP


Where do you place a MACRO that is supposed to run automatically after every entry on a worksheet?
Must manually run the Macro after each entry.
In a cell, place a hyperlink to the code on a webpage.
Must use a Form to do this.
On the worksheet tab, go to "View Code" and place the Macro there.


A function inside another function is called a _____ function.
Nested
Round
Text
2nd Level


True or False: It is possible to upload a SWF file into Excel.
False
True


How do you make sure that a MACRO won't automatically run on a spreadsheet you've downloaded?
Impossible to block unless you open the spreadsheet and delete/modify the MACROs
Don't download spreadsheets from the Internet.
Set your Security settings so that Excel will ask permission first.
Control-Alt-Delete


Which function repeats text a given number of times?
LOWER
PROPER
REPT
VALUE
TEXT


How do you change all positive numbers in a sheet to negatives?
Type everything again with a - sign
Add "-" manually in front of each number
Write -1 anywhere in the sheet, copy it, select the data to change, Paste Special, select Multiply
Select - and Press Enter
Delete the numbers and reenter


Which worksheet function returns the individual term binomial distribution probability?
BETADIST
AVEDEV
BINOMDIST
AVERAGEA
BETAINV


Pressing CTRL-Home moves the cursor to:
Cell A1
The upper left cell in the active range
The A column within the current row
The upper right cell in the active range


To unhide a sheet and make viewable with other tabs in the current window:
Press CTRL-U
Right click on any tab, select Unhide, choose the tab from the list to unhide
Adjust Custom View selection in View menu
In the View menu select Unhide


You have a table of data listing each sales person in one column and their territory in a second column. Each sales person is only listed once but there are hundreds of sales people. What function could you use to quickly display a salesperson's territory by typing in their name?
TEXT
SUBSTITUTE
ISERROR
VLOOKUP


How can you remove borders applied in cells?
Both by choosing "NONE" on the Border tab of Format cells AND opening the list on Border on the Formatting toolbar, then choosing "no border"
By going to page set up
Only by opening the list on Border on the Formatting toolbar, then choosing "no border"
Only by choosing "NONE" on the Border tab of Format ce


What is the correct forumla to calculate an average between cells B3 and B6?
=AVERAGE(B3:B6)
=AVERAGE(B3+B4+B5+B6)/4
SUM(B3+B4+B5+B6)/4
=AVE(B3+B4+B5+B6)/4
=AVE(B3:B6)


In Sheet1, column A is January's sales numbers. In Sheet2, column A is February's sales numbers. Which equation on Sheet 3 totals both columns, in row 5?
=Sheet1!A5+A5
=A5+A5
=Sheet1!A5+Sheet2!A5
=Sheet1 + Sheet 2


How are columns named?
Metamorphically
Numerically
Alphabetically
Symbolically


What does the equation =TODAY() show?
The current date from Microsoft
The current date on your computer
The Julian date
Error since there has to be something in () for the equation to work.


What is a circular reference?
An animation tool
A modeling tool
A formula that either directly or indirectly depends on itself
A cell that points to a drawing object
Always wrong


Your spreadsheet is printing, but the grid lines aren't showing up. What is most likely the problem?
Change out the printer cartridge.
Go to page set up and set to print as black and white.
Need to set the cell to bold.
The formatting for grid lines isn't set on the cell.


If you would like to count the number of cells within a range that fit a specific criteria, which function would use?
COUNTTHOSEARE
COUNTONLYFIT
NUMFITCRITERIA
IFFIT
COUNTIF()


The cell reference of a range of cells that starts in cell B1 and goes over to column G and down to row 10 is ….
B1;G10
B1*G10
G1-G10
B1:G10


The Solver in MS Excel is used for what?
To find only optimal maximum solutions to linear programming problems
To find optimal minimum or maximum solutions to linear programming problems
To find only optimal minimum solutions to linear programming problems


What tool allows you to summarize and analyze data in various categories?
Pie Chart
Copy and Paste
Pivot Table
Square Root Analysis


Which worksheet function converts a time in the form of text to a serial number?
SECOND
TIMEVALUE
NOW
NETWORKDAYS
TIME


The Freeze Panes function allows the user to:
keep an area of a worksheet visible while you scroll to another area of the worksheet.
drag an existing formula across multiple cells within a range by utilizing the following.
add a macro which sets size-specific formatting within a worksheet.
lock in all existing formulas and formats within the cells that they currently reside.


In which tab is the page breaks preview located?
file
help
tools
design
View


Does delete button remove cell?
Deletes all spreadsheets data
YES it removes cell
Deletes entire Row
DELETE has no effect on cells
NO, it clears cell contents


What tool is used to split the window into different parts of the same worksheet?
Duplicate Box
Seperate Box
Split Box
Title Bar
Remove Box


How do you change the orientation of a spreadsheet from portrait to landscape?
Page layout > orientation > landscape
Page layout > scale to fit > landscape
Page layout > landscape > orientation


How do you turn data from rows into columns in Excel?
Copy data and then use Paste Special option with Transpose box checked.
Regular Copy and Paste Process
Sort Function
You Can't


How do you create Word Art?
On the Page Layout tab, in the Page Setup group, click WordArt
On the Review tab, in the Text group, click WordArt
On the Page Layout tab, in the Themes group, click Effects
On the Insert tab, in the Text group, click WordArt


When a formatted number does not fit within a cell, what does the cell display?
#####
none of these
#DIV/0
#DIV@


Which of the following formulas will Excel NOT be able to calculate?
=SUM(Sales)-A3
=SUM(A1:A5)-10
=SUM(A1:A5)/(10-10)
=SUM(A1:A5)*.5


What tool allows you to summarize and analyze data in various categories?
Square root analysis
Pie Chart
Pivot tables
Copy and paste


In Excel a ____ is an instruction in a spreadsheet to carry out a calculation
Data
Insert
VLOOKUP
Formula
Function


You can change the color of the following in Excel:
Headers/Footers
Background
Font
Borders
All Listed




You can edit a cell by
Selecting Edit>Edit Cell from the menu
Clicking the formula button
Double clicking the cell to edit it in-place
None of the Above


How do you keep others from changing the values on a worksheet?
Name the file "Do not change.xls"
Make all values Bold font.
Use "Protect Sheet" and assign a password.
Use track changes to see what has been changed.


What does this formula do? =IF(E6=G6;"Nice";"Not so Nice")
Sums up the range E6 to G6 and if the sum is greater than zero then, the formula returns 'Nice'
Creates borders around cell G6
It changes your font color to Red
Checks if E6 equals G6 and if it does, the result is true and the formula returns 'Nice'


If $34 is entered into a cell, Excel will interpret it as:
A reference to the sum of values in the 34th row
A value(number) with currency format
A #NAME? error
Text


True or False: You cannot insert comments for individual cells.
False
True


In Excel a ____ is an instruction in a spreadsheet to carry out a calculation
Insert
Data
Function
Formula
VLOOKUP


True of False: You can filter an Excel Pivot chart so that it shows only the information you want it to show.
True
You can only filter subsets in a Pivot table
Only by creating completely new pivot tables
False


Which would you choose to create a bar diagram?
Insert, Chart
Format, Chart
Tools, Chart
Edit, Chart


Which keyboard shortcut pastes copied information?
Ctrl + v
Ctrl + p
Ctrl + c
Ctrl + Shift + v
Shift + v


How to you create a pivot table?
right click> options> pivot table
Insert>pivot table


Identify the error: When entering 325 in a cell, the result shows up as 3.25.
This is a common glitch in Excel 2007. Upgrade ASAP.
Fixed Decimal mode is turned on.
"What-if" analysis has converted all entries for simple compiling.
Rounding has been set to reduce all numbers by 99%.


You can edit a cell by
None of above
Double clicking the cell to edit it in-place
Selecting Edit>Edit Cell from the menu
Clicking the formula button


True or False: Sparklines are a new part of Excel 2010/2011 and are not available in previous versions.
True
False


To ensure a worksheet will print all on one page, adjust the settings in the:
Zoom command
Margin Dialog box: Shrink to Fit
Page Layout/Page Setup dialog box
Paper size option


Which solver method will change variable cells to meet an objective cell?
Cannot be done in Solver
Evolutionary
All Solver Methods
LP Simplex
GRG Nonlinear


You can use the formula pallette to...
enter assumptions data
copy a range of cells
create and edit formula containing functions
format cells containing numbers


True or False: Sparklines are also often called "micro charts."
True
False


What does this formula do? =IF(E6=G6;"Nice";"Not so Nice")
Sums up the range E6 to G6 and if the sum is greater than zero then, the formula returns 'Nice'
Creates borders around cell G6
It changes your font color to Red
Checks if E6 equals G6 and if it does, the result is true and the formula returns 'Nice'


If you want to highlight a row of numbers by less than, greater than or equal to, what should you use?
Font
Page Layout
Conditonal Formatting
Fx
Print Preview


How do you ensure that the person using a spreadsheet can always see the column headings?
Re type the headings every few lines.
Use Freeze Panes or Freeze Top Row.
Reset the view Zoom percentage.
Print to fit on one page.


What is the best formula to use to calculate 12 to the 4th power?
4!12
=12+12+12+12
=12!4
=12^4
=12*12*12*12


True or False: You can download content from the net directly into Excel.
True
False


Pressing Ctrl - Home moves the cursor to
the A column within the current row
cell A1
the upper left cell in the active range
the upper right cell in the active range


A ________________ consists of a grid made from columns and rows?
Cell
Spreadsheet
Formula
Label


Right clicking on a cell then selecting insert from the cell menu allows you to do what?
Add a new cell, row, or column
Nothing
Insert an entirely new workbook
Creates a formula


True or False: A fixed dollar sign appears to the far left in the cell often with spaces between it and the first digit. Whereas a floating dollar sign appears immediately to the left of the first digit with no spaces.
True
False


If there is text in a column and you try to sort the entire column:
it will not sort the column
sorting function will be disabled
it will keep the text in the same cell and sort the numeric values
an error message will appear


Which worksheet function returns the F probability distribution?
SQDEV
QIDIST
TDIST
ZDIST
FDIST


The formula =NOW() displays:
The local computer's actual date and time
The time the file was opened
The time the file was last changed
The time the file was last saved


True or False? A pivot table must have column headers.
True
False


How many sheets are there in Excel Workbook by default?
5
4
2
3


The "Merge & Center" button does what?
Combines the quantities in two cells and centers the result
Merges cells together that are not physically attached
Changes the font size on a cell
Merges a cell with text with attached cells and centers the text over the range of cells


What is the correct formula to calculate the total of cell B3 and B4?
=TOTAL(B3+B4)
=summary(b3+b4)
=SUM B4+B3
=SUM(B3:B4)
=SUMMARY(B3+B4)


Cells B3 through B121 list several values. What equation shows the average of those values?
=AVERAGE(B3:B121)
=SUM(B3:B121)
=SUM(B3:B121)/10
=COUNT(B3:B121)


How do you make a chart?
Highlight cells of interest go to File, Cart
Highlight cells of interest go to Insert, Chart
Highlight cells of interest go to Tools, Chart
Highlight cells of interest go to Edit, Chart


Which one of the following formulas cannot be calculated by Excel?
=SUM(Sales)-A3
=SUM(A1:A5)-10
=SUM(A1:A5)/(10-8)
=SUM(A1:A5)*.5


How do you view the 5th decimal place when calculating costs?
Cannot view more than 2 decimals on currency.
Convert to text and view.
Increase the decimal to 5 digits.
Set to smaller font size.


How can an interactive chart be created?
With Form Controls, or with Active-X Controls, or with Custom VBA Userforms
Only with Active-X Controls
Only with Custom VBA Userforms
Only with Form Controls
Only with a third-party add-in


True or False: Once cells are merged in Excel, they can never be un-merged.
False
True


How do you insert saved images?
Choose Insert/Object
Choose Insert/Picture
Choose Home/Illustrations/Picture
Choose Home/Images


You can sort rows by:
Smallest to largest
Z to A
Oldest to newest
A to Z
All listed are correct


Where do you change the default font for new workbooks?
File --> Help
File --> Info --> Permissions
File --> Options --> Advanced
File --> Options --> Proofing
File --> Options --> General


Are you able to do spell check in Excel?
Depends on Excel version
Yes, but only if there are no charts in the workbook.
No
Yes


Cells D3 through Z3 list sales total for various months. What does the equation "=SUM(D3:Z3)" show?
Average sales for all the months in D3 through Z3
Sales quota for each month.
Sale averages for each month.
Total sales for all the months in D3 through Z3.


If you have an error, how can you find ways to fix it?
Go to "Data" and click on data tools.
You cannot request assistance.
Left click on the triangle, pause until you see a question mark, click on the arrow, and scroll to " Help on this error."


How can you change the date format of a cell or group of cells?
right click, copy, paste
Select cell or cells, right click, format cells, date, choose option.
insert, data, choose format
Ctrl + F3
type preferred format into formula bar


What is the proper shortcut for "Refresh All"?
Shift + A
Ctrl + Alt + F5
Ctrl + A
Ctrl + Shift + A


When you write up a lookup formula to look up the text "budget", the formula considers any of the following a match: BUDGET, Budget, or BuDgEt. Is it possible to perform a case-sensitive lookup?
Yes
No


True or False: To calculate trig functions in degrees, you must convert them, or Excel will calculate them in radians.
True
False


What is the purpose of the Σ button?
Sorting Data
Totaling a column or row
Recording a macro
Auto-saving


In a large data set with 121,005 rows, You are on the top row (row 1) of the data set. What is the fastest way to get to row 121,005?
ctrl + D
ctrl + A + delete
ctrl + down arrow
double click down arrow


If you want to set the print area which ribbon should you use?
PAGE LAYOUT
DATA
REVIEW
HOME
FORMULA


Cell A1 contains the formula =$F$39. Copying that formula to cell B2 will result in:
=G40
=$F$39
=$A$1
=$F40


A ________________ consists of a grid made from columns and rows?
Spreadsheet
Label
Formula
Cell


Which of the following formulas is not entered correctly?
=B1(N1)
=98-B11
=B1/N1
98-B11=


How does one save the active workbook without altering the file or folder name.
Highlight and drag a cell.
Copy and paste.
Double click the box.
Click save button (on top of home)


What do you use to create a chart?
Data Wizard
Excel Wizard
Pie Wizard
Chart Wizard


What is a logical function?
A function that inserts or calculates dates or times
A function that works with statements that are either true or false
A function related to monetary calculations


A ________________ consists of spreadsheets.
column
form
row
workbook
cell


What happens when you double click on a cell?
It is highlighted
It become editable
Nothing
Its contents are deleted


How do you ensure that the person using a spreadsheet can always see the column headings?
Re type the headings every few lines.
Print to fit on one page.
Use Freeze Panes or Freeze Top Row.
Reset the view Zoom percentage.


How many sheets are there in Excel Workbook by default?
4
3
2
5


Which symbol is used as a reference operator?
A Colon
A Space
All of these can be used
A Comma


You have two columns summing your account totals. The first column looks exactly right but the second one is not displaying the way you want. How do you make the two columns look the same?
Delete both columns. Reytpe values.
Copy the first column. Select the second column. Paste.
Copy the first column. Select the second column. Use "Paste Special" to paste "Formats".
Delete second column. Insert new column. Retype values.


When you insert a row, you have to reset all the equation references because:
The results will skew otherwise
The columns will resize
The formatting has to change as well
Inserting a row does not require you to reset the equation references


What will the formula =A1*C1 return?
An error
B1
The sum of A1 and C1
A1 raised to the power of C1
The product of A1 and C1


A pivot table allows you to:
Save data within a cell
Maneuver from one application database such as Access to another such as Excel
Merge columns and rows together
Create a worksheet with data using a filtering window where you can include or exclude desired fields of the original table
Import data from MS Word


How do you control which cells are printed?
Highlight the cells you want to print, go to File, and select Print Area => Set Print Area
Highlight the cells you want to print, go to Format, select Format Cells, and select the Border option. Then place a border around the cells you want to print.


Which keyboard shortcut copies cell data?
Ctrl + Shift + c
Shift + c
Ctrl + c
Shift + v
Ctrl + v