We often need to compare and mark the table setting conditions at work. But how can we see the trend of the data more intuitively?

Take this table as an example. Suppose now we want to find out the names of milk tea with a quantity larger than 100 and mark the color.

1. Select the quantity area, and click the Conditional Formatting drop-down button in the Home tab.

2. Choose the Highlight Cells Rules option in it. Then, you can a popup window, where you can select Greater Than, Less than, Between, Equal To, and other rules.

3. Select the command called Greater Than to pop up the Greater Than dialog box.

4. Enter “100” in the Value input box, and select the Light Red Fill with Dark Red Text . In this way, you can find out all numbers greater than 100 since the numbers are marked in red.

We can also find out the names of the top ten milk tea in sales volume and mark them in color.

1. Enter =B2*C2 into the cell, and press Enter to get the result of milk tea sales volume value.

2. Double-click the lower-right corner of the cell when the mouse cursor changes into a cross. By doing this, we can get all the milk tea sales values. Also, we can use the SUMPRODUCT function to do the calculation.

If you want to learn more about how you can use more functions, please check out our videos on the usage of different functions in WPS Academy.

1. Select the sales volume data area.

2. Choose the Top/Bottom Rules option to pop up a dialog, where we can see the Top 10 Items, Bottom 10 Items, Top 10%, Bottom 10%, and other commands.

3. In this case, we should select the Top 10 items command. Enter 10 in the Value box and select the Yellow Fill with Dark Yellow Text option.

4. Click OK. Then you can find the ten top sold milk tea marked in red.

Data Bars can allow us view the data trend clearly.

1. Select the milk tea sales volume area and click the Data Bars option.

2. Here we can choose ‘Gradient Fill’, ‘Solid Fill’, and ‘More Rules’. In this case, we select gradient fill, and choose blue to fill in the cells.

In this way, we can add a colored data bar to represent the value in the cell.

Note that the data bar is getting longer when it comes with a larger value in the cell. Therefore, the comparison of sales volumes among different milk tea is intuitively presented in the worksheet.

Color Scales has the same effect as above.

Select the sales volume area and click color scale.

Then select the Green-White Color Scale style to present the data trend in color scale.

Similarly, Icon Sets is also one of the ways to reflect the data trends. Select the sales volume area.

1. Click on the Icon Sets option. Then, we can see that there are ‘Directional’, ‘Shapes’, ‘Indicators’, ‘Ratings’, etc.

2. Select Ratings, and then Five Ratings style. Then, the data trend will be presented graphically in the worksheet.

If you can't find what you need from the built-in rules above, we can also customize new rules.

1. Select the cell range with sales volume.

2. Click the New Rules button.

3. Select the rule type and rule description in the pop-up dialog box.

For instance, if we only want to format values that are higher or lower than the average, here are the steps.

1. Select Above in the rule description.

2. Choose a format in the Preview area, where we can choose ‘numbers’, ‘fonts’, ‘borders’, and ‘patterns’ in the category of Format. In this case, we need to choose pattern, and then standard yellow.

3. Click OK. Then you can customize the format of the cell area.

In this case the data with milk tea sales volumes above the average will be highlighted in standard yellow.

We can add, edit and delete rules in the rules management, and we even can select Clear rules from selected cells and Clear rules from entire sheet in the Clear Rules option.

1. Select the cell area where the rules need to be cleared.

2. Choose Clear rules from selected cells option in the pop-out submenu titled Clear Rules.

In this way, we can clear the cell rules that have been set previously.

Security

How to cancel the file password

Graphic skills

Trending Tutorials