Miscellaneous Articles: Excel and Google Sheets

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...

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:...

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,...

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...

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,...

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...

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...

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...

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...

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...