Let’s take a look. Excel’s LEFT and RIGHT functions are easy to use when you know how many characters you want to extract. But what if you want to extract the first name from a full name? If you try LEFT by itself, you’ll quickly realize you have a problem because you don’t know how many characters to extract. For example, with this list of names, if I enter “5” to extract five characters, it does work for some names like Traci, and Linda, and Sarah....

Data labels are used to display source data in a chart directly. They normally come from the source data, but they can include other values as well, as we’ll see in in a moment. Generally, the easiest way to show data labels to use the chart elements menu. When you check the box, you’ll see data labels appear in the chart. If you have more than one data series, you can select a series first, then turn on data labels for that series only....

When you first create an Excel Table, you’ll see filter buttons automatically added to the top of each column. Each filter is customized to match the contents of the column. For example, when I click the filter for Color, Excel displays a checkbox for each color that appears in the data. This list is dynamic. If I change Purple to Yellow in a row, Yellow appears automatically in the filter menu....

Let’s take a look. The COUNT function counts numeric values. For example, if I enter the formula =COUNT(B7:B11), Excel will return “4” because that range contains four numbers total. The COUNT function ignores blank cells and text values. The formula =COUNT(D7:E9) will return “3” because COUNT treats the date, time, and percentage as numbers. A good way to check the actual value in a cell is to temporarily apply the General format....

A boolean is a data type with only two possible values. In Excel, these are the logical values TRUE and FALSE. You’ll notice that Excel treats TRUE and FALSE in a special way. If I type the word “true” in lowercase, Excel automatically capitalizes it. The same thing happens if I type “false”. Note also that Boolean values are automatically centered in the cell. Excel actually has a function called “ISLOGICAL” that will test for the boolean values TRUE and FALSE....

where B5 contains the text to process, things is the named range E5:E9, the delimiter is a comma, and the default value is an empty string (""). See below for a detailed explanation. The first step in creating a custom function with the LAMBDA function is to verify the logic needed to solve the problem. The formula below will do the job and return the result seen in column C: This formula uses four separate functions: TEXTJOIN, FILTER, ISNUMBER, and SEARCH....

The lookup value is a so-called “big text” (sometimes abbreviated “bigtext”) which is intentionally a value “bigger” than any value that will appear in the range. When working with text, which sorts alphabetically, this means a text value that will always appear at the end of the alphabetic sort order. Since this formula matches text, the idea is to construct a lookup value that will never occur in actual text, but will aways be last....

The N function takes a value and returns a number. When given a text value, the N function returns zero. In this case, the primary formula the SUM function, and the N function simply evaluates the comment text and returns zero: The comment is visible in the formula bar when the cell is selected. 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....

In the example, we need to map five text values (statuses) to numeric status codes as follows: Since there is no way to derive the output (i.e. it’s arbitrary), we need to do some kind of lookup. The VLOOKUP function provides an easy way to do this. In the example shown, the formula in F8 is: Although in this case we are mapping text values to numeric outputs, the same formula can handle text to text, or numbers to text....

Fields The pivot table shown is based on three fields: Region, Color, and Sales: Region has been configured as a Row field, Color as a Column field, and Sales is a Value field. Data has been filtered by Region to exclude East: To force the display of items with no data, “Show items with no data” has been enabled on the Layout & Print tab of the Color field settings, as seen below:...

Fields The pivot table shown is based on two fields: Date and Color: The Color field is configured as a row field, and a value field. In the Values area, the Color field has been renamed “Count” and set to summarize by count: The Date field is grouped by Months only: To force display of months with no data, the Date field has “Show items with no data” enabled: Date filter is set to display only desired months:...

Radar charts can be used to plot the performance of employees, athletes, products, and companies in various categories. They can be used for performance evaluations and satisfaction surveys. Pros Compact presentation Can show at-a-glance strength or weakness Can handle more than one data series Cons Unusual chart type may confuse audience More difficult to read for most people Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

where tblData (B5:E15) and tblPoints (G5:H10) are Excel Tables. Background study Excel Tables (overview) What is an Excel Table (3 min. video) Introduction to structured references (3 min. video) How to use INDEX and MATCH (overview) Basic index and match approximate (example) Calculating rank The first part of the problem is to calculate the rank of each score in tblData. This is done with the RANK function like this: This is an example of a structured reference, a special kind of reference that makes formulas that deal with Excel Tables easier to work with....

The formula is a bit convoluted, but the steps are simple. We first count how many spaces exist in the text using LEN and SUBSTITUTE. Next, we use the somewhat obscure instance argument in the SUBSTITUTE function to replace the last space with a tilde (~). Finally, we use FIND to figure out where the tilde is, and the MID function to discard everything after the tilde. Working from the inside-out, we use the LEN and SUBSTITUTE functions to count how many spaces appear in the next: For the text in B5, this returns 6....

In this worksheet, we have a list of names, projects, values, and regions. This data is not sorted. Our goal is to sort the data first by region, then by name, and finally by value, with larger values first. Let’s start with the SORTBY function. The first argument, array, is the data we want to sort, in this case, all data in the range B5:E16. Now, the SORTBY function is designed to sort data by more than one dimension at the same time....

where data (B5:C16) and birthdays (C5:C16) are named ranges. The result is the list of birthdays sorted by month and day, ignoring year. Note: the SORTBY function is new in Excel 365. Below, I explain how to use a helper column to sort birthdays by month and day. To sort the data by month and then by day, ignoring year, we need to construct another array in memory that we can use for sorting....

where data is the named range B5:B14. The result is a new random sort order whenever the worksheet is recalculated. COUNTA counts the values in data, and returns the number 10, which goes into the RANDARRAY function as the rows argument. RANDARRAY returns an array of 10 decimal values like this: This array is provided to the SORTBY function as the by_array1 argument. SORTBY uses the random values to sort the data, and returns the 10 letters into a spill range starting in D5....

which returns 7, the position of the number 3 in the string “apples30”. As with most formulas that split or extract text, the key is to locate the position of the thing you are looking for. Once you have the position, you can use other functions to extract what you need. In this case, we are assuming that numbers and text are combined, and that the number appears after the text....

where data (C5:E16) and group (B5:B16) are named ranges. The result is the sum of values in group “A” for all three months of data. As the formula is copied down, it calculates a sum for each group in column G. The FILTER function is a new function in Excel. See below for alternatives that will work in older versions of Excel. In the latest version of Excel, the FILTER option (#2) is easy and intuitive....

which calculates the surface area of a sphere with the radius given in column B. Units are indicated generically with “u”, and the result is units squared (u2). The Greek letter π (“pi”) represents the ratio of the circumference of a circle to its diameter. In Excel, π is represented in a formula with the PI function, which returns the number 3.14159265358979, accurate to 15 digits: To square a number in Excel, you can use the exponentiation operator (^): Or, you can use the POWER function: Rewriting the formula using Excel’s math operators we get: Or, with the POWER function: For example, with a radius of 5 hardcoded into the formulas we have: and both formulas return the same result....