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.
The TEXTJOIN function takes three required arguments: delimiter, ignore_empty, and text1. Delimiter is the text to use between values that are concatenated together and should be enclosed in double-quotes (""), for example, a space (" “) or a comma with a space (”, “). To use no delimiter, supply an empty string (”"). Ignore_empty is a Boolean (TRUE/FALSE) value that controls whether empty values should be ignored or added to the result....
The TRUNC function takes two arguments: number and num_digits. Number is the numeric value to truncate. The num_digits argument is optional and specifies the place at which number should be truncated. Num_digits defaults to zero (0). Examples By default, TRUNC will return the integer portion of a number: To control the place at which number is truncated, provide a value for num_digits. When num_digits is negative, the TRUNC function will replace the number at a given place with zero:...
Let’s take a look. There are two basic paths to horizontal alignment in Excel. The first is the Alignment group on the home tab of the ribbon, which gives you access to left, right, and center alignment. The second path is the Alignment tab on the Format Cells dialog box. The Format Cells dialog is more complete and gives you access to all the cell alignment options in Excel. As we’ve seen previously, Excel will, by default, align text to the left and numbers and dates to the right....
Radar charts can be used to plot the performance, satisfaction, or other rating data across multiple categories in a single chart. In this worksheet we have rating information for an employee in 6 categories: Technical Skill, Experience, Accomplishments, Personality, Sense of humor, and Work ethic. All categories are rated on the same 5-point scale, where 1 is poor and 5 is excellent. You won’t find radar charts in Recommended Charts....
Unlike the MATCH function, which gets the position of an item in a list or a table, INDEX assumes you already know the position, and it gets the value of the item at that position. Let’s take a look. INDEX is a powerful and flexible function that can be used for advanced features like dynamic ranges and two-way lookups. However, in this example, we’re going to use INDEX for the simplest use case possible—retrieving items from a known position....
Let’s look first at the CODE function. The CODE function accepts one argument, which is the text for which you’d like a numeric code. If I use CODE with a capital A (“A”) it returns 65. I’ll get the same result if I supply a reference to B6, which also contains a capital “A.” With a lowercase “a” CODE returns 97. If I copy that formula down, you can see that CODE returns a number for each character, including punctuation and special symbols....
Let’s take a look. Here we have a large table that shows average monthly temperatures for Salt Lake City from 1949 through 2012. Let’s use a color scale to help visualize the numbers. There are twelve presets in the Color Scales category of the Conditional Formatting menu. The first six presets are 3-color color scales, and next six presets are 2-color color scales. For all presets, the color at the top is applied to the higher values in the selection, and the color at the bottom is applied to the lower values in the selection....
In the example shown, we want to mark rows where the color is red with an “x”. In other words, we want to test cells in column B, and take a specific action when they equal the word “red”. The formula in cell D6 is: In this formula, the logical test is this bit: This will return TRUE if the value in B6 is “red” and FALSE if not. Since we want to mark or flag red items, we only need to take action when the result of the test is TRUE....
The ReplaceChars function is designed to accept three input parameters: str is the text to perform replacements on, chars is a text string containing the characters to replace, and sub is is the character (or characters) to substitute when a character is found. In the example shown, ReplaceChars is configured to find the punctuation characters seen in the second argument (chars), and replace them with an empty string (""). This custom LAMBDA formula illustrates a feature called recursion, in which a function calls itself....
In the example shown, the formula in D5 is: Last relative position When constructing more advanced formulas, it’s often necessary to figure out the last location of data in a list. Depending on the data, this could be the last row with data, the last column with data, or the intersection of both. It’s important to understand that we are after the last relative position inside a given range not the row number on the worksheet:...
The INDIRECT function then coerces the text “Sheet1!A1” into a valid reference, which is passed into the SHEET function. The SHEET function then returns the current index for each sheet as listed. 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....
Where names is the named range B6:B17, and times is the named range D6:D17. Notes: this is an array formula and must be entered with Control + Shift + Enter. Later versions of Excel have a MAXIFS function, see note below. The result is an array of TRUE / FALSE values like this: TRUE values correspond to rows where the name is “Hannah”. For all other names, the value is FALSE....
With the POWER function The POWER function is another way to perform exponentiation in Excel. To get the nth root of a number with POWER, use the number with 1/n for the power argument: So for the example shown, the formula in D5 would be: 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....
Fields The source data contains two fields: Name, and Birthdate. Both fields are used to create the pivot table: The Birthdate field has been added as a Row field and grouped by Months only: The Name field has been added as a Value field. Because Name is a text field, the field is summarized by count automatically: Helper column alternative As an alternative to automatic date grouping, you can add a helper column to the source data, and use a formula to extract the month....
Fields The pivot table shown is based on three fields: First, Last, and Department. The Department field is configured as a Row field, and Last is configured as a Value field, renamed “Count”. The Last field is renamed “Count” and configured to summarize by count: In the example shown, the pivot table uses the Last field to generate a count. Any text field in the data that is guaranteed to have data can be used to calculate count....
The exercise is to verify the value of pi by plotting circumference vs. radius of a number of objects measured in a lab. Here is the data: Here is the original result: 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....
where amount (D5:D16), client (B5:B16), and date (C5:C16) are named ranges. Summary table setup The first step in solving this problem is creating the summary table seen in the range F4:I8. The values in F4:F9 are text values. However, the month names in G4:I5 are actually valid Excel dates, formatted with the custom number format “mmm”: SUMIFS solution The SUMIFS function can sum values in ranges based on multiple criteria....
Start is the named range G5, which is used by formulas in columns C and D, as explained below. The first step is to calculate the decimal hours as seen in column C. We could also work with Excel time, but decimal hours are more convenient when we calculate the day labels later below. The formula in C5, copied down, is: We subtract the start time from the end time and multiply by 24....
where code (B5:B15) and quantity (C5:C15) are named ranges. In the example shown, XLOOKUP is configured to match the value entered in cell E5, which may appear anywhere in the lookup values in B5:B15. The formula in F5 is: lookup_value - E5, with asterisks (*) concatenated front and back lookup_array - the named range code (B5:B15) return_array - the named range quantity (C5:C15) if_not_found - the string “no match” match_mode - provided as 2 (wildcard match) search_mode - not provided....
