Tip #1

Multi-Condition Counting and/or Summing

Want to count or sum based on two or more conditions?  Hint: SUMIF/COUNTIF won't work...

Tip #2

Creating Dynamic Ranges

Tired of editing the ranges in your formulas when your data grows? Find out how to create a flexible dynamic range.

·   More details...Multi-Condition Counting and/or Summing

·  More details...Creating Dynamic Ranges

Tip #3

Unique Value Counting

Want to count without counting duplicates? Learn how with this great tip.

Tip #4

Multi-Key or Multi-condition VLOOKUP

Trying to lookup a value from values in two different columns?

·  More details...Unique Value Counting

·  More details...Multi-Key or Multi-condition VLOOKUP

Tip #5

Highlight Duplicates in a List

Want to highlight your duplicates?  Learn how to add a little color to flag duplicates.

Tip #6

Lookup with more than one result

Do you need to lookup data with more than one result?  With a little help you can do just that.

·  More details...Highlight Duplicates in a List

·  More details...Lookup with more than one result

Tip #7

Ordinal Numbers

1st, 2nd, 3rd...111th?  Learn how to create ordinal numbers with a formula.

Tip #8

Last Name First, First Name Last

Want to sort by last name but your data is "first-name-first and "last-name-last"?  Check out this neat tip.

·  More details...Ordinal Numbers

·  More details...Last Name First, First Name Last

Tip #9

Unique Validation List

Do you want to create a validated list where if the item in the list has already been used then it won't show in the list?  Learn how here...

Tip #10

Too many Nested IF's

Something that goes unnoticed when trying to figure out how to fit more than seven IF statements into one formula is that most of the time you can use VLOOKUP or some other LOOKUP formula instead.  VLOOKUP can LOOKUP exact matches or LOOKUP values in a range as in greater than 10 but less than 20.  See if it will work for you.

·  More details...Unique Validation List

Tip #11

VLOOKUP and HLOOKUP have some limitations that can hinder your spreadsheet design (have you ever had to move columns so that you can do a VLOOKUP?)  Here's not only a workaround but a useful, dynamic, and robust combination of functions to VLOOKUPs, HLOOKUPs, and all kinds of advanced lookups.

Tip #12

Sum every Other Row and/or Column

VLOOKUP and HLOOKUP have some limitations that can hinder your spreadsheet design (have you ever had to move columns so that you can do a VLOOKUP?)  Here's not only a workaround but a useful, dynamic, and robust combination of functions to VLOOKUPs, HLOOKUPs, and all kinds of advanced lookups.

·  More details...Alternative to VLOOKUP/HLOOKUP

·  More details...Sum every Other Row and/or Column

Tip #13

 Advanced Formula Cheat Sheet

Download this Word document with tips on how to create some of the more common advanced formulas. (In .zip format)

Tip #14

·  More details...Download