Google Sheets

In a previous post, I explored how to ignore blank cells when using the QUERY function in Google Sheets, which had its own distinct way of removing blank cells from the data capture. But how do you ignore blank cells in your spreadsheet when you’re not in a QUERY function? What if you’re in one of the many different IF functions such as IF, IFS, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS (etc). How do you write a condition that checks if a cell is blank in those functions? ...

If you’re looking at too many numbers on the screen and they are large enough to be reduced by thousands you can reduce them using a simple custom number format in Google Sheets. A B C D E 1 719,568 947,834 1,028,012 1,075,867 1,426,528 2 61,898 70,544 84,735 96,244 111,498 3 98,370 100,337 110,341 112,221 114,139 4 100,150 118,869 143,510 163,357 198,662 5 979,986 1,237,584 1,366,599 1,447,688 1,850,817 Too many numbers on a spreadsheet can make it difficult to read. ...

The DATEDIF function calculates the number of periods between two dates. The best way to remember this function is it calculates the DATE DIFference between two dates. What Is DATEDIF? The DATEDIF formula calculates the difference between two dates according to a third parameter determining the type of difference needed, for example, days, months, years (etc). The DATEDIF function is a popular formula that has 3 parameters that are all required. If you encounter any error when using this function check you have populated all three fields correctly. ...

Previously I posted how you can use the INDEX() function to obtain the fields needed for a simple SUM() function. Then I came across another handy Google Sheet function ARRAY_CONSTRAIN(). What ARRAY_CONSTRAIN Does There are three parameters with this formula: range – insert the range for the formula to operate on. num_rows – set the number of rows to compress. num_cols – set the number of columns to compress. All are required fields. ...

The QUERY function in Google Sheets is a powerful function that helps to operate on a range of data, however, on a current project I needed the QUERY function to ignore rows where a certain column was empty. Here was how I was able to get the desired output. To ignore blank or empty cells using Google Sheet’s QUERY function add the condition IS NOT NULL in the WHERE clause for the column where the blank or empty cells are found. ...

If you have two or more columns of data and you want to merge these columns into one column then using the Google Sheets QUERY formula may be one way you can achieve this. Here’s an in depth explanation of how to achieve this: Step 1 – Prep your data If you data doesn’t contain any spaces then you’re good to go, if though your data does contain spaces then you will need to define a character at the end of each cell to get this to work. ...

How do you merge multiple columns and then expand them into a different arrangement using Google Sheets? Using a working example I will demonstrate how to migrate a specific data set containing columns, into a different data set using a different arrangement of columns. The final formula is quite the monster and I’ll dissect this piece by piece to help demonstrate the process: = A R R A Y F O R M U L A ( S P L I T ( A R R A Y F O R M U L A ( S U B S T I T U T E ( T R A N S P O S E ( S P L I T ( J O I N ( " " , Q U E R Y ( D a t a C o n c a t ! A : L , , R O W S ( D a t a C o n c a t ! A : A ) ) ) , " " ) ) , " ~ " , " " ) ) , " ; " , T R U E , F A L S E ) ) Original Data Structure I had exported the following salary data from our budgeting software which contained how much each staff person would be receiving each month throughout the financial year. ...

How do you apply the sum to a certain number of cells according to another input cell? The SUM function works amazingly well when the range sought for its total value is static, and one way of making a SUM function somewhat dynamic is to make the cells it references change, but what if you need the SUM function to total a range according to an input cell? Something that would look a little like this in words: ...

Perhaps the easiest way to assign a default value to a variable is to append the || conditional after referencing the variable. For example, after looping through an array and mapping the values to properties within an object, I needed to test whether the property had been assigned. As I use Google Spreadsheets to iterate through data imagine the following data set: A B C 1 Name Age Choice 2 Ryan 50 A 3 Sara 27 4 Jude 29 C Data set containing rows of data, except for one row with a blank “Choice” value. ...

Recently, I had to merge two columns into only one column on a spreadsheet. The way I found to do this was by using the following common spreadsheet functions: JOIN, TRANSPOSE and SPLIT, and if needed UNIQUE. I was able to find a solution, and I’ll illustrate how it worked by using an example. Let’s assume the following columns of data: The first thing we will do is move to the next adjacent column, column C, and in cell C1 enter the following formula: ...