Prometheus Exam 2 Flashcards
A ___ is an Excel file that stores all the information, previous calculations, and analyses that you may have already completed.
Workbook
A ___ is organized into a collection of cells arranged in the form of a table.
Worksheet
___ store individual pieces of data or calculations.
Cells
A ___ contains the actual data and calculations.
Worksheet
The ___ menu items are used to change how a worksheet is displayed on the screen.
View
The ___ menu items help in sharing an Excel workbook with colleagues.
Review
The ___ menu items are used to format the appearance of worksheets.
Page Layout
Items in the ___ area under the File Tab are used make this workbook available for colleagues.
Share
The ___ menu items allow you to manage users.
Account
Referring to more than one cell at a time as long as the cells are in a continuous block is called ___
Referencing a Range of Cells
The ___ feature allows you to automatically populate cells in a worksheet.
Fill
use Series dialogue window to fill a cell range… Step Value and Stop Value
When all the contents of a cell don’t fit/display in a cell, what function of Excel will you use to fix it?
“Column Width” under Format Tab
The ___ feature in Excel allows you to “Lock” rows and columns so that they don’t disappear when you navigate through the data table.
Freeze Panes
The menu items used to change the appearance of the contents of cells can be accessed from the ___ and ___ groups on the “Home” menu tab.
Font and Alignment
The ___ drop-down menu allows you to choose the font from the list of installed system fonts on your computer.
Font-Face
The ___ feature of Excel allows you to angle cell contents.
Orientation
Excel has a number of worksheet styles, called ___, which you can apply to quickly add colors and other style effects to your worksheets
Themes
The ___ and ___ features of Excel allow you to reorganize and limit the items in a table to help you make sense of the table.
Sorting and Filtering
The ___ feature in Excel allows you to display only specific information on a data table.
Filtering
___ can provide interesting insights not readily apparent in a data table
Sorting
We call cell references that automatically update when a formula is pasted to a new cell ___
Relative Cell References
A cell reference that remains constant when a formula is pasted into a new cell is called an ___
Absolute Cell Reference
The Insert function window is more commonly known as the ___
Function Wizard
What is the difference between the COUNT and COUNTA function?
COUNT
-counts all cells in a range that have a number
COUNTA
-counts all cells in a range that have data in them (numbers or letters)
*both do not count blank cells
___ calculates the interest rate earned for an investment given the number of payments made as part of the investment, the payment amount, and the current value of the investment.
RATE (nper, pmt, pv, [fv], [type], [guess])
___ calculates the annual percentage rate for an interest rate given the number of times per year that interest is charged.
EFFECT (nominal_rate, npery)
___ calculates the number of payments that will be made to pay off a loan given the interest rate, payment amount, and original loan amount.
NPER (rate, pmt, pv, [fv], [type])
___ calculates the payment amount for a loan given the interest rate, number of payments to be made to pay off the loan, and the original loan amount.
PMT (rate, nper, pv, [fv], [type])
___calculates the current value (accounting for compounding interest) of an investment given the interest rate, number of payments to be made, and the amount of the payment.
PV (rate, nper, pmt, [fv], [type])
___ calculates the future value of an investment given the interest rate, number of payments to be made, and the amount of the payment.
FV (rate, nper, pmt, [pv], [type])
___ feature allows you to see which cells are referenced in a particular formula or function.
Trace Precedents
___ feature allows you to see which other cells reference a particular cell.
Trace Dependants
___ feature displays the functions and formulas used in each cell of a worksheet instead of the formula or function results.
Show Formulas
Who and when was Boolean Logic invented?
Invented by George Boole in 1800s
What is it called when you have an “IF” function inside of another “IF” function?
Nesting
If you need to accommodate 101 possible outcomes, how many IF functions will you have?
100
*Always one less
___ allow you to perform calculations where the cell references used to complete the calculations depend on the values of other cells in a worksheet
Conditional Functions
When working with a VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup]):
What does TRUE/FALSE mean for “range_lookup”
What will Excel assume if you do not use it in your argument?
TRUE = approximate match
FALSE = exact match
*Excel will assume TRUE
What is the difference between the “lookup_value” argument in VLOOKUP and HLOOKUP
VLOOKUP assumes that it will search in the first column of the table to find a match
HLOOKUP assumes that it will search in the first row of the table to find a match
How does Excel treat a date?
How does Excel treat text?
- Treats a date like a number
- Treats text like a string, or collection of useless Characters
How many different ways are there to display a date in an Excel cell?
17
When Excel expresses a date as a number, what do the numbers to left/right of the decimal place represent?
Left of the decimal place represents DATE
Right of the decimal place represents the specific point in time of the date (fraction of the date)
What do the Excel functions TODAY() and NOW() return?
TODAY() returns the current date
NOW() returns the current time
What are the number intervals that the SECOND, MINUTE, and HOUR functions can return?
0-59
How many characters can Excel store in a one cell?
32,767
*can only visually show 1024 once printed
When using the SEARCH function of Excel, does it return the beginning character account number of the specified text or the end character count?
Beginning
The ___ function of Excel is used to replace characters in a block of text with other characters.
SUBSTITUTE
What do you click to display numerical values of a chart’s data?
Data Labels
The ___ tab of the “Chart Tools” menu scheme contains the menu items used to make high-level manipulations to your charts.
Design
The ___ tab of the “Chart Tools” menu scheme contains the menu items that are used to modify the colors and textual elements of your charts.
Format
___ are used to compare different categories of items or the same category over time
Column Charts
A ___ is used to help the user differentiate between the various data series depicted in a chart.
Chart Legend
A ___ is essentially a column chart rotated 90 degrees to the right.
Bar Chart
___ breaks the categories into segments that represent subcategories that are consistent across the columns.
Stacked Column Charts
Which type of chart is best suited to situations that require the comparison of categories?
Stacked Column Chart
___ are used to compare the parts of category to the whole.
Pie Charts
When inserting a pie chart, the horizontal axis represents the ___
categories
Which type of chart is best used when all the parts of the category are known?
Pie Chart
___ compare the particular values of a category overtime
Line Charts
Which type of chart is best used for identifying trends in a dataset
Line Charts
___ allow you to use spreadsheets to make good decisions based on sophisticated and relevant analyses of the problems you are trying to solve.
Models
What are the 3 things every good model will have in Excel?
- changeable inputs
- outputs
- intermediate calculations
Generally, where are the changeable inputs and outputs found on an Excel model?
Changeable inputs @ top of worksheet
Outputs @ bottom of worksheet
According to the author, what is the most important requirement for building an effective Excel spreadsheet model?
Thoroughly understanding the process
What are the 3 specific tools Excel has for conducting a What-If-Analysis
- Scenario Manager
- Goal Seek
- Data Table
The ___ is useful for examining the impact of several inputs to a spreadsheet model changing at the same time
Scenario Manager
The ___ compares each scenario and how each influences a specified model outcome.
summary worksheet
Using the What-If-Analysis, ___ is used to work backwards from an outcome calculation to determine an input condition necessary to arrive at the desired outcome.
Goal Seek
What are 2 limitations of goal seek?
- you have to enter the exact desired outcome (no ranges)
- can only analyze one changeable input at a time
When using the Data Table tool of the What-If-Analysis, what is it called when only one input is used? What is it called when two changeable inputs are used?
1 = one-way data table 2= two-way data table
___ provide a powerful and flexible way to analyze large amounts of data quickly and easily in Excel.
PivotTables
True or False: When constructing a PivotTable in Excel, the Create PivotTable dialogue window allows the user the import data from a DIFFERENT workbook to use in the existing workbook if he/she chooses
True
___ is used to screen the data as it is placed in a PivotTable.
Report Filter
The PivotTable automatically calculates the ___
Grand Total for all the values on the table
When dealing with PivotTables under the “Show Value as” function, what does the Index tool do?
Ranks the “relative importance” of the cell based on an Excel algorithm
What are the two ways you can filter data in a PivotTable?
Report Filters and Slicing
