'TRANSPOSE function' category

Get date ranges from a schedule
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
How to sum overlapping time
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]
How to extract numbers from a cell value
What's on this page How to extract numbers from a cell value - Excel 2016 Sort and return unique distinct […]
Count cells containing text from list
Table of Contents Count cells containing text from list Count entries based on date and time 1. Count cells containing […]
This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]
SUMPRODUCT – multiple criteria
This article demonstrates ways to sum values based on criteria. Table of Contents SUMPRODUCT - based on a list of […]
INDEX MATCH with multiple criteria
This article demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions […]
Distribute values across numerical ranges
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
How to extract a case sensitive unique list from a column
This article demonstrates a formula that extracts unique values from a column also considering upper and lower characters (case sensitive). […]
Split search value using a delimiter and search for each substring
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]
Count identical values if they are on the same row
This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]
A beginners guide to Excel array formulas
Array formulas allows you to do advanced calculations not possible with regular formulas.
Count overlapping days across multiple date ranges
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
How to use the TRANSPOSE function
The TRANSPOSE function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]
Sum numerical ranges between two numbers
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
Sort based on frequency row-wise
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]
Count overlapping days in multiple date ranges, part 2
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
Dynamic scoreboard
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
Working with overlapping date ranges
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
How to calculate overlapping time ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Find numbers closest to sum
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]
Working with classic ciphers in Excel
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]
Identify rows of overlapping records
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
Wildcard lookups and include or exclude criteria
This article demonstrates three different ways to filter a data set if a value contains a specific string and if […]
Find earliest and latest overlapping dates in a set of date ranges based on a condition
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
Group rows based on a condition
What's on this page Group rows based on a condition Merge matching rows 1. Group rows based on a condition […]
Excel calendar
Table of Contents Excel monthly calendar - VBA  Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]
Partial match and return multiple adjacent values
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
How to create permutations
I discussed the difference between permutations and combinations in my last post, today I want to talk about two kinds […]
Search each column for a string each and return multiple records – OR logic
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]
Filter unique distinct records case sensitive
This article demonstrates two ways to extract unique and unique distinct rows from a given cell range. The first one […]
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
A Comprehensive Guide to Splitting Text in Excel
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Concatenate cell values
Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]
Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
Extract unique distinct values based on the 4 last characters
Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]
List dates outside specified date ranges
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Match two criteria and return multiple records
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
Perform multiple partial matches and return records – AND logic
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]
Search for a text string in a data set and return multiple records
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
Count unique distinct values
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Partial match for multiple strings – AND logic
This article demonstrates formulas that let you perform partial matches based on multiple strings and return those strings if all […]
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Calculate machine utilization
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
Extract shared values between two columns
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]

Excel formula categories

AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique distinct recordsUnique distinct valuesUnique recordsUnique valuesVlookupVlookup return values

Excel categories