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

We often use the drop-down fill technique in Excel, which can help us quickly fill blank cells with data. Of course, different filling methods are used for various requirements. Today, Mr. Wang is here to share with you 16 common tips for filling Excel.

1, hold down the Ctrl key to fill the sequence, the normal drop-down fill will repeat the cell data, if we hold down the Ctrl, fill down, we will get the sequence.

2. Quickly fill the specified range sequence how to quickly fill the specified range of values? For example, I want to fill in 1-100. We can first enter "1" in the cell, then enter "start"-"Edit"-"fill"-"sequence", select "column", and enter "100" in the "end value".

3. Fill the date by month. We can drop down and fill it first, then click the "Auto fill option" button next to it and select "fill with month".

Fill the date by year. Fill the date by year.

5. Fill the date range with the specified date range.

6. after the quick fill workday drop-down fill, select "fill working day" in the "automatic fill options" button next to it.

7, double-click fill many friends only know, drop-down fill, in fact, we can also position the cursor to the lower right corner of the cell, double-click directly, you can achieve fast filling.

8. Ordinary filling by letter can only achieve 1, 2, 3 numeric filling. If you want to achieve letter filling, you can use a formula:

= SUBSTITUTE (ADDRESS (1 perfect row (A1), 4), 1,)

9. Uninterrupted sequence even if one or more of the sequences are deleted, the last value can be automatically continuous. Formula:

= ROW ()-1

10. After screening the filled sequence, the normal sequence can still be displayed. Formula:

= SUBTOTAL (3 Bever1B 2)-1

11. Populate the merged cells. How to cancel and populate the merged cells? First cancel the merge and then center, then F5 enter the "positioning condition", select "null value", then enter "=" in the edit bar, select the previous cell, and press "Ctrl + enter" to fill it.

12. Filling classification sequences what should be done if you want to classify each group and fill in a separate set of sequences?

= COUNTIF ($Bath2purB2JB2)

13. Repeat the filling sequence sometimes, we need to repeat the sequence multiple times, for example: 1, 1, 1, 2, 2, 2, 3, 3, so we can use the formula:

= INT ((ROW (A1)-1) / 3) + 1 repetition times, in which "/ 3" can be modified.

14. Specify a range of values for cyclic filling. For cyclic filling, you can use a formula:

= MOD ((ROW (A1)-1), 3) + 1 value range, which can be modified by yourself.

15. How does a custom incremental fill perform incremental filling? You can first enter data in the starting cell, and then select the cells you need to fill into, enter "start"-"Edit"-"fill"-"sequence", select "column", and enter the number you want to increase in the "step value". And then make sure.

16. There are some custom padding contents that cannot be populated in Excel to get the desired results. Why? In fact, this is because only fixed padding is stored in Excel. To fill in according to our own ideas and results, we can first store these results in Excel, and the next time we fill in, we can get the effect we want.

As shown in the figure below, I wanted to fill in the circled number, but I didn't get the result I wanted.

Go to "File"-"options"-"Advanced"-"Edit Custom list", enter the fill effect we want in "input sequence", and then click the "add" button to make sure. When we get back to the table interface, let's try again, shall we?

This article comes from the official account of Wechat: Word Alliance (ID:Wordlm123), author: Wang Wangqi

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.