Excel

how to use flash fill and custom list in excel

2

First, we learn,

What is the filling series?

Instead of entering data manually on a worksheet, you can use the featured autofill to fill a cell with data following a pattern of data in other cells, Excel supports a wide variety of fill options. go to HomeTab ⇒ Edit group ⇒ Fill ⇒ Series to display the series dialog box.

These are the options you can select while creating a series.
Select the option Linear from the type list. From the series in option choose any of the two:

  • Row: To fill the series horizontally
  • Column: To fill the series vertically
Creation of Linear Series

Liner series is for the series that increase by adding the same value to each successive cell.

  1. Select the cell you want to generate the series based on.
  2. Select any of the options from the series in options according to your requirement. We’re selecting columns here. Select the Linear option from the list of types. Input Step value to let Excel know the increment in values and Stop value to define the end point of the series.

Creation of Growth Series

Growth series is for the series that increases geometrically multiplying by a value.

  1. Select the cell you want to generate the series based on.
  2. Select any of the options from the series in options according to your requirement. Here we select columns.
  3. Select the Growth option from the list.
  4. Input step value and stop value requirement. Here we use 5 as a step value and 400 as a stop value. It will create a series such as 3, 15, 75, 375
Creation of Date Series

Select Date and select the date interval in the Date Unit area. Your choices are Day, Weekday, Month, Year. “Ille Date Unit is only enabled if a date is contained in the source cell.

  1. Select the cell you want to generate the series based on.
  2. Select the Date options from the Type List and Date Unit as a weekday. Depending on the nature of the value increase, you can select day/month/ year.
  3. Select any of the options from the series in options according to your requirement.
  4. Here we select columns. Input step value and stop value as your requirement.
  5. Here we use 1 as step value and 10/04/2013 as stop value.

Using Trend to generate a series.

  1. Select the cell based on which you want to generate the series.
  2. Select the option Linear from the Type list. You can also select growth to generate a Growth series.
  3. Select any of the options from the Series in options according to your requirement. We’re selecting columns here.

Flash Fill

A new feature in Excel 2013, Flash Fill can automatically enter data into your worksheet, saving you a lot of time and effort. Just like the Fill handle, Flash Fill can guess what kind of information you enter into your worksheet. Flash Fill is like a data assistant that finishes your work for you. Once it detects what you want to do, Flash Fill enters the rest of your data in one fell swoop, following the pattern it recognizes in your data. Shortcut Key: Ctrl +E

Turn Flash Fill on

Flash Fill is on by default and automatically fills your data when it senses a pattern. However, if it’s not working as expected, here’s how you can check if Flash Fill is turned on.

  1.  Click File Options.
  2. Click Advanced and make sure the Automatically Flash Fill box checked.

custom List.

Entering a series is similar to filling a cell’s content for a group of cells. Suppose you want to create a series of days like Sunday, Monday, and so on. Type Sunday into a cell and drag the cell’s fill handle in any direction and check a series of days will be generated. Similarly, you can create a series of months, i.e. January, February, and so on in the same way. These series are generated as they are already defined in Excel. You can create your own series using text or number, etc.

To define your own list Go to File Button Options ⇒ Advance Tab ⇒ Edit Custom Lists.

 

How do you make tables style using easier formatting

Previous article

How to create advanced number formatting in excel

Next article

You may also like

2 Comments

  1. I learned a new feature thaks to author

    1. Welcome Mr’ Tanmoy

Leave a reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More in Excel