Web Analytics India

Excel Tip 1: Copying cell values horizontally or vertically

- contributed by Deep Moni Hazarika

A colleague once pinged me “How do I copy a value written in a cell in all the cell below it?” Well, almost anyone familiar with basic excel usage might have figured that out. Anyhow, I wrote back in the chat window explaining how to do it. Didn’t work! I called up the number at his desk. Tried explaining how to do it…. “double click on the black dot at the lower right corner of the selected cell. If there are values to the adjacent column, that value will get duplicated in the column till there are values in the adjacent column”. These things I am sure most of us had figured out on our own. No, don’t blame my colleague to be stupid. Unexpectedly, speaking over the phone also didn’t help him. Then I walked up to his desk. Wait a minute. Where’s the black dot at the lower right end of the selected cell. That explains why it didn’t work through chat or even explaining over the phone. Well, I couldn’t address that problem. There has to be some setting for that I guess.

Long time back, while working on Perl programming, I had learnt “There is more than one way to do it” or TIMTOWDI. Python programming however rejects that saying “There should be one obvious way to do it right”. Well, in this case I didn’t want to stick to the obvious way. The things needed to get done, whichever way!

I went ahead showing the other way to do it. Just select the cell with the value you want duplicated. If you want the value to be same in 10 subsequent cells below, hold shift button and left click on the 10th cell below. In the process, all 10 cells in the column are selected. Now type CTRL+D and Bingo! There are 10 cells in the column with same value. That copied the value downwards. In case this is needed horizontally in a row and not down a column, typing CTRL+R will copy the value sideways. He was cheerful and thanked me.

There are many more such small tricks and workarounds and I shall be coming up with them one after another. Keep checking for future reads!