where value (H4) and data (B5:E104) are named ranges. The VLOOKUP function supports wildcards, which makes it possible to perform a partial match on a lookup value. For instance, you can use VLOOKUP to retrieve values from a table based on typing in only part of a lookup value. To use wildcards with VLOOKUP, you must specify exact match mode by providing FALSE or 0 (zero) for the last argument called range_lookup....

Fields The source data contains three fields: Date, Sales, and Color. Only two fields are used to create the pivot table: Date and Color. The Color field has been added as a Row field to group data by color. The Color field has also been added as a Value field, and renamed “Count”: The Date field has been added as a Column field and grouped by month: 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 year....

Fields The source data contains three fields: Date, Sales, and Color. A fourth field, Group is created by the grouping process: Before grouping, the original pivot table looks like this: Manual grouping is done by selecting the cells that make up a group. The control key must be held down to allow non-contiguous selections. With cells selected, right-click and select Group from the menu: Repeat the process with the second group of items, Gold and Silver....

Once you’ve removed extra spaces, you can copy the cells with formulas and paste special elsewhere as “values” to get the final text. Video: How to clean text with TRIM and CLEAN TRIM with CLEAN If you also need to remove line breaks from cells, you can add the CLEAN function like so: The CLEAN function removes a range of non-printing characters, including line breaks, and returns “cleaned” text. The TRIM function then takes over to remove extra spaces and returns the final text....

where “start” is the named range F6, “n” is F4, and “value” is F5. If the date is not greater than the start date, the formula returns zero. If the date is greater than or equal to the start date, the IF function runs this snippet: Inside MOD, the DATEDIF function is used to get the number of months between the start date and the date in B4. When the date in B4 equals the start date, DATEDIF returns zero....

In the example shown, the formula in cell D7 is This tells Excel to take the value in B7 (1999.9985) and round it to the number of digits in cell C7 (2) with a result of 1999.99 Notice that even though the number in the 3rd position to the right of the decimal is 8, it is still rounded down. In the table, the ROUNDDOWN function is used to round the same number (1999....

I have a spreadsheet that contains music data. The table contains columns for Genre, Artist, Album, Year, Song, and Time, but only the first value is filled in, much like an outline. I want to run this data through a pivot table to analyze the music in in different ways, but because the data isn’t well structured… most cells are blank, so the counts in the pivot table summary are off....

First, just to recap, number formats control how a number is displayed, but they have no effect on the number’s value. You can find a list of available number formats on the home tab of the ribbon. Or you can use Control + 1 (command + 1 on a Mac) to see a list of number formats there. Seven of these number formats have dedicated shortcuts, and they all begin with Control + Shift....

To find something in Excel, you can use Control + F on Windows and Command + F on a Mac. Control F also works on a Mac. Once you’ve found something, you can press Return or Enter to “find again”. If you hold down the shift key, you can move through matches in the opposite direction. After you run a find, and the Find dialog is closed. You can repeat the last search with the keyboard shortcut Shift F4 on Windows, and Command G on a Mac....

The result is $18,550, the sum of Amounts in the range C5:C16 when the date in B5:B16 is greater than 15-Oct-2022. Note: for SUMIFS to work correctly, the worksheet must use valid Excel dates. All dates in Excel have a numeric value underneath, and this is what allows SUMIFS to apply the logical criteria described below. SUMIFS function The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria....

which calculates the volume of a sphere with the radius given in column B. Units are indicated generically with “u”, and the result is units cubed (u3). Where r represents radius, and 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 cube 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: The result is the same for both formulas....

Where “holidays” is the named range E3:E13. For example, B4 contains January 1, 2014, but displays only “Jan” per the custom number format. The formula itself is based on the NETWORKDAYS function, which returns the number of working days between a start date and end date, taking into account holidays (if provided). For each month, the start date comes from column B and the end date is calculated with the EOMONTH function like so: EOMONTH takes a date and returns the last day of a month....

All Excel tables are composed of rows and columns, and share a number of common elements. First, there’s the table itself, which is a rectangular range of cells with a unique name. You’ll see the table name on the design tab, and in the name box menu. The data range in a table is dynamic. As rows are added or removed, Excel will keep track of these changes. All tables contain three primary parts - the header row, the data in the table, and the total row....

Sorting with formulas is one of those traditionally hard problems in Excel that new dynamic array formulas have made much easier. In this worksheet, we have a list of names, scores, and groups. Currently the data is not sorted. Our goal is to sort this data in descending order by score. There are two new functions in Excel for sorting: SORT and SORTBY. Either function will work in this case, but we’ll use the SORT function....

No need to enter 1 for num_chars in LEFT, since it will default to 1. The second expression extracts the remaining characters with MID: The text comes from B5, the start number is hardcoded as 2, and num_chars is provided by the LEN function. Technically, we only need to extract (length - 1) characters, but MID won’t complain if we ask for more characters, so we’ve left things in the simplest form....

In this example, a conditional formatting rule is set up to highlight cells in the range C5:G15 when then are greater than the value entered in cell J6. The formula used to create the rule is: The rule is applied to the entire range C5:G15, and the value in J6 can be changed at any time by the user. When a new value is entered, the highlighting is immediately updated....

To extract the time, the formula in D5 is: To assemble a datetime, the formula in E5 is: Date To get the date, we extract the first 10 characters of the value with the LEFT function: The result is text, so to get Excel to interpret as a date, we wrap LEFT in DATEVALUE, which converts the text into a proper Excel date. Time To get the time, we extract 8 characters from the middle of the text with the MID function: Again, the result is text....

The result is 4, since there are four cells in B5:B15 that contain five characters. SUMPRODUCT with LEN One way to solve this problem is to use the SUMPRODUCT function with the LEN function. In the example shown, the formula in E6 is: Working from the inside out, the LEN function is used to get the length of each value in the range like this: Since the range B5:B15 contains 11 cells, LEN returns 11 results in an array like this: Each number in the array is the length of a cell in B5:B15....

Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365. They key to the formula is knowing that the FREQUENCY function gathers numbers into “bins” in a particular way. Each bin represents an upper limit, and contains a count of all numbers in the data set that are less than or equal to the upper limit, and greater than the previous bin number....

which returns 4, since there are 4 unique employee ids in the list. The Excel FREQUENCY function returns a frequency distribution, which is a summary table that shows the frequency of numeric values, organized in “bins”. We use it here as a roundabout way to count unique numeric values. Working from the inside-out, we supply the same set of numbers for both the data array and bins array to FREQUENCY: FREQUENCY returns an array with a count of each numeric value in the range: The result is a bit cryptic, but the meaning is 905 appears four times, 773 appears two times, 801 appears once, and 963 appears three times....