IF Function: Basic Example (TRUE/FALSE)

Function
=IF(logical_test, value_if_true, value_if_false)

3 Parameters

logical_test = Any value or expression that can be evaluated to TRUE or FALSE. (e.g. cell A1>10)
value_if_true = The value (or formula) that you want to be returned (or calculated) if the logical_test argument evaluates to TRUE.
value_if_false = The value (or formula) that you want to be returned (or calculated) if the logical_test argument evaluates to FALSE.

Conditional Formatting (Automated!!)

PIVOT TABLES: The Basics

More Keyboard, Less Mouse!

From YouTube: a great video tutorial by Mike “excelisfun” Girvin going over the keyboard shortcuts.

Okay, maybe it’s close to impossible to completely rid of the mouse, but I did notice that the keyboard shortcuts are very underused. Truth is that most don’t even know about these keyboard shortcut. Here are some keyboard shortcuts that can dramatically speed up your productivity,

The Shortcuts

Ctrl + C = Copy
Ctrl + V = Paste
Ctrl + X = Cut
Ctrl + F2 = Print Preview
Excel 2003 Alt + F + U = Page Setup
Excel 2007 Alt P + S + P = Page Setup
Ctrl + Arrows keys = Move to end of current region
Ctrl + Home = select cell A1
Ctrl + Roller-ball-on-mouse = Zoom
Atl + “=” = is the keyboard shortcut for Auto SUM
Shift + Click selects range between to cells
Ctrl + Shift + arrow = Select whole current region (This trick is the best Excl keyboard shortcut)

Master the Arrow Keys!!

Ctrl + ARROW KEY

Ctrl + SHIFT + ARROW KEY

The Tip: implementing the Ctrl and Shft key in your artillery of keyboard shortcut can be one of the most useful and important ones to speed up productivity of your excel project.

The Trick: there are three different ways to utilize the arrow keys in Excel

Arrow Keys (only): one key stroke moves the cursor in the direction of the Arrow Key.

Holding Ctrl + Arrow Keys: one key stroke on the arrow key while holding the “Ctrl” key will move the cursor to the last “non-blank” cell right before the next “blank” cell.

VLOOKUP: Get a value from a different report!

Function
=VLOOKUP(lookup_value,table_array,col_index_num)

3 Parameters
lookup_value = the value to look up (e.g. reference number/text)
table_array = the source table to lookup
col_index_num = column number where the information is in

CAVEATS

Lookup Value & Table Array
The 1st column MUST be the column where the lookup value is. E.g.) If your lookup value is a site number, the 1st column of the Table Array MUST be a site number column
The format of the Lookup Value and the 1st column of the Table Array MUST be the same. Note: some numbers may be stored as text if the VLOOKUP function fails (“#N/A”)

VLOOKUP is a function that’s useful to get data from another report (or worksheet) into another. For instance, you may have a list of sites by their site numbers, but it does not have a country column that you want. In this case, you can use the VLOOKUP function to pull the country information from a different report (worksheet) to the report that you want.

Index & Match Functions – A More Flexible VLOOKUP!!

MATCH FUNCTION

INDEX FUNCTION

MATCH FUNCTION

Function: a lookup function that returns the relative position of an item in a list

=MATCH(lookup_value,lookup_array,match_type)

Parameters: 3

lookup_value = the value you use to find the value you want in a table.
lookup_array = contiguous range of cells containing possible lookup values.
match_type = tells index which row to look at.

0 = Exact Match

1 = Approximate Match (less than the lookup_value)

-1 = Approximate Match (greater than the lookup_value)

INDEX FUNCTION

Function: lookup function that can do a two-way lookup.

=INDEX(array,row_num,column_num)