7. Creating series of numbers and filling up cells

skjermbilde-2017-02-13-16-05-51MS Excel includes a function which helps you fill in a lot of consecutive cells at once. This is called autofill.

Assuming that you have to fill in the cells from A1 to A10 with 10 copies of “1”, you just need to type in 1 in A1, then select the cell, 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 10 copies of 1. If your plan was to fill up the range A1:A10 with numbers from 1 til 10, just click on the icon under the selection range and select Fill series. The same method applies if you need to fill up with similar/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 more complexe 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: 5]