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

The original title: "add a conditional format to your form, the effect of leverage."... "

There is a very special feature in Excel that will brighten people's eyes.

It will highlight the focus of your table data and show it more prominently in front of the public.

It will help you automatically complete the formatting you want, whether it is existing data or new data.

It can also beautify the form.

This is what it is: conditional format.

As shown in the figure below, this is a capital flow account:

If it is the content of "opening balance" and "total of the current period" in the summary, the whole line is automatically set to yellow. Become like this:

What should I do?

Problem analysis if the problem is solved manually, you can filter directly in the summary column and fill in the cell colors separately.

As shown below:

Once set up, you can cancel the filter.

However, if there is more content, it will be more tedious to set up.

If you want to make the contents of a cell equal to a certain value, let it be automatically colored, of course, you have to come up with the "conditional format" in Excel!

Next, come with me to see how to do it!

Problem solving ❶ selects the data area and calls up the conditional format dialog box. Select the [A2:F10] cell range, then click "start" = "[conditional format] =" [New Rule], and bring up the "New format Rule" dialog box:

Select use Formula to confirm the cells you want to format, and enter the following:

The formula is as follows:

= $C2 = {"opening balance", "total of the current period"} means:

This conditional format holds if the content of the [C2] cell is equal to "opening balance" or "total of the current period". The cell format will be set according to our settings below.

Column C should be fixed here because we only need to compare the contents of the summary bits.

Click the "format" button and make the following settings:

Then click [OK] to return to the previous interface.

After setting up, click [OK].

Ah ou!

What's wrong?

See what is written in the prompt box:

You cannot use array constants in conditional formats!

What about this problem?

Don't worry, it's easy! Easy!

Even if it is not written in the text box here, we can write it in the cells and then refer to these cells.

Create a new [Table 2] worksheet and write the contents in [A1:A3].

Then make the following settings in "Edit format rules":

Finally, [OK], as shown in the following figure:

Oh, something still doesn't seem right?

The line in which the opening balance is located has been automatically filled in, but the total line has not changed in the current period.

What's the problem?

The reason for this problem lies in the formula itself.

= $C2 = Table 2 contains two logical values because the result of this formula produces two logical values.

When on line 2, the result is as follows:

When on line 9, the result is as follows:

Although this formula generates two values, in the end, it will only return the first value in the array. If the first value is TRUE, it meets the requirements of conditional formatting, and the row will be filled with yellow; if the first value is FALSE, it will not meet the requirements of conditional formatting and will not be filled with color.

Now that the principle is known, it will be easy to do! Put an OR function on the outside of the formula.

= OR ($C2 = Table 2) the formula means:

As long as one of the two values is TRUE, the final result returns TRUE, and the row is automatically colored.

If both values are FALSE, the final result returns FALSAE, and the row is not automatically colored.

The final effect is as follows:

Our problem's solved!

Knowledge extension ❶ before we automatically extend the formula conditions in the conditional format, we set two conditions in the [Table 2] worksheet, one is the opening balance, and the other is the total of the current period.

If you want to add another condition: "current year accumulation", you need to enter the current year accumulation in the worksheet [A4] cell in [Table 2].

And you have to reset the formula in the conditional format. As shown below:

The final results are as follows:

In this way, the work of repeatedly setting conditional format is relatively inefficient.

Can you automate the format setting when the conditions are added?

Of course, let's improve it according to the following ideas.

① sets the conditional area in the [Table 2] worksheet to a super table.

Select the [A2] cell in Table 2, and then press [CTRL+T], as shown below:

Finally, click OK.

② modifies the formula in the conditional format.

As shown below:

The formula is as follows:

= OR ($C2=INDIRECT ("Table 1 [name]")) changes the range of cells directly referenced in the original condition to the following form:

INDIRECT + Table name + [+ Table title name +]

The name of the super table here is: table 1

The title name of the super table is: name

Finally, the cell reference is formed by using the INDIRECT function.

After setting up the above two steps, let's verify the effect.

Enter the current year accumulation in the [A4] cell in the [Table 2] worksheet, as shown in the following figure:

[table 1] this year's cumulative rows in the worksheet will be automatically colored.

Isn't it amazing!

❷ automatically extends the conditional formatting area in the data source. When we first set the conditional formatting, we only selected the [$A$2:$F$10] cell range with data.

We can also manually make the area of this conditional format large enough, such as selecting the [$A$2:$F$9999] area.

The advantage of this setting is that it can meet the increase of later data, and there is no need to manually reformat the conditional formatting area.

The disadvantage is that when there is more table data, there may be table stutters.

Of course, we can also set the data source area to super table form as above. For example, set the data source to the name of the super table [Table 2].

The region of the conditional format selects the actual [$A$2:$F$10] data region.

At this point, when we have new data, such as writing the opening balance in the [C11] cell, the row will be automatically colored.

Let's take a look at the application to the data region in the conditional format Rule Manager.

This area will be automatically expanded to [= $A$2:$F$11].

Isn't it amazing!

The advantage of this setting is that when the data increases, the area of the conditional format will also be automatically expanded to avoid setting too many useless areas.

At the end of the day, we shared one of the brightest features in Excel [conditional format].

And we can turn the conditional format in the data area [apply to the region] and the [formula conditional setting] in the conditional format into an automatic expansion function, which greatly improves our work efficiency. Really achieved a setting, life-long use, no need to manually change the area or reset the conditions.

Use conditional formatting to remind leaders or bosses what data they need to focus on. It's really a very easy to use and powerful feature.

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.