Read more to better understand the methodology. It can be found under the data tab in the what-if analysis section. Consider the data table Data Table A data table in excel is a type of what-if analysis tool that allows you to compare variables and see how they impact the result and overall data. The second approach is explained to arrive at the sum of the color cells in excel, as discussed in the below example. #2 – Sum by Color using Get.Cell Function Once the excel filter has been applied, the data table will be filtered for only light red background cells, and the subtotal formula applied at the bottom of the data table would display the summation of the colored cells, which are filtered as shown below.Īs shown in the above screenshot, the computation of the colored cell is achieved in cell E17, subtotal formula.Then select filter by color and choose the light red cell color under ‘Filter by cell color.’ Below is the screenshot to better describe the filter.Apply filter to the data table by going to Data then selecting a filter. As seen in the above screenshot, a summation of the USD amount has been calculated in order to compute the amounts highlighted in a light red background color.Here number ‘9’ in the function_num argument refers to sum functionality, and the reference argument is given as the range of cells to be computed. The formula that is entered to calculate the summation is The syntax for the SUBTOTAL formula is shown below.
Now, to achieve the sum of cells that are colored in excel, enter the formula for SUBTOTAL below the data table.Read more or by highlighting the cells manually, as shown below. It can be found in the styles section of the Home tab. Suppose we would like to highlight those cells which are negative in values for indication purpose, this can be achieved by either applying conditional formatting Applying Conditional Formatting Conditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions.
0 kommentar(er)
