How to enter the same data (formula) into all selected cells at a time

In this quick tip you will learn about 2 fast ways to enter the same formula or text into several Excel cells at a time. It is very useful if you need to insert the formula to all cells in a column or fill all blank cells in a table with the same value (e.g. "N/A"). Both tricks work in Microsoft Excel 2016, 2013, 2010, 2007 and lower.

Knowing these simple techniques will save you a lot of time that you can spend on more enjoyable things.

• How to select all the cells where you want to enter the same data
  • Select the entire column
  • Select the entire row
  • Select several cells
  • Select the whole table
  • Select all cells in a worksheet
  • Select blank cells within a certain area (row, column, table)
• The fastest way to insert a formula into the entire column
• Insert the same data into multiple cells using Ctrl+Enter

Select all the cells where you want to enter the same data

Here are the quickest ways you can select cells:

Select the entire column

• If your data are in a full-fledged Excel table, just click on any cell in the column and press Ctrl+Space.
• If you have a simple range (when you click on any cell with the data, you don't see "Table Tools" tab in the top-right corner of the Excel ribbon):

  Note.
  Note: Unfortunately, simply pressing Ctrl+Space will select all the cells in the column, i.e. from C1 to C1048576, even if only cells C1-C100 contain data.

  Put the cursor to the first cell in the column (or the second one if your Table has headers), then press Shift+Ctrl+End to go to the end of your table, hold Shift and press the Left key repeatedly until only the needed column gets selected.

  This is the fastest way to select all the cells in the column, especially if the column contains several blank cells between the data.

Select the entire row

• If your data are in a full-fledged Excel table, just click on any cell in the row and press Shift+Space.
• If you have a simple range, click on the last cell in the row, then press Ctrl + Home.

Select several cells

Hold Ctrl and left-click on all cells that you want to fill with data.

Select the whole table

Click on any cell in your table and then press Ctrl+A.

Select all cells in a worksheet

Press Ctrl+A, then Ctrl+A again.

Select blank cells within a certain area (row, column, table)

Select the area you need (see below), e.g. the whole column.

Press F5 to bring up the "Go To" dialog and click on the Special… button in that dialog.

Select the "Blanks" radio button in the "Go To special" window and click OK.

You will get back to Excel and see that only blank cells in the selected area are highlighted. That's right; it is faster to select 3 blank cells by clicking on them with your mouse cursor. But what if you have more than 300 blanks randomly distributed between 10000 cells :)?

The fastest way to insert a formula into the entire column

You have a large table and you want to add a new column with some formula. For example, you get a list of links (new backlinks to www.ablebits.com :) ) and you want to extract the domain names from these links for further work.

1. Convert your range to an Excel table. Select any cell within your data range and press Ctrl+T to bring the "Create Table" dialog (the shortcut is Ctrl+L in Excel 2003). If your data have column titles, make sure the checkbox "My table has headers" is selected. Usually Excel recognizes your table headers automatically, if not, check this checkbox manually.
2. Insert a new column into your table. It is a lot easier to add a new column to a table than to a simple data range. Just click on any cell in the column next to where you want to add a new one and choose Insert > Table Column to the Right ( or "Table Column to the Left").
3. Name the newly added column.
4. Enter your formula in the first cell of the new column. I'll use the following formula for extracting domain names in my example: =MID(c2,FIND(":",c2,"4")+3,FIND("/",c2,9)-FIND(":",c2,"4")-3)
5. Press Enter. Voila! Excel automatically fills all blank cells down your column with the same formula

   If you want to switch back from a table to a simple range for some reason (I don't know any :) ), select any cell in your table, then press the "Convert to range" button on the Design tab.

You can apply this tip only if all cells in the column are blank, so the best way is to add new columns. The next tip is universal.

Insert the same data into multiple cells using Ctrl+Enter

Select the cells that you want to fill with the same data in your Excel worksheet. Please see the tips above for quick data selection.

Say, we have a table with a list of our customers (this is a fake list, of course :) ). There is a column listing the websites from which our customers come. We want to fill the blank cells with "_unknown_" to make filtering easier in the future:

1. Select all the blank cells in a column.
2. Press F2 to edit the last selected cell and type some data: it can be text, a number, or a formula (e.g. "_unknown_")
3. Press Ctrl+Enter instead of Enter. All the selected cells will be filled with the data that you typed.

If you know more tricks that speed up data input, please share them in the comments. I'll be happy to add them with your authorship to this article.

You may also be interested in

• How to copy formula in Excel: down a column, without changing references, etc.
• Basic Excel formulas & functions with examples
• How to insert dates in Excel - add today's date, auto fill a column with dates