Common Excel Functions, Tips, and HotKeys

Common Functions for Microsoft Excel:

MATH FUNCTIONS:

=SUM( Number 1,Number 2,…Number N)

Computes the sum of a group of numbers

=COUNT(Value1, …Value N)

Counts the number of cells that contain a number

=MAX(Value 1,…Value N)

Calculates the maximum value out of a set of numbers

=MIN(Value 1,…Value N)

Calculates the minimum value out of a set of numbers

=AVERAGE(Value 1,…Value N)

Returns the (arithmetic) mean of a set of numbers.

=MEDIAN(Value 1,…Value N)

Returns the median of a set of numbers

=MODE(Value 1,…Value N)

Returns the mode (most frequently occurring number) of a set of numbers

=ABS(Value)Returns the absolute value of a given number

=ROUND(Value, # of decimal places)

Rounds a given number to the nearest specified decimal place;  Round(Number, 0) will round to the nearest integer.

=ROUNDUP(Value, # of decimal places)

Rounds away from zero to the next specified decimal place; Negative numbers round lower and positive numbers round higher.

=ROUNDDOWN(Value, # of decimal places)

Rounds towards zero to the next specified decimal place

=EXP(VALUE)

Returns e raised to the power of a given number

=LN or LOG or LOG10(Value, Base)

Returns the natural log, log base 10, or log with a specified base

=STDEV(Value 1,…ValueN)

Returns the standard deviation of a set of numbers

=SQRT(value)

Returns the square root of a given value

=COUNTIF(range, criteria)

Counts the number of cells within a given range that meet the specified criteria

STRING FUNCTIONS:

=CONCATENATE(Value1,…Value N)

Combines strings together into one string

=COUNTIF(range, criteria)

Counts the number of cells within a given range that meet the specified criteria

=LEFT(text, # of characters)

Returns the leftmost X number of characters in a string

=RIGHT(text, # of characters)

Returns the rightmost X number of characters in a string

=VALUE(text)

Converts a number in string format to its numerical value; for instance "2"(string) can be converted to 2 (numerical). 

=UPPER(Text)

Converts a string to upper case

=LOWER(Text)

Converts a string to lower case

LOGICAL FUNCTIONS:

=IF( Logical test, Value if TRUE, value if False)

=AND

= NOT

=OR

We will discuss the logical functions more in a later lesson. If you are curious you can look at the excel help and try to figure them out on your own.

Tips for working with data:

1. When selecting continuous data, the shift [SHIFT] key can be held to highlight a series of continuous rows/columns. On the other hand, discontinuous cells can be selected by holding control [CTRL] and then selecting each section of the spreadsheet.
2. Using the end key [END] and arrow keys you can easily navigate to the extreme top, bottom, left or right of a dataset. This can be combined with the shift key to select all data from one point to the start/end of a row or column [SHIFT+END+ARROW].
3. Finding values or finding and replacing values can be accomplished by clicking [CTRL+F] and using either the find or replace tab.  This can be very useful for navigating the data.

Some other hotkeys:

CTRL+C- Copy

CTRL+X- Cut

CTRL+V Paste

CTRL+ALT+V- Opens paste special dialogue

CTRL+D- Fills a formula down

CTRL+Z –undo

CTRL+Y- Redo

CTRL+F- find/replace

CTRL+P- Print

F4- repeats last command or action


Still having trouble? Excel 2007 For Dummies