Study your flashcards anywhere!
Download the official Cram app for free >
 Shuffle
Toggle OnToggle Off
 Alphabetize
Toggle OnToggle Off
 Front First
Toggle OnToggle Off
 Both Sides
Toggle OnToggle Off
 Read
Toggle OnToggle Off
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
47 Cards in this Set
 Front
 Back
Total from multiple sheets 
=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 

Select entire worksheet 
CTRL + A + A if active cell is w/in range CTRL + A if active cell is not in a range 

Select range 
CTRL + A when active cell is w/in range 

syntax for workbook name when working with multiple workbooks 
[example] 

syntax for named worksheet if includes spaces 
single quotes `example' 

syntax for cell reference working with multiple worksheets 
! exclamation point example: =sum(Sheet3!a3,Sheet5!g7) 

Quick Analysis Tool options 
Formatting Charts Totals (sum = grand total) Tables Sparklines 

Create sparkline 
Select cell Insert Choose type (Line, Column, Win/Loss) Select data range Enter 

Sparkline Tool Design tab options 
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 

Win/Loss 
When have negative and positive numbers To add contrast 

Change result of formula into a value 
Click in cell with formula F2 then F9 

Edit sparkline data options 
show empty cells as gaps, zeros, connect data points with lines Show data in hidden rows/columns 

Data consolidate  definition 
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. 

Data consolidation  how 
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 

Data consolidation  hierarchy outline 
1 = highest (collapsed) level 2 = expands data 3 = view entire worksheet click + to expand and  to collapse 

Keyboard shortcut to reveal all formulas 
CTRL ` 

Subtotals 
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 

Group data 
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 +) 

copy data from subtotaled range 
Select range Go To (CTRL + G) > Special > by criterion > Visible cells only CTRL + C to copy Select location CTRL + V to paste 

VLOOKUP 
Doesn't calculate  just geographic identity v for Vertical  Hlookup for horizontal RANGE lookup = default (#1)  only 3 arguments = ascending (AZ) 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. 

VLOOKUP Type 2 
exact match  4 arguments =VLOOKUP(lookup_value, table_name or data range, column_index_number,FALSE) FALSE because override default "approximate" lookup (TRUE) 

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

ABS formula 
=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 

Autosum speed key / shortcut 
ALT = 

Display formula Function key 
F2 when in cell with formula 

=FIND 
=FIND( useful in nested formulas example: =FIND("",b4) Displays character position of character included in formula within cell reference 

=LEFT 
Displays/extracts all characters to left of column index # included in formula =LEFT(E2,3) = fixed position NESTED example for nonfixed position: =LEFT(E2,FIND("",E2))  extracts all characters to left of dash  aaa aa a 

=ROW 
Tells what row cell is in example: =ROW(G4) = row #4 

=COLUMN 
Provides column index # for cell example: =COLUMN(E3) = column #5 

R1C1 cell reference style 
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 

Conditional Logic 
=IF =AND =OR =NOT =IFERROR =SUMIF =COUNTIF =AVERAGEIF =SUMIFS =COUNTIFS =AVERAGEIFS 

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

Nested IF 
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. 

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

Can't widen columns 
Turn wrap text off 

Formula wizard 
CTRL + A once formula begun OR use FX button in formula bar to select from list of recently used or via categories 

=AND 
=AND(A2>20000,A2>30000) BOTH logical tests MUST BE TRUE to display TRUE  otherwise FALSE 

=OR 
=OR(A2>20000,A2<30000) Only one logical test has to be TRUE to result in TRUE  so only FALSE is BOTH FALSE 

=NOT 
EXCLUDES Displays OPPOSITE of logical test result =NOT(A2>100000) 

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

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

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

=COUNTIF 
count selected cells that meet criteria 

=COUNTIFS 
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%. 

=AVERAGEIF 
Average selected cells that meet criteria 

=AVERAGEIFS 
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%. 

Wildcard symbol 
*  replace characters to right of text string 