Excel Apply Formula To Entire Column Example

In this article, I will tell you how to apply an Excel formula to the entire column quickly. That means you input the formula in one cell of the column, then it can be applied to all the other cells in the column.

1. How To Apply Excel Formula To Entire Column Example.

  1. Input the below example data in one Excel worksheet.
       A       B        C
    1  ID     Name     URL
    2   1     Apple    http://www.apple.com/index.html
    3   2     Google   http://www.google.com/index.html
    4   3     Amazon   http://www.amazon.com/index.html
  2. Select one of the cells in the above data area, and press the Ctrl + A key to select all the cells with data.
  3. Click the Insert tab, then click the Table icon button in the Tables group to convert the data range to an Excel table.
  4. Check the My table has headers checkbox in the popup Create Table dialog window.
  5. Click the OK button to create the table and close the dialog window.
  6. Then there will be a drop-down arrow in each cell (ID, Name, URL) of the header row.
  7. Insert a new column Domain at the end of the table, the word Domain will be added in cell D1.
  8. Select the cell D2, input the formula =MID(C2,FIND(“:”,C2,”4″)+3,FIND(“/”,C2,9)-FIND(“:”,C2,”4″)-3) in cell D2‘s formula bar, or you can input the formula in cell D2 directly.
  9. Press the Enter key, then you can find the domain data of each URL has been parsed out and added in column D as below.
       A       B        C                                    D
    1  ID     Name     URL
    2   1     Apple    http://www.apple.com/index.html    www.apple.com
    3   2     Google   http://www.google.com/index.html   www.google.com
    4   3     Amazon   http://www.amazon.com/index.html   www.amazon.com
  10. When you select cell D3, you can find the formula =MID(C3,FIND(“:”,C3,”4″)+3,FIND(“/”,C3,9)-FIND(“:”,C3,”4″)-3) in it.
  11. When you select cell D4, you can find the formula =MID(C4,FIND(“:”,C4,”4″)+3,FIND(“/”,C4,9)-FIND(“:”,C4,”4″)-3) in it.
  12. Now the formula has been applied to all the cells in the Excel column entirely.

Leave a Comment

Your email address will not be published. Required fields are marked *

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