Excel 2013 Session 3

Front 1

Total from multiple sheets

Back 1

=sum(

then click in cells to add up

hit Enter

If many worksheets type =sum(

then click first cell in 1st worksheet to add to formula

then SHIFT and click last worksheet tab then Enter

Front 2

Select entire worksheet

Back 2

CTRL + A + A if active cell is w/in range

CTRL + A if active cell is not in a range

Front 3

Select range

Back 3

CTRL + A when active cell is w/in range

Front 4

syntax for workbook name when working with multiple workbooks

Back 4

[example]

Front 5

syntax for named worksheet if includes spaces

Back 5

single quotes `example'

Front 6

syntax for cell reference working with multiple worksheets

Back 6

! exclamation point

example: =sum(Sheet3!a3,Sheet5!g7)

Front 7

Quick Analysis Tool options

Back 7

Formatting

Charts

Totals (sum = grand total)

Tables

Sparklines

Front 8

Create sparkline

Back 8

Select cell

Insert

Choose type (Line, Column, Win/Loss)

Select data range

Enter

Front 9

Sparkline Tool Design tab options

Back 9

Edit data (change how hidden/empty cells are treated)

Change type (Line, Column, Win/Loss)

Show - high point, low point, Negative points, First point, last point

Change style

Change sparkline or marker colors

Change axis, group/ungroup, clear

Front 10

Win/Loss

Back 10

When have negative and positive numbers

To add contrast

Front 11

Change result of formula into a value

Back 11

Click in cell with formula

F2 then F9

Front 12

Edit sparkline data options

Back 12

show empty cells as gaps, zeros, connect data points with lines

Show data in hidden rows/columns

Front 13

Data consolidate - definition

Back 13

when worksheets have different column/row headers (e.g. people, products, etc.). Make sure names spelled same across all sheets or they'll be treated as different data elements

can do:

totals

count

average

max

min

count #s

standard deviation

etc.

Front 14

Data consolidation - how

Back 14

Data tab - Consolidate button

Select range with mouse

Click Add

Repeat as necessary with other data ranges

Select labels - top and left

Select "create links to source data"

Enter

Front 15

Data consolidation - hierarchy outline

Back 15

1 = highest (collapsed) level

2 = expands data

3 = view entire worksheet

click + to expand and - to collapse

Front 16

Keyboard shortcut to reveal all formulas

Back 16

CTRL `

Front 17

Subtotals

Back 17

Click inside data range

Data tab -> Subtotal

Select which labeled columns to add subtotals to (eg. at end of every year)

Functions available: Sum, Count, Ave, Max, Min, Product, Count numbers, StdDev, StdDevp, Var, Varp

also creates a grand total

Front 18

Group data

Back 18

Select data range

Data tab -> ounline group -> group -> choose rows or columns

add level to outline/hierarchy that can be hidden (collapsed with -) or show (expand with +)

Front 19

copy data from subtotaled range

Back 19

Select range

Go To (CTRL + G) -> Special -> by criterion -> Visible cells only

CTRL + C to copy

Select location

CTRL + V to paste

Front 20

VLOOKUP

Back 20

Doesn't calculate - just geographic identity

v for Vertical - Hlookup for horizontal

RANGE lookup = default (#1) - only 3 arguments = ascending (A-Z) order sort

Exact Match = #2 special form - 4 arguments - no sorting necessary

=VLOOKUP(lookup value, table array, column index # of data to be returned as answer to query,range_lookup)

example: =VLOOKUP(K2,F2:H10,3)

Range lookup optional - default is TRUE -

TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value.

FALSE searches for the exact value in the first column.

Front 21

VLOOKUP Type 2

Back 21

exact match - 4 arguments

=VLOOKUP(lookup_value, table_name or data range, column_index_number,FALSE)

FALSE because override default "approximate" lookup (TRUE)

Front 22

Nested Formulas

Back 22

Up to 64

order = deeply nested 1st then work out to left

order indicated by color (1st is black)

pairs indicated by parantheses

ABS

FIND

LEFT

ROW

COLUMN

Front 23

ABS formula

Back 23

=ABS(cell) - removes sign from number

ex: $,-,%, etc.

example: =abs(sum(data range)) Enter

make sure to include correct number of closing parentheses

If get error- pop up window shows suggested formula format

Front 24

Autosum speed key / shortcut

Back 24

ALT =

Front 25

Display formula Function key

Back 25

F2 when in cell with formula

Front 26

=FIND

Back 26

=FIND(

useful in nested formulas

example: =FIND("-",b4)

Displays character position of character included in formula within cell reference

Front 27

=LEFT

Back 27

Displays/extracts all characters to left of column index # included in formula

=LEFT(E2,3) = fixed position

NESTED example for non-fixed position: =LEFT(E2,FIND("-",E2)) - extracts all characters to left of dash -

aaa-

aa-

a-

Front 28

=ROW

Back 28

Tells what row cell is in

example: =ROW(G4) = row #4

Front 29

=COLUMN

Back 29

Provides column index # for cell

example: =COLUMN(E3) = column #5

Front 30

R1C1 cell reference style

Back 30

uses row index # and column index # to reference cells

example: A10 becomes R10C1 (row 10, column 1)

can be useful when using more than 26 columns - such as cell AX1 = column AX, row 1 = in RICI format it's R1C50

To change to RICI format, FILE tab -> Options -> Formulas -> RICI

Front 31

Conditional Logic

Back 31

=IF

=AND

=OR

=NOT

=IFERROR

=SUMIF

=COUNTIF

=AVERAGEIF

=SUMIFS

=COUNTIFS

=AVERAGEIFS

Front 32

=IF

Back 32

means "if true, then X, otherwise Y"

=IF(A1=-100,"HURRAY!","NO GOOD")

so, if statement true- display second argument, if not true display third argument

=IF(A2<>-200,0,"") so if data in cell A2 is not equal to -200, display a zero, otherwise display a blank cell

Can display text string, blank cell, math operation, or formula

=IF(A2<>-200,0,ABS(SUM(H2:H5)))

so if data in A2 is not equal to -200, display 0, otherwise display Absolute Value of sum total of range H2:H5

Often nested with VLOOKUP formulas - test X and if true look up Y or if false look up Z

Front 33

Nested IF

Back 33

test 2 statements

=IF(A3<>-300,"xxx",if(A3=-300,"A true answer",0))

so if A3=-300 display "A true answer", or if not equal to -300 display xxx.

Front 34

Compatibility Mode

Back 34

older files before Office 2007 - some stuff won't display

look in file title to see if opened in compatibility mode

Some Excel 2013 features won't work- features will be greyed out, e.g. Flash Fill

Convert = FILE tab -> Info -> Convert -> rename or replace

Front 35

Can't widen columns

Back 35

Turn wrap text off

Front 36

Formula wizard

Back 36

CTRL + A once formula begun

OR use FX button in formula bar to select from list of recently used or via categories

Front 37

=AND

Back 37

=AND(A2>20000,A2>30000)

BOTH logical tests MUST BE TRUE to display TRUE - otherwise FALSE

Front 38

=OR

Back 38

=OR(A2>20000,A2<30000)

Only one logical test has to be TRUE to result in TRUE - so only FALSE is BOTH FALSE

Front 39

=NOT

Back 39

EXCLUDES

Displays OPPOSITE of logical test result

=NOT(A2>100000)

Front 40

=IFERROR

Back 40

Tells Excel what to do if errors

=5/0

if lots of errors- allows to display something other than #DIV! etc

=IFERROR(A1/B2, 0) = so divide A1 by B2 but if produces error then display alternative value

Front 41

=SUMIF

Back 41

=SUMIF(range, criteria) So sum all data selected that meet certain criteria

example: =SUMIF(A2:A12,"<20") means sum data in range A2 thru A12 that is less than 20

Front 42

=SUMIFS

Back 42

Sum all data selected that meet both criteria

=SUMIFS(B2:E2,B4:E4,">=75",B3:E3,"<40%")

for the four days, add the total hours of sunshine (range B2:E2) WHEN the average temperature (range B4:E4) was at least 75 degrees Fahrenheit and the average humidity (range B3:E3) was less than 40%.

use CTRL + A to get formula wizard

Front 43

=COUNTIF

Back 43

count selected cells that meet criteria

Front 44

=COUNTIFS

Back 44

count selected cells that meet both criteria

=COUNTIFS(B4:E4,">=75",B3:E3,"<40%")

For the four days count only those days when the average temperature (B4:E4) was at least 75 degrees Fahrenheit and the average humidity (B3:E3) was less than 40%.

Front 45

=AVERAGEIF

Back 45

Average selected cells that meet criteria

Front 46

=AVERAGEIFS

Back 46

Average selected cells that meet both critera

=AVERAGEIFS(B2:E2,B4:E4,">=75",B3:E3,"<40%")

For the four days calculate the average hours of sunshine (B2:E2) when the average temperature (B4:E4) was at least 75 degrees Fahrenheit and the average humidity (B3:E3) was less than 40%.

Front 47

Wildcard symbol

Back 47

* - replace characters to right of text string