Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle 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


Play button


Play button




Click to flip

47 Cards in this Set

  • Front
  • Back

Total from multiple sheets


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


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



Totals (sum = grand total)



Create sparkline

Select cell


Choose type (Line, Column, Win/Loss)

Select data range


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


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

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:






count #s

standard deviation


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"


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



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


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.


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


Display formula Function key

F2 when in cell with formula



useful in nested formulas

example: =FIND("-",b4)

Displays character position of character included in formula within cell reference


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 -





Tells what row cell is in

example: =ROW(G4) = row #4


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













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


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



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



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


Displays OPPOSITE of logical test result



Displays OPPOSITE of logical test result



Tells Excel what to do if errors


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


Sum all data selected that meet both criteria


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


count selected cells that meet criteria


count selected cells that meet both criteria


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


Average selected cells that meet criteria


Average selected cells that meet both critera


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