7. Creating series of numbers and filling up cells


creating seriesMS Excel includes a function which helps you filling in a lot of consecutive cells at once, as long as the content of those cells is identical OR follows a “logical” pattern such as consecutive numbers, dates, letters.

Assuming that you have to fill in the cells from A1 to A10 with numbers ranging from 1 to 10, you just need to type in 1 in A1 and 2 in A2, then select both cells (A1 and A2), click on the black square at the bottom right corner of the selection frame (note: when you place the mouse on that square, the pointer turns into a cross) and drag it down until you reach A10. The range A1:A10 now contains numbers from 1 to 10. The same applies if you need to fill up with consecutive dates, or the combination of a letter and a number (as long as a digit comes last).

“Strangely”, this doesn’t work if you want to fill in with letters from A to J or combinations of letters and numbers where the letter comes last. In both cases, you will end up with multiple duplicates of your original selection (ab becomes ababababab instead of abcdefghij; 1A 1B becomes 1A 1B 1A 1B 1A 1B 1A 1B 1A 1B instead of 1A 1B 1C 1D 1E 1F…).

 

You might want to fill in the cells with “irregular” series such as odd numbers only (1, 3, 5, 7…), or numbers arranged by increments of 3 (0, 3, 6, 9…). In that case, fill in the first two cells with the first to numbers of the series you want and drag the selection down. Things become a bit more tricky when you want to fill in with a series of numbers such as 1, 4, 16, 64… (where consecutive numbers are the result of a multiplication by 4, for instance). In this case, type the value 1 in A1, select the range A1:A10, click on the button Fill (in the section Editing located in the ribbon when Home is activated), click on Series.... In the new dialog box, select the type Growth, enter the step value 4 and the stop value of your choice (which is the highest value you want to obtain in your series) then OK. 

  Fant du det du lette etter? Did you find this helpful?
[Average: 0]