Google Sheets

The VLOOKUP formula is a popular function for getting the value from a tabular data set and has 3 required parameters and an optional fourth, and looks something like this: = V L O O K U P ( r e f e r e n c e _ v a l u e , d a t a , r e t u r n i n g _ c o l u m n _ v a l u e , [ i s _ s o r t e d ] ) The first parameter reference_value is the value you are searching for in the first column of your data set (the second parameter). The third parameter returning_column_value is the column from your data set that you wish to return, and the optional fourth parameter is_sorted is a boolean type where if this is true (by default it is) then the returned value from this formula will be its first match, otherwise if FALSE then returns the closest match. ...

Can you use noncontiguous ranges in the data parameter in a QUERY() function? Yes, you can. Google Sheet’s QUERY() function permits the ability to use noncontiguous ranges, but they must be wrapped in set notation with curly braces {}. When applying a filter in the query parameter, you will need to use the ColX notation to reference the specific range according to its order in the set. Recall that the QUERY formula contains a total of three parameters as follows: ...

Merged cells are a great way to span content over multiple cells, and you can easily remove a single merged cell with one click by clicking on the merge cell button, but unfortunately, you cannot apply the same process when trying to unmerge a whole array of rows or columns that contain cells with multiple merges. For example, have a look at the following spreadsheet which contains a multiplicity of rows all containing 3 merged cells: ...

There will come a time when using Google Sheets where you will be checking the value of a cell against multiple criteria, which one is the best to use? There are three handy functions you can use within a single cell to handle multiple conditions: IF, IFS and SWITCH. Let’s examine each formula individually and how they might fit your needed case. IF Formula The IF formula is the simplest and probably one of the first formulas most users of spreadsheets start with. Its a formula that contains three parameters: ...

Recently I had a column of names in a spreadsheet with the following structure: Last Name, First Name and they needed to change to the structure: First Name Last Name. For example, the original structure of someone’s name would be Smith, John and this needed to change to John Smith. So a couple of medications were needed. First, fetch the respective names and identify them positionally according to their place on either side of the comma. Then, remove the comma and concatenate the names using a space character to separate them. ...

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

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