Quick-Tip: Easy entering

Imagine you have a table with 200 rows of data. You don't have one? Lucky as you are, I haveprepared one for you

Download it here: Easy Entering (ZIP, 5kB)

We have a list with 200 account numbers there on our sheet and we want to extract the the two digit regional code with the LEFT function (if you want to learn more about the LEFT function in Excel, take a look at this article).

Instead of entering the formula into every cell or copy it and paste it down, there is an easier approach. Mark all relevant cells (in this case the yellow area) and make sure that B2 is your active cell.

Now put your cursor into the formula bar and enter the following formula (DO NOT press Enter after that!):

=LEFT(A2,2)

This will extract the first to characters starting from the left with the first character. Now instead of hitting the Enter key on your keyboard hold down the CTRL-key and press enter. Excel automatically copies the formula with its adjusted cell references into all selected cells.

Okay, I must admit for this very simple table, this approach is not really faster than copy paste or clicking the little black square when cell B2 is selected, but imagine using a complex formula with matrix functions where you have to extract values from a table with several columns and rows as we did with the article on dynamic charts. Then this approach is a very handy time-saver.

Discuss this blog post in the forums (click the blue button)