Excel Final Exam

Hire date, and birth date

Use separate fields for address data instead of one that contains everything

• 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])

• 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

• Determines if a cell contains an error value  and display the message you choose 
• Ex) #DIV/0, #NAME, #N/A

using a table name and a structured reference

A set of concepts, rules, and procedures that organize, understand, and help make informed decisions

property of an object or event that can take on different values

• 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

• 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

• Measure of how expectations compare to results 
• Must be random, raw, mutually exclusive, and drawn from independent variables

• used to determine whether a set or sets of scores are from the same Population 
• Will tell you if a single variable is significant

• Will tell you if a group of variables are jointly significant 
• will contain both a F value and an F critical value

If your f-statistic in a test is smaller than your F value, you can reject the null hypothesis

Assuming the F test showed the variables are jointly significant, shows which variables within a group are significant 

• 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

SUMIFS, COUNTIFS, AVERAGEIFS

• SUMIFS and AVERAGEIFS must contain at least 5 arguments 
• COUNTIFS must contain at least 4 arguments

Range, Criteria, Sum_Range

Range, Criteria, Average_range

Range, Criteria

Multiple conditions

• 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

• 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)

Value used to divide a range of numbers into four equal groups

• INC) identifies the value at a specific quartile in a dataset
• EXC) returns the value at a specific quartile excluding 0 and 4

• Population- Dataset containing all the data to be evaluated 
• Sample- Smaller, more manageable segment of the population

• Variance- measure of a dataset's dispersion
• Standard Deviation- Measure of how far the data sample is distributed around the mean

• 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

• Offers 19 statistical functions
• Toolpak generates a report while functions only return values

• 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

• Similar to correlation- measure of how two sets of data vary simultaneously
• Population, and Sample
• Can create a Covariance report

• 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

specifies the range that contains the values to compare such as D$2:D$13

• 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

• 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

• Yes
• allows you to reference cells in other worksheets in the same workbook 
• Requires using the name of the worksheet before the cell name

• Separates a worksheet and a cell reference 
• =Atlanta!B3
• Worksheets are absolute, while cell references can be absolute, relative, or mixed

'

• Divide a worksheet into two or four resizable panes 
• click split a window on the view tab, and to remove panes click split again

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
• E-Mail Address

• A formula that refers to the same range in multiple worksheets
• ex) =October!E3+November!E3

pointer to a cell in another worksheet

is the process of connecting cells B/W workbooks

• refers to the same cell or range in multiple worksheets in the same workbook
• ex)=Atlanta:Boston!E3

Occurs when a formula or function violates correct construction

Incorrect construction produces inaccurate results

a set of tools that enable you to display or reach relationships for formula cells, show formulas, check for errors, and evaluate formulas.

Cells referenced by a formula in another cell

Contain formulas that refer to other cells

Help you identify cells that cause errors

• Blue arrows show cells with no errors 
• Red arrows show cells that cause errors

checks for errors that have occurred in formulas anywhere in a worksheet

To work with data across multiple worksheets

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

Is the workbook that contains the data

The workbook that receives the data

To see how changes in data affect formulas on another table

the destination file is updated automatically

enables you to control the data that can be entered into a cell

appears when the cell become active and can be used to specify the type of data the user should enter in that cell

• The process of inserting data from another application 
• Can be text files or Access database files

• 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

Formatting, sound, or video

one tab separates columns

• Commas separate columns 
• Newline characters separate rows

a special character that designates the end 0f a line and separates data for the next line or row

As a table, as a pivottable report, and as a pivotchart

A group of tables

A group of related records

The process that establishes a connection B/W the application and business logic

• A standard way of extracting information from a data source, such as a database from the web
• SQL-Structured Query Language

• 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

Helps you view or remove existing connections from queries

• 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

Command to separate data into multiple columns

To import sport scores into a spreadsheet

24

joins two or more strings of text into one string

Substitutes or replaces new text for old text in a text string

Removes leading and trailing spaces in a text string. does not remove spaces B/W words

• 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

Adds text cells together

• 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

• 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

eXtensible markup language

Backstage

Tab delimeterd

True

contains the name of the element preceded by a slash/

contains the name of the element

data that describes other data

Attributes that describe the file

• 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.

Save,Undo,Redo

• Occurs when  multiple people work together to achieve a common goal by using technology
• Tools inlcude:
• share,compare and merge
• comments
• track changes

A file that enables multiple users to make changes at the same time and allow everyone to see the changes

• Person who created the workbook and designates it as shared 
• resolves conflicts and has full rights

• Conflict resolutions dialog box
• the change is then resolved based on settings in the shared workbook dialog box

determines who has the right to open and modify files

• only one person can work on the workbook at the same time 
• second person receives a "file in use" notice

• 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

a notation attached to a cell to pose a question or provide commentary

• 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

• Records certain types of changes made in a workbook 
• Colored blue triangle in the top left indicates a cell has been changed

A worksheet that contains a list of changes made to your workbook

• 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

• Detects issues that could hinder a disabled persons ability to use a workbook 
• error, warning, tip

• Detects data and features that are not compatible with previous versions of excel
• use HELP

• 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

• 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.

enables a person to type or insert a visible digital signature to authenticate the workbook

A special workbook file used as a model to create similarly structured workbooks

a collection of colors, fonts, and effects

• an image that appears behind the worksheet data onscreen
• DOES NOT PRINT 
• Watermarks will print

a set of formatting options applied to worksheet cells

To help other people understand the design of the worksheet

Prevent users from making changes to that cell in a protected worksheet

• up to 255 characters 
• case sensitive, and you must remember it, if its lost its gone forever

A set of instructions that tells excel which commands to execute

• 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

• 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.

To give a set of commands to Excel

an Office application used to create, edit,execute, and debug macros using programming language

•a named sequence of statements that execute as one unit; always begins and ends with the Sub and End Sub statements

a special programming syntax used for a specific purpose

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.

The rules governing the formation of statements in a programming language

Event driven and object oriented

A collection of macros, worksheets, data entry forms and other items that make up the customized application your trying to create

A window in Visual basic editor that displays a hierarchical list of all currently open projects and their contents

• 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

any element within excel working environment  such as a worksheet, cell, workbook, or excel itself

Displays the VBA macro code associated with any item in project explorer

performs an action on your project or workbook, such as formatting a cell or displaying a chart

An action that can be performed on an object

a named element in a program that can be used to store and retrieve information

Keeps looping until a specific condition is met

Keeps looping while a specified condition is met

Executes all statements between the words for and next

Transfers control from anywhere in a subroutine to the End Sub statement

Displays Information to the user while the Macro is executing

Accepts information from the user while the macro is executing

• Syntax errors-VBA will identify
• Run time errors- Program will crash 
• Logic errors-runs, but results incorrect

Enables you to trust network documents to open without excel displaying any security warnings