Excel Apply Custom Function on a Large Range

Here is a quick tip on how to select a large set of cells in excel and apply a custom function on that selection.

There are multiple ways of applying a formula to multiple cells in a excel sheet: If the formula needs to be applied to consecutive cells in a row or column, we can either

  • type the formula in one cell double click on the right bottom corner and drag to all the cells to apply the formula. Or
  • first select the range of cells, type the formula and hold down ctrl while pressing enter to apply the formula to whole selection. Or
  • type the formula in a cell, copy the cell,  select the range of cells where it needs to be applied, and use paste special to apply the formula.

If the range is small, it is easier to select the range, but it becomes trickier when the range is big, say 75K cells. At least it was trickier to me until I came across this short cut 🙂

  • You might be already aware that F5 is used to goto a specific cell in excel. Same  F5 can be used to select a large range of cells as well and here is step by step procedure.
  • Go to the start of the range, For e.g., C2 .  (Control+Home takes you to top of the sheet, if you want.)
  • Apply the formula in single cell and copy the cell.
  • Press F5 and enter the end of the range,  say C75000 and hold down control key while pressing enter.
  • With this, whole range (C2-C75000 in the example) will get selected.  Now right click and ‘paste Special’ to apply the formula to the selection.

Leave a Reply

Required fields are marked *.

This site uses Akismet to reduce spam. Learn how your comment data is processed.