Sharing skills of quickly filling multi-line discontinuous serial numbers in Excel

Original title: "if a colleague is asked to fill in 1000 lines of discontinuous serial numbers, how can he leave work in 2 minutes?" "

Behind the headlines! Hello everyone ~ here is the workplace struggle satellite paste trying to introduce Excel knowledge in the most easy-to-understand language.

Filling must be no stranger to everyone, after all, many times as long as we use good filling, we can solve a lot of repetitive work!

But there are so many filling skills, there are always some you need but don't know ~

You don't believe me? Let's see ↓↓↓.

❶ fills the merged cell

❷ segmented serial number filling

1. Old friends who fill in merge cells Akiba must know that we have always stressed that when using Excel to organize data, do not merge cells at will!

This is beautiful, but it will bring great trouble to the later calculation!

For example, Xiao Yu, a newcomer to the workplace, came to me with a bitter face last week and asked me if there was any way to quickly fill in the merged cells.

It turned out that after the merger, she found that the serial number had not been filled in yet.

You know, it is impossible to drop down and fill in merged cells, so many serial numbers, if you type them one by one, you have to do it until the end of the year.

In fact, just select all the merged cells and enter the formula:

= MAX ($A$2:A2) + 1 finally press the shortcut key [CtrI+Enter] and it's done.

Or enter a formula:

= COUNTA ($B$3:B3) and then press [CtrI+Enter] to achieve the same.

How's it going? Have you learned it?

In the future, we can say: this article fills the gap that the merged cells cannot be filled.

Ahem, I'm kidding. Let's move on to the next trick.

2. Segmented sequence number filling and merge filling is no problem, but there is another table that looks like this:

How do you fill in the serial number? Drop-down fill? Afraid of accidentally brushing out the title?

Well, hey, actually, it can be like this, ↓↓↓.

First [Ctrl+G] locate the null value, and then enter the formula:

= N (A2) + 1 and then press the shortcut key [CtrI+Enter] to get it done.

So, is it SO EASY Duck?

Friends who don't remember how to locate null values can review the following article:

Press [Ctrl+G] once and found 3 divine tricks!

3. At the end of the day, we solved two small problems in filling sequence numbers with three functions:

Using MAX and COUNTA to deal with merged cell padding

Fill in the segmented serial number with the N function.

Have you learned it?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: satellite paste