Spreadsheets

One of the first formulas I started with when exploring more about the functionality of spreadsheets is the IF formula. The IF formula is easy to understand and contains only three parameters which are all required. The first parameter is the condition to check, the second is the returned value if the condition is true, and the third parameter is the returned value if the condition evaluates to false. = I F ( c o n d i t i o n , v a l u e _ i f _ t r u e , v a l u e _ i f _ f a l s e ) Let’s look at a simple example: ...

How do you check if a cell is empty or blank in Google Sheets? There is a handy function called ISBLANK which enables you to check if a cell is empty. What Does Empty Really Mean? In Google Sheets there are two ways of having an empty cell, one way is by defining an empty string "" and another way is by having nothing in that cell. To check that a cell meets these criteria of being “empty” we use the ISBLANK function, like so: ...

Recently, I had an issue where I needed to calculate the age of a person at specific dates throughout the year. Using a Google Sheet, I thought I could simply subtract one date from the other and divide by 365, but this ended up not being as accurate as I wanted. To calculate somebody’s age at a specific point in time you need their birth date and a comparison date to calculate the age of the person by. Start by subtracting the years from each other, and then if the month and day of the comparison date are before their birth month and date, then subtract one. ...

How do you count a range of cells and exclude counting empty or blank ones in Google Sheets? The easiest approach to count cells that are not blank is to use the COUNTA() function. The COUNTA() function has the following parameters: = C O U N T A ( v a l u e 1 , [ v a l u e 2 . . . ] ) The COUNTA() Google Sheets function takes one or more values and counts those values that do NOT have any content. However, just because a cell may look like it doesn’t contain content it may still be added to the count. ...

Very similar to our previous article on how to make a header row in Google Sheets, in this article we’ll show how you can lock a column in Google Sheets. To lock a column in Google Sheets easily just navigate and click on the View menu, then on the sub-menu Freeze, then click on either of the options presented: “No column” (to remove any locked columns), “1 column” (to lock the first column), “2 columns” (to lock the first two columns), or “Up to current column” (depends on where the current active cell is located). ...

I was recently working with a lot data on my Google Sheets spreadsheet and as I scrolled down the page the information from the top rows moved off and I could no longer see (and could no longer remember) what each column’s label in the first row was. Thankfully there’s a nifty little feature in Google Sheets where you can freeze a set number of rows to lock the screen from moving as you continue to scroll down the page. ...

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