Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

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

image

Play button

image

Play button

image

Progress

1/35

Click to flip

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:
1-Cell (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 “Edit|Copy” from the menu bar or (copy/paste buttons)
Spread Sheet Basics (cont.)
Sorting Cells:
Basic Sorts: Sort Ascending (A-Z) or Sort Descending (Z-A)

Complex sorts: highlight rows or columns to be sorted, select “DATA|SORT”
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 column-row 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
Y-axis 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 chart be placed
Embedded
Chart sheet
What features should be 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 EDIT|PASTE 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 non-OLAP 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.