Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP HOW TO W3.CSS C C++ C# BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS R TYPESCRIPT ANGULAR GIT POSTGRESQL MONGODB ASP AI GO KOTLIN SASS VUE DSA GEN AI SCIPY AWS CYBERSECURITY DATA SCIENCE
     ❯   

Excel Tutorial

Excel HOME Excel Introduction Excel Get Started Excel Overview Excel Syntax Excel Ranges Excel Fill Excel Move Cells Excel Add Cells Excel Delete Cells Excel Undo Redo Excel Formulas Excel Relative Reference Excel Absolute Reference Excel Arithmetic Operators Excel Parentheses Excel Functions

Excel Formatting

Excel Formatting Excel Format Painter Excel Format Colors Excel Format Fonts Excel Format Borders Excel Format Numbers Excel Format Grids Excel Format Settings

Excel Data Analysis

Excel Sort Excel Filter Excel Tables Excel Conditional Format Excel Highlight Cell Rules Excel Top Bottom Rules Excel Data Bars Excel Color Scales Excel Icon Sets Excel Manage Rules (CF) Excel Charts

Table Pivot

Table Pivot Intro

Excel Case

Case: Poke Mart Case: Poke Mart, Styling

Excel Functions

AND AVERAGE AVERAGEIF AVERAGEIFS CONCAT COUNT COUNTA COUNTBLANK COUNTIF COUNTIFS IF IFS LEFT LOWER MAX MEDIAN MIN MODE NPV OR RAND RIGHT STDEV.P STDEV.S SUM SUMIF SUMIFS TRIM VLOOKUP XOR

Excel How To

Convert Time to Seconds Difference Between Times NPV (Net Present Value) Remove Duplicates

Excel Examples

Excel Exercises Excel Certificate

Excel References

Excel Keyboard Shortcuts


Excel Fill


Filling

Filling makes your life easier and is used to fill ranges with values, so that you do not have to type manual entries.

Filling can be used for:

  • Copying
  • Sequences
  • Dates
  • Functions (*)

For now, do not think of functions. We will cover that in a later chapter.


How To Fill

Filling is done by selecting a cell, clicking the fill icon and selecting the range using drag and mark while holding the left mouse button down.

The fill icon is found in the bottom right corner of the cell and has the icon of a small square. Once you hover over it your mouse pointer will change its icon to a thin cross.

Click the fill icon and hold down the left mouse button, drag and mark the range that you want to cover.

In this example, cell A1 was selected and the range A1:A10 was marked.

Now that we have learned how to fill. Let's look into how to copy with the fill function.


Fill Copies

Filling can be used for copying. It can be used for both numbers and words.

Let's have a look at numbers first.

In this example we have typed the value A1(1):

Filling the range A1:A10 creates ten copies of 1:

The same principle goes for text.

In this example we have typed A1(Hello World).

Filling the range A1:A10 creates ten copies of "Hello World":

Now you have learned how to fill and to use it for copying both numbers and words. Let's have a look at sequences.



Fill Sequences

Filling can be used to create sequences. A sequence is an order or a pattern. We can use the filling function to continue the order that has been set.

Sequences can for example be used on numbers and dates.

Let's start with learning how to count from 1 to 10.

This is different from the last example because this time we do not want to copy, but to count from 1 to 10.

Start with typing A1(1):

First we will show an example which does not work, then we will do a working one. Ready?

Lets type the value (1) into the cell A2, which is what we have in A1. Now we have the same values in both A1 and A2.

Let's use the fill function from A1:A10 to see what happens. Remember to mark both values before you fill the range.

What happened is that we got the same values as we did with copying. This is because the fill function assumes that we want to create copies as we had two of the same values in both the cells A1(1) and A2(1).

Change the value of A2(1) to A2(2). We now have two different values in the cells A1(1) and A2(2). Now, fill A1:A10 again. Remember to mark both the values (holding down shift) before you fill the range:

Congratulations! You have now counted from 1 to 10.

The fill function understands the pattern typed in the cells and continues it for us.

That is why it created copies when we had entered the value (1) in both cells, as it saw no pattern. When we entered (1) and (2) in the cells it was able to understand the pattern and that the next cell A3 should be (3).

Let's create another sequence. Type A1(2) and A2(4):

Now, fill A1:A10:

It counts from 2 to 20 in the range A1:A10.

This is because we created an order with A1(2) and A2(4).

Then it fills the next cells, A3(6), A4(8), A5(10) and so on. The fill function understands the pattern and helps us continue it.


Sequence of Dates

The fill function can also be used to fill dates.

Note: The date format depends on you regional language settings.

For example 14.03.2023 vs. 3/14/2023.

Test it by typing A1(29.07.2021):

And fill the range A1:A10:

The fill function has filled 10 days from A1(29.07.2021) to A10(07.08.2021).

Note that it switched from July to August in cell A4. It knows the calendar and will count real dates.


Combining Words and Letters

Words and letters can also be combined.

Type A1(Hello 1) and A2(Hello 2):

Next, fill A1:A10 to see what happens:

The result is that it counts from A1(Hello 1) to A10(Hello 10). Only the numbers have changed.

It recognised the pattern of the numbers and continued it for us. Words and numbers can be combined, as long as you use a recognizable pattern for the numbers.


Test Yourself With Exercises

Excel Exercise:

Complete the sequence of numbers:

A1(1)
A2(2)
A3

Start the Exercise


×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookie and privacy policy.

Copyright 1999-2024 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.