There is nothing more frustrating in MS Excel and Google Spreadsheets than their inability to recognize a pattern in referenced cells.
For example, if you type 1, 3, 5 and 7 in four cells and then drag them down, Excel knows that the next in series is 9, 11, and 13 etc.
However, if you type =B2, =B4, =B6 and =B8 in four cells and then drag them down, Excel does not know that the next in line should be =B10, =B12, =B14 and so on.
Even Google Spreadsheet doesn’t do this and I don’t know if this is a feature or a bug but I’m leaning towards a bug, as I’ve felt the need for this many many times, and although there are workarounds to do this I haven’t seen any really easy ways to do it until recently.
After breaking my head on the Offset function for a few hours today I started searching for a new solution to do this and stumbled upon this neat find and replace trick to skip rows while using them in a formula.
What you do is instead of typing =B2, =B4, =B6 and =B8 in four cells and then dragging them down, type something like $B2, $B4, $B6 and then drag it down. Excel will correctly populate the series for you now.
After that all you have to do is select your cells and find and replace the dollar sign with the equal sign to convert the cells into a formula and voila — you have the correctly referenced cells.
This is one of the most useful Excel tricks that I’ve ever come across, and I’m really amazed at the simplicity and the effectiveness of this. Anyone who has tried to do this using round about ways will realize how much time this will save, and how useful this is becase there are just countless times that you need to skip rows while using them in an Excel or a Google Spreadsheet formula.