businees workout

7/28/2019 Businees Workout

1/122

What is Excel? ==> Calculations Data Analysis

Column

Row

Cell

Worksheet

Sheet Tabs

Workbook

File Type

Ribbon

QAT

Scroll Bars

Numbers Aligned Right

Text Aligned Left

Keyboard Shortcuts

Formulas Calculating Text

Order of Operations

Built-in Functions SUM AVERAGE DOLLAR IF VLOOKUP

Name BoxNames

Cell References

Efficient Formulas

Assumption Tables and data that can vary

Style Number Formatting

Number Formatting As Faade

Date

Time

ROUND function

Charts

Page SetupData Analysis: Sort Filter Subtotal Pivot Table

148840046.xls.ms_office Topics Page 1 of 122


2/122

C Column

Row 3 Cell C3

1)

2)

3)4)

5)

6)

7)

8)

9)

10)

11)

12)

13)

14)

15)


3/122

Cell = Intersection of Column & Row

Worksheet = Sheet = all the cells in one sheet

Worksheet name = Sheet name = 'What is Excel'Sheet Tab holds the Worksheet name 'What is Excel'

Some other Worksheet names that Sheet Tabs hold are: 'Topics' and 'Formulas'

Use Ctrl + PageUp & PageDown to scroll through Sheets

Workbook = file = all the Sheets

File type examples; .xls, .xlsx, .xlsm

Use .xls if you are going to share files with people that have older versions

Use .xls if you are going to share files with people that have older versions

Use .xlsx if you are going to only share files with people that have Excel 2007 and you will not run Macros (VBA

Use .xlsm if you are going to only share files with people that have Excel 2007 and you will run Macros (VBA

The Ribbon replaces the old menus. Click on a Ribbon tab like Home or Data and look for a group such as

Clipboard or Font, then look for the icon button you want. You can also look for the dialog launchers (smalldiagonal arrows) in lower right corner of groups to launch the old dialog boxes (usually more efficient). Try the

dialog launcher in the Page Layout Ribbon tab, Page Setup group.

The QAT (Quick Access Toolbar) has buttons on a toolbar so you can complete actions like formatting or Save or

Undo. You can be moved above or below Ribbon (Right-click the QAT to do this). You can add buttons to the QAT

in two ways; 1) right-click any button in the Ribbon; 2) Right-click QAT, "Customize QAT", from dropdown select

"All Commands", find any item (including items not in Ribbons) to QAT (also, use this method to find items that

Scroll Bars


4/122

Type Word: By Default Alignment is as follows:Text To Left Numbers To Right

Type Number: Left 432/2/2001 2/2/2001

1 11 1

8:01:33 8:01:33

Ctrl + 1 is keyboard shortcut for

Format Cells Dialog Box


5/122

Keyboard Shortcuts DataSet Sales Total Sales

Copy = Ctrl + C Data Data 1

Cut = Ctrl + X Data Data 3

Paste = Ctrl + V Data Data 1

Save = Ctrl + S Data Data 2

Save As = F12 Data Data 1

Ctrl + Arrow = move to end of currentregion Data Data 2

Ctrl + Shift + Arrow = highlight to end of

current region Data Data 2

Current Region means last cell with data,

or last blank cell Data Data 4

Page Setup dialog box = Alt + P + S + P 3

3

5

3

1

2

2

2

2

3

5

2

1

2

4

52

2

5

2

2

4

5

2

2

3

32

1

2

1

2

1

3

2


6/122

1

5

3

3

4

2

15

5

5

3

2

3

5

4

3

3

2

2

3

3

3

1

5

1

4

2

32

5

3

2

3

3

3

2

2

2

12

2

4

2

5

3

5

1


7/122

2

1

2

2

3

2

13

2

2

4

1

1

4

1

1

4

5

4

1

3

5

5

1

3

3

1

23

3

1

1

3

3

1

1

2

1

23

5

4

2

4

3

3

4


8/122

3

4

5

3

4

3

34

2

5

1

4

4

1

1

4

1

3

5

3

3

5

1

3

5

2

2

25

3

1

1

1

3

3

3

2

3

31

4

4

2

1

3

4

1


9/122

3

2

5

2

3

4

15

4

4

5

5

1

4

4

4

3

2

2

5

2

2

5

2

4

1

5

52

1

2

2

3

5

4

3

2

3

13

3

4

3

5

5

2

3


10/122

1

1

1

3

1

1

41

1

2

5

3

5

1

2

2

4

2

4

1

2

1

3

3

4

3

2

52

5

5

4

2

3

3

4

1

1

23

3

2

2

5

3

2

1


11/122

5

2

2

4

4

1

15

2

1

1

4

2

5

2

3

1

2

1

4

2

5

1

3

1

1

3

13

4

3

2

2

5

4

4

1

1

45

4

5

3

1

4

2

3


12/122

2

4

4

4

1

3

23

3

1

5

5

2

5

4

2

5

2

1

2

4

4

5

5

1

3

3

15

1

4

2

2

5

3

3

4

3

13

2

1

1

1

1

2

3


13/122

3

4

3

1

4

3

41

1

2

4

4

3

3

3

3

1

5

4

5

1

3

5

3

5

4

5

32

3

1

2

1

3

5

1

5

5

32

2

5

1

2

4

1

2


14/122

5

3

2

3

1

5

13

4

4

1

4

1

2

1

4

4

4

5

3

5

3

1

1

2

3

3

52

5

5

1

4

2

2

2

4


15/122

1) 162 Excel Keyboard Shortcuts

2) http://www.cpearson.com/excel/KeyboardSho

Key Alone Shift Ctrl

F1 Help What's This Help

F2 Edit Mode Edit Comment

F3 Paste Name Formula Paste Function Define NameF4 Repeat Action Find Again Close Window

F5 Goto Find Restore Window Size

F6 Next Pane Prev Pane Next Workbook

F7 Spell Check Move Window

F8 Extend Selection Add To Selection Resize Window

F9 Calculate All Calculate Worksheet Minimize Workbook

F10 Activate Menu Context Menu Restore Workbook

F11 New Chart New Worksheet New Macro Sheet

F12 Save As Save Open

A Select All

B Bold

C Copy

D Fill Down

E

F Find

G Goto

H Replace

I Italics

J

K Insert Hyperlink

LM

N New Workbook

O Open Workbook

P Print

Q

R Fill Right

S Save

T

U Underline

V Paste

W Close WorkbookX Cut

Y Repeat Active

Z Undo

` (~) Toggle Formula View

1 (!) Cell Format

2 (@) Toggle Bold

3 (#) Toggle Italics

4 ($) Toggle Underline
http://www.cpearson.com/excel/KeyboardShortcuts.htmhttp://www.cpearson.com/excel/KeyboardShortcuts.htm


16/122

5 (%) Toggle Strikethru

6 (^) a

7 (&) a

8 (*) Outline

9 (() Hide Rows

0 ()) Hide Columns

- Delete Selection= (+) Formula

[ Direct Dependents

] All Dependents

; (semicolon) Insert Date

' (apostrophe)

: (colon) Insert Time

/ Select Array

\ Select Differences

Insert Insert Mode Copy

Delete Clear Delete To End Of Line

Home Begin Row Start Of Worksheet

End End Row End Of Worksheet

Page Up Page Up Previous Worksheet

Page Down Page Down Next Worksheet

Left Arrow Move Left Select Left Move Left Area

Right Arrow Move Right Select Right Move Right Area

Up Arrow Move Up Select Up Move Up Area

Down Arrow Move Down Select Down Move Down Area

Space Bar Space Select Row Select Column

Tab Move Right Move Left Next Window

BackSpace Goto Active Cell

Enter

In Edit Mode, put thing

in cell and move active

cell down


in cell and move active

cell up


in cell and keep current

cell as active cell


17/122

Alt Shift Ctrl

Insert Chart Sheet

Save As

Names From LabelsQuit Excel How many

162

Switch To VBA Prev Workbook

Macro List

VB Editor

Print

Formula Arguments

Data Menu

Edit Menu

File Menu Font Name

Help Menu

Insert Menu

Format Menu Select Comments

Font Size

Tools Menu

Window Menu

General Format

Number Format

Time Format

Date Format

Currency Format


18/122

Percent Format

Exponent Format

Apply Border

Select Region

Unhide Rows

Unhide Columns

Control Menu No BorderAuto Sum Insert dialog

Direct Precedents

All Precedents

Select Visible Cells Insert Time

Style Copy Cell Value Above

Select Array

Select Unequal Cells

Left 1 screen

Right 1 screen

Select Current Range Left

Select Current Range Right

Select Current Range Up

Drop down list Select Current Range Down

Control Box Select All

Next Application Previous Window

In Edit Mode, put

thing in cell and keep

current cell as active

cell


19/122

Sales 5,598.00

Expense 6,580.00

Net Income

equal sign, cell references

and operator (minus - )

SalesRep1 25.00SalesRep2 78.00

SalesRep3 101.00

Total

Alt + = is keyboard shortcut

for SUM function

Label For Total

equal sign, text in quotes,

Ampersand & (join symbol)

Annual Rate 18.00%

Monthly Rate

equal sign, cell reference,

operator (division / ),

number

Annual Rate 18.00%

Annual Rate 12.00%

Monthly Rate at

for APR of

18.00%

use Enter to put formula in

cell and move down

Monthly Rate atfor APR of

12.00%

use Enter to put formula in

cell and move down

SalesRep2 78.00

SalesRep3 101.00

Total

use Ctrl + Enter to put

formula in cell and keep

cell selected

Units 10 Cost $10.00

Price Per Unit $15.00 1 - Markup on Sales Price 0.666667

Total

Price = Cost/(1 - Markup on

Sales Price)

use Tab to put formula in cell and move to right

Units 10 Cost $10.00

Formulas

Definition of a formula:

(The long version: anythis not preformatted as Te

Advantages of a formustrings, or deliver a rang

How to create a formul 1. Cell referenc2. Operation sig3. Functions4. Text that is in5. Ampersand s

i. To coampe

1.6. Numbersi. The o

the nu7. Arrays (exam

How to enter a formula

1. ENTER (Acti2. Ctrl + Enter (3. Tab(Active C4. Shift + Tab (5. Shift + Enter


20/122

Price Per Unit $15.00 1 - Markup on Sales Price 0.666667

Total

Price = Cost/(1 - Markup on

Sales Price)

use Shift + Tab to put formula in cell and move to right

If you have more than one cell highlighted, Ctrl + Enter will populate all the

cells with the thing (formula, text or other) that you enter into the active cell

(active cell is the light colored cell).

use Shift + Enter to put formula in cell

and move up

Sales Total Sales

1

3

1

2

12

2

4

3

3

5

3

1

2

2

22

3

5

2

1

2

4

5


21/122

2

2

5

2

2

4

52

2

3

3

2

1

2

1

2

1

3

2

1

5

3

3

4

2

1

5

55

3

2

3

5

4

3

3

2

2

33

3

1

5

1

4

2

3


22/122

2

5

3

2

3

3

32

2

2

1

2

2

4

2

5

3

5

1

2

1

2

2

3

2

1

3

22

4

1

1

4

1

1

4

5

4

13

5

5

1

3

3

1

2


23/122

3

3

1

1

3

3

11

2

1

2

3

5

4

2

4

3

3

4

3

4

5

3

4

3

3

4

25

1

4

4

1

1

4

1

3

5

33

5

1

3

5

2

2

2


24/122

5

3

1

1

1

3

33

2

3

3

1

4

4

2

1

3

4

1

3

2

5

2

3

4

1

5

44

5

5

1

4

4

4

3

2

2

52

2

5

2

4

1

5

5


25/122

2

1

2

2

3

5

43

2

3

1

3

3

4

3

5

5

2

3

1

1

1

3

1

1

4

1

12

5

3

5

1

2

2

4

2

4

12

1

3

3

4

3

2

5


26/122

2

5

5

4

2

3

34

1

1

2

3

3

2

2

5

3

2

1

5

2

2

4

4

1

1

5

21

1

4

2

5

2

3

1

2

1

42

5

1

3

1

1

3

1


27/122

3

4

3

2

2

5

44

1

1

4

5

4

5

3

1

4

2

3

2

4

4

4

1

3

2

3

31

5

5

2

5

4

2

5

2

1

24

4

5

5

1

3

3

1


28/122

5

1

4

2

2

5

33

4

3

1

3

2

1

1

1

1

2

3

3

4

3

1

4

3

4

1

12

4

4

3

3

3

3

1

5

4

51

3

5

3

5

4

5

3


29/122

2

3

1

2

1

3

51

5

5

3

2

2

5

1

2

4

1

2

5

3

2

3

1

5

1

3

44

1

4

1

2

1

4

4

4

5

35

3

1

1

2

3

3

5


30/122

2

5

5

1

4

2

22

4


31/122

Anything in a cell when the first character is an equal sign.

ing in a cell or formula textbox when the first character is an equal sign and the cellxt.)

a: You are telling Excel to do calculations, look into another cell, create text

a:Type =, followed by:s (also: names and sheet references)ns

quotes (ex: For The Month Ended)

mbol: &bine information from different cells, text in quotes, or functions use the

sand: &

Example: ="For The Month Ended "&B5

ly numbers that ever go in a formula are numbers that will never change (such asmber of months in a year)

ple: {1,Sioux;2,Chin;3,Fred}into a cell: hit one of the following:

ve Cell goes down)Active Cell remains the same)ell goes left)ctive Cell goes right)

Active Cell goes up)


32/122

Arithmetic operation signs in Excel: Item1 Item2 Item3

( ) represents Parentheses 2 3 10

^ represents Exponents (powers and roots) 2 3 10

* represents Multiplication 20 3 10

/ represents Division Correct 20 10+ represents Addition Incorrect

represents Subtraction

Use Formula Evaluato

Order of Operations form Algebra class (Each one is left to right):

Formula evaluator is in th

Please Parenthesis ( )

A TRUE / FALSE formula c

Excuse Exponents ^ 2^2 = 4 or 4^(1/2) = 2

The IF function can be u

My DearMultiplication * and Division /

Aunt Sally Adding (SUM) + and Subtraction -

Excel's Order of Operations:

Parenthesis ( )

Ranges use of colon symbol ":"

Example: =SUM(A1:A4)

Evaluate intersections (spaces (labels))

(If you ever use labels for natural language formulas)

Evaluate unions (,)

Example: =SUM(A1:A4,B2:C7)Negation (-)

Example: =-2^4 16

Example: =-(2^4) -16

Converts % (1% .01)

Exponents (^)

Example: 4^(1/2) = 2

Example: 3^2 = 9

Multiplication (*) and division (/), left to right

Adding (+) and subtracting (-), left to right

Text operators (& Concatenation)Comparative symbols: =, , >=,


33/122

Question Formula

What is 2+3*10 equal to?

What is (2+3)*10 equal to?

Is 20-3 > 10 TRUE or FALSE?

If 10 > 20, then put the word 'Correct' in cell

G5, otherwise put the word 'Incorrect'

o see how Excel evaluates formula using the

Order Of Operations

e formula Ribbon, Formula Auditing group (in

003 it is on the Tools menu)

an be created in a cell by using a comparative

perator (=, >, =,


34/122

Sales Sales

40 26

37 14

35 40

26 18

16 42

28 2644 12

24 27

41 19

SUM function adds Average Function takes Mean (add/count)

Yearly Contribution $4,000.00

Assumed Annual Rate 4.00%

Years Invested 40

Future Value

FV function calculates the worth of yourinvestment in the future

The DOLLAR function takes a number, formats it as aCurrency, and converts it to text

Your Score 60.000

Your Grade

The VLOOKUP function allows you to lookup an item in the leftmost column of a table, then jump over to

another column in the same row, grab the item, and then bring it back to the cell where the formula is.

Score Grade

0 Greater Than Or Equal To 0 And Less Than 60 F60 Greater Than Or Equal To 60 And Less Than 75 D

75 Greater Than Or Equal To 75 And Less Than 80 C

80 Greater Than Or Equal To 80 And Less Than 90 B

90 Greater Than Or Equal To 90 A


35/122

businees workout

Documents