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

Can you highlight the whole line of data in Excel?

As soon as I turned on my cell phone in the morning, I saw that my friend sent me such a message, joking, "can I do such a simple Excel?"

While typing a reply, she added, "it is not an ordinary manual filling color." "

What kind of effect? Look, it looks like this:

Harm, isn't this "conditional format" + "data validation" ~

01 cousins who know something about Excel must have heard of the name "spotlight"!

In Excel, using conditional formatting [using formulas to determine the cells to be formatted], you can achieve the "spotlight effect":

Formula:

= OR (CELL ("row") = ROW (), CELL ("col") = COLUMN () PS. When we switch between selected cells, the spotlight effect cannot be achieved automatically; instead, we have to press the [F9] key to recalculate the entire workbook.

What I'm going to talk about next, "changing the color of the whole row of data according to conditions", is actually done by using this Excel function.

You don't believe me? Check it out! 👇👇👇

Step 1 of the 02 ❶ new rule is particularly critical. The scope of selection is the scope of application of the rule, which will affect the specific writing of the formula.

① selects the range of cells A2:C11

②, select "conditional format"-"New Rule".

❷ add Formula Rule ① Select [use Formula to determine the cells to format]

② input formula = $B2=$E$2

③ sets fill color

④ clicks the OK button.

What needs special attention here is the use of the $symbol.

❸ New data validation ① selected E2 cell

②, select the "data" tab-"data Verification"-"data Verification".

❹ Settings data validation ① validation conditions allow you to select [sequence]

② Source Select B2:B11

③ clicks the OK button.

At this point, everything that should be set is set up!

Next, let's verify to see if we have successfully realized "discoloring the whole row of data according to conditions" ~

If the data in column B is duplicated, all related rows will be highlighted.

Did you learn it?

03 similarly, it is possible to "change the color of the whole row of data according to conditions" without data verification.

For example, mark all the rows in the following table with a quantity greater than 30 as orange.

Most of the operations are the same as above, but the formula has been changed:

= $C230

The effect is as follows:

04. There are many functions written in the last Excel, which refers not only to the many functions of its own system, but also to the different uses of various functions. If you want to fully master it, you must make some efforts.

But if you just want to improve your productivity and learn 20% of your skills, you can actually meet most of your needs!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiaolan Editor: Xiaoyin

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.