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

The original title: "1 minute!" Learn to record progress automatically with Excel and bid farewell to manual inefficiency! "

Recently, Xiao Wang next door received a task to count the completion of the project since 2019 to evaluate whether to continue to develop new projects, such as live streaming. Here are some of the tables:

The requirements are:

❶ completed projects enter "OK" and the cells are marked yellow

❷ marks completed projects with gray.

❸ marks overdue projects as overdue and calculates the number of days overdue.

If you think the text description is not intuitive enough, you can take a look at this picture ↓↓↓

All right, you understand all the requirements!

Next, let's help him think about how to accomplish this task.

Eliminate manual, use automatic marking, we want to be efficient workplace people!

So, how do you do it? Keep looking down, and the answer will be revealed right away.

01, automatically mark the cell color of this operation, know a little bit of Excel students should know how to do!

If you don't believe me, let's have a look!

❶ Select column E

❷ found [conditional formatting]-[highlight cell rules]-[equals] under the start tab

❸ enters the formula in the pop-up dialog box:

= "OK"

❹ fill color!

Have you learned everything?

Next, let's take a look at how to automatically tag an entire line of colors:

02, automatic marking the whole row of colors to achieve this operation, the method to be used, and the automatic marking cell color is somewhat similar, all need to use the [conditional format] function.

The difference is that you need to [use a formula to determine the cell to be formatted], but the formula will not be used, it is super simple! Xiaobai can also do it easily!

Let's take a look at the specific steps.

❶ selects the range of A1:G20 cells

❷ found [conditional format]-[New Rule] on the "start" tab

❸ in the pop-up window, select use Formula to determine the cell to format, and enter the formula in the following dialog box:

= $E1 = "OK"

❹ as above, set "format"-[OK]

❺ adjusts the rule order of conditional formatting and puts the rule of "automatically marking cell colors" on top.

Select the A1:G20 area, and click "conditional format"-"manage rules"

Select "Cell value" in the conditional format Rule Manager, click the upward arrow, and make sure! The result is as follows:

After the setup is complete, let's take a look at the effect image:

03. Automatic marking overdue calculation of overdue days next, you need to complete the third requirement: automatically mark overdue, and calculate the number of overdue days.

Here you need to use two relatively simple and common functions: the IF function and the TODAY function.

First of all, let's calculate the "days overdue". Then, we will judge whether it is "overdue" according to the number of days overdue and whether it is "OK" or not.

👉 procedure:

❶ select G2 cells, enter the following formula, and drop down to fill:

= IF (E2 = "OK,", TODAY ()-D2)

The meaning of 👉 formula:

If the E2 cell is marked with "OK", the current cell is empty; otherwise, the day difference between today and the D2 cell is returned.

If it is negative, it means that the task has not yet expired.

PS: start date and due date must be in a standard date format.

❷ select the F2 cell, enter the following formula, and drop down to fill:

= IF (G2 > 0jue E2 "OK"), "overdue", "")

Formula meaning: if G2 cell is not empty and E2 cell is not marked "OK", "overdue" is displayed, otherwise it is displayed as "empty".

At this point, we have all set up successfully!

👉 final effect picture:

04. To sum up, let's review. This article covers a total of three tips:

❶ automatically marks the cell color

❷ automatically marks the whole line of colors

❸ automatically marks the overdue date and calculates the overdue days.

PS: the above three tips can also be split and used.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: witty Qiu Xiao E, editor: Zhu Lan

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.