All resources related to Miscellaneous for Excel and Google Sheets.

How to Check if a Range Contains One of Many Substrings

We can check if a range of cells contains one of many substrings with the SUMPRODUCT and COUNTIF functions. This article will walk through the process. Figure 1: How to Check if a Range of Cells Contains One of Many Substrings Formula =SUMPRODUCT(COUNTIF(A4:A9,{"*Rome*";"*Giant*";"*Five*"}))>0 Setting up the Data We will input...

How to Sum/Return an Array with the Index Function

We can use the INDEX function to return an ARRAY of values to another function like the SUM function by integrating INDEX with the IF and N functions. We can use this to SUM values that are present in specific cells. This article will walk through the process. Figure 1:...

Random sort formula

While working with Excel, we are able to randomly sort values in a list and display them in a certain order by using the INDEX, MATCH and ROWS functions. This step by step tutorial will assist all levels of Excel users in randomly sorting values using a formula with INDEX,...

Reverse a list or range

After completing a list, it is hard to manually reverse all the data. It takes a lot of time and effort. But in excel, we can do it using three commonly used functions. We can use the INDEX, COUNTA and ROW functions together to reverse a list or string. In...

How to Convert Column Numbers to Letters in Excel

Convert column numbers to letters In this example, we want to input the column number in the first column of the table then allow the second column to show the corresponding Excel column letter. Letter A corresponds to the first column in an Excel worksheet, B to the second column,...

Nth Root of a Number in Excel

There are two ways to find the nth root of any number in Excel. These are using the POWER function or using the caret “ ^ ”. This tutorial will walk through each method. Find the nth root of a number in Excel Figure 1. Sample table showing the nth...

Pad a number with zeros

While working with Excel, we are able to pad a number with zeros by using the TEXT and REPT functions, or by simply changing the format of the number. This step by step tutorial will assist all levels of Excel users in padding a number with zeros in three different...

How to Sort Numbers in Ascending or Descending Order in Excel

There are times when you need to sort your large data in an ascending or descending order. By sorting your data in ascending or descending order, you can quickly find information on your list. You can use the “expanding range” to get this function done effectively. How to sort numbers...

How to Sort Text and Number with a Formula

We can sequentially arrange text and numbers in excel based on ranks assigned to the text and numbers in another cell. We can do this with the COUNTIF, ISTEXT, INDEX, and MATCH Functions. This step by step tutorial will assist all levels of Excel users in sorting text and numbers...

Split payment across months

While working with Excel, we are able to distribute a certain amount across a period of time based on some given conditions by using the AND function. This step by step tutorial will assist all levels of Excel users in splitting payment across months. Figure 1. Final result: Split...