Posts

Note: this formula deals with weekend shading only. To see how to build the date bars with conditional formatting, see this article. To shade days that are weekends, we are using a formula based on the weekday function. By default, the weekday function returns a number between 1 and 7 that corresponds to days of the week, where Sunday is 1 and Saturday is 7. However, by adding the optional second argument called “return type” with a value of 2, the numbering scheme changes so that Monday is 1 and Saturday and Sunday are 6 and 7, respectively....

Let’s take a look. Once you add a field to a Pivot Table, you can view and change attributes of the field using the Field Settings dialog box. One way to get to this dialog box is to use the drop-down menu for that field in the Field List pane, and select Value Field Settings, or Field Settings from the menu. The settings you have access to will vary depending on whether the field is a Value field, or a Label field....

Let’s take a look. Before you apply borders, you may want to turn off the gridlines that appear by default in Excel. This will make it easier to see the borders you create. You can turn off gridlines by unchecking Gridlines on the Layout tab of the ribbon. To apply a border, first select the cells you’d like to add borders to. Then, open the Format Cells dialog box and navigate to the Border tab....

Let’s take a look at a few ways to create named ranges. The simplest way to create a named range is to use the name box, which sits to the left of the formula bar. Simply select the cells you want to name, and type the name in the box. For example, to give the data in this table a name, first select the cells that include the data, then enter a name in the name box....

Here we have some data that is a good candidate for a table. Each row represents an entry or record with information that belongs together. Each column has a unique name. The first step in creating a table is to remove any blank rows or columns. Tables are designed to manage data in one contiguous block of cells. Next, make sure column names are unique. Then, to create a table, select any cell in the data range and click the Table button on the Insert tab of the ribbon....

Whenever you have a lookup table visible to users, a nice touch is to highlight the rows and columns that match the current lookup. This makes it easy for users to see where a value is coming from, and it’s a nice way make your spreadsheets more friendly and transparent. This effect is easy to create with conditional formatting. To start off, I’ll delete the existing conditional formatting rules and then rebuild them step-by-step....

At the core, this formula simply divides tasks complete by the total task count: which is then formatted as a percentage. To count completed tasks, we count non-blank cells in the range C5:C11 with the COUNTA function: Unlike the COUNT function, which counts only numeric values, COUNTA will count cells that include numbers or text. To count total tasks, we count non-blank cells in the range C5:C11, again with COUNTA: After COUNTA runs, we can simply the formula to: Four divided by 7 results in the decimal number 0....

Where scores is the named range C6:C13. RANK has two modes of operation: ranking values where the largest value is #1 (order = 0), and ranking values where the lowest value is #1 (order = 1). In this case, we are ranking test scores, so the highest value should rank #1, so we omit the order argument, which defaults to zero: The following formula, which includes order set to zero, is equivalent:...

Context - sample data The workbook contains 4 worksheets total. Sheet1, Sheet2, and Sheet3 each contain 1000 random first names that look like this: Working from the inside out, this expression is used to build a full sheet reference: The single quotes are added to allow sheet names with spaces, and the exclamation mark is a standard syntax for ranges that include a sheet name. The text “1:1048576” is a range that includes every row in the worksheet....

The result is $30.45, the sum of Shampoo ($9.50), Shaving Cream (11.95), and Shaving Soap ($9.00). Note the SUMIF function is not case-sensitive. Wildcards Certain Excel functions like SUMIF and SUMIFS support the wildcard characters “?” (any one character) and “*” (zero or more characters), which can be used in criteria. These wildcards allow you to create criteria such as “begins with”, “ends with”, “contains 3 characters”, etc. as shown in the table below: Note that wildcards are enclosed in double quotes ("") when they appear in criteria....

where “times” is the named range C5:C14. This results in an array like this: The second expression is a logical test for all times greater than 30 minutes: This creates an array of TRUE FALSE values: Inside SUMPRODUCT, these two arrays are multiplied together to create this array: Notice negative values in the first array are now zeros. During multiplication, the TRUE FALSE values are converted to 1 and zero, so FALSE values “cancel out” times that are not greater than 30 min....

This formula returns a count of rows where the value in column one is A or B and the value in column two is X, Y, or Z. ISNUMBER + MATCH Working from the inside out, each condition is applied with a separate ISNUMBER + MATCH expression. To generate a count of rows in column one where the value is A or B we use the following code: Notice the values “A” and “B” are supplied as an array constant and match_type is set to zero for an exact match....

One of the new functions that comes with the dynamic array version of Excel is SEQUENCE. The SEQUENCE function lets you generate numeric sequences, which can be used for dates, times, and more. The SEQUENCE function takes four arguments. The first argument, rows controls how many rows SEQUENCE returns. This argument is required. The second argument, columns, controls the number of columns returned by SEQUENCE. Columns is optional and defaults to 1....

Which returns a match on the value in G5, with all 4 fields in a different sequence. The lookup_value comes from cell G5 The lookup_array is E5:E15 (codes) The return_array is B5:E15 (all fields) The match_mode is is not provided and defaults to 1 (exact match) The search_mode is not provided and defaults to 1 (first to last) The result is a match on “AX-160”, returned as an array of all four fields in the original order: This result is delivered directly to the second (outer) XLOOKUP as the return array argument....

where dob (H5), sign (B5:B16), symbol (D5:D16), start (E5:E15), and end (F5:F16) are named ranges. Using the lookup table as shown is somewhat challenging. See below for an alternative self-contained formula. Using the lookup table as shown is somewhat challenging. This article describes two ways to solve the problem. The first way uses INDEX and MATCH with the table as shown. The second way is a VLOOKUP formula with a hard-coded array constant....

Context Sometimes, when you use functions like SMALL, LARGE, or RANK to rank highest or lowest values, you end up with ties, because the data contains duplicates. One way to break ties like this is to add a helper column with values that have been adjusted, then rank those values instead of the originals. In this example, the logic used to adjust values is random - the first duplicate value will “win”, but you can adjust the formula to use logic that fits your particular situation and use case....

where start (H4), end (H5), age (D5:D16), and joined (E5:E16) are named ranges. At each new row, the formula returns the count of all rows between start and end dates (inclusive) that also fall into the age ranges shown in column G. Note: this is a somewhat advanced formula that demonstrates how the criteria for COUNTIFS can be extended, and quite a lot of the complexity comes from having to parse the age ranges in column G....

Matching any time greater than or equal to the time E5 (5:00). The second range/criteria pair is: Matching any time less than the time in E6 (6:30). With hard-coded values The formula in E7 could be written with hard-coded time values as follows: Excel translates a string like “5:00” into the correct numeric time value. With the TIME function The formula in E7 could be written with the TIME function like this: The TIME function provides a simple way to assemble a valid time using discreet hour, minute, and second values....

In the example above, we are using: The result of all this calculation is a list of items, where there is one item per cell in the range, and each item a number based on the calculation above. In other words, we have a list of word counts, with one word count per cell. SUMPRODUCT then sums this list and returns a total for all cells in the range. Note that the formula inside SUMPRODUCT will return 1 even if a cell is empty....

In the example shown, the data validation applied to C5:C7 is: The UPPER function changes text values to uppercase, and the EXACT function performs a case-sensitive comparison. The AND function takes multiple arguments (logical conditions) and returns TRUE only when all arguments return TRUE. The first logical condition compares the value input by the user to an uppercase version of the same value: The second logical condition tests that input to C5 is actually text If both conditions are TRUE, the AND function returns TRUE and input passes validation....