In Excel Adding background color creates the amazing visual effect, but we can evaluate values by color? Use this technique to turn valuable into valuable information.
Background Color is a great visual tool in most of the spreadsheets. That’s why Excel’s built-in features include color. Instead of color function available in excel sometimes we need for instant visual interpretation; you might want something more functional color effects. For instance, you might want the auto sum of the values that share color effects. Or you might want to count by colors or group by colors. In this article, I’ll show you a simple way to separate values by the cell’s fill-in color by using Excel’s built-in filtering and sorting or ascending and descending feature.
Also Check :
- Don’t do these 10 things in Excel
- Business Success and Microsoft 365 Implementation
- Fixing Microsoft Office Setup Installation Error 1401,xx02, xx06?
- Aktivieren Sie Ihr Office Produkt
I’m using Excel 2016 (desktop) on a Windows 10 64-bit system, but filtering and sorting are available in previous versions. For support filtering by color for a demonstration, you can download the demonstration .xlsx file. The browser version does not support filtering and sorting by color.
Apply fill colors
Work with colors themes: Setting the data manually, but let’s use built-in functions of conditional formatting for this example with a basic data set:
1. Select C2: C18-these are the values we’ll be evaluating.
2. Under Tools click, Conditional Formatting drop-down, choose Color Scheme and then choose the first option that’s it! With just a few clicks, like this, we can add several colors to the call value data set. Choosing a built-in format is more valuable than applying the colors to the filtered cells or sorting cells.
Create a Table object
The cell values now have several colors, but now, they are not much help for counting or totaling the table values. What you might not know is the capability for sorting a table by color is a built-in function of the excel. For that reason, we’ll change the data range to a table format. To do so, click anywhere in the data. Then, click the Insert tab and then click Table option in the Tables menu. In the resulting dialog, check the My Table Has Headers option and clicks OK. At this point, we have a table, and the table has a conditional format.
Add Total row
Now, we can filter it by color-but now we can see the different values and a way to evaluate the values. For that purpose, let’s add a sum to the bottom of the table. To do so, Sum Row in the Table Color Options group on the background Design tab. This row adds aSUBCOUNT() function to the date and time field. Delete the function in A19. Now, selectG19 cell and click AutoSum in the Edit meun of the Menu bar, Home tab to enter a function that countall cells value that columns have. If necessary, apply the current currency format to the same cell which has numerical values in it.
The total of the Sum Amount column is $ 7,556. AutoSum is smart enough to calculate the total of all the words aboveSUBCOUNT() function and a SUM () function.
You might be looking for the difference between SUM () and SUBTOTAL (). A SUM () function is used to add all the values of the cell to the total in the function cell. SUBTOTAL () is used to get the total of the group cells under the different categories of the SUM () function.
Start filtering
Ready to filter by a color and see how the totaling row responds and to see the difference between the results now. To apply a simple filter, click the Filter from the dropdown option for the total Amount column. Next, select Filter by Color and select a yellow color from the color pick option. This submenu shows only a few of the colors; to see the rest, choose More Cell Colors shows the resulting filter. The SUBTOTAL() function is used to total the only cell which is having a color or only the visible records. A SUM() function would continue to evaluate all the records with all the rows whereas the SUBTOTAL() function evaluates the cells which are having the color in the cells for filtering.
The built-in Color with the conditional format updates as you modify the data with the selected rows and columns set.
