Shulou(Shulou.com)11/24 Report--

There is a filling function in Excel, which can be used to fill in and input data quickly.

There is no need for us to enter one by one manually.

This can greatly improve our work efficiency.

It can be said that mastering the filling function is one of the necessary skills to become a master of Excel data processing.

Let's take a look at it with me.

1. The filling date is shown in the figure below, which is a salary summary table. The date in the [A] column needs to be filled in on the last day of each month.

Change from the above picture to the following picture.

Let's think about it, how is it entered?

Is it like this: first enter 2023-1-31 in [A3], then click the fill button in the lower right corner with the mouse, drag to the [A8] cell, and select "fill in month] in the menu.

As shown below:

However, if filled in this way, the original interlaced coloring format will be destroyed.

Some friends may think this way, how about selecting [without formatting] in the menu?

As shown below:

The result is not populated on a monthly basis.

In other words, the options in this menu are single-option and cannot achieve both functions at the same time.

What should I do?

First fill in the month, and then set the cell color one by one?

Or after filling it, use the format to brush it?

Well, all of these methods are fine. But I know a faster way, ↓↓↓.

❶ first enters the first date 2023-1-31 in [A3], and then enters the second date 2023-2-28 in [A4], which is the last day of the first two months of input.

Select [A3:A4] at the same time after ❷, and double-click the fill button in the lower right corner with the mouse.

The date is automatically filled in month, and then select "No format fill" in the menu to complete.

2. Fill in cyclic sequences sometimes we need to fill in some cyclic sequence numbers when we make a table. As shown below:

For example, we want to fill in three lines for each number, increasing by 1 in turn.

How do you do this?

With a function?

Of course you can.

Of course, it can also be done with filling.

The methods are as follows:

Enter 1 in [A2:A4], then enter: = A2room1 in [A5], and then drag down by clicking the fill button in the lower right corner to complete the input of the cyclic sequence.

There is also a loop sequence that looks like this:

Press three numbers to cycle around.

There are two ways to do this loop sequence.

Method 1: can be the same as above, but do not need + 1, directly equal to [A2].

Method 2: directly select [A2:A4], then click the fill button in the lower right corner and drag down to [A10], and finally select [copy Cell] in the menu.

3. Forecast the future data as shown in the chart below, which is a sales forecast table. we would like to forecast the sales in the next three years based on the sales situation in the last five years.

In general, we might use the predictive function (FORECAST) to deal with it.

For example, enter the following formula in [B7] cell:

= FORECAST (A7) then populate the formula into the [A9] cell.

The formula roughly means that future values are calculated or predicted using a given existing value (x value and y value).

Of course, for the function rookie, it is difficult to understand.

We can use the fill function instead of this function here.

Check the 2018-2022 existing value [B2:B6], and then double-click the fill button or drag down the fill to the [B9] cell.

In this way, the data prediction work is completed.

And the result is exactly the same as that obtained by using the function.

4. at the end of the day, we share the filling functions commonly used by three experts, which can greatly improve our work efficiency.

In addition, there are some commonly used padding. For example:

Batch cell padding (Ctrl+Enter)

Multi-table filling

Interlaced intercolumn filling

Shortcut key padding (Ctrl+D, Ctrl+R), etc.

Friends can try it for themselves.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.