If the value in F6 is changed, the highlighting will update automatically. If you want to highlight cells that equal a specific value, you can use a simple formula that returns TRUE when the condition is met. For example, to highlight any cells in the range C5-C11 that contain the text “dog”, you can use conditional formatting with this formula: In the example shown we have placed the value we are looking for in cell F6, so it can be easily changed....
All Excel Tables come with a powerful filter enabled by default, which makes it easy to filter by one or more fields. But you can also add a slicer to a table. Slicers provide the same function as filters. They let you selectively display rows based on field values. However, instead of drop-down menus, slicers provide large, friendly buttons that are always visible. This table holds basic employee information. To add a slicer that filters by department, click the Insert Slicer button on the Design tab of the Table Tools menu....
Let’s take a look. Excel provides a large number of conditional formatting presets, but there are many situations where you’ll want to use a formula to trigger a rule. The main requirement is that the formula return either TRUE or FALSE. When a formula returns TRUE, the rule is triggered and the formatting is applied. To illustrate, let’s build a conditional formatting rule to flag the odd numbers in this set of random values....
Let’s take a look. This pivot table is displaying just one field: Total Sales. After we add Product as a row label, notice that a drop-down arrow appears in the header area. When we open this menu, we see a variety of filter options. The simplest way to filter is to simply include or exclude items by using the checklist that appears below. This is called “Manual Filtering,” and we can select any combination of items that makes sense....
Let’s take a look. This pivot table shows total sales and orders for the sales data we’ve looked at previously. One field we haven’t looked at yet is Quantity. If we take a quick look at the source data, we can see that quantity refers to the number of items in each order. Let’s break down total sales by order quantity. To start off, let’s add quantity to the pivot table....
Let’s take a look. Here we have a small list of numbers that need special formatting. These numbers can be entered manually using approaches we’ve looked at previously. This works fine, but Excel’s Special formats can save you some time typing and keep the number formatting consistent. Special formats aren’t listed in the number formats menu on the ribbon. To access, select More Number Formats. Let’s go through our list and set the Special formats first, and then come back to enter the numbers....
Let’s take a look. In this first set of tables, we have two named ranges, “number” and “color.” In column G, I’ll enter a formula that satisfies the conditions in column E. The COUNTIFS function accepts arguments in pairs. The first item in the pair is the range, and the second item is the criteria. Note that all ranges that you use must always be the same size. For the first example, I need to enter “number” for range and “15” for criteria—just like the COUNTIF function....
which returns “x” when B6 is “red” AND C6 is “small”, and returns an empty string ("") if not. In the example shown, we simply want to “flag” records where the color is red AND the size is small. In other words, we want to check cells in column B for the color “red” AND check cells in column C to see if the size is “small”. Then, if both conditions are TRUE, we mark the row with an “x”....
In the example shown, the formula in cell F6 is: This formula uses the value of cell E6 to lookup and retrieve the right cost in the range B5:C7. If VLOOKUP finds a matching value, the associated cost will be retrieved from the 2nd column (column C). If no match is found, VLOOKUP will return the #N/A error. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
which generates a random time at a 15-minute interval. 0.54739314 0.919767722 0.633760119 Dates in Excel are defined as simple numbers, where 1 = 1 day. This means you can simply divide 1 by the decimal value of time to get a value that corresponds to time as Excel sees it, for example: 1/12 = 12 hours = .5 days 1/6 = 6 hours = .25 days 1/8 = 8 hours = ....
which returns a group number for each name listed in column B, where “randoms” is the named range C5:C16, and “size” is the named range G5. To assign a full set of random values in one step, select the range C5:C16, and type =RAND() in the formula bar. Then use the shortcut control + enter to enter the formula in all cells at once. Note: the RAND function will keep generating random values every time a change is made the worksheet, so typically you will want to replace the results in column C with actual values using paste special to prevent changes after random values are assigned....
To solve this formula, Excel first extracts the year, month, and day values from the date in B6, then adds 1 to the month value. Next, a new date is assembled by the DATE function, using the same day and year, and month + 1 for month. The first formula therefore returns a new date of 2/15/2010, one month later than the starting date. Once the first formula is entered, it is copied down as far as needed....
With “Blue” in cell F5, the result is 38. As the formula is copied down, it returns a count of each color listed in column F. See below for an explanation of the formula in column D. Note: both formulas below use full column references (e.g. B:B, C:C). Full column references are a compact way to express a formula, and they automatically pick up new data in a column. However, they can also cause performance problems in some formulas, and they can return incorrect results when they intersect data elsewhere in a worksheet ....
where the following named ranges are defined: If you’d rather avoid named ranges, use the ranges above entered as absolute references. The logical expressions in H6 and H7 can be combined, as explained below. This results in an array or TRUE FALSE values, which are coerced into ones and zeros with the double negative (–) operation. The result is this array: Notice the array contains 10 values, one for each row....
where “limit” (F6), “tier1” (F4), and “tier2” (F5) are named ranges. When B5 (the current amount) is less than the limit (10,000), the test returns TRUE and the IF function calculates a tier 1 tax only, and returns a final result with this: However, when the amount is greater than the limit (10,000), the logical test returns TRUE. The IF function then runs an expression to calculate tax for both tier 1 and tier 2 taxes: Translation:...
where data is the named range B5:D15. The result is a list of unique pairs of color and region, sorted by color. Working from the inside out, the FILTER function is used to filter out the middle column, “Qty”, like this: The array constant {1,0,1} is what does the actual filtering, and notice this is a horizontal array, separated by commas. The result from FILTER is a two-dimensional array with 2 columns and 11 rows like this: Notice data in the “Qty” column has been removed....
Note: line breaks added for readability. If the result of the logical test above is FALSE, the formula checks to see if D5 is less than the next threshold, which is 15 minutes: The same pattern repeats at each threshold. Because the tests are run in order, from smallest to largest, there is no need for more complicated bracketing. The VALUE function is used to make Excel treat time value at each threshold as a number instead of next....
If the number in column B is greater than zero, IF returns 1, otherwise IF returns zero. With the IF function One way to handle this problem is with the IF function. In the example shown, the formula in D5, copied down, is: The logic is simple: when the number B5 is greater than zero, IF returns 1, otherwise IF returns 0. The logical test inside IF can be adjusted to apply different logic if needed....
In the generic version of the formula, start = start date, end = end date, and dow = day of week. When given a date, WEEKDAY simply returns a number between 1 and 7 that corresponds to a particular day of the week. With default settings, 1 = Sunday and 7 = Saturday. So, 2 = Monday, 6 = Friday, and so on. The trick to this formula is understanding that dates in Excel are just serial numbers that begin on Jan 1, 1900....
which returns 7, since there are seven unique colors in B5:B16. Unlike the COUNT function, which counts only numbers, COUNTA counts both text and numbers. Since there are seven items in array, COUNTA returns 7. This formula is dynamic and will recalculate immediately when source data is changed. With a cell reference You can also refer to a list of unique values already extracted to the worksheet with the UNIQUE function using a special kind of cell reference....
