Different methods to create Serial Numbers in Excel

Following are 10 different methods to create a Number Series (1, 2, 3,… 49, 50) in Excel. Excel Fill Handle to create Serial Numbers To create a number series like 5, 6, 7,… up to the number14, type 5 into a cell > 6 into the cell just below it > select these two cells…

Count birthdays of a month

Let’s see how to combine the SUMPRODUCT and TEXT functions in Excel to find out the number of birthdays in a particular month. Formula We have a list of Date of Births in the cells C2:C17. The following formula will return the number of birthdays in the month of July. =SUMPRODUCT(–(TEXT($C$2:$C$17,”MMMM”)=”July”)) Explanation SUMPRODUCT functions multiplies…

Get the First name from a Name

The following is the generic formula to extract the first name from a full name. =LEFT(“Full Name”,SEARCH(” “,”Full Name”)-1) Explanation SEARCH function in the formula will return the position of ” ” (Space character) from the Full name. In the formula used in the cell C3, SEARCH function will return the position of ” ”…

Excel formula to Split data into Rows or Columns

A User Defined Function (UDF) which can split data into multiple Rows or Columns on the basis of the specified delimiter in Excel is explained in this blog post. Watch the video on Excel Formula to split data into Columns or Rows Text to Columns feature is the most commonly used Tool to split data…