Google Sheets

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 Drop Down List First, create your drop-down list of items by selecting the cell, or range of cells, where the drop-down list will be available. ...

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 range of cells according to the MONTH() of a cell containing a date value. ...

What do you do when you’re running a Google App Script and you encounter the following error in the Execution Log area? W e ' r e s o r r y , a s e r v e r e r r o r o c c u r r e d w h i l e r e a d i n g f r o m s t o r a g e . E r r o r c o d e N O T _ F O U N D . 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. ...

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 data set first, before then moving on to creating the desired phone number format. ...

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 in column B and their hire date in column C. You cannot search by column B to return column A using the VLOOKUP function. ...

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) and the month was an abbreviated month (eg. Jan, Feb, Mar… etc) and even the year was abbreviated too (eg. 2022 = 22, 2023 = 23, etc). ...

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: If the Sort Range option is greyed out it means you haven’t selected a range. Check you have a range highlighted as shown above where I’ve selected cells A1 to B4. ...

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 of the SWITCH function: ...

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

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 Large number in cell The easiest way to change a number being displayed as E+n (where n is a number) is to right-click on the cell, click on the context menu item Format cells and then in the Number tab and the Category section select the option of Number. ...