Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
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 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 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.
|