# Excel Final Exam

The flashcards below were created by user jrad95 on FreezingBlue Flashcards.

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, COUNTIFS, AVERAGEIFS

• 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
• An electronic marker that connects to a(n):
• specific cell in the same workbook
• specific cell in a different workbook
• existing file
• web page
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"
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
• 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?
24
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
• XML TAGS ARE CASE SENSITIVE
88. What does XML stand for?
eXtensible markup language
89. which of the following is not a customizable feature in excel
Backstage
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
True
92. end tag
contains the name of the element preceded by a slash/
93. start tag
contains the name of the element
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
Save,Undo,Redo
98. Collaboration
• Occurs when  multiple people work together to achieve a common goal by using technology
• Tools inlcude:
• share,compare and merge
• 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
• DOES NOT PRINT
• 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
 Author: jrad95 ID: 301726 Card Set: Excel Final Exam Updated: 2015-05-03 17:46:57 Tags: Excel Folders: Description: Final exam in Excel for BITM 215 at the University at Albany Show Answers: