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.
- 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 - Select one of the cells in the above data area, and press the Ctrl + A key to select all the cells with data.
- Click the Insert tab, then click the Table icon button in the Tables group to convert the data range to an excel table.
- Check the My table has headers checkbox in the popup Create Table dialog window.
- Click the OK button to create the table and close the dialog window.
- Then there will have a drop-down arrow in each cell (ID, Name, URL) of the header row.
- Insert a new column Domain at the end of the table, the word Domain will be added in cell D1.
- 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.
- 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 Domain 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 - 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.
- 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.
- Now the formula has been applied to all the cells in the excel column entirely.
