Contents
In this article, you will learn the 2 fastest ways to insert the same formula or text into multiple cells at once in Excel. This is useful in situations where you want to insert a formula into all cells in a column, or fill all empty cells with the same value (for example, “N/A”). Both techniques work in Microsoft Excel 2013, 2010, 2007 and earlier.
Knowing these simple tricks will save you a lot of time for more interesting activities.
Select all cells in which you want to insert the same data
Here are the fastest ways to highlight cells:
Select an entire column
- If the data in Excel is designed as a full table, just click on any cell of the desired column and click Ctrl+Space.
Note: When you select any cell in a full table, a group of tabs appears on the Menu Ribbon Work with tables (Table Tools).
- If this is a normal range, i.e. when one of the cells of this range is selected, a group of tabs Work with tables (Table Tools) does not appear, do the following:
Note: Unfortunately, in the case of a simple range, pressing Ctrl+Space will select all the cells of a column in the sheet, e.g. from C1 to C1048576, even if data is contained only in cells C1: C100.
Select the first cell of the column (or the second, if the first cell is occupied by a heading), then press Shift+Ctrl+Endto select all table cells up to the far right. Next, holding Shift, press the key several times left arrowuntil only the desired column remains selected.
This is the fastest way to select all cells in a column, especially when the data is interleaved with blank cells.
Select an entire line
- If the data in Excel is designed as a full-fledged table, just click on any cell of the desired row and click Shift+Space.
- If you have a regular data range in front of you, click the last cell of the desired row and click Shift+Home. Excel will select a range starting from the cell you specified and up to the column А. If the desired data starts, for example, with a column B or C, pinch Shift and press the key Right arrowuntil you get the desired result.
Selecting multiple cells
Hold Ctrl and click the left mouse button on all the cells that need to be filled with data.
Select the entire table
Click on any cell in the table and press Ctrl + A.
Select all cells on a sheet
Press Ctrl + A one to three times. First press Ctrl + A highlights the current area. The second click, in addition to the current area, selects rows with headers and totals (for example, in full-fledged tables). A third press selects the entire sheet. I think you guessed it, in some situations it will take you only one click to select the entire sheet, and in some situations it will take as many as three clicks.
Select empty cells in a given area (in a row, in a column, in a table)
Select the desired area (see the figure below), for example, an entire column.
Press F5 and in the dialog that appears The transition (Go to) press the button Highlight (Special).
In the dialog box Select a group of cells (Go To special) check the box Empty cells (Blanks) and knead OK.
You will return to the edit mode of the Excel sheet and you will see that only empty cells are selected in the selected area. Three empty cells are much easier to select with a simple mouse click – you will say and you will be right. But what if there are more than 300 empty cells and they are scattered randomly over a range of 10000 cells?
Fastest way to insert formula into all cells of a column
There is a large table, and you need to add a new column with some formula to it. Suppose this is a list of Internet addresses from which you want to extract domain names for further work.
- Convert the range to an Excel table. To do this, select any cell in the data range and press Ctrl + Tto bring up a dialog box Creating a table (Create Table). If the data has column headings, check the box Table with headers (My Table has headers). Usually Excel recognizes the headings automatically, if that doesn’t work, check the box manually.
- Add a new column to the table. With a table, this operation is much easier than with a simple range of data. Right-click on any cell in the column that comes after where you want to insert the new column, and from the context menu choose Insert > Column on the left (Insert > Table Column to the Left).
- Give a name to the new column.
- Enter the formula in the first cell of the new column. In my example, I use the formula to extract domain names:
=MID(C2,FIND(":",C2,"4")+3,FIND("/",C2,9)-FIND(":",C2,"4")-3)=ПСТР(C2;НАЙТИ(":";C2;"4")+3;НАЙТИ("/";C2;9)-НАЙТИ(":";C2;"4")-3) - Press Enter. Voila! Excel automatically filled in all the empty cells in the new column with the same formula.
If you decide to return from the table to the usual range format, then select any cell in the table and on the tab Constructor (Design) click Convert to range (Convert to range).
This trick can only be used when all cells in a column are empty, so it’s best to add a new column. The next one is much more general.
Paste the same data into several cells using Ctrl + Enter
Select the cells on the Excel sheet that you want to fill with the same data. The techniques described above will help you quickly select cells.
Suppose we have a table with a list of customers (we will, of course, take a fictional list). One of the columns of this table contains the sites from which our clients came. Empty cells in this column must be filled with the text “_unknown_” to facilitate further sorting:
- Select all blank cells in the column.
- Press F2to edit the active cell, and enter something into it: it can be text, a number, or a formula. In our case, this is the text “_unknown_”.
- Now instead of Enter click Ctrl + Enter. All selected cells will be filled with the entered data.
If you know other quick data entry techniques, tell us about them in the comments. I will gladly add them to this article, citing you as the author.