Study your flashcards anywhere!
Download the official Cram app for free >
 Shuffle
Toggle OnToggle Off
 Alphabetize
Toggle OnToggle Off
 Front First
Toggle OnToggle Off
 Both Sides
Toggle OnToggle Off
 Read
Toggle OnToggle Off
How to study your flashcards.
Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key
Up/Down arrow keys: Flip the card between the front and back.down keyup key
H key: Show hint (3rd side).h key
A key: Read text to speech.a key
35 Cards in this Set
 Front
 Back
What is Excel ?

A “spreadsheet” software program that is used to record, manipulate and analyze data and help people make decisions.
Used for Analysis Financial Accounting Marketing Used for Projections Budgeting & Sales Trend & Financial Analysis Detecting Patterns & Relationships Used for decision making: What IF scenarios 

Standard Excel Tool Bar

Very similar to MS Work Tool Bar ShortCuts
New, Open, Save, Print, Print Preview , Spell Check, Cut, Copy, Paste, and Format Painter, Undo and Redo, Insert Hyperlink  To insert a hyperlink to a web site on the Internet. Autosum, Function Wizard, and Sorting – Functions Zoom  To change the size that the worksheet appears on the screen 

Spread Sheet Basics

Inserting: Rows; Columns, Worksheets
Use “Insert” command from menu bar Resizing Rows and Columns (2 ways) Place cursor in row or column and select: Format:Row: Height Format:Column:Width Resize row or column by dragging row label or column label 

Spread Sheet Basics (cont.)

Selecting Cells – before a cell can be modified it must first be selected (highlighted). You can select:
1Cell (click once on cell) Entire Row (click the row label) Entire Column (click the column label) Entire Worksheet (click the worksheet button) Cluster of cells (drag mouse over the cells) Copying Cells – use the “EditCopy” from the menu bar or (copy/paste buttons) 

Spread Sheet Basics (cont.)

Sorting Cells:
Basic Sorts: Sort Ascending (AZ) or Sort Descending (ZA) Complex sorts: highlight rows or columns to be sorted, select “DATASORT” 

Excel Formulas

Spreadsheet key feature is use of mathe
matical formulas and execute functions Formulas entered in worksheet cell & must begin with an equal sign “=“. 

Referencing cells

Referencing cells
Relative: just calling cells by their columnrow label (A1) Absolute: locking in a cell reference in a formula ($A$1) Mixed: locking a column or a row reference (A$1 or $A1) Why use absolute or mixed? 

Basic Functions

Functions can be a more efficient method of performing mathematical operations
Example: adding: “=D1+D2+D3+D4+D5+D6” a shorter way would be “=Sum(D1:D6)” =Sum(A1:A100) – finds sum of cells A1 to A100 =Average(B1:B10) – finds the average of cells B1 through B10 =Today() – gives the current date =Max(C1:C100) – returns highest value from cells C1 through C100 =Min(D1:D100) – returns lowest value from cells D1 through D100 

Charts

Charts allow you to present data entered into the worksheet in a visual format using a variety of graph types. Before you can make a chart you must first enter data into a worksheet.


Data series

Set of values to chart
Up to 32,000 data points (values) 

Categories

Used to organize values in data series
Labels or headings under which the values are stored 

CHARTING BASICS

Chart Wizard – Takes you through process of creating a chart
Enter data into worksheet & highlight all the cells to be included in the chart, including headers 

Selecting nonadjacent data ranges

Click and drag through first cell range
Press and hold Ctrl key Click additional cells or click and drag additional cell ranges When finished, release Ctrl key 

CHART PLACEMENT

Embedded chart
Placed on worksheet near data Chart sheet Placed on separate sheet in workbook 

UPDATING A CHART

Automatically linked to data from which created
Updated as soon as enter data Entire chart resizes automatically Yaxis values will also adjust if large change was made 

DEVELOPING AND PLANNING A CHART

What data are to be represented
Which chart type is best to represent the data Where should the chartbe placed Embedded Chart sheet What features shouldbe selected 

EMBELLISHING A CHART

Vary typeface
Use color in background, text and foreground of objects Add graphics Use patterns for fills rather than colors when printing black and white 

Embedding Charts into Word or PowerPoint

Excel Graphs can be embedded into Word or PowerPoint as either:
A Picture file (windows metafile) A Picture “Object” (Microsoft Excel Chart Object) Under the menu bar go to EDITPASTE SPECIAL What are the advantages/disadvantages to both options? 

Function Basics

FunctionName(argument1,argument2, …)
Precede with = if first in expression Returns single answer Value of function’s evaluation Function name indicative of task More than 240+ functions available!!!! 

Function Basics (cont.)

240+ Functions Categorized:
Financial (i.e., IRR) Date and time (i.e., now) Math and trig (i.e., sine, cosine) Statistics (i.e., Ttest) Lookup and reference (searching) Database (i.e., average, sum, count) Text (i.e., trim) Logical (i.e., IF) 

Functions Basics: Argument List

Argument list has 0 or more arguments
Zero argument is empty () Gets argument from system Still needs parentheses Example Today() or Now() Functions Separate arguments with commas, no spaces Positional arguments Arguments listed in specific order Inflexible Necessary for correct substitution in hidden equation 

Argument list has 0 or more arguments

Zero argument is empty ()
Gets argument from system Still needs parentheses Example Today() or Now() Functions Separate arguments with commas, no spaces 

Positional arguments

Arguments listed in specific order
Inflexible Necessary for correct substitution in hidden equation EX: Future Value 

Assumptions Data

Example data used to test worksheet
Erased when convinced worksheet works properly 

External Data

Data generated from external sources


IF Function

Belongs to logical function category
Conditional test is equation comparing two values (functions, formula labels, or logical values) Relational operator compares two parts of a formula Result of comparison is either true or false 

Relational Operators

IF(logical test, value if true, value if false)
provide a choice of action based on one or more conditions Comparison operator symbols less than < greater than > less than or equal to <= greater than or equal to >= equal to = not equal to <> 

Financial Analysis & Statistical Functions

Many financial functions & statistical functions available
Be aware they exist & know that they can be useful for Statistical & Financial analysis Common Stats functions AVG, STDEV, CHITEST Popular financial functions PMT, IPMT, PPMT, PV 

Pivot Tables

Excellent method to distill some meaningful information from large data sets.
Create Pivot Tables: Use Pivot Wizard under DATA  Pivot Table and PivotChart Report 

Row field

A field from the source data that you assign to a row orientation in a PivotTable report.


Column field

A field from the source data that you assign to a column orientation in a PivotTable report.


Page field

A field from the source data that you assign to a page (or filter) orientation in a PivotTable report.


Item

A subcategory of a row, column, or page field.


Data field

A field from the source data that contains data to be summarized.
Note If you're using nonOLAP source data, you can choose how to summarize data (for example, by sum, average, or count). A data field usually summarizes numeric data, but it can also summarize text. 

Data area

The cells in a PivotTable report that contain summary data.
