Excel
Formatting that enables a label to appear on multiple lines within the current cell
Wrap Text
Chart type that shows the relationship between two variables.
X Y (Scatter) chart
Detects issues that could hinder user's ability to use a workbook.
accessibility checker
A notation attached to a cell to pose a question or provide commentary.
comment
Requires that rules be followed in order to allow data to be entered in a cell.
data validation
A cell containing a formula that is dependent on other cells to obtain its value
dependent cell
An electronic notation in a document to authenticate the contents.
digital signature
Detects hidden and personal data in a workbook to remove.
document inspector
An attribute, such as an author's name or keyword, that describes a file.
document property
Lists particular types of changes made to a workbook.
history worksheet
An electronic marker to another location in a worksheet, workbook, file, Web page, or e-mail.
hyperlink
A description or instructions for data entry.
input message
The process of connecting cells between worksheets.
linking
A cell that is referenced by a formula in another cell.
precedent cell
A vertical or horizontal line that frames panes in a worksheet and enables the user to resize the panes.
split bar
The process of dividing a worksheet window.
splitting
The process of deselecting worksheets that were grouped
ungrouping
How much interest is paid on the first payment of a $15,000 auto loan financed at 3.25% interest paid monthly over 6 years? $40.63 $50.25 $47.55 None of the above
$40.63
Given the formula =B1*B2+B3/B4^2 where B1 contains 3, B2 contains 4, B3 contains 32, and B4 contains 4, what is the result? 14 121 76 9216
14
A formula or function that refers to the same range in multiple worksheets.
3D formula
A formula containing the entry =$B3 is copied to a cell one column to the right and two rows down. How will the entry appear in its new location? =B3 =$B5 =$C5 =$B3
=$B5
What function would most appropriately accomplish the same thing as =(B5+C5+D5+E5+F5)/5? =COUNT(B5:F5) =SUM(B5:F5)/5 =MEDIAN(B5:F5) =AVERAGE(B5:F5)
=AVERAGE(B5:F5)
Which of the following is not an appropriate use of the SUM function? =SUM(A8:A15,D8:D15) =SUM(F1:G10) =SUM(B3:B45) =SUM(D15-C15)
=SUM(D15-C15)
A program that can be added to Excel to provide enhanced functionality.
Add in
The position of data between the cell margins
Alignment
Which check box in the Table Style Options group enables you to apply different formatting to the records in a table? Banded Columns Banded Rows Total Row Header Row
Banded Rows
Which date filter option enables you to specify criteria for selecting a range of dates, such as between 3/15/2016 and 7/15/2016? (1 point) All Dates in the Period Before Between Equals
Between
A constraint that Solver enforces to reach the target value.
Binding constraint
Calculates the correlation coefficient of two data series.
CORRELL function
Tallies the number of cells in a range that contain values.
COUNT function
Counts the number of cells in a range when a specified condition is met.
COUNTIF function
The intersection of a column and row
Cell
You have a large dataset that will print on several pages. You want to ensure that related records print on the same page with column and row labels visible and that confidential information is not printed. You should apply all of the following page setup options except which one to accomplish this? Change the print page order. Adjust page breaks. Set a print area. Print titles.
Change the print page order.
Chart type to compare multiple categories of data vertically.
Clustered Column chart
A conditional format that displays a particular color based on the relative value of the cell contents to the other selected cells.
Color scale
The horizontal measurement of a column
Column Width
Drag fields here to add more vertical data to a PivotTable.
Columns area
A set of rules that applies specific formatting to highlight or emphasize cells that meet specifications.
Conditional formatting
A limitation that imposes restrictions on Solver.
Constraint
In the Advanced Filter dialog box, where do you enter the location for placing the output? Unique records only Criteria range Copy to List range
Copy to
Measure of how two sample sets of data vary simultaneously
Covariance
What keystroke combination is required to calculate a Frequency data array? Ctrl+Shift+Delete Ctrl+Enter Ctrl+Shift+Enter Alt+Enter
Ctrl+Shift+Enter
A file that contains a pointer to the source file.
Destination file
You have a workbook that contains sales data for different regional sales reps of a company. Which task is the least likely to be done while the worksheets are grouped? Fill the sales categories across the worksheets. Format the values with an appropriate number style. Format the column and row labels at the same time. Enter specific values for the first sales rep.
Enter specific values for the first sales rep.
The smallest data element contained in a table, such as first name, last name, address, and phone number.
Field
A small green square at the bottom-right corner of a cell
Fill Handle
The background color appearing behind data in a cell
Fill color
You have created a PivotChart showing sales by quarter by sales rep. Before presenting it to management, you notice the name of a rep who has since been fired. How do you remove this rep from the chart without deleting the data? Filter the Sales Rep field in the PivotChart and deselect the employee's check box. Make the employee's data points and axis titles invisible. Hide that rep's row(s) in the underlying list, which automatically removes that rep from the chart. You cannot delete the rep from the chart without first deleting the data.
Filter the Sales Rep field in the PivotChart and deselect the employee's check box.
The process of specifying conditions to display only those records that meet those conditions.
Filtering
Drag fields here to be able to specify which values or content to include or exclude in the PivotTable.
Filters area
When you select the ______ rule type, the New Formatting Rule dialog box does not show the Format button. Format only cells that contain Format only unique or duplicate values Format all cells based on their values Use a formula to determine which cells to format
Format all cells based on their values
Displays the content (text, value, date, or formula) in the active cell.
Formula Bar
You are working with a large worksheet. Your row headings are in column A. Which command(s) should be used to see the row headings and the distant information in columns X, Y, and Z? Freeze Panes command Split Rows command Hide Rows command New Window command and cascade the windows
Freeze Panes command
The process of keeping rows and/or columns visible onscreen at all times even when you scroll through a large dataset
Freezing
Determines the number of occurrences of numerical values in a dataset based on predetermined bins.
Frequency function
This tool calculates the value required in a single cell to produce a desired result with in a related cell. Solver One- or Two-variable data table Goal Seek Scenario Manager
Goal Seek
What should you do if you see a column of pound signs (###) instead of values or results of formulas? Adjust the row height. Increase the column width. Delete the column. Increase the zoom percentage.
Increase the column width.
Which statement is not a recommended guideline for designing and creating an Excel table? Ensure no blank columns separate data columns within the table. Leave one blank row between records in the table. Avoid naming two fields with the same name. Include field names on the first row of the table.
Leave one blank row between records in the table.
An expression that evaluates to true or false.
Logical test
A group of related fields representing one entity, such as data for one person, place, event, or concept.
Record
An association created between two tables where both tables contain a matching field.
Relationship
A predefined formula that calculates an aggregate value, such as totals, for values in a range, a table, or a database.
SUBTOTAL function
Calculates the total of values contained in two or more cells.
SUM function
A worksheet contains a list of traffic stops over the past 12 months. The police chief wants to determine the total dollar amount of all traffic tickets written in the ZIP code 46208. What function derives the correct answer? COUNTIFS SUMIF DCOUNT AVERAGEIFS
SUMIF
A table row that appears below the last row of records in an Excel table and displays summary or aggregate statistics, such as a sum or an average
Total row
A data analysis tool that provides results based on changing two variables.
Two variable data table
Which tool is most effective when comparing the impacts of various combinations of interest rates and down payments on a mortgage? Scenario Manager Goal Seek Two-variable data table Solver
Two-variable data table
Indicates a cell's specific location; the cell reference does not change when you copy the formula.
Absolute cell reference
Which tool detects issues that could hinder a user's ability to use a workbook? Compatibility Checker Accessibility Checker Change tracker Document Inspector
Accessibility Checker
The Track Changes feature does not detect what type of change? Applying Percent Style for the range B4:B10 Deleting text within a label Inserting a new row above row 18 Changing a value of a number from 10 to 25
Applying Percent Style for the range B4:B10
An input, such as a cell reference or value, needed to complete a function.
Argument
Enables you to copy the contents of a cell or cell range or to continue a sequence by dragging the fill handle over an adjacent cell or range of cells
Auto fill
Calculates the future value of an investment.
FV function
A built-in add-in program that enables users to create a PivotTable from multiple related tables.
PowerPivot
Which what-if analysis tool is the best option for complex calculations requiring constrained optimization? Scenario Manager Data Tables Goal Seek Solver
Solver
A structure that organizes data in a series of records (rows), with each record made up of a number of fields (columns).
Table
The rules that control the fill color of the header row, columns, and records in a table.
Table style
You want to create a single chart that shows each of five divisions' proportion of yearly sales for each year for five years. Which type of chart can accommodate your needs? Clustered bar chart Pie chart Surface chart 100% stacked column chart
100% stacked column chart
The original mortgage loan was for $300,000 with a 5% APR for 30 years. You want to calculate the interest on the last monthly payment at the end of the 15th year. What value should be referenced for the per argument in the IPMT function? 0.05/12 180 30 15
180
A colored line that indicates relationships between precedent and dependent cells.
tracer arrow
Occurs when multiple people work together to achieve a common goal by using technology to edit the contents of a file.
collaboration
A colored triangle in the top-right corner of a cell to indicate that the cell contains comments
comment indicator
Detects data and features that are not compatible with previous versions of Excel
compatibility checker
A message that appears when the user enters invalid data in a cell containing a validation rule.
error alert
What is the difference between a sample and a population? A sample contains all data from the population except statistical outliers. A sample contains all data possible for evaluation, while a population contains a portion of data available. There is no difference. A population contains all data possible for evaluation, while a sample contains a portion of the data.
A population contains all data possible for evaluation, while a sample contains a portion of the data.
What indicates that the closing price was higher than the opening price in a stock chart? A positive data label above the data series for a particular day A double vertical line between the price markers A rectangle with a black fill color A rectangle with a white fill color
A rectangle with a white fill color
Calculates the average of values in a range when a specified condition is met.
AVERAGEIF function
An add-in program that contains tools for performing complex statistical analysis.
Anaylsis toolpack
Currently, the House Types field is in the Row Labels area, the Real Estate Agent field is in the Column Labels area, and Sum of List Prices is in the Values area. How can you modify the PivotTable to display the agent names as subcategories within the house types in the first column? Drag the House Types field from the ROWS area and drop it below the Real Estate Agent field in the COLUMNS area. Drag the Real Estate Agent field from the Column Labels area and drop it above the House Types field in the Row Labels area. Drag the House Types field from the ROWS area to the FILTERS area and drag the Real Estate Agent field from the COLUMNS area to the ROWS area. Drag the Real Estate Agent field from the COLUMNS area and drop it below the House Types field in the ROWS area.
Drag the Real Estate Agent field from the COLUMNS area and drop it below the House Types field in the ROWS area.
Which database function would you use to count a range of cells that have some cells that are blank without excluding the blank cells? DCOUNT DSUM DCOUNTA COUNT
DCOUNTA
Identifies the highest value in a database column based on specified conditions.
DMAX function
Identifies the lowest value in a database column based on specified conditions.
DMIN function
Adds values in a database column based on specified conditions.
DSUM function
What type of conditional formatting displays horizontal colors in which the width of the bar indicates relative size compared to other values in the selected range? Color Scales Sparklines Data Bars Icon Sets
Data Bars
A conditional format that displays horizontal gradient or solid fill indicating the cell's relative value compared to other selected cells.
Data bar
Descriptive label that shows the exact value of the data points on the value axis.
Data label
If you want to show exact values for a data series in a bar chart, which chart element should you display? Legend Data labels Value axis title Chart title
Data labels
The process of analyzing large volumes of data to identify patterns and trends
Data mining
Numeric value that decribes a single value on a chart
Data point
Group of related data points that display in row(s) or column(s) in a worksheet
Data series
Analyzes data for selected records in a table.
Database function
Which tab contains commands to apply a predefined chart layout that controls which elements are included, where, and their color scheme? Page Layout Layout Format Design
Design
The _______ tool detects particular properties, such as Author, and removes those properties from a file that you plan to distribute. Document Inspector Advanced Properties Accessibility Checker Compatibility Checker
Document Inspector
Which of the following characteristics is not applicable to the Accounting Number Format? Two decimal places Dollar sign immediately on the left side of the value Commas to separate thousands Zero values displayed as hyphens
Dollar sign immediately on the left side of the value
Doug would like to purchase a new automobile. He has budgeted for $600 per month. If the interest and number of payments are constant variables that cannot change, which analysis tool should Doug use to calculate the amount to spend on a car? Goal Seek One- or Two-variable data table Scenario Manager Solver
Goal Seek
Which of the following is not an acceptable range name? FICA Goal for 2016 Test_Weight Target_2015
Goal for 2016
A tabular display of data that displays the frequencies of occurrence organized into bins
Histogram
Evaluates a condition and returns one value if the condition is true and a different value if the condition is false.
IF function
Checks a value and returns the result if possible or an error message.
IFERROR function
Calculates periodic interest for a fixed-term, fixed-rate loan or investment.
IPMT function
A conditional format that displays an icon representing a value in the top third, quarter, or fifth based on values in the selected range.
Icon set
Assume that the data on a worksheet consume a whole printed page and a couple of columns on a second page. You can do all of the following except what to force the data to print all on one page? Select a smaller range as the print area. Decrease column widths if possible. Decrease the Scale value. Increase the left and right margins.
Increase the left and right margins.
A range of cells containing values for variables used in formulas.
Input area
Which document property cannot be changed within the Backstage view? Categories Author Title Last Modified date
Last Modified date
Identifies the highest value in a range.
MAX function
Identifies the midpoint value in a set of values.
MEDIAN function
Displays the lowest value in a range
MIN function
If you want to balance a title over several columns, what do you do? Use the Increase Indent command until the title looks balanced. Click Center to center the title horizontally over several columns. Merge and center the data over all columns. Enter the data in the cell that is about midway across the spreadsheet.
Merge and center the data over all columns.
You want to display a total row that identifies the oldest date in a field in your table. What function do you select from the list? Count Min Sum Max
Min
Contains both an absolute and a relative cell reference in a formula; the absolute part does not change but the relative part does when you copy the formula.
Mixed cell reference
Returns TRUE if the argument is false and FALSE if the argument is true.
NOT function
Displays the current date and time.
NOW function
What function would you use to calculate the total number of periods in a loan or investment? FV PV NPER RATE
NPER
Calculates the net present value of an investment with periodic payments and a discount rate
NPV function
Identifies the address of the current cell
Name Box
What Excel interface item displays the address of the current cell? Status bar Name Box Formula Bar Quick Access Toolbar
Name Box
Which statement about comments in Excel is true? Comments remain onscreen in the right margin area. After you insert a comment, Excel prevents you from changing it. Position the mouse pointer over the cell containing a comment indicator to display a comment box. Comment boxes display the date and time that the user inserted the comment in the worksheet.
Position the mouse pointer over the cell containing a comment indicator to display a comment box.
A worksheet contains a PivotTable placeholder and the PivotTable Fields task pane. Where do you drag the State field if you want a list of each state in the first column of the PivotTable? COLUMNS area VALUES area FILTERS area ROWS area
ROWS area
Given the function =VLOOKUP(C6,$D$12:$F$18,3), the entries in: The third column of the lookup table must be text only. Range D12:D18 are in ascending order. Range D12:D18 contain multiple values in each cell. Range D12:D18 are in descending order.
Range D12:D18 are in ascending order.
A word or string of characters that represents one or more cells.
Range name
Indicates a cell's location from the cell containing the formula; the cell reference changes when the formula is copied.
Relative cell reference
The vertical measurement of a row
Row height
A set of values that represent a possible situation
Scenario
Which tools are best suited to calculate the impact of multiple interest rates on an auto loan? (Check all that apply.) Goal Seek Scenario Manager Solver One-variable data table
Scenario Manager, One-variable data table
Which dialog box enables you to specify the result cells for a scenario summary report? Solver Options Scenario Summary Add Scenario Scenario Values
Scenario Summary
Enables you to define and manage scenarios to compare how they affect results.
Scenario manager
A worksheet that contains scenario results.
Scenario summary report
What is the typical sequence for creating a chart? Click the cell to contain the chart, select the chart type, and then select the data source. Select the data source, size the chart, select the chart type, and then position the chart. Select the chart type, select the data source, and then size and position the chart. Select the data source, select the chart type, and then size and position the chart.
Select the data source, select the chart type, and then size and position the chart.
Displays the name of a worksheet within a workbook
Sheet tab
The value axis currently shows increments such as 50,000 and 100,000. What do you select to display increments of 50 and 100? Show Axis in Millions Show Right to Left Axis More Primary Vertical Axis Title Options Show Axis in Thousands
Show Axis in Thousands
Enables a person to type or insert a visible digital signature to authenticate the workbook.
Signature line
Which of the following applies to a sparkline? Legend Single-cell chart Multiple data series Chart title
Single-cell chart
Indicators that enable you to adjust the height and width of a selected chart.
Sizing handle
A central storage location where you can save and access files via Internet connection.
Sky drive
A window listing all items in a field and enabling efficient filtering.
Slicer
he label that appears at the top of a slicer window. By default, it displays the name of the field used.
Slicer caption
An add-in application that manipulates variables based on constraints to find the optimal solution to a problem.
Solver
This analysis tool has the ability to handle multiple adjustable cells while minimizing, maximizing, or meeting goals. Goal Seek Scenario Manager Solver One- or Two-variable data table
Solver
Which of the following is an Excel add-in? Goal Seek Scenario Manager One- or Two-variable data table Solver
Solver
Which of the following tools can incorporate constraints? Goal Seek Scenario Manager Solver Data Tables
Solver
How can you determine if the Solver add-in is active? (Check all that apply.) Solver is an option in the Home tab of the Ribbon. Solver appears in the Goal Seek dialog box to make it appear as a label. Solver appears in the Data tab of the Ribbon. Solver is available via right-click.
Solver is an option in the Home tab of the Ribbon. Solver appears in the Data tab of the Ribbon.
A tag or use of a table element, such as a field label, as a reference in a formula. Field labels are enclosed in square brackets, such as [Amount] within the formula.
Structured reference
Replaces the original value of a variable in a data table
Substitution value
A row within a dataset that displays the total or another statistic for a particular category
Subtotal
You created a PivotTable to summarize salaries by department. What is the default summary statistic for the salaries in the PivotTable? Sum Max Count Average
Sum
A set of rules that governs the structure and components for properly entering a function
Syntax
Displays the current date.
TODAY function
Window of options to format and customize chart elements.
Task pane
Tools to enable you to detect and correct errors in formulas by identifying relationships among cells.
formula auditing
The process of selecting worksheets to perform the same action at the same time.
grouping
Records certain types of changes made in a workbook.
track changes
If you want to save a file in a format that preserves worksheet formatting, prevents changes, and ensures that the document looks the same on most computers, save the workbook in the _______ file format. Excel template .pdf Excel 97-2003 workbook .csv
What is the default file format for a basic Excel 2013 file? .csv .pdf .xlsx .xls
.xlsx
A worksheet contains test scores for students in an aviation class. The scores are 95, 90, 90, 85, 80, 75, 75, 70, and 60. Using the RANK function that is considered a best practice, what is the rank of the second student who scored 90? 3 2 2.5 None of the above
2.5
Given that cells A1, A2, and A3 contain values 2, 3, and 10, respectively, and B6, C6, and D6 contain values 10, 20, and 30, respectively, what value will be returned by the function =IF(B6>A3,C6*A1,D6*A2)? 40 60 10 90
90
Which PivotTable calculated field is correctly constructed to calculate a 20% tip on a meal at a restaurant? (1 point) =B5*1.2 =Meal Cost * 20% ='Meal Cost'*.2 ="Meal Cost"*.2
='Meal Cost'*.2
You are preparing an accreditation report for your university. You have several workbooks of data for each college, such as Arts, Sciences, and Business. Assuming the individual workbooks are stored in the same folder as the University workbook, how would a link to cell B15 in the Digital Media worksheet in the School of Computing workbook appear in the University workbook? ='[School of Computing.xlsx]Digital Media'!$B$15 ="School of Computing"!'Digital Media'!B15 ='School of Computing'![Digital Media]:$B$15 =Computing.xlsx:Digital Media:B15
='[School of Computing.xlsx]Digital Media'!$B$15
A local police office wants to create a rule that if an officer pulls over a person for exceeding the speed limit by at least five miles per hour or if that person has two or more speeding violations on record, the officer will fine the speeder the higher of $200 or $50 for each mile over the speed limit. Otherwise, the fine is $45. The speed limit is entered in cell B5, the person's speed is entered in cell B10, and the person's number of previous tickets is entered in cell B11. What function derives the correct answer? =IF(AND(B10-B5>=5,B11<2),MAX(200, (B10-B5)*50),45) =IF(OR(B10-B5>=5,B11>=2),MAX(200, (B10-B5)*50),45) =IF(OR(B10>B5,B11>=2),MAX(200,50),45) =IF(AND(B10>B5,B11>=2),200,45)
=IF(OR(B10-B5>=5,B11>=2),MAX(200, (B10-B5)*50),45)
A personal trainer stores how much weight each person can lift in several categories. Each week's data are stored in a separate worksheet within the same workbook, and each worksheet has an identical structure. Assume cell F5 contains the weight the first person can bench press. What function can identify that person's highest amount bench-pressed in all worksheets? =COUNT(Week 1,Week4:F5) =SUM(Week 1:Week4:F5) =MAX(Week1:Week4:'F5') =MAX('Week 1:Week 4'!F5)
=MAX('Week 1:Week 4'!F5)
Cell B10 contains a date, such as 1/1/2016. Which formula will determine how many days are between that date and the current date, given that the cell containing the formula is formatted with Number Format? =TODAY()+NOW() =CURRENT()-B10 =TODAY()-B10 =TODAY()
=TODAY()-B10
Returns TRUE when all arguments are true and FALSE when at least one argument is false
AND function
A statistical tool that compares the means between two data samples to determine if they were derived from the same population.
ANOVA
Which of the following is a nonadjacent range? C15:D30 A1:A10, D1:D10 L15:L65 A1:Z99
A1:A10, D1:D10
Calculates the arithmetic mean, or average, of values in a range.
Average function
Label that describes either the category axis or the value axis.
Axis title
What settings should you select for a PivotTable if you want to apply a different color scheme and display different fill colors for main category rows and horizontal lines within the PivotTable? Banded Rows and Banded Columns check boxes Banded Rows check box and a different PivotTable style Banded Columns check box and a different PivotTable style A different PivotTable style only
Banded Rows check box and a different PivotTable style
Chart type that compares categories of data horizontally.
Bar chart
You have a list of all the employees in your organization. The list contains employee name, office, title, and salary. You want to list all employees in each office branch. The branches should be listed alphabetically, with the employee earning the highest salary listed first in each office. Which is true of your sort order? Salary is the primary sort and should be from lowest to highest. Branch office is the primary sort and should be in A to Z order. Branch office is the primary sort and should be in Z to A order. Salary is the primary sort and should be from highest to lowest.
Branch office is the primary sort and should be in A to Z order.
A worksheet contains data for businesses that are sponsoring this year's Arts Festival. The worksheet contains these columns in this sequence: Business Name, Address, City, State, and Donation Amount. Data are sorted by State and then by City. What is the default At a change in setting within the Subtotal dialog box, and what would be a more appropriate setting? Donation Amount (default field), Address (correct field) Business Name (default field), State (correct field) Address (default field), Donation Amount (correct field) Business Name (default field), Donation Amount (correct field)
Business Name (default field), State (correct field)
A workbook contains a list of university students. You want to identify the total number of students who are seniors and who are majoring in biology. Without modifying the original student dataset, what function can you use to find the answer to your question? COUNTIFS SUMIF AVERAGEIFS DCOUNT
COUNTIFS
Which function should you use to calculate the total interest paid for all monthly payments for the second year of a four year automobile loan? CUMPRINC PPMT CUMIPMT IPMT
CUMIPMT
Calculates cumulative interest for specified payment period.
CUMIPMT function
What function would you use to calculate the total principal paid on a loan over a specific start and end date? IPMT PPMT CUMIPMT CUMPRINC
CUMPRINC
Label that describes the chart.
Chart title
You created a PivotTable and made some changes to values in the original dataset from which the PivotTable was created. How does this affect the PivotTable? Click Refresh in the Data group on the ANALYZE tab to update the PivotTable. You must create a new PivotTable if you want updated results in a PivotTable. Click the DATA tab and click Update to update the PivotTable to reflect changes you made in the dataset. The PivotTable updates automatically when you make changes to the dataset.
Click Refresh in the Data group on the ANALYZE tab to update the PivotTable.
Currently, a column chart shows values on the value axis, years on the category axis, and state names in the legend. What should you do if you want to organize data with the states on the category axis and the years shown in the legend? Change the chart type to a clustered column chart. Click Switch Row/Column in the Data group on the Design tab. Click Legend in the Labels group on the Layout tab and select Show Legend at Bottom. Click Layout 2 in the Chart Layouts group on the Design tab and apply a different chart style.
Click Switch Row/Column in the Data group on the Design tab.
To study the results of a formula on the Summary worksheet when you change an input value on the Input worksheet, what can you do? Display the trace precedents and dependents arrows on both worksheets as you change the input value. Create a hyperlink from the results to the input cell and from the input cell to the formula. Create a watch for the formula and display the Watch Window while changing the input value on the Input worksheet. Create a watch for the input cell and display the Watch Window on the Summary worksheet.
Create a watch for the formula and display the Watch Window while changing the input value on the Input worksheet.
The function =FV(D10,D8,-D5) is entered in cell D12. Which cell is a dependent of cell D8? D1 D5 D10 D12
D12
Averages values in a database column based on specified conditions.
DAVERAGE function
A workbook contains a list of university students. You want to identify the total number of students who are seniors and who are majoring in biology. Without modifying the original student dataset, what function can you use to find the answer to your question? DCOUNT COUNTA Nested IF COUNT
DCOUNT
Counts the cells that contain a number in a database column based on specified conditions.
DCOUNT function
Which dialog box specifies a cell containing an error and the type of error, such as Divide by Zero Error? Error Checking Evaluate Formula Circular Reference Watch Window
Error Checking
our manager sent you a workbook that contains data validation rules. One rule specifies a maximum value of 15% with a warning alert. You try to enter 22% in that cell. What happens? Excel enters the 22% with no message boxes. Excel enters the 22% and provides a message box to inform you that your entry violates the validation rule. Excel displays a message box informing you the value is above the maximum value and lets you choose to go ahead and enter that value or a different value. Excel displays a message box and prevents you from entering 22%.
Excel displays a message box informing you the value is above the maximum value and lets you choose to go ahead and enter that value or a different value.
You want to create a hyperlink within your document to the SEC Web site. Which type of link do you create? Create New Document Existing File or Web Page E-Mail Address Place in This Document
Existing File or Web Page
A combination of cell references, operators, values, and/or functions used to perform a calculation
Formula
When you start =AV, what displays a list of functions and defined names? Insert Function dialog box Function Arguments dialog box Formula AutoComplete Function ScreenTip
Formula AutoComplete
A tool that identifies the necessary input value to obtain a desired goal.
Goal seek
Horizontal or vertical line that extends from the horizontal or vertical axis through the plot area.
Gridline
A process of joining related rows or columns of related data.
Grouping
Returns a value or reference to a value within a range.
INDEX function
Key that identifies the color, gradient, picture, texture, or pattern fill assigned to each data series in a chart.
Legend
Chart type that shows trends over time in which the value axis indicates quantities and the horizontal axis indicates time.
Line chart
A schedule showing monthly payments, interest per payment, amount toward paying off the loan, and the remaining balance for each payment.
Loan armortization table
A range that contains data for the basis of the lookup and data to be retrieved.
Lookup table
A worksheet contains the times in which runners completed a race, with the times organized from fastest to slowest. You will use the MATCH function to identify what place a runner came in given a time of 4:05 (four minutes and five seconds). Which argument should contain the specific runner's time? Match_type Lookup_value Row_num Lookup_array
Lookup_value
Identifies a searched item's position in a list.
MATCH function
A data analysis tool that provides various results based on changing one variable
One variable data table
If you want to display a portion of all three worksheets in a workbook, what should you do? Use the Freeze Panes option and cascade the title bars of all open workbooks. Use the Split, Freeze Panes, and Arrange All commands at the same time. Double-click the split boxes to display four window panes, click within each pane, and then click the worksheet tab to display its content. Open two new workbook windows, arrange windows, and then click a different worksheet tab in each window.
Open two new workbook windows, arrange windows, and then click a different worksheet tab in each window.
Finds the highest, lowest, or exact value for one particular result by adjusting values for selected variables.
Optimization model
You can do all of the following except _______ for both customizing the Ribbon and customizing the Quick Access Toolbar. Reset the customizations Print Add a command Import and export customizations
The range of cells within a worksheet that will print.
Print area
The sequence in which the pages are printed
Print order
Identifies the value at a specific quartile, exclusive of 0 and 4.
QUARTILE.EXC function
Identifies the value at a specific quartile.
QUARTILE.INC function
Identifies the rank of a value, providing an average ranking for identical values.
RANK.AVG function
Which of the following tools is not a part of the Data Analysis ToolPak add-in? RANK.EQ ANOVA Histogram Covariance
RANK.EQ
Identifies the rank of a value, omitting the next rank when tie values exist.
RANK.EQ function
A rectangular group of cells
Range
Drag fields here to display categories horizontally in a PivotTable.
Rows area
What function would you use to calculate the spread of data around the mean in a data sample? CORREL STDEV.S FREQUENCY STDEV.P
STDEV.S
What is the difference between STDEV.S and STDEV.P? STDEV.P calculates the standard deviation of a population; STDEV.S calculates average variation. STDEV.S calculates standard deviation of a sample; STDEV.P calculates the standard deviation of a population. STDEV.P calculates the standard deviation of a population; STDEV.S calculates variance. There is no difference.
STDEV.S calculates standard deviation of a sample; STDEV.P calculates the standard deviation of a population.
Calculates the total of a range of values when a specified condition is met.
SUMIF function
If you want to save a workbook to a highly secure central location to enable efficient collaboration: Save the workbook to a SharePoint site or to a shared SkyDrive folder. Send the workbook as an e-mail attachment. Backup the workbook to an external hard drive. Save the workbook to a regular Web site.
Save the workbook to a SharePoint site or to a shared SkyDrive folder.
The process of listing records or text in a specific sequence, such as alphabetically by last name.
Sorting
A file that contains original data.
Source file
Miniature chart contained in a single cell.
Sparkline
Measures how far the data sample is spread around the mean.
Standard deviation
What is the first step in planning an effective worksheet? State the purpose of the worksheet. Identify the input and output areas. Enter labels, values, and formulas. Decide how to format the worksheet data.
State the purpose of the worksheet.
Chart that shows the high, low, and close prices for individual stocks over time.
Stock chart
Includes letters, numbers, symbols, and spaces
Text
Why would you press Ctrl+` in Excel? To undo a mistake you made To enable the AutoComplete feature To display the print options To display cell formulas
To display cell formulas
You suspect a table has several identical records. What should you do? Find the duplicate records and change some of the data to be different. Use the Remove Duplicates command. Do nothing; a logical reason probably exists to keep identical records. Look at each row yourself and manually delete duplicate records.
Use the Remove Duplicates command.
Looks up a value in a vertical lookup table and returns a related result from the lookup table.
VLOOKUP function
A number that represents a quantity or an amount
Value
Displays incremental numbers to identify approximate values, such as dollars or units, of data points in a chart.
Value axis
Drag fields here to display data as aggregates, such as sums or averages.
Values area
A value that you can change to see how that change affects other values.
Variable
A descriptive statistics tool that determines the summation of the squared deviations divided by the amount of the n - 1.
Variance
The process of changing variables to observe how changes affect calculated results.
What if analysis
A file containing related worksheets.
Workbook
A spreadsheet that contains formulas, functions, values, text, and visual aids.
Worksheet
You created an outline for a dataset. What does the + button indicate to the left of a row heading? (1 point) One or more columns are hidden. You can click it to expand the details of that category. You can click it to collapse the details of that category. You can add a new row at that location only.
You can click it to expand the details of that category.
Occurs when a formula adheres to syntax rules but produces inaccurate results.
logic error
Pieces of data such as a keyword, that describe other data, such as the contents of a file.
metadata
A file that enables multiple users to make changes at the same time.
shared workbook
Occurs when formula construction rules are violated.
syntax error
Rules that dictate the data to enter a cell.
validation criteria
The function =PMT(C5,C7,-C3) is stored in cell C15. What must be stored in cell C5? Loan amount Periodic interest rate Number of payment periods APR
Periodic interest rate
Chart that shows each data point in proportion to the whole data series.
Pie chart
Which type of chart is the least appropriate for depicting yearly rainfall totals for five cities for four years? Bar chart Column chart Line chart Pie chart
Pie chart
A graphical representation of aggregated data derived from a PivotTable.
Pivot chart
An organized structure that summarizes large amounts of data without altering the original dataset
PivotTable
A window that enables you to drag fields to particular areas to build and arrange data in a PivotTable.
PivotTable field task pane
Section of a chart that contains graphical representation of the values in a data series
Plot area
A universal file format that preserves a document's original data and formatting for multiplatform use.
Portable document format (PDF)
You hypothesize that there is a relationship between lack of regular exercise and illness. To research this theory, you have compiled a sample set of data that contains numbers of days in which an hour or more of exercise is completed as well as numbers of days sick within a calendar year. What tools in Excel could you use to investigate the relationships between the data? Select all that apply. CORREL PERCENTRANK.INC COVARIANCE SUMIFS
CORREL COVARIANCE
Calculates cumulative principal for specified payment periods.
CUMPRINC function
A user-defined field that performs a calculation based on other fields in a PivotTable.
Calculated field
Provides descriptive group names for subdividing a data series.
Category axis
Which icon does not display to the right of a selected chart? Chart Styles Chart Quick Analysis Chart Elements Chart Filters
Chart Quick Analysis
Boundary that contains the entire chart and all of its elements, including the plot area, titles, legends, and labels.
Chart area
If cell D15 contains the formula =$C$5*D$15, what is the D15 in the formula? Circular reference Relative reference Absolute reference Range name
Circular reference
Occurs when a formula directly or indirectly refers to itself
Circular reference
You selected and copied worksheet data containing formulas. However, you want the pasted copy to contain the current formula results rather than formulas. What do you do? Click the Paste arrow in the Clipboard group and select Formulas. Click Paste in the Clipboard group on the Home tab. Click the Paste arrow in the Clipboard group and select Values & Source Formatting. Display the Paste Special dialog box and select Formulas and number formats.
Click the Paste arrow in the Clipboard group and select Values & Source Formatting.
A constraint that does not restrict the target value that Solver finds
Non binding constraint
Returns TRUE if any argument is true and returns FALSE if all arguments are false.
OR function
The cell that contains the formula-based value that you want to maximize, minimize, or set to a value in Solver.
Objective cell
A workbook that has been marked as final: Must contain a digital signature. Opens in Read-Only mode. Is password protected to make any changes. Displays a nonstop, flashing Message Bar that the workbook is final.
Opens in Read-Only mode.
Rules that control the sequence in which Excel performs arithmetic operations
Order of precedence
A hierarchical structure of data.
Outline
A range of cells containing results based on manipulating the variables.
Output area
Identifies a value at a specified percentile; however, the .EXC excludes 0th or 100th percentiles.
PERCENTILE.EXC function
Identifies the kth percentile of a specified value within a list of values, including the 0th and 100th percentiles.
PERCENTILE.INC function
What function would you use to identify a values rank as a percentile, excluding 0 and 1? PECENTRANK.INC PERCENTRANK.EXC RANK.EQ QUARTILE.INC
PERCENTRANK.EXC
Identifies a value's rank as a percentile between 0 and 1 of a list of values.
PERCENTTRANK.INC function
Which function cannot be used in a 3-D formula? PMT AVERAGE SUM MIN
PMT
Calculates the periodic payment for a loan with a fixed interest rate and fixed term
PMT function
Calculates the principal payment for a specified payment period given a fixed interest rate, term, and periodic payments.
PPMT function
Calculates the present value of an investment
PV function
An indication of where data will start on another printed page.
Page break
You just created a slicer for the State field in a PivotTable. Which of the following does not characterize the initial slicer? The slicer may display on top of the PivotTable data. The slicer buttons are set to filter out all records. The slicer caption is State. The slicer contains one column of state names or abbreviations.
The slicer buttons are set to filter out all records.