Excel Cell References 15 Examples Formulas, Conditional Formatting & Data Validation

This 50 minute video shows 15 examples of how to use Cell References:
1)Relative Cell References
2)Absolute Cell References
3)Mixed Cell References With Column Locked
4)Mixed Cell References With Row Locked
5)Formula for Commissions
6)Formula for Deduction Calculation
7)Multiplying Table for Memorization Trick
8)Financial Forecast Formula (Budget Formula) 4 examples
9)Markup On Cost and Profit Formula
10)Running Total Formula
11)Conditional Summing Formula SUMIFS and SUMPRODUCT Formulas
12)Conditional Averaging Formula AVERAGEIF and SUMPRODUCT Formulas
13)Conditional Formatting Formula for Row In Bank Reconciliation Table
14)Conditional Formatting Formula for Highlighting Duplicates
15)Data Validation Formula for Preventing Duplicates

Category:

Education

Tags:

• Excel
• Cell
• References
• Relative
• Absolute
• Mixed
• excelisfun
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• Busn
• 216
• Conditional
• Formatting
• Highlighting
• Duplicates
• Data
• Validation
• Preventing

[allchangesintime]

i love your tutorials, thanks!

[ExcelIsFun]

You are welcome!

[ecceservices]

The link you posted on the video to downlaod files doesnt work

Is there a way to get those files?

[ExcelIsFun]

The link does work when i tried it.

Try watching this video (it shows all the tricks for using excelisfun):

youtube[dot]com/user/ExcelIsFu­n

[ilhamideniz]

hi, have a question,

1.) at sheet1 on cell C5 i collect data from sheet2 cell C2, by formatting C2 as

'' =sheet2!$C$2 ''

2.) now, at sheet2 i shift Row 2 down by inserting a new row.

3.) I want in sheet1 at cell C5, still the cell content at sheet2 cell C2.

but, by shifting at 2.) is my cell format at sheet1 cell C5 is changed to

'' =sheet2!$C$3 '' ..... please can you explain me how to prevent this

have nice day,

[ExcelIsFun]

Here , I made this video for you:

Excel Magic Trick 691: Look At Cell C2 No Matter How Many Rows Or Columns You Insert Or Delete

[ExcelIsFun]

Try these (even better):

=INDIRECT("'sheet2'!C2")

or

=INDEX('sheet'!1:1048576,2,3)

[ExcelIsFun]

In cell C5, try this formula:

=OFFSET(INDEX(Sheet2!$C:$C,1),­1,)

I do not have a video on why this works, but I will make on in about a week...

[ExcelIsFun]

I am glad that you like it!! And you are correct - this is difficult topic that most people have a hard time with.