Mis Excel Exam Two

Front 1

What is Excel ?

Back 1

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

Front 2

Standard Excel Tool Bar

Back 2

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

Front 3

Spread Sheet Basics

Back 3

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

Front 4

Spread Sheet Basics (cont.)

Back 4

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)

Front 5

Spread Sheet Basics (cont.)

Back 5

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”

Front 6

Excel Formulas

Back 6

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

Front 7

Referencing cells

Back 7

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?

Front 8

Basic Functions

Back 8

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

Front 9

Charts

Back 9

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.

Front 10

Data series

Back 10

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

Front 11

Categories

Back 11

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

Front 12

CHARTING BASICS

Back 12

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

Front 13

Selecting nonadjacent data ranges

Back 13

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

Front 14

CHART PLACEMENT

Back 14

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

Front 15

UPDATING A CHART

Back 15

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

Front 16

DEVELOPING AND PLANNING A CHART

Back 16

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

Front 17

EMBELLISHING A CHART

Back 17

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

Front 18

Embedding Charts into Word or PowerPoint

Back 18

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?

Front 19

Function Basics

Back 19

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!!!!

Front 20

Function Basics (cont.)

Back 20

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)

Front 21

Functions Basics: Argument List

Back 21

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

Front 22

Argument list has 0 or more arguments

Back 22

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

Front 23

Positional arguments

Back 23

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

Front 24

Assumptions Data

Back 24

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

Front 25

External Data

Back 25

Data generated from external sources

Front 26

IF Function

Back 26

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

Front 27

Relational Operators

Back 27

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

Front 28

Financial Analysis & Statistical Functions

Back 28

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

Front 29

Pivot Tables

Back 29

Excellent method to distill some meaningful information from large data sets.

Create Pivot Tables: Use Pivot Wizard under DATA | Pivot Table and PivotChart Report

Front 30

Row field

Back 30

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

Front 31

Column field

Back 31

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

Front 32

Page field

Back 32

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

Front 33

Item

Back 33

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

Front 34

Data field

Back 34

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.

Front 35

Data area

Back 35

The cells in a PivotTable report that contain summary data.