For example, assume you have a simple table of data in B4:E11 and you want to highlight all rows that contain the text “dog”. Just select all data in the table and create a new conditional formatting rule that uses this formula: Note: with conditional formatting, it’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case....
Let’s take a look. When you create a new pivot table, you’ll see Grand Totals displayed below the table and to the right of the table. Column Grand Totals appear in the last row of the table, and row Grand Totals appear in the last column of the table. However, depending on your needs, you may want to turn these on or off. There are two ways to manage Grand Totals....
When you create a pivot table with source data in a worksheet, Excel quietly creates a hidden data cache that travels along with the pivot table. If you copy and paste the pivot table into a new worksheet, remember that the data will come along with the pivot table in the hidden cache. Let me show you how this works. If I copy this pivot table, then paste into a new workbook, I can still use the pivot table thanks to the pivot cache....
Let’s take a look. Here we have the worksheet we looked at previously. Let’s run through a few examples to explore how Find and Replace work. First, let’s replace every occurrence of Ann with Anna. We need to enter Ann in the “Find what” box, and enter Anna in the “Replace with” box. As before, we’ll use “Match entire cell contents” to restrict our search to cells that only contain Ann....
The #VALUE error is a bit tricky because some functions automatically ignore invalid data. For example, the SUM function just ignores text values, but regular addition or subtraction with the plus (+) or minus (-) operator will return a #VALUE! error if any values are text. The examples below show formulas that return the #VALUE error, along with options to resolve. Example #1 - unexpected text value In the example below, cell C3 contains the text “NA”, and F2 returns the #VALUE!...
Let’s take a look. When you’re working with a pivot table, you can move fields from one area to another at any time. Each time you move a field, the pivot table will rebuild itself to respect the new field configuration. The easiest way to move a field is just to drag it from one area to another. In this pivot table, we have the Product field in the Row Labels area and Region in the Column Labels areas....
Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
Using INDEX and MATCH with an Excel Table is wonderfully straightforward. To illustrate, I’ll build INDEX and MATCH formulas that do the same thing as the VLOOKUP formulas already on this worksheet. First, to recap, these VLOOKUP formulas currently retrieve information from Table1 based on the ID provided in K4. Each formula uses the lookup_value from K4, the entire table for table_array, and the MATCH function to figure out what column number to return, based on column names in column J....
A structured reference is a term for using a table name in a formula instead of a normal cell reference. Structured references are optional, and can be used with formulas both inside or outside an Excel table. Let’s take a look at some examples. On this sheet, I have population data for 25 states in the US, based on the 2000 and 2010 census data. I’ll convert this data to an Excel Table, and name the table “states”....
In the example shown, the formula in G5 is: With nested IF statements, the flow is from outer IF statements to inner IF statements, and the challenge is always to construct the flow so that the formula returns a logically correct result. Here, the outermost IF tests first to see if the balance is zero: If TRUE, the formula returns “Paid”. If not, the result of the first IF is FALSE, and another IF statement is run....
However, if you simply join a date with text and don’t control the date format, the date will revert to it’s raw serial number format, and the result will look like this: “The date is 42887”. To control the date format, use the TEXT function, which is designed to convert a number into text using a specified number format. This article shows a list of codes you can can use for date formats....
This formula returns TRUE if a value is a whole number, and FALSE if not. Any decimal number will have a remainder equal to the decimal portion of the number: Therefore, we can simply compare the result to zero with a logical expression that returns TRUE or FALSE: This is the approach taken in the worksheet as shown, where the formula in C5 is: At each row in the data, the formula returns TRUE for whole numbers only....
In the example shown, the formula in C5 is: Which returns a random color from the values provided. In this case, we are using four values: Red, Blue, Green, and Pink, so we need to give CHOOSE a number between 1 and 4. To generate this number, we use RANDBETWEEN, a function that returns a random integer between a bottom and top value. Since we are only working with 4 values in CHOOSE, we supply 1 for the bottom number and 4 for the top number....
which replaces the first 24 characters in the text with an empty string (""). The REPLACE function can handle this easily, we just need to provide a cell reference (B6), a starting position (1), the number of characters to replace (24), and the text to use for replacement (""): For replacement, we use an empty string ("") which causes REPLACE to replace characters 1-24 with nothing. Alternative with SUBSTITUTE Since the text in this case never varies, we could also use the the SUBSTITUTE function to perform the name operation:...
In this example, we want to round up to the nearest half, so we provide 0.5 to CEILING as the multiple. In the example shown, the formula in C5 is: Even though the value in B5 is 1.1, CEILING rounds up to the next multiple of .5, which is 1.5. CEILING works like the MROUND function, but unlike MROUND, which rounds to the nearest multiple, CEILING always rounds up to the given multiple....
where custom is the named range J5:J7 that defines desired sort order. The SORTBY function allows sorting based on one or more “sort by” arrays, as long as dimensions are compatible with the source data. In this case, we can’t use the named range custom directly in SORTBY, because it only contains 3 rows while the table contains 10 rows. However, to create an array with 10 rows that can be used as a “sort by” array, we can use the MATCH function like this: Notice we are passing in the Group values in D5:D14 as lookup values, and using custom as the lookup table....
where allowed is the named range E5:E9. If the test returns FALSE, the formula returns an empty string (""). For the logical test, we are using the COUNTIF function like this: COUNTIF counts occurrences of the value in B5 in named range allowed (E5:E9). This may seem “backwards” to you, but if you think about it, it makes sense. If the value in B5 is found in the allowed list of values , COUNTIF will return a positive number (in this case 1)....
The term “array” comes from programming, but you’ll hear it come up often in the context of more advanced Excel formulas. What does it really mean? An array is a structure or container that holds a collection of items. For example, this array contains 3 items, the numbers 10, 20, and 30: {10,20,30} And this array contains three text strings: {“red”; “blue”; “green”} Note that arrays in Excel are always enclosed in curly braces....
The goal of this formula is to return “size” when given an animal and a weight. Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365. This snippet tests the values in B5:B10 to see if they match the value in G6 (the animal). Where there is a match, the corresponding values in C5:C11 are returned. If there is no match FALSE is returned....
which returns 1995, the year the movie Toy Story was released. Note that the last argument is FALSE, which forces MATCH to perform an exact match. MATCH finds “Toy Story” on row 4 and returns this number to INDEX as the row number. INDEX is configured with an array that includes all the data in the table, and the column number is hard-coded as 2. Once MATCH returns 4 we have: INDEX then retrieves the value at the intersection of the 4th row and 2nd column in the array, which is “1995”....
