Sometimes multiple conditional formatting rules in Excel conflict. A higher rule always wins. This example illustrates two different results. 1. The value 95 is higher than 80 but is also the highest value (Top 1). The formats (yellow fill vs green fill and yellow text color vs green text color) conflict. A higher rule always […]
This example describes how to compare two lists using conditional formatting. For example, you may have two lists of NFL teams. To highlight the teams in the first list that are not in the second list, execute the following steps. 1. First, select the range A1:A18 and name it firstList, select the range B1:B20 and […]
This example shows you how to use conditional formatting to shade alternate rows. Shading every other row in a range makes it easier to read your data. 1. Select a range. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click New Rule. 4. Select ‘Use a formula to determine which […]
This example teaches you how to find duplicates (or triplicates) in Excel. Go here to remove duplicates. 1. Select the range A1:C10. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Highlight Cells Rules, Duplicate Values. 4. Select a formatting style and click OK. Result. Excel highlights the duplicate names. […]
If the Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales and Icon Sets are not sufficient, you can create a new rule. For example, highlight the codes below that occur more than once in the range A2:A10 and have a score greater than 100. 1. Select the range A2:A10. 2. On the Home tab, […]
Icon Sets in Excel make it very easy to visualize values in a range of cells. Each icon represents a range of values. To add an icon set, execute the following steps. 1. Select a range. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Icon Sets and click a […]
Color Scales in Excel make it very easy to visualize values in a range of cells. The shade of the color represents the value in the cell. To add a color scale, execute the following steps. 1. Select a range. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Color […]
Data bars in Excel make it very easy to visualize values in a range of cells. A longer bar represents a higher value. To add data bars, execute the following steps. 1. Select a range. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Data Bars and click a subtype. […]
To view all conditional formatting rules in a workbook, use the Conditional Formatting Rules Manager. You can also use this screen to create, edit and delete rules. 1. Select cell A1. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click Manage Rules. The Conditional Formatting Rules Manager appears. Note: because […]
Highlight Cells Rules | Clear Rules | Top/Bottom Rules Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell’s value. the following are some conditions by which a user can effectively manage his data. Highlight Cells Rules Color Scales Manage Rules Conflicting Rules Shade Alternate Rows New Rule […]
Number and Text Filters | Date Filters | Advanced Filter | Data Form | Remove Duplicates | Outlining Data Filter your Excel data if you only want to display records that meet certain criteria. 1. Click any single cell inside a data set. 2. On the Data tab, in the Sort & Filter group, click Filter. Arrows in the column […]
One Column | Multiple Columns You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order. One Column To sort on one column, execute the following steps. 1. Click any cell in the column you want to sort. 2. To sort in ascending order, on the […]
This example shows you how to solve a system of linear equations in Excel. For example, we have the following system of linear equations: If A-1 (the inverse of A) exists, we can multiply both sides by A-1 to obtain X = A-1B. To solve this system of linear equations in Excel, execute the following […]
This example teaches you how to find the most frequently occurring word in Excel. You can use the MODE function to find the most frequently occurring number. However, the MODE function only works with numbers. You can use the COUNTIF function to count the number of occurrences of each word. However, we are looking for […]
This example teaches you how to perform a two-column lookup in Excel. See the example below. We want to look up the salary of James Clark, not James Smith, not James Anderson. 1. To join strings, use the & operator. 2. The MATCH function returns the position of a value in a given range. Insert […]
Summing with Or criteria in Excel can be tricky. This article shows several easy to follow examples. 1. We start simple. For example, we want to sum the cells that meet the following criteria: Google or Facebook (one criteria range). 2a. However, if we want to sum the cells that meet the following criteria: Google […]
This example shows you how to create an array formula that sums a range with errors. You can also use the AGGREGATE function to sum a range with errors. 1. We use the IFERROR function to check for an error. Explanation: the IFERROR function returns 0, if an error is found. If not, it returns […]
This example shows you how to create an array formula that sums the largest numbers in a range. 1. We use the LARGE function. For example, to find the second to largest number, use the following function. 2. To sum the 4 largest numbers (don’t be overwhelmed), add the SUM function and replace 2 with […]
This example shows you how to create an array formula that sums every nth row in Excel. We will show it for n = 3, but you can do this for any number. 1. The ROW function returns the row number of a cell. 2. The MOD function gives the remainder of a division. For […]
Without Array Formula | With Array Formula | F9 Key | Count Errors | Count Unique Values | Count with Or Criteria | Sum with Or Criteria | This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell. Without Array Formula Without using an array formula, we would execute the […]
