Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I was wondering if there was, and I bet there is, an easier and faster method for an issue I have when working with a certain kind of Excel sheets.

At my work, I’m working with 2 or 3 different and new sheets per day with an arbitrary number of rows but usually there are between 10.000 and 250.000 rows in each document. In each sheet I need to create a new colon which identify this specific document (this is for parsing it later as a CSV file). Right now I’m copying the number from top to bottom and with 250.000 rows this can be a rather time consuming task. I would really save my day if someone had a solution for how to deal with this in an easy and fast manner. It could be a Macro or a formula. I’ve tried different approaches but with no luck.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

There are several ways to do this; I've highlighted a couple of my favourites below:

Quick way for a couple of cells, easily done with just the mouse:

  1. Create your formula in the top cell of your column.
  2. Highlight the cell by clicking on it.
  3. Drag the black box from bottom right hand corner down.

Or the quickest way with large amounts of data (best for your solution):

  1. Create your formula in the top cell of your column.
  2. Highlight the cell by clicking on it.
  3. Copy the cell (either by right clicking and choosing copy, or Ctrl+C)
  4. Highlight the column by clicking the heading (ie F)
  5. Paste into the column (either by right clicking and choosing paste, or Ctrl+V)
share|improve this answer

If you only want to fill cells in rows that already have data:

  1. Add your column, or select the next column.
  2. Select the first row in your column and enter your value.
  3. Double-click the black fill handle (bottom right corner of cell).
  4. Excel will fill the value from your first cell down as long as there's data in the adjoining column.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.