Spreadsheets

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

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: = I F E R R O R ( V L O O K U P ( s e a r c h , r a n g e , c o l u m n _ i d x , m a t c h _ t y p e ) , d e f a u l t _ v a l u e ) Where default_value is the placeholder where you would the data inserted into your spreadsheet if the item is not found in the first column of the range. ...

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 Google Sheets is to use the keyboard shortcut Cmd + U (or Ctrl + U for Windows). If you have selected text within the formula bar and apply this keyboard shortcut while having text highlighted, it will underline just the selected text as shown here: ...

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 speed up the processing of your data, for example when using the VLOOKUP formula. If you can create a column with unique ID’s you could then make the fourth paramter of the VLOOKUP function TRUE which speeds up processing time. ...

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

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 the other is a function. ...

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 and ends with cells closest to Z, or in descending order which does the opposite. ...

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 operators work, here is a sheet labelled Data which will be used as the basis for performing the QUERY: ...

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 that can help. ...

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 parameters. The first search_key is the value to be found in the first row of the range (second parameter), with the index (third parameter) returning the nth cell from the first row. The final parameter is_sorted is not a required field, but if the range is not sorted, set this parameter to FALSE, and it will search for an exact match. ...