How to format red negative values ​​and green positive values ​​in Excel

Suppose you have the following simple Excel table which gives the sales profit percentage. Loss is represented by minus, profit is represented by 无negative. Although the table is self-explanatory, other users may sometimes find this representation a bit clunky. Sales The column has negative and positive values ​​and a percent sign. It definitely does not provide a visually appealing presentation of data to the user.

#So, is there a way to make this Excel table more attractive, like the one in the screenshot below? In the table below, we color negative values ​​red and positive values ​​green. We also added a red downward triangle and a green upward triangle for negative and positive values ​​respectively to make the data more attractive and easy to understand. Okay, we know you're wondering how to make this possible. Well, congratulations, you've come to the right place. Happy reading!

Solution

Step 1: First,selectthe cells you want to format with colored icons grid. You can do this by dragging and selecting the cells, or you can simply select the first cell and then press the CTRL SHIFT DOWN ARROW keys simultaneously to select the one below the selected cell. all cells. After selecting the cells,

right-click

anywhere in the selected cell group, and then click the "Set Cell Format" option.

Step 2

: In the "CellFormat" window, first click the leftmost "number" tab. Next, from the

Category

options list, click on the last option, which is Custom. Now, on the right side of the window

, under the "

Type" field, enter a formula that instructs Excel to replace the text color with dark green for positive values ​​and dark green for negative values. Replace the text color with red. We will also add a down or upward triangle to represent a loss or profit. Copy and paste the following formula in the Type field.

[颜色10]0%▲；[红色]0%▼

You may be wondering how to enter up arrow and down arrow. Well, one way is to simply copy and paste the formula above. Another way is to type them. To insert an

upward

triangle, press the ALT 30 keys simultaneously. To insert a downward triangle, press the ALT 31 keys simultaneously.

Note:

Color10 means dark green. Click the

OK

button after completion.

Step 3

: You are here! Your Excel worksheet now has the functionality you've always wanted. Welcome to the club!

Note that you can also apply the same logic in any column except the percentage column.

The above is the detailed content of How to format red negative values ​​and green positive values ​​in Excel. For more information, please follow other related articles on the PHP Chinese website!