Mixed cell references in Excel is an important type of cell references. By default we can use relative cell address or reference with in a formula. Mixed cell references is combination of relative and absolution cell references. Using mixed cell reference either your column label absolute and row number is relative or row number is absolute while column label is relative. You can easily switch between relative to absolute or mixed cell reference by manually or using F4 function key.
Must Read: How to Identifying Duplicate Values in two Excel worksheets
During working on the mixed cell references you have to put dollar ($) sign either on column label or row number. Where you put dollar sing that will be converted in to absolute while another part is relative. For eg: If you have a relative cell reference B5, now when you convert in to mixed cell reference you have to put dollar sign. $B5 it means B column is absolute while row number 5 is relative. If you have B$5 that means your column B is relative while row number 5 is absolute.
Sometime during working on excel sheet you must have to fix any particular row or column during applying any formula in Excel sheet. When you fix any row or column using mixed cell references you can easily copy single or range of cells formula into another location. After pasting the formula at another location your relative reference is adjusted while absolute reference don’t change.
Beauty of Mixed Cell references in Excel
Step 1: Prepare the following given worksheet to calculate the incentive amount.
Step 2: Select cell G2 in which you want to enter the formula. Start typing formula with equal sign (=) operator in a formula. For eg. =F2*H1. Now when you copy the formula to watching the result for rest of the cell, you will get 0 value. Because G2 is changed during copying the formula.
Must Read: VLookup to find first few characters in Excel
Step 3: Now if you want to fix row number of percentage then you can do this job either typing the $ (dollar) sign before the row number. You can also use F4 function key to quickly change the reference type of any formula. Now you can check your formula has been changed and it looks like this =F2*H$1.
Now, press the enter key to view the result and copy the formula for rest of the cells.
I hope you can understand importance of mixed cell references in Excel. With the help of mixed cell reference you can easily fix either row number or column label while second part will be relative. If you have any suggestion regarding this guide then please write us in the comment box. Thanks to all.