Excel Final Exam

  1. Create Fields requiring least maintenance
    For example...
    Hire date, and birth date
  2. Store in smallest units of data as possible
    Use separate fields for address data instead of one that contains everything
  3. Structured Reference
    • A tag of field name used in a formula or function
    • If data table range changed the structured reference will remain valid and adjust as the table changes
    • If you add/delete columns/rows, the cell references may no longer be valid but the structured references will
    • Ex)=SUM(DeptSales[SaleAMt])
  4. Qualified Reference
    • Means the table is explicitly identified, you may used the table name in the formula
    • If you use a structured reference within a table it can be unqualified
    • if you use a structured reference outside a table you need to use a fully qualified reference
  5. IFERROR Function
    • Determines if a cell contains an error value  and display the message you choose 
    • Ex) #DIV/0, #NAME, #N/A
  6. What constitutes a fully qualified reference?
    using a table name and a structured reference
  7. Statisitcs
    A set of concepts, rules, and procedures that organize, understand, and help make informed decisions
  8. Variable
    property of an object or event that can take on different values
  9. Measures of Center(Central Tendency)
    • Plotting data in a frequency distribution shows the general shape of the distribution and gives a general sense of how the numbers are bunhced
    • Mode,Median,Mean
  10. Measures of Spread(Variability)
    • Provide information about the degree to which individual scores are clustered about or deviate from the average value in a distribution 
    • Range,Variance,Standard Deviation
  11. Chi Squared
    • Measure of how expectations compare to results 
    • Must be random, raw, mutually exclusive, and drawn from independent variables
  12. t-test
    • used to determine whether a set or sets of scores are from the same Population 
    • Will tell you if a single variable is significant
  13. F-statistic/Test
    • Will tell you if a group of variables are jointly significant 
    • will contain both a F value and an F critical value
  14. F Value and F Critical
    If your f-statistic in a test is smaller than your F value, you can reject the null hypothesis
  15. p-test
    Assuming the F test showed the variables are jointly significant, shows which variables within a group are significant 
  16. Statistical Functions and Conditional Statistical Functions
    • Statistical Functions- perform calculations on the range specified in the argument ..Ex)sum,average,count
    • Conditional Statistical Functions- perform calculations based on a condition... Ex) Sumif, countif,averageif
  17. When more than one condition must be met, What statistical functions do you use?

    • SUMIFS and AVERAGEIFS must contain at least 5 arguments 
    • COUNTIFS must contain at least 4 arguments
  18. SUMIF Arguments
    Range, Criteria, Sum_Range
  19. AVERAGEIF Arguments
    Range, Criteria, Average_range
  20. COUNTIF Arguments
    Range, Criteria
  21. Statistical Functions (suffixed with an S) perform calculations based on?
    Multiple conditions
  22. RANK.EQ and RANK.AVG
    • EQ) identifies a values rank within a list of values 
    • AVG) identifies a values rank of a value but assigns an average rank when identical values exist 
    • Arguments- Number,Ref,Order
  23. Percentile.INC and Percentile.EXC
    • INC) displays a values rank as a percentile of the range of data in a datasheet
    • EXC) Returns a values rank as a percent excluding 0 and 1
    • Arguments-ARRAY,X, Significance(optional)
  24. Quartile
    Value used to divide a range of numbers into four equal groups
  25. Quartile.INC and Quatile.EX
    • INC) identifies the value at a specific quartile in a dataset
    • EXC) returns the value at a specific quartile excluding 0 and 4
  26. Population and Sample
    • Population- Dataset containing all the data to be evaluated 
    • Sample- Smaller, more manageable segment of the population
  27. Variance and Standard Deviation
    • Variance- measure of a dataset's dispersion
    • Standard Deviation- Measure of how far the data sample is distributed around the mean
  28. Correlation and Frequency Arguments
    • Data_array- Range of cells that contain the values to be evaluated 
    • Bins_array- predefined set of numerical values are used to organize and count the data
  29. Analysis ToolPak
    • Offers 19 statistical functions
    • Toolpak generates a report while functions only return values
  30. ANOVA(Analysis of Variance)
    • A statistical hypothesis test to determine if samples of data were taken from the same population 
    • ANOVA report-analysis toolpak
    • Single-factor- most comonly used ANOVA function
  31. Covariance
    • Similar to correlation- measure of how two sets of data vary simultaneously
    • Population, and Sample
    • Can create a Covariance report
  32. Histogram
    • Visual display of tabulated frequencies 
    • requires bins to tabulate the data and returns a frequency distribution table 
    • Analysis Toolpak can be used to create them
  33. Array
    specifies the range that contains the values to compare such as D$2:D$13
  34. Grouping
    • Selecting two or more worksheets so you can perform the same action at the same time 
    • Can group contiguous worksheets by holding shift key
    • can group non contiguous by holding control and selecting
    • helps to do data entry, formatting, and other structural changes across worksheets
  35. Ungrouping
    • the process of deselecting grouped worksheets so that actions preformed on one sheet do not affect other worksheets
    • Can be done by right clicking and selecting ungroup 
    • or by clicking another worksheet
  36. Can you copy sheets from one workbook to another?
    • Yes
    • allows you to reference cells in other worksheets in the same workbook 
    • Requires using the name of the worksheet before the cell name
  37. What does an ! do?
    • Separates a worksheet and a cell reference 
    • =Atlanta!B3
    • Worksheets are absolute, while cell references can be absolute, relative, or mixed
  38. If a worksheet contains a space, what should surround the worksheet before a cell reference?
  39. Split a window
    • Divide a worksheet into two or four resizable panes 
    • click split a window on the view tab, and to remove panes click split again
  40. Synchronized Scrolling
    Allows you to scroll in one pane while the other pane(s) move similarly
  41. Hyperlink
    • An electronic marker that connects to a(n):
    • specific cell in the same workbook
    • specific cell in a different workbook
    • existing file 
    • web page
    • E-Mail Address
  42. 3-D formula
    • A formula that refers to the same range in multiple worksheets
    • ex) =October!E3+November!E3
  43. Worksheet Reference
    pointer to a cell in another worksheet
  44. Linking ( with respect to 3-D Formulas)
    is the process of connecting cells B/W workbooks
  45. 3-D reference
    • refers to the same cell or range in multiple worksheets in the same workbook
    • ex)=Atlanta:Boston!E3
  46. Syntax Error
    Occurs when a formula or function violates correct construction
  47. Logic Error
    Incorrect construction produces inaccurate results
  48. Formula Auditing
    a set of tools that enable you to display or reach relationships for formula cells, show formulas, check for errors, and evaluate formulas.
  49. Precedent Cells
    Cells referenced by a formula in another cell
  50. Dependent Cells
    Contain formulas that refer to other cells
  51. Tracer Arrows
    Help you identify cells that cause errors
  52. Difference B/W blue and red arrows in the formula auditing group?
    • Blue arrows show cells with no errors 
    • Red arrows show cells that cause errors
  53. Error Checking button
    checks for errors that have occurred in formulas anywhere in a worksheet
  54. What is the Purpose of a  3-D workbook?
    To work with data across multiple worksheets
  55. Watch Window
    Formulas in cells that are not visible can be "watched"
  56. Link
    A connection B/W files that allows data to be transferred from one file to the other
  57. Source File
    Is the workbook that contains the data
  58. Destination file (Dependent file)
    The workbook that receives the data
  59. What is the purpose of a Watch Window
    To see how changes in data affect formulas on another table
  60. If both the source and destination files are open when you make the change...
    the destination file is updated automatically
  61. Data Validation
    enables you to control the data that can be entered into a cell
  62. Input message
    appears when the cell become active and can be used to specify the type of data the user should enter in that cell
  63. Importing
    • The process of inserting data from another application 
    • Can be text files or Access database files
  64. Embed Data
    • When you import data, but do not want to maintain a link
    • When you want to edit data within excel
  65. Link Data
    • When you want to create a link
    • When you want imported data updated if changes are made to the original data source
  66. What does a text file NOT contain?
    Formatting, sound, or video
  67. Tab Delimeterd files
    one tab separates columns
  68. Comma-separated value (CSV)
    • Commas separate columns 
    • Newline characters separate rows
  69. Newline Character
    a special character that designates the end 0f a line and separates data for the next line or row
  70. Access databases may be imported in three ways...
    As a table, as a pivottable report, and as a pivotchart
  71. Database
    A group of tables
  72. Table
    A group of related records
  73. Data Binding
    The process that establishes a connection B/W the application and business logic
  74. Query
    • A standard way of extracting information from a data source, such as a database from the web
    • SQL-Structured Query Language
  75. Web Query
    • used to set up a connection table on a web page
    • if you have to log in to the web site, the query generally will not work
  76. Workbook connections
    Helps you view or remove existing connections from queries
  77. Trusted Location
    • a folder or a document location, from which Excel will trust documents and allow them to open and run code without sending a notification to the user 
    • be VERY careful when you choose one
  78. Text to Columns
    Command to separate data into multiple columns
  79. What would you use a Web Query for in excel
    To import sport scores into a spreadsheet
  80. How many Text functions does excel have?
  81. CONCATENATE function
    joins two or more strings of text into one string
  82. SUBSTITUTE function
    Substitutes or replaces new text for old text in a text string
  83. TRIM function
    Removes leading and trailing spaces in a text string. does not remove spaces B/W words
  84. Flash Fill
    • An Excel feature that allows you to enter data in one or two cells to set an example 
    • if it recognizes the pattern it will fill in the remaining cells
  85. What does the CONCATENATE function do?
    Adds text cells together
  86. eXtensible Markup Language (XML)
    • a standard file format that enables sharing across hardware, operating systems, and applications 
    • XML describes the structure of the data, but not the appearance or formatting
  87. XML Document
    • divided into elements which contain a start tag enclosed in angle brackets, an end tag preceded by a slash in angle brackets, and the associated data 
  88. What does XML stand for?
    eXtensible markup language
  89. which of the following is not a customizable feature in excel
  90. which of the following file types uses tabs to separate data into columns
    Tab delimeterd
  91. T Or F- the user cannot change automatically updated properties
  92. end tag
    contains the name of the element preceded by a slash/
  93. start tag
    contains the name of the element
  94. MetaData
    data that describes other data
  95. Document Properties
    Attributes that describe the file
  96. Properties dialog box Sections
    • General-displays the file name, file type, location, size, creation date, modification date, and last accessed date.
    • Summary-displays properties the user can enter and change.
    • Statistics- includes creation, modified, accessed, and printed dates
    • Contents- displays the worksheets names contained in the workbook
    • Custom- enables the user to create and maintain custom properties for the current workbook.
  97. Quick access toolbar contains three commands
  98. Collaboration
    • Occurs when  multiple people work together to achieve a common goal by using technology
    • Tools inlcude:
    • share,compare and merge
    • comments
    • track changes
  99. Shared Workbook
    A file that enables multiple users to make changes at the same time and allow everyone to see the changes
  100. Owner
    • Person who created the workbook and designates it as shared 
    • resolves conflicts and has full rights
  101. If two users try to make changes to cells at the same time what pops up?
    • Conflict resolutions dialog box
    • the change is then resolved based on settings in the shared workbook dialog box
  102. Network permission
    determines who has the right to open and modify files
  103. Non-Share able
    • only one person can work on the workbook at the same time 
    • second person receives a "file in use" notice
  104. Compare and Merge workbooks
    • Collaboration command that combines shared workbooks into one workbook so that you can compare the changes and decide which ones to keep 
    • Must be marked as shareable, have unique workbook names, and be saved in the same drive and folder
    • goes onto the quick access toolbar
  105. Comment
    a notation attached to a cell to pose a question or provide commentary
  106. Comment Indicator
    • A red colored triangle in the top right of the cell indicating it contains a comment
    • can print the comments if open, or can move to another worksheet
  107. Track Changes
    • Records certain types of changes made in a workbook 
    • Colored blue triangle in the top left indicates a cell has been changed
  108. History Worksheet
    A worksheet that contains a list of changes made to your workbook
  109. Document Inspector
    • Detects hidden and personal data in a worksheet for removal 
    • Create a copy of a worksheet before doing this, because you cant always restore all the data it removes
  110. Accessibility Checker
    • Detects issues that could hinder a disabled persons ability to use a workbook 
    • error, warning, tip
  111. Compatibility Checker
    • Detects data and features that are not compatible with previous versions of excel
    • use HELP
  112. Mark as Final
    • Communicates that this is the final version and it is read only
    • you cant invoke a password on a workbook if it is already marked as final
  113. Digital signature
    • is an electronic, encrypted notation that stamps document to authenticate the contents, cnfirms that a particular
    • person authorized it, and marks the workbook as final.
  114. Signature Line
    enables a person to type or insert a visible digital signature to authenticate the workbook
  115. Template
    A special workbook file used as a model to create similarly structured workbooks
  116. Theme
    a collection of colors, fonts, and effects
  117. Background
    • an image that appears behind the worksheet data onscreen
    • Watermarks will print
  118. Cell style
    a set of formatting options applied to worksheet cells
  119. Why should you include comments in a workbook?
    To help other people understand the design of the worksheet
  120. Locked Cells
    Prevent users from making changes to that cell in a protected worksheet
  121. Creating passwords in excel
    • up to 255 characters 
    • case sensitive, and you must remember it, if its lost its gone forever
  122. Macro
    A set of instructions that tells excel which commands to execute
  123. Personal Macro Workbook
    • the default store my macro is in this workbook
    • a personal macro workbook is a hidden workbook stored in the XLstart folder that contains macros and opens automatically when you start excel
  124. Macro Recorder
    • An application within Excel that records
    • actions performed in a worksheet and uses that series of actions to create code
    • that can repeat those actions elsewhere in the document.
  125. What are macros used for in Excel?
    To give a set of commands to Excel
  126. Visual Basic for Applications(VBA)
    an Office application used to create, edit,execute, and debug macros using programming language
  127. Procedure
    •a named sequence of statements that execute as one unit; always begins and ends with the Sub and End Sub statements
  128. Keyword
    a special programming syntax used for a specific purpose
  129. Comment
    which are indicated by an apostrophe and appear in green, provide information about the macro, but do not affect its execution and are considered documentation.
  130. Syntax
    The rules governing the formation of statements in a programming language
  131. VBA is..
    Event driven and object oriented
  132. Project
    A collection of macros, worksheets, data entry forms and other items that make up the customized application your trying to create
  133. *Project Explorer*
    A window in Visual basic editor that displays a hierarchical list of all currently open projects and their contents
  134. *Module*
    • A VBA module is a file that stores sub procedures and functions. modules can be created and viewed in a VBA editor
    • Collection of VBA macros
  135. Object
    any element within excel working environment  such as a worksheet, cell, workbook, or excel itself
  136. Code window
    Displays the VBA macro code associated with any item in project explorer
  137. Sub Procedure
    performs an action on your project or workbook, such as formatting a cell or displaying a chart
  138. Method
    An action that can be performed on an object
  139. Variable (in VBA)
    a named element in a program that can be used to store and retrieve information
  140. Do Until Loop
    Keeps looping until a specific condition is met
  141. Do while Loop
    Keeps looping while a specified condition is met
  142. For...Next statements
    Executes all statements between the words for and next
  143. Exit Sub
    Transfers control from anywhere in a subroutine to the End Sub statement
  144. MsgBox Statement
    Displays Information to the user while the Macro is executing
  145. InputBox Function
    Accepts information from the user while the macro is executing
  146. Debugging in VBA
    • Syntax errors-VBA will identify
    • Run time errors- Program will crash 
    • Logic errors-runs, but results incorrect
  147. Trusted documents
    Enables you to trust network documents to open without excel displaying any security warnings
Card Set
Excel Final Exam
Final exam in Excel for BITM 215 at the University at Albany