Google Sheets QUERY WHERE IN List: OR and MATCHES Examples
Filter a Google Sheets QUERY by a list of values using OR or MATCHES. Includes fixed lists, cell-driven lists, numbers, exclusions and common errors.
Read guideSpreadsheets
91 guides in this category.
Google Sheets QUERY Errors: Troubleshooting Guide
Fix common Google Sheets QUERY errors including parse errors, NO_COLUMN, mismatched array row size, mixed data types, headers, Col notation and ARRAY_LITERAL.
Read guideGoogle Sheets QUERY: Blank, Null and Empty-String Conditions
Filter blank and non-blank rows in Google Sheets QUERY with is null and is not null, including empty strings, whitespace and formula blanks.
Read guideHow To Ignore Blank And Empty Cells In Google Sheets
Ignore genuinely blank cells, empty strings and whitespace in Google Sheets using FILTER, COUNTIF, LEN, ISBLANK and QUERY.
Read guideJavascript Equivalent of Common Financial Formulas Found In Google Sheets
Continuing my work on RTU assets from yesterday, I found I needed to program some of the common formulas you see in Google Sheets into Javascript to be used in my Suitescript code. These common functions include: PV(rate, number periods, payment amount, future value, end or beginning) FV(rate, number periods, payment amount, present value, end or beginning) PMT(rate, number periods, present value, future value, end or beginning) Each of the…
Read guideHighlight Entire Row When Date Column Is In The Same Month And Year As Today
How do you highlight an entire row in Google Sheets on a table of data where a column in your table containing dates matches the same month and year as today? For this technique, you will need to use the Conditional Formatting section in Google Sheets and a Custom Formula. Here’s the Custom Formula I needed to enter (I’ll explain it underneath so that you can amend it for your…
Read guideHighlight Row When Two Date Cells In Row Have A Date Between Them: Conditional Formatting Example In Google Sheets
How can you highlight a column of dates based upon a condition where comparison is needed with the current column of dates to another column containing dates? I had a requirement where I needed to compare a column of dates to another column of dates and if a specific date was between those dates to highlight one of the columns. For example, I needed to highlight a cell when the…
Read guideRandomise Range In Google Sheets In 2 Simple Steps (No Plugins Or Script Needed)
How can you randomise a range in Google Sheets without needing a plugin or Google App Script? To randomise a range, simply use the RAND() formula in a column to set the random numbers for each element in your range, and then with the INDEX() and RANK() formulas combined, produce the new random range. Here’s an example of what this looks like in a Google Sheet spreadsheet starting with the…
Read guideNot Null In Google Sheets
Test and filter non-empty Google Sheets cells using ISBLANK, LEN, FILTER, COUNTIF and QUERY is not null, with empty-string caveats.
Read guideHow To Use Google Sheets QUERY SELECT & WHERE With Examples
Are you looking to learn how to use the Google Sheets QUERY function to select and filter data based on specific conditions? In this blog post, you’ll explore the ins and outs of the powerful QUERY function, particularly focusing on SELECT and WHERE clauses, to help you get the most out of your Google Sheets experience. In the following sections, I’ll start with an introduction to Google Sheets and its…
Read guideWhat Are Sheets in Excel? A Clear Explanation for Beginners
As someone who has been using Excel for several years, I know how important it is to understand the basics of the program. One of the most fundamental elements of Excel is the sheet, which is essentially a grid of cells that can be filled with data. Sheets in Excel are used to organise and manipulate data in a variety of ways. Each sheet can contain a vast amount of…
Read guideHow to Sort Data In Excel: Single Column & Multi-Column
Sorting data in Excel is a fundamental skill that can help you better analyse and understand your data. Whether you are working with a small or large dataset, sorting can help you quickly identify patterns, trends, and outliers. Excel provides several options for sorting data, depending on your specific needs. You can sort data by one or more columns, in ascending or descending order, and even customise the sort order…
Read guideWhat Are Cells in Excel: A Simple Explanation
What is a cell in Excel? Once you dive into learning Excel it doesn’t take long before you discover a whole new world with its own language! And cells are a frequent term used, so what is it? Simply put, cells are the individual unit of an Excel grid spreadsheet. Each cell can contain a piece of data, such as a number, text, or formula, and can be formatted in…
Read guideAbsolute Cells in Excel: Explained With Images
A visual Excel-specific guide to locking cells with $A$1, mixed references and the F4 shortcut when copying formulas.
Read guideDifference Between HLOOKUP And VLOOKUP In Excel
What’s the difference between the HLOOKUP and VLOOKUP formulas in Excel? While both functions are used to look up and retrieve data from a table, they operate in slightly different ways. HLOOKUP stands for “Horizontal Lookup,” while VLOOKUP stands for “Vertical Lookup.” The main difference between the two is the direction in which they search for data. HLOOKUP searches horizontally (from left to right) across the first row in the…
Read guideExcel HLOOKUP Examples With Tips And Tricks
How do you use the HLOOKUP function in Excel properly and effectively? As someone who frequently uses Excel, I understand how important it is to master various functions and features to enhance productivity and effectiveness. One such function that can be incredibly useful when working with large data sets is the HLOOKUP function. In this article, I’ll share a few examples to demonstrate how this function can improve your data…
Read guideWhy Is My VLOOKUP Remaining as a Formula? Troubleshooting
Are you struggling with your VLOOKUP formula staying as a formula instead of returning the desired value? If so, you are not alone, and the solution is relatively simple. One common reason VLOOKUP stays as a formula is due to the formatting of the cells. If the cells are formatted as text , Excel will treat the VLOOKUP formula as text and not perform the lookup. To fix this issue,…
Read guideHow to VLOOKUP Across Multiple Sheets in Excel: Examples Included
Do you have multiple sheets in Excel and need to find and extract data quickly? VLOOKUP is a powerful function in Excel that allows you to search for and retrieve data from a specific column in a table. However, when you have multiple sheets, using VLOOKUP can be a bit tricky. In this article, we will show you how to use VLOOKUP across multiple sheets in Excel with examples. When…
Read guideExcel SUMIF: How to Sum Cells Starting with a Letter
If you are an Excel user, you are likely familiar with the SUMIF function. However, did you know that you can use SUMIF to add up values based on whether a cell starts with a specific letter? This can be a useful tool for quickly calculating totals for a specific category or group. The SUMIF function in Excel allows you to add values in a range of cells that meet…
Read guideSum Only Positive Numbers in Excel: SUMIF & SUMIFS Examples
If you’re working with a large dataset in Excel, you might need to sum only the positive numbers. This can be a tricky task, especially if you have a lot of data to work with. Fortunately, Excel has a built-in function that makes it easy to sum only positive numbers. The SUMIF function in Excel allows you to sum only the cells that meet certain criteria. In this case, you…
Read guideExcel SUM Function Not Adding Correctly: Troubleshooting Tips
What do you do when you use the SUM function and it doesn’t add up the values correctly? If you are a regular user of Microsoft Excel, you may have encountered an issue where the sum function does not add up correctly. This can be a frustrating issue, especially when you are dealing with large amounts of data and need to ensure accuracy. The sum function is one of the…
Read guideHow to Change an Excel Formula: A Simple Guide for Beginners
If you’re working with Excel, you’ll likely find yourself needing to change formulas from time to time. Whether you’re fixing an error or updating a calculation, it’s important to know how to make changes without disrupting your spreadsheet. Changing an Excel formula is a simple process that can be done in just a few steps. First, you’ll need to select the cell containing the formula you want to change. From…
Read guideCalculate Duration In Years And Months: Excel
One of the most useful features of Excel is its ability to calculate durations in years and months. This can be particularly helpful when you need to work with data that involves time, such as project timelines or financial data. Calculating durations in years and months is a relatively simple process in Excel. By using the DATEDIF function, you can easily calculate the number of years, months, and days between…
Read guideHow To Build A Formula in Excel
If you work with data, you’ll likely need to use Excel’s formulas. Formulas are equations that perform calculations on values in your worksheet. They can help you automatically calculate values, manipulate data, and make your work more efficient. In this article, we’ll explore how to build formulas in Excel. Excel has hundreds of built-in functions that you can use to create formulas. These functions are organised into categories such as…
Read guideIF Formulas in Excel: A Beginner's Guide
Microsoft Excel is a powerful tool that is widely used for organising, analysing, and presenting data. One of the most useful features of Excel is the ability to create formulas that automate calculations based on the data in your spreadsheet. One of the most commonly used formulas in Excel is the IF formula, which allows you to perform different calculations based on whether a certain condition is met or not.…
Read guideMost Used Excel Formulas: Boost Your Productivity With These Essential Functions
Microsoft Excel is a powerful tool that is used by millions of people worldwide. It is a spreadsheet program that allows users to organise, manipulate, and analyse data. One of the most important features of Excel is its ability to perform calculations using formulas. Excel formulas are a set of instructions that tell Excel what to do with the data in a particular cell or range of cells. There are…
Read guideHow to Find Data in Excel: A Step-by-Step Guide
If you work with large amounts of data in Excel, you know how important it is to be able to quickly and easily find specific information. Luckily, Excel has a built-in Find feature that makes it easy to do just that. Whether you need to find a specific word or number or you need to locate a certain cell or range of cells, the Find feature can help. To use…
Read guideMicrosoft Excel Quit Unexpectedly: Troubleshooting Tips and Solutions
What happens when your favourite software, Microsoft Excel, crashes? Sometimes Excel may quit unexpectedly, which can be frustrating for users who rely on the program for their work. This issue can occur for various reasons, including software conflicts, corrupted files, or outdated versions of Excel. If Excel quits unexpectedly, it can result in loss of unsaved data, disruption of work, and wasted time. This issue can occur at any time,…
Read guideHow to Improve at Excel: Tips and Tricks for Mastering Spreadsheets
Microsoft Excel is a powerful tool that can be used for a variety of tasks, from simple calculations to complex data analysis. However, many people struggle to use Excel effectively, often because they lack the necessary skills and knowledge. Fortunately, there are several ways to improve your Excel skills and become more proficient in using this valuable tool. One of the most important things you can do to improve your…
Read guideWhen To Use $ In Excel: A Quick Guide
When it comes to using Excel, there are many different functions and formulas that can be used to make calculations and organise data. One of the most commonly used symbols in Excel is the dollar sign ( $ ), which is used to indicate absolute references . Absolute references are a way of locking a cell reference in place so that it doesn’t change when you copy or fill a…
Read guideWhy Does Excel Use # Characters in Cells?
If you’ve ever worked with Microsoft Excel, you may have noticed the “ ” character appearing in a cell. This can be frustrating, especially if you’re not sure what it means or how to fix it. Fortunately, understanding why this happens can help you avoid this issue in the future and work more efficiently with your data. The “ ” character typically appears in a cell when the data in…
Read guideWhy Learn Excel?
Excel is a powerful spreadsheet software that has been around for decades. It has become an essential tool for businesses, students, and individuals alike. One of the reasons for its popularity is the vast range of features it offers. However, some users may wonder why Excel has certain features that they may not use or understand. One reason for the various features in Excel is to make it a versatile…
Read guideGoogle Sheets Named Functions In 5 Minutes
What is a named function in Google Sheets and when is it best to use this new feature in your spreadsheet? Google Sheets new Named Functions feature enables you to refactor long formulas into what appears as a native function in your Google Sheets spreadsheet. Use this feature in Google Sheets if you find you are using a complex formula more than once within your Google Sheets. A recent need…
Read guideGoogle Sheets SWITCH Formula Example: Refactor IF Functions By 20%
How do you use the Google Sheets SWITCH() formula? The SWITCH() formula in Google Sheets enables you to compress a series of IF statements, even nested IF statements, into one succinct function. Take a recent example where I refactored the following formula which helped to add the appropriate years, months or days to an existing date. Here was the original formula which would calculate the next date according to an…
Read guideHow To Set A Default Value For VLOOKUP
How can you set a default value when using the VLOOKUP function? As VLOOKUP throws an N/A error when the searched item cannot be found in the first column range, wrap the VLOOKUP function in an IFERROR formula and set the value to the default sought. For example like this: Where default value is the placeholder where you would the data inserted into your spreadsheet if the item is not…
Read guide3 Different Ways To Underline In Google Sheets: Shortcuts, Borders & Formulas
How many ways can you underline in Google Sheets? There are three broad approaches when seeking to emphasise text in Google Sheets by applying underlines. These three approaches are: underlying specific or whole text in the cell, underlying the whole cell itself with different styles, or using underscore characters. Here is each approach with examples below: Underline Specific Text The most common approach to underlying text in a cell in…
Read guideCreate Unique ID With Dates: Google Sheets
How can you create a unique ID with date values in Google Sheets? To create a unique ID of date values in Google Sheets use the TEXT() function to change the date into a string and then append any other useful identifier to that string to make the value unique, such as ROW() or a counter such as COUNTIFS() . Creating a unique ID for your data rows can help…
Read guide5 Easy Ways To Fix Google Sheets When It Is Slow
How do you fix Google Sheets when it is running slow? The five ways to speed up working in Google Sheets when it is running slow are: filter your data when working on specific rows, convert formulas to values where possible, sort your data if you are working with functions like VLOOKUP , create a unique column ID to help find data quicker, and look at alternative functions instead of…
Read guide3 Ways To Wrap Text In Google Sheets
How can you wrap text in a cell in Google Sheets? There are 3 ways you can wrap text in a cell in Google Sheets. The most popular method is to click on the Google Sheets wrap icon, whereas the two other two methods are more manual and require you to enter the line break for the cell – of the two manual methods, one is a keyboard shortcut, and…
Read guide5 Ways To Alphabetize In Google Sheets (Screenshots)
How can you alphabetize or sort your data in Google Sheets? There are 5 ways to alphabetize data in Google Sheets: two approaches involve using formulas; namely, the SORT() and QUERY() functions, and the other three approaches involve using the menu items located in the menu bar. All approaches require knowing if the sorting will be done in ascending order, where your data starts from those cells closest to A…
Read guide3 Ways To Use Multiple Criteria In Google Sheets QUERY Function (Examples)
Can you use multiple criteria to filter data in Google Sheets using the QUERY function? Within the query parameter of the QUERY function the WHERE clause enables users to filter data based on multiple criteria. The three types of logical operators permitted when combining multiple criteria are AND , OR and NOT . Here are some examples demonstrating each of the logical operators, and to assist in demonstrating how these…
Read guideGoogle Sheets: 3 Ways To Reference Data In Another Sheet (Examples)
How do you reference data in another sheet using Google Sheets? If you need to reference data in the same Google Sheet, there are two means: the sheet reference syntax using the name of the sheet followed by an exclamation mark and the range (i.e. "Sheet2!A1" ), or the function called INDIRECT . If you need to reference data in an external Google Sheet, there is a function called IMPORTRANGE…
Read guideGoogle Sheets HLOOKUP Function With Examples
How do you use the HLOOKUP function in Google Sheets, and what are some best use cases? The HLOOKUP function searches for data in the first row of a range and returns a specific nth cell in the column found. HLOOKUP is an excellent function to use in data sets where the primary search needs to be performed on data contained in the first row. The HLOOKUP function contains three…
Read guideHow To Change Aggregate Name In Google Sheet QUERY Function (Examples)
How do you change the default aggregate name created in Google Sheets when using the QUERY() function? To change the header label of an aggregate column from a QUERY() function append LABEL aggregate column 'YOUR LABEL' to your SELECT statement. For example, if you had the following QUERY formula in your Google Sheet and you wanted to change the default label of sum(Sales Qty) to Total Sold then this is…
Read guideHow To Aggregate Data From Multiple Sheets With QUERY In Google Sheets
How can you aggregate data using multiple sheets with the QUERY function in Google Sheets? To aggregate data sourced from multiple sheets, create a data set using the set notation {} by referencing each sheet then within the query statement of the QUERY function reference columns using ColX (with X being the index number of the column, starting at 1). As the QUERY function contains three parameters, the first parameter…
Read guideConcatenate Multiple Ranges Into One And Filter Using QUERY: Google Sheets (Example)
How do you concatenate two ranges into one contiguous range for use in the QUERY function for the data parameter in Google Sheets? To concatenate two ranges into one for use as the first parameter in the QUERY function in Google Sheets, simply combine your data sets together using the set notation {} and the semi-colon character to separate each range ; e.g. {{Data!A:A, Data!B:B};{Data!A:A, Data!C:C}} . For example, suppose…
Read guideGoogle Sheets QUERY Function WHERE Reference To Cell Value: Examples
How do you reference a cell in the WHERE clause of a Google Sheets QUERY function? To reference a cell in the Google Sheets’ QUERY function WHERE clause, simply break the query string by closing with a double-quoted string " append the concatenation symbol & then reference the cell append the & to open up the query string again " so you can continue writing the rest of your query…
Read guideConditional Formatting With Checkboxes In Google Sheets
How can you apply conditional formatting on checkbox cells in Google Sheets? Conditional formatting in Google Sheets can be applied to a range of checkboxes by applying the conditional formatting condition of Is equal to to TRUE . Here’s an example demonstrating how to set a conditional format on checkboxes. Create Checkbox Range To create a range of checkboxes, select your range and then click on the Data Validation menu…
Read guideHow To Highlight Duplicates In A Range: Google Sheets Conditional Formatting
How do you highlight duplicates in a defined range using conditional formatting in Google Sheets? To highlight cells that are the same value in a range, select the range and use a custom formula in the conditional formatting area that uses relative referencing. The custom formula you will want to insert into the conditional formatting area is: Where range is the same highlighted range of the conditional formatting range. Here’s…
Read guideREGEXEXTRACT Date From String In Google Sheets
How can you extract a date from a string in a cell in Google Sheets using the powerful REGEXEXTRACT() function? The REGEXEXTRACT(text, regular expression) function has two parameters with the first labelled as text being the string operated on and the second labelled as regular expression being the regular expression (using RE2 syntax) to extract data from. Here are a few popular examples of how you can use this powerful…
Read guideApply Conditional Formatting To Entire Row: Google Sheets
How can you highlight an entire row based on a single condition in another column? To highlight an entire row based on a value in a column using conditional formatting requires using the INDIRECT() formula. A spreadsheet contains the following simple data where the first column contains a list of dates and the other columns contain corresponding data for that date. Here’s a snapshot of the spreadsheet which contains Date…
Read guideWhat Does $A$1 Mean in Excel or Google Sheets?
In Excel and Google Sheets, $A$1 is an absolute cell reference: both column A and row 1 remain fixed when a formula is copied.
Read guideWhat Does $A$1 Mean? Absolute References in Excel and Google Sheets
Understand what $A$1, $A1 and A$1 mean and how absolute and mixed references behave when formulas are copied in Excel and Google Sheets.
Read guideHow To Delete App Script File And Project From Google Sheets
How do delete an attached Google App Script file and project from a Google Sheet? I recently wanted to copy a Google Sheet and as I did I noticed it had an attached Google App Script file. Here’s what that dialog window displayed: Copying a Google Sheet will bring with it it’s App Scripts However, I didn’t want the adjoining scripts with the copy, in fact I wanted to delete…
Read guideConditional Formatting With Custom Formula Using Relative References
How can you apply conditional formatting using a custom formula that contains a relative reference to an adjacent row or column in Google Sheets? If you want to highlight a cell in Google Sheets using conditional formatting based on the condition of a nearby cell you can easily do so by using the Custom Formula feature along with the INDIRECT() formula that contains a relative reference. The INDIRECT(cell reference, is…
Read guideGoogle Sheets Multi Select Dropdown List
Can a Google Sheets drop-down list in a cell allow you to do multiple select? Google Sheets doesn’t natively support the ability to select more than one item on a drop-down list in a cell, but there is a way where you can click an item in the drop-down list and have the clicked item populate the active cell. Here’s an example of what this would look like: Create Your…
Read guideHow To Make An Email Address Clickable In Excel
How do you make an email address in an Excel spreadsheet clickable? To make an email address clickable in a spreadsheet check the format of the cell is not set to text, if not when entering an email address Excel should automatically detect that it is an email address and add the mailto: hyperlink automatically. If after checking the cell is not set to Text and even after entering a…
Read guideSet Conditional Format Based On Another Cell Value In Google Sheets
How can you create a conditional format in Google Sheets based on the value from another cell or column regardless of whether that cell is on the same sheet or another? To reference any cell or column in the Custom Formula field in Google Sheets’ conditional formatting section use the INDIRECT() function referencing that cell or adjacent column. Recently I had a simple requirement where I wanted to highlight a…
Read guideWe're sorry, a server error occurred while reading from storage. Error code NOT_FOUND
What do you do when you’re running a Google App Script and you encounter the following error in the Execution Log area? Check the libraries you have attached to your project on the left-hand side and see if they are correctly referencing the versions you need from them to operate your code properly. Otherwise, if everything is working well there then go to your IDE and check the appscript.json file.…
Read guideHow To Clean And Format Phone Numbers In Google Sheets
If you’re working with data in Google Sheets you’ll soon come across a time when you will need to clean and format phone number data entries. To clean phone numbers in Google Sheets using the REGEXEXTRACT() function extract all the different fields according to the phone number entries and then combine them all into the desired format. Here’s an example walking step by step through the process of cleaning the…
Read guide5 Alternatives To VLOOKUP Function: How To Get Values To The Left Of VLOOKUP Range
How do you get the value from a column that is to the left of a VLOOKUP range? The VLOOKUP function is a powerful function that enables you to capture the value from a range, provided the value is to the right of the lookup range. For example, suppose you have the following data in your spreadsheet that contains the employee ID in column A, the name of the employee…
Read guideHow To Format Text Like 1st Jan 22 To A Date In Google Sheets
How do you format a cell that contains text like 1st Jan 2022 into a cell that Google Sheets can recognize as a date cell? There are certain types of date formats that can prevent the automatic import of data into Google Sheets into dates. One recent data type I had to deal with was where the day field was an abbreviated ordinal number (i.e. 1st, 2nd, 3rd, 4th… etc)…
Read guideHow To Sort By Date In Google Sheets And What To Do When It Doesn't Work
How do you sort a range containing dates in Google Sheets? Sorting a data set in columns or rows containing date data in Google Sheets can easily be done by highlighting the range and then clicking on Data > Sort Range > Advanced range sorting options , as shown below: To sort a range highlight first, then click Data, then Sort Range, then Advanced range sorting options If the Sort…
Read guideHow To Use The SWITCH Function In Google Sheets?
The SWITCH() function in Google Sheets is handy when dealing with multiple criteria based on a single result. The SWITCH() function takes at least 3 parameters, with the first parameter being the value to evaluate, the second and third parameter representing a pair of combined cases and values. There’s also an optional final parameter that acts as the default result if no cases are satisfied. Here is a simple demonstration…
Read guideAdd Formula In Excel: Examples
What is the formula to add in Excel? There are two popular ways to add numbers together in Excel: using the plus sign + or using the SUM formula. Use the + sign where values being added are manually entered, and use SUM when referencing specific cell values. Here are some examples demonstrating the use of each type: When To Use + (Plus) Sign In a spreadsheet the plus sign…
Read guideHow To Separate Characters Into Cells In Excel
If a cell contains words it can be easy to split these into individual cells using the Text to columns feature in Excel. Simply select the cells you want to split into multiple columns, navigate to the Data menu then click on the Text to Columns button. From this Text Wizard window select Delimited width (click Next ), then set the delimiter type to Space (click Next ) then click…
Read guideHow Do You Type A + (Plus) In Excel?
When you start entering text into a cell and the first character of that cell is a plus symbol ( + ) you will get an error NAME? and you would have noticed the cell changed to =+A . So how can you just display a cell with a plus sign? A --- --- 1 \ NAME? =+A {.spreadsheet} Result after starting cell with + To display a cell that…
Read guideHow To Get Rid Of E+(Number) In Excel
When using large numbers in Excel or any other spreadsheet application, such as Google Sheets, some cells may display a number in scientific notation like 9.991E+35 . How do you get rid of that E+n bit, where n is some number, in the cell? A --- --- 1 9.997E+11 =9999^3 {.spreadsheet} Large number in cell The easiest way to change a number being displayed as E+n (where n is a…
Read guideWhy Does VLOOKUP Return #N/A When Value Exists? (Examples)
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: 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…
Read guideHow To Use Noncontiguous Ranges In QUERY Function: Google Sheets
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…
Read guideHow To Unmerge Multiple Cells With One Click: Google Sheets
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 un merge 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…
Read guideHow Do You Put Multiple Conditions In One Cell In Google Sheets?
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…
Read guideReverse Last Name, First Name With Comma Using One Formula: Google Sheets
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…
Read guideGoogle Sheets: IF Statement Multiple Conditions
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. Let’s…
Read guideHow To Check If Cell Is Empty (Or Not): Google Sheets
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…
Read guideWhat Is The Formula To Calculate Age?
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…
Read guideGoogle Sheets Count If Not Blank: Best Approach
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: 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…
Read guideHow To Lock A Column In Google Sheets
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…
Read guideHow To Make A Header Row In Google Sheets
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…
Read guideIgnore Blank Cells In IF Functions 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…
Read guideHow To Format Numbers In Thousands (K) In Google Sheets
Show Google Sheets numbers in thousands (k) or millions (m) with a custom number format code, including rounding, decimals and negative values.
Read guideCalculate Days Between Two Dates In Google Sheets: DATEDIF Formula
The DATEDIF function calculates the number of periods between two dates. The best way to remember this function is it calculates the DATE DIF ference 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…
Read guideFlip ARRAY_CONSTRAIN Formula in Google Sheets (Example)
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…
Read guideIgnore Blank Cells in QUERY Function Google Sheets
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…
Read guideMerge Two Columns Using QUERY: Google Sheets (Step By Step Example)
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…
Read guideGoogle Sheets QUERY: Merge Multiple Columns - Expand Into New Structure (Example)
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: Original Data Structure I had exported the…
Read guideHow To Sum A Dynamic Range: Google Sheets (Example)
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…
Read guideHow To Fix Invalid Property Assignment (Error 450)
If you are testing the return value of a function in the immediate window in VBA and get the following error: Wrong number of arguments or invalid property assignment (Error 450) What you are doing is something like this: Then in the immediate window typing: The code appears to work fine, but the error is a mystery. The reason for the error is that the immediate window call expects a…
Read guideGoogle Apps Script: Set Value Of Blank Cell
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…
Read guideHow To Merge Two Columns Into One (Google Sheets)
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: Appending two columns of data, our…
Read guide3 Ways To Make VLOOKUP Dynamic In Google Sheets (Examples)
How do you make the range and column index number in a VLOOKUP function in Google Sheets dynamic? The VLOOKUP function is a popular formula used in spreadsheets to source data using the first column of a range as the primary key to search the search key and then to return the intersection of the cell in that row with column index number . Here is the syntax of the…
Read guide