Excel Shortcuts and Formulas

flashcards to help remember basic keyboard shortcuts and functions for Microsoft Excel

CTRL+SHIFT+4

format a number as currency

CTRL+SHIFT+5

format a number as a percentage

CTRL+SHIFT+1

format a number with two decimal places

SHIFT+ARROW KEY

select multiple cells

CTRL + ARROW KEY

jump to last non-empty cell

CTRL + SHIFT + ARROW KEY

select all non-empty cells in the direction of the arrow key

=COUNT(startcell:endcell)

count all the cells that contain numbers in a specified range

=IF(condition, value_if_true, value_if_false)

formatting for an IF statement

?

replaces a single character in a text search to allow results with any character in that position

*

replaces any number of characters in text search to allow results with anything in the position that the character marks

=SUMIF(cell1:cell2, “condition”, additional_range(s))

calculates the sum of cells within the specified range that meet the condition; if an additional range is added, then the cells for this range that correspond to the cells in the first range that meet the specified condition are the ones that get summed

=SUMIFS(range1, range2, condition)

allows multiple conditions to be added to the sum function with the first range being the cells to be summed

XOR

returns true if only one of its arguments is true

ISBLANK

returns true or false for whether a cell or range of cells is blank

ISEVEN

returns true or false for whether the value of a cell is an even number

ISTEXT

returns true or false for whether the value of a cell is text data

ISNUMBER

returns true or false for whether the contents of a cell is numerical data

IFS

allows testing for multiple criteria and returns the value associated with the first condition to register as true, similar to an if-elseif loop

SWITCH(CELL, VALUE1A, VALUE1B, VALUE2A, VALUE2B, … DEFAULT)

searches a list of A values for the one contained in a specified cell and returns the corresponding B value, or the default value if the cell’s value is not in the list

SHIFT+SPACE

selects an entire row

CTRL+SPACE

selects an entire column

CTRL+A

creates a selection marquee rectangle around all contiguous filled cells

CTRL+SHIFT++

inserts a column to the left of a selected column or a row above a selected row

CTRL+-

deletes a selected column or row

CTRL+ARROW KEY

jumps to last occupied cell in the specified direction

VLOOKUP(match_value, search:area, # of columns to right, 0/1 (false/true for approximate matching)

search a vertical list and find a corresponding value in another column

=HLOOKUP(“search value”, range, # of rows from position, t/f)

function to search a horizontal list and return the value from a corresponding row

=XLOOKUP(“search value”, lookup range, return range)

a lookup function that allows searches in columns or rows on either side of the lookup value

=LEN(cell)

returns number of characters in a cell

=LEFT(cell, #)

returns a specified number of leftmost characters in a cell

=RIGHT(cell, #)

returns a specified number of rightmost characters in a cell

=MID(cell, position, #)

returns a specified number of characters starting at a specified position within a cell

=FIND(“string”, cell, position (opt))

returns the first position in a string at which the specified substring begins, with the option of specifying a position within the larger string at which to begin searching. Is case-sensitive and does not accept wildcard arguments.

=SEARCH(“string”, cell, position)

returns the first position in a string at which the specified substring begins, with the option of specifying a position within the larger string at which to begin searching. Is not case-sensitive and accepts wildcard characters.

ALT+H

shortcut to “Home” tab

ALT+N

shortcut to Insert tab

ALT+P

shortcut to Page Layout tab

ALT+M

shortcut to Formulas tab

ALT+A

shortcut to Data tab

ALT+R

shortcut to Review tab

ALT+W

shortcut to View tab

ALT+A+T

shortcut to filter a list

ALT+A+S+A

sorts a list in ascending order

ALT+A+M

removes duplicates from a list

=LARGE()

returns the nth largest number from a list where n is the second argument

=SMALL()

returns the nth smallest value from a list where n is the second argument

=FLOOR()

rounds a number (first argument) towards zero (down for positive, up for negative) to the nearest multiple of the second argument

=CEILING()

rounds a number (first argument) away from zero (up for positive, down for negative) to the nearest multiple of the second argument

=DATE()

takes three number arguments and returns them in date format

=DATEDIF()

returns the difference between two dates in days, months, or years, depending on the final argument

=DAYS()

returns the number of days between two dates