Excel Exam (60 Mins) UAlbany

Card Set Information

Excel Exam (60 Mins) UAlbany
2015-04-12 14:05:51

Key terms for the BITM Excel Test at the University at Albany
Show Answers:

  1. Spreadsheet
    an electronic file used to organize related data and perform calculations
  2. Worksheet
    A spreadsheet that contains formulas, values, text and visual aids
  3. Workbook
    a file containing related worksheets
  4. What are the two basic components of a spreadsheet?
    workbook and worksheets
  5. Input Area
    range of cells containing values
  6. Output Area
    range of cells containing results
  7. Cell
    intersection of a column and a row
  8. Which way do worksheet columns lie?
  9. Heavy Border around a cell indicates?
    it is the active cell
  10. Contents of an active cell are displayed in the?
    Formula bar
  11. Text Data
    combination of letters, numbers, and some symbols
  12. Number Data
    is any numerical value that can be used in a mathematical calculation
  13. Date and Time Data
    commonly recognized formats for date and time values
  14. Formulas
    combinations of cell addresses, math operators, values and/or functions (a formula begins with an = sign) return a value.. uses operators
  15. The best way to use cell addresses in formulas vs actual data is...
    Cell Referencing
  16. example 
    of a valid formula for Excel?
  17. Difference B/w Clearing and Deleting
    • Clearing data from a worksheet removes the data but leaves the blank cells
    • Deleting data from a worksheet removes both the data and the cells
  18. Cell Range
    a group of cells
  19. Order of Precedence
    A set of predefined rules that excel follows to calculate the result of a formula (PEMDAS)
  20. Auto Fill
    copy the contents of a cell or cell range or to continue a series using the fill handle
  21. Moving a Worksheet
    Changes its order among sheet tabs
  22. Copying a Worksheet
    makes a duplicate sheet at the new location
  23. Hiding and Unhiding a Column or Row
    • Hiding prevents from displaying and printing
    • Unhiding returns it to view
  24. Range and Nonadjacent range
    • Range is a rectangular group of cells 
    • Nonadjacent range a group of ranges that are not next to each other
  25. Moving/Copying a Range
    Preserves text and values, but cell addresses in the formulas will be altered in the pasted location
  26. Paste Special Command
    used to paste data from the clipboard using a different format
  27. Formatting
    accentuates and draws attention to meaningful portions of a worksheet
  28. Header/Footer
    • Header is content appearing at the top of each printed page
    • Footer is content appearing at the bottom of each printed page
  29. Absolute Reference
    • Remains constant throughout a copy operation 
    • (specified with a dollar sign before the column and row i.e $B$4)
  30. Relative Reference
    • Adjusts during a copy operation
    • specified without dollar signs, I.E B4
  31. Mixed Reference
    • Either the Row or the Column is absolute; the other is relative
    • Specified with a dollar sign before the absolute part of the reference, I.E B$4
  32. 3 types of cell references
    • Absolute $A$4
    • Relative A1
    • Mixed $A1 or A$1
  33. What does the $ indicate in Cell referencing
    Indicates that the row number or column letter will not be modified during a copy
  34. Semi-Selection
    • Uses the mouse pointer to build a formula containing cell references or ranges 
    • Technique also called pointing
  35. How do Identify an Absolute Reference
    Use a $
  36. Relative Cell- Reference
    Indicates a cells relative location from the call containing the formula
  37. When would a Circular error warning occur?
    • If a formula refers to itself
    • results in a popup warning
  38. Syntax
    The set of rules that govern correct formation of a function
  39. Argument
    is an input, such as a cell or range
  40. When would you use the insert function dialog box?
    • Search for a function or select it from a list
    • a functions arguments dialog box offers help on each argument
  41. SUM function
    • Returns the mathematical sum for a number of cells or ranges; for example
    • =SUM(A1:A3) or =SUM(A1,B3,C5) or =SUM(A1:B3,C5:E8)
  42. Common Statistical functions
  43. TODAY/NOW Functions
    • TODAY displays the current date 
    • NOW function displays the current date and time
  44. IF functions 3 arguments
    • •A
    • logical test or condition that is true or false

    • •The
    • resulting value if the condition is true

    • •The
    • resulting value if the condition is false
  45. AND function
    Returns a TRUE value if all arguments are TRUE... maximum of 30 arguments
  46. OR function
    returns a TRUE value if any of the logical conditions are true and a FALSE value if all logical conditions are false
  47. NOT function
    • Reverses the results of a logical test 
    • if the test has a true result, NOT returns a False, and if the test has a false result NoT returns a true
  48. Nested Function
    occurs when one function is embedded as an argument to another function
  49. Lookup table
    • table that organizes data you want to retrieve into different categories 
    • categories for the lookup table are called Compare values
  50. Breakpoint
    Lowest value for a category or series
  51. VLOOKUP Function
    • Requires three arguments:
    • 1) numeric Value (or cell) to look up
    • 2) the range of the table 
    • 3) the column number containing the value you want to return
  52. PMT financial function
    Calculates the periodic payment for a loan with a fixed interest rate and term length
  53. Range Name
    a word or phrase used to identify a cell or cell range
  54. PRESENTATION COUNTS! in other words
    How you say something is often as important as what you say
  55. Chart
    Graphical or visual representation of data in a worksheet
  56. Data points
    Numeric values
  57. Data series
    a grouping of data points
  58. Chart elements include...
    Chart area, plot area, x-axis, y-axis, category axis, value axis, legend
  59. What is the word used to identify the guide for understanding a charts contents?
  60. Pie and Exploded Pie charts are effective for...
    • Displaying proportional relationships
    • only used when the number of categories is small and the relative sizes of the different slices is easily determined
  61. Column Charts/Stacked Column Charts
    • Used for displaying numbers
    • displays data vertically also have clustered column chart
    • depicts total by category instead of each individual data point 
    • useful when you want to compare totals by category
  62. Bar Charts
    show numbers horizontally
  63. Line Chart
    • This type of chart uses a line to connect data points in order to show trends over a period of time (x-axis displays time units, y-axis displays values for each point)
    • best for categories that follow sequential order with a constant time interval
  64. Area Chart
    Emphasizes the magnitude of changes over time by filling in the space B/W lines with a color
  65. XY (Scatter) Chart
    • This type of chart shows the relationship B/W two variables
    • show patterns or a relationship B/W 2 or more sets of value
  66. Stock Chart
    shows the high, low, opening, and closing prices for individual stocks over time
  67. Surface Chart
    Displays trends using two dimensions on a continuous curve
  68. Doughnut Chart
    Displays values as percentages of a whole, but may contain more than one data series
  69. Bubble Chart
    Shows the relationship among three values by using bubbles
  70. What are line charts best used for?
    Showing changes in time
  71. KISS Principle in designing charts
  72. Pie, column, bar and line charts assume?
    numbers are plotted against categories
  73. If you change the cell values in the tables related to the charts, do the values in the charts automatically change?
  74. Data Source
    Is the range that contains the data you want to display in the chart
  75. Embedded Chart
    Chart is placed in a worksheet next to its data source
  76. Sparkline
    A miniature chart displayed in a single cell
  77. Chart Filter
    Controls which data series and categories are visible in a chart
  78. Column
  79. Row
  80. Freezing Rows and Columns
    Lets you keep headings visible as you work with the data in a large worksheet
  81. Excel Table
    Structured range of related data formatted to enable data management and analysis
  82. Field
    An individual piece of data
  83. Record
    Complete set of data for an entity
  84. Can table names contain spaces?
  85. Table style
    Controls the fill color of the row, columns, and records
  86. Sorting Data
    Rearrange, or sort, the records in a table or range based on the data in one or more fields
  87. Structured Reference
    A tag or table field name used in a formula or function
  88. Filtering
    • The process of displaying only records that meet specific conditions
    • Ex) Numeric, Date, "Equals",
  89. What is the subtotal function used for?
    Provides a sum for a range of data
  90. Rule
    Specifies the type of condition (such as formatting cells greater than a specified value), the type of formatting when that condition occurs
  91. When is the New Formatting Rule dialog box used?
    To create a customized rule
  92. Can file Names have Blanks?
  93. Page Break
    an indication where data will start on another printed page
  94. Conditional Formatting
    A set of rules that applies specific formatting to highlight or emphasize cells that meet specifications
  95. Indenting
    Helps others see the hierarchal structure of data
  96. Wrap Text
    makes the data appear on multiple times by adjusting the row height to fit the cell contents within the column width
  97. COUNTA Function
    tallies the numbers of cells in a range that are not blank
  98. Value Axis
    displays incremental numbers to identify the worksheet values (such as number of jobs or revenue) used to create the chart.
  99. Chart Element
    component that completes or helps clarify the chart
  100. Gridlines
    Horizontal and vertical lines than span across the chart to help people identity the values plotted by the visual elements, such as column, Excel displays horizontal gridlines for column, line, scatter, stock surface and bubble charts and vertical gridlines.
  101. Axis Titles
    Labels that describe the category and value exes
  102. Subtotal
    • An aggregate calculation, such as SUM or AVERAGE, that applies for a subcategory of related data within a larger dataset
    • Subtotaling feature creates outlines
  103. Outline
    A hierarchical structure of data organized so that groups can be expanded to show details or collapsed to show high-level structure.
  104. Data Mining
    Process of identifying trends and patterns in data
  105. Pivot Table
    • Excels Interactive data mining Feature
    • Great way to summarize data in a professional looking report 
    • enables you to summarize a ray of data in a excel table into a concise, tabular format for easier reporting and analysis
  106. Can you change the data directly in a PivotTable?
    NO, you must edit the excel table then refresh or update, the pivot table to reflect the current state of art objects list
  107. What process to PivotTables support?
    Data mining
  108. Type of Sorts and Filters for a PivotTable
    • Sorts- Smallest to largest and Vice Versa
    • Filters- report and group
  109. Slicer
    • Graphical
    • provide buttons that you can click for quick filtering 
    • indicate the current filtering state
  110. What are Slicers used for in Excel
    Filtering PivotTable data
  111. PivotChart
    Graphical representation of a PivotTable
  112. Binding Constraint
    A constraint in Solver enforces to reach a target value
  113. Variable
    A value that you can change
  114. What-If-analysis
    process of changing variables to see how changes will affect the calculated results
  115. Substitution Value
    Value that replaces the original value of a variable in a data table
  116. Scenario Manager
    • Helps to perform more sophisticated what-if analysis 
    • Best Case, Most likely, Worst case
    • Creates a scenario summary report
  117. Add-In
    A program that can be added into excel to provide enhanced functionality
  118. Scenario
    A set of values that represent a possible situation
  119. One variable data table
    A data analysis tool that provides various results based on changing one variable
  120. Goal Seek
    Method for finding solutions in What-If analysis by working backwards from the end result to determine the input to produce that result
  121. Solver
    An add in application that manipulates variables based on constraints to find the optimal solution to a problem
  122. INDEX Function
    A lookup and reference value or reference to a value within a range
  123. What What-if analysis tool is best for manipulating one variable to determine one result?
    Goal seek
  124. Once a slicer is inserted into a worksheet, which of the following actions cannot be performed?
    Append the Slicer
  125. True or False: Histogram is a tool of the Analysis tool pack?
  126. Nested Logical function
    Logical function embedded in another logical function
  127. True or False: the not function can only contain one argument
  128. Match function
    Returns the position of a value on a list
  129. Index Function
    Returns a value or the reference to a value within a range based on x and y coordinates
  130. Autofilter
    • Set criteria for fields using the drop-down list for the field 
    • Can set criteria for multiple fields
  131. Advanced Filter extends Autofiltr in 2 important ways
    • Creates more complex criteria 
    • copies the rows to another section of the worksheet, leaving the original list intact
    • Criteria are case sensitive
    • Text entries treated as though they have wildcards(*)
  132. Same Row
  133. Different Row
  134. Database Functions
    • similar to statistical functions. their names are preceded with a D
    • Three arguments: database,field,criteria
  135. Profit Loss/Income Statement
    How much money businesses make or lose over a period of time
  136. Interpolation
    Determining values that do not appear in a given set of values
  137. Extrapolation
    Inferring values that do not appear in a given value set
  138. Depreciation Functions
    • Cost, salvage value, life
  139. Amortization
    • Process of decreasing or accounting for an amount; over a period
    • PMT,IPMT(Interest), PPMT(principal)
  140. Analysis Tool
    • Add in, just like solver 
    • Helps create Amortization tables