When you write a formula in apps like Microsoft Excel or Google Sheets, the last thing you expect to see is the Formula Parse Error message pop-up. Whether you’re an experienced user or completely new to using Google Sheets and formulas, you may come across this error.

In this tutorial, we’ll talk about what Google Sheet Formula Parse Error means, the most common types of formula parse errors, and how to fix them. We’ll go through the step-by-step instructions on how to fix these errors, as well as how to avoid them in the future.

What’s a Formula Parse Error in Google Sheets

First, let’s break down the terms:

• Formula – Instructions entered into the function bar in Google Sheets.
• Parse – Breaking down something into smaller parts to understand it better. It’s like reading a sentence word by word to grasp its meaning.
• Error – A problem occurs when Google Sheets can’t comprehend or execute the requested function.

A formula parse error happens when Google Sheets can’t grasp your instructions. To fix this error, review your formula or function’s structure and wording. It happens when you write a formula, but the software doesn’t understand what you’re asking it to do. In other words, it’s like talking in a language the software doesn’t know.

This error occurs mainly because of two reasons: you might have made a mistake in the formula, like a spelling error, or the order of steps in the formula is confusing for the software.

To sum up, there are several reasons why you may be seeing a formula parse error in your Google Sheets document:

• Missing input values. When required values for the formula are absent.
• Out-of-bounds input. Such as inputting a negative number into a square root formula.
• Mismatched data type input. When the data type doesn’t align with what the formula requires.
• Invalid reference. Referring to a cell or range that doesn’t exist.
• Misspelled function name. Incorrectly typing the name of a function.
• Invalid numerical values. Providing improper or invalid numeric data.

Sometimes, the issue might be unidentifiable by Google Sheets. However, the platform generally tries to diagnose the cause of the formula parse error.

We’ll delve into each specific error, providing interpretations and solutions for each.

How to Fix Formula Parse Errors

Formula parse errors in Google Sheets almost always stem from formula syntax issues. Syntax refers to the rules defining a language’s structure. Use the checklist below to pinpoint the cause of your formula parse error:

1. Check for syntax errors. Review your function for misspellings, typos, or incorrect syntaxes that might be present.

2. Look for incomplete syntax. Ensure that your function contains all necessary elements like quotation marks, closing brackets, or parentheses. Additionally, check for operators placed together without appropriately separating characters like commas.

3. Verify value types. Sometimes, a function requires a specific type of value (e.g., a number or text). If you input a number where text is expected, or vice versa, it could trigger a formula parse error.

4. Confirm the order of operations. Check if you mixed up the order of operations. In complex functions with multiple mathematical operators, stick to Google Sheets’ order of operations: operations enclosed within parentheses, then exponential calculations, then multiplication and division (in their order of appearance), and addition and subtraction (in their order of appearance).

The Most Common Formula Parse Errors in Google Sheets

When you see a formula parse error warning message, it means Google Sheets can’t follow the instructions in your formula. These errors can vary, from simple mistakes like forgetting a comma to more complex issues like trying to do impossible math, such as dividing by zero.

Below you’ll find some examples of these errors so you can recognize and fix them in your formulas.

1. #N/A Error

What it means: This error arises from lookup functions, like VLOOKUP or INDEX, when the desired value is not available in the specified range. It doesn’t necessarily indicate an error but rather the absence of the sought-after information.

Encountering an #N/A error in Google Sheets can stem from various reasons, like missing values in a selected range, or missing input values. When you hover your cursor over the cell containing the error, you’ll see a message where Google Sheets gives you more details of what caused it. After you identify the part causing the problem, the error becomes easy to fix.

How to fix it: This error message tells you that the value you’re looking for is unavailable. This is most common when you’re using lookup functions like VLOOKUP, HLOOKUP, QUERY, INDEX, or MATCH.

To fix it, use the IFERROR function by wrapping your formula in the IFERROR function to get a custom message or value when the data you want is not found.

2. #DIV/0! Error

What it means: There are two reasons why you might get a #DIV/0! error. It appears when you attempt to divide a value by zero (which can occur when you have a zero or a blank cell reference in the denominator), an invalid mathematical operation, or when you try to divide with a blank space. Depending on the error message you see, you’ll understand the context you need to correct your formula.

To tackle this type of error, use the IFERROR function to specify an alternate output when division by zero occurs.

How to fix it: Wrap your Google Sheets formula with the IFERROR function to handle instances where division by zero happens, displaying a specific message or value instead.

3. #NUM! Error

What it means: This error signifies that Google Sheets cannot compute or display the requested value, often due to excessively large calculations or instances where the requested results surpass available data.

Simply speaking, this error occurs when your formula contains an invalid number. This number might be too large to be displayed within the scope of Google Sheets, or this number may be invalid because it’s not the type of number the syntax calls for (for example, you put a decimal number in a place where a whole number is required). One classic example is trying to find the square root of a negative number, which isn’t allowed.

When hovering over the cell containing the error, you’ll see a message that provides you with the location of the number that can’t be displayed correctly.

How to fix it: Check the formula for problematic calculations causing the error. This might include situations with extremely large numbers or requesting more items than are present in the dataset.

4. #NAME? Error

What it means: This error appears when referencing an unrecognized named entity, such as misspelling a built-in function or using a nonexistent named range. The error refers to an invalid name that exists somewhere in your formula. Maybe you’ve tried to reference a cell range named “tech tips” by typing “tech tip” – that would cause this error to appear.

You’ll also get the same name error if you miss quotation marks (or double quotes) by mistake. These errors don’t always give you additional context, so you’ll have to thoroughly check your formula to find the place where you’ve made a mistake.

How to fix it: Review your formula for any incorrectly spelled names or incorrect usage of named objects.

5. #REF! Error

What it means: This error results from invalid references, like deleted cells or named ranges used in your formula. It may also occur due to circular references or when copying formulas with relative references. Here’s an example of making an invalid cell reference: when you referenced a cell range of B2:F2 in your SUM function, but you deleted your original column C, making your SUM function appear in column F. In this case, Google Sheets will throw a #REF! error to let you know it doesn’t have a valid reference.

You’ll also get a #REF! error when a circular dependency is detected (when the formula refers to itself).

The reference error message normally contains more information that you can use to adjust your function to include the correct cell reference and fix the error.

How to fix it: Edit the formula to replace invalid references with valid ones or rectify circular reference issues.

6. #VALUE! Error

What it means: This type of error message occurs when the provided data type in the formula doesn’t match the expected data type. You’ll see this error in your Google Sheets formula when this formula contains the wrong type of value. For example, if you used a multiplication operation on one cell with a number and another cell with text. The operation expects both cells to hold numbers, resulting in a #VALUE error.

Hover over the red arrow to see the error message which will specify which parameter of the formula caused the mismatch.

How to fix it: Click on the cell with the error to view the complete message, identify the parameter causing the data type mismatch, and fix it. Understanding and rectifying these common errors in Google Sheets can improve your formula accuracy and ensure effective spreadsheet functionality.

7. #ERROR! Error

What it means: This error arises when Google Sheets cannot interpret your formula due to an unexpected typo or an extra/missing character. For instance, omitting quotation marks around text values or using an incorrect operator sequence can trigger this error.

This error doesn’t provide you with any information. When you click on the red arrow in your problematic cell, it will simply say Formula parse error.

How to fix it: To resolve this error, carefully check your formula for any missing or superfluous characters, including spaces, punctuation, or alphanumeric elements. Rectify any inaccuracies, such as missing quotation marks, to resolve this error.

Tips on How to Avoid Formula Parse Errors

By employing the following practices, you can minimize formula parse errors and streamline your experience while working in Google Sheets.

1. Check and Review

The most common reason for formula parse errors is typographical mistakes in the formula. To reduce these errors, ensure accuracy in your syntax and numerical inputs.

2. International Considerations

For international Google Sheets, keep an eye on regional differences. Some versions may use semi-colons (;) instead of commas (,). Be mindful of this distinction when copying and pasting formulas between sheets from different locales.

3. Cell Highlighting

Double-clicking a cell containing an error-ridden formula will highlight the problematic segments in red. This highlighting feature is a quick visual cue that can help pinpoint where the formula needs correction.

4. Using Formula Suggestions and Help

Google Sheets provides built-in assistance for functions and formulas. Take advantage of this support by accessing formula suggestions and help. It offers guidance on syntax, expected value types, usage examples, and additional resources to enhance your understanding.

5. IFERROR Function for Custom Messages

Certain errors like #N/A or #DIV/0! might occur in specific scenarios. You can employ the IFERROR function to customize the message displayed when such errors arise, allowing for clearer communication within your spreadsheet.

6. Using Formula Help

When working with built-in functions in Google Sheets, leverage the available formula help. Access instructions, syntax suggestions, expected value types, examples, and additional guidance by double-clicking within the function or clicking on the vertical ellipsis in the bottom-right corner.

No More Formula Parse Errors in Google Sheets

Now that you know what formula parse errors look like and what they mean, you can successfully avoid them in your formulas and enjoy using Google Sheets without the headache of fixing these errors.

The post How to Fix Google Sheets Formula Parse Errors first appeared on Online Tech Tips.

Because there are several types of validation rules you can apply to your dataset, let’s walk through each one individually so you can pick the one that fits best.

Validate Text

With the text validation feature in Google Sheets, you can check for entries that contain, don’t contain, or are the same as specific text. You can also validate the cell for an email address or URL.

For example, we’ll validate the text in our cell to make sure it contains “ID” for our product numbers.

Select the cell or range and use Data > Data validation to open the sidebar and pick Add rule.

Apply to range: Confirm the cell or range and use the icon on the right to add another cell range. Note: As shown below, you should use the sheet (tab) name with the cell reference or data range. Our sheet name is Dates.

Criteria: Pick the text option you want to use and enter the text in the field beneath. For our example, we choose “Text contains” and enter “ID.”

Advanced options: To show and enter help text or choose a warning or rejection for invalid data, expand Advanced options, and mark the ones you want to use.

Select Done to apply the rule. You can then test your data validation rule. Input a valid entry and then an invalid entry to make sure the rule works as you expect.

Validate Dates

For validating dates in Google Sheets, you have several options. You can check for a date on, after, before, or between as well as a valid date.

As an example, you may want to confirm that the dates entered fall after a particular date, such as January 1, 2023, for your company’s 2023 financials.

Select the cell or range and use Data > Data validation to open the sidebar and pick Add rule.

Apply to range: Confirm the cell or range and optionally add another.

Criteria: Pick the date option you want to use the drop-down menu or text field to pick or enter the corresponding date. For our example, we pick “Date is after,” choose “exact date,” and enter “1/1/23” in the field beneath.

Advanced options: Like the text validation above, you can expand this section to add help text and pick an invalid input action.

Select Done when you finish. You can then test your date validation by entering a valid and invalid date per your rule.

Validate Numbers

If you want to validate numbers in Sheets, you can set up a rule that checks for those greater than, less than, equal to, between, and more.

For this example, we want to confirm that the number entered is between 1 and 17 for parents entering their minor child’s age.

Select the cell or range, pick Data > Data validation, and choose Add rule.

Apply to range: Confirm the cell or range and optionally add another.

Criteria: Pick the date option you want to use and enter the text in the field beneath. For our example, we pick “Is between” and enter “1” in the first field and “17” in the second.

Advanced options: Like the above validations, expand this section to add help text and pick an invalid data action.

Select Done to apply the rule. Test your number validation rule by entering both a correct and incorrect number.

Create a Drop-Down List

Drop-down lists are another validation type you can use in Sheets. What’s different about these is that you can insert a drop-down list using the Insert or the Data menu. Either way, you’ll use the Data Validation sidebar to set up the list of items.

Here, we’ll set up a drop-down list to choose a menu item.

Do one of the following to add a drop-down list:

• Select the cell and pick Insert > Dropdown in the menu. The sidebar will open.
• Right-click the cell and pick Dropdown.
• Select the cell, pick Data > Data validation, and choose Dropdown in the Criteria menu in the sidebar.

Enter your list items in the Option 1 and Option 2 fields and use the Add another item button to include more. You can also reorder the items using the grid icons on the left of each.

Choose a color for each list item in the color palette drop-down box on the left.

Advanced options: Expand this section to show help text, choose an invalid data action, and pick the display style for the list.

Select Done when you finish, and you’ll see your drop-down list ready to go.

Insert a Checkbox

Similar to the drop-down list validation above, you can add a checkbox to a cell using one of two options and customize the values in the Data Validation sidebar.

Here, we’ll add checkboxes to add dishes to our meals.

• Select the cell and pick Insert > Checkbox in the menu. The sidebar will open.
• Select the cell, pick Data > Data validation, and choose Checkbox in the Criteria menu in the sidebar.

To use specific values for the checked and unchecked box statuses, mark the option Use custom cell values and enter those you want to use. For our example, we enter “Yes” and “No.”

Advanced options: Expand this section to show help text and choose an invalid input action.

Select Done to apply the checkbox rule to the cell or range of cells.

Use a Custom Formula

One more way to use data validation is with a custom formula in Google Sheets. This is a good option when none of the above preset rules apply. You can do things like make sure the cell contains text or limit the number of characters in the cell.

As an example, we’ll set up the validation rule to check for text in the cell. If a number or date is entered, this invokes the invalid data action.

Select the cell or range and pick Data > Data validation.

Apply to range: Confirm the cell or range and optionally add another.

Criteria: Pick “Custom formula is” and enter the formula in the field beneath. Using our example, we enter the formula “=ISTEXT(A2)” to check that cell A2 contains text.

Advanced options: Expand this section to enter help text and choose an invalid data action. For our example, we’ll mark the option to show help text and enter a custom message to display along with rejecting the input.

Select Done to apply the rule. Then, give your new validation rule a test by entering both valid and invalid data to make sure the formula works as expected. In the screenshot below, you can see our message for an invalid entry.

Edit or Remove Data Validation

If after you set up a data validation rule, you want to change it or simply remove it, both are easy to do.

Select the cell or range containing the validation and choose Data > Data validation in the menu to open the sidebar.

Then, do one of the following:

• To edit a rule, select it, make your changes, and choose Done to save it.
• To remove a rule, hover your cursor over it and select the Delete (trash can) icon.
• To remove every rule in the list, use the Remove all button.

With the data validation feature in Google Sheets, you can make sure your data is entered correctly. Whether you pop up a warning message or provide drop-down list options, you can save yourself the aggravation of checking for invalid data later.

If you also use Microsoft applications, look at how to create a drop-down list in Excel too.

The post How to Use Data Validation in Google Sheets first appeared on Online Tech Tips.

Convert a Number to Text: TEXT

Starting with a simple way to convert a number to text using a specified format is the TEXT function. You can use it for a date, time, percentage, currency, or similar number.

The syntax for the formula is TEXT(number, format), where you can use the exact number or a cell reference for the first argument. Depending on the format you want to use, you can visit the Google Docs Editors Help page for the list of over a dozen options for the second argument.

As an example, we’ll format the time 22:30 in a 12-hour format with AM or PM and as text using this formula:

=TEXT(“22:30”,”hh:mm AM/PM”)

For another example, we’ll format the number in cell A1 as text with a percent sign using this formula:

=TEXT(A1,”0%”)

Combine Text: CONCATENATE

If you want to join two strings of text, you can use the CONCATENATE function. You may have a first name and last name, city and state, or similar that you want to combine into a single cell.

The syntax is CONCATENATE(string1, string2,…), where you can use the text or cell references for the arguments.

In this example, we’ll combine the text in cells A1 through D1 into a single string with this formula:

=CONCATENATE(A1:D1)

If you want to place a space between the words, you can insert a blank space within quotation marks between each cell reference using this formula:

=CONCATENATE(A1,” “,B1,” “,C1,” “,D1)

For another example, we’ll combine the text “First name: ” with the text in cell A1 with this formula:

=CONCATENATE(“First name: “,A1)

Combine Text With a Delimiter: TEXTJOIN

The TEXTJOIN function is similar to CONCATENATE for combining text. The difference is that you can use a delimiter (separator) and combine arrays with TEXTJOIN.

The syntax is TEXTJOIN(delimiter, empty, text1, text2,…). For the delimiter argument, place the space, comma, or other delimiter in quotes and for the empty argument, use True to exclude empty cells or False to include them.

As an example, we’ll join the text in the cell range A1 through C2 with a space as the delimiter and TRUE to ignore the empty cell (A2). Here’s the formula:

=TEXTJOIN(” “,TRUE,A1:C2)

For another example, we’ll combine the text in cells A1 through A10 with a comma as the delimiter and FALSE to include the empty cells (A4 through A8) so you can see how the result looks. Here’s the formula:

=TEXTJOIN(“,”,FALSE,A1:A10)

Tip: If you want to combine numbers, you can use the JOIN function.

Separate Text: SPLIT

Maybe you want to do the opposite of the above and separate text rather than combine it. For this, you can use the SPLIT function.

The syntax is SPLIT(text, delimiter, split_by, empty). Use the split_by argument with True (default) to separate the text around each character in the delimiter, otherwise use False. Use the empty argument with True (default) to treat consecutive delimiters as one, otherwise use False.

Here, we’ll split the text in cell A1 using a space as the delimiter and the defaults for the other arguments with this formula:

=SPLIT(A1,” “)

For another example, we’ll split the text in cell A1 using “t” as the delimiter. This removes the “t” like it removes the space delimiter above and leaves the rest of the text. Here’s the formula:

=SPLIT(A1,”t”)

Now, if we add FALSE as the split_by argument, this formula separates the text only at the “t[space]” mark:

=SPLIT(A1,”t “,FALSE)

Compare Text: EXACT

Are you working on comparing data in your sheet? Using the EXACT function, you can compare two strings of text and receive a simple True or False result for whether they match or not.

The syntax is EXACT(text1, text2), where you can use text or cell references for the arguments.

As an example, we’ll compare the two text strings in cells A1 and B1 with this formula:

=EXACT(A1,B1)

For another example, we’ll compare the text in cell A1 with “Google” using this formula:

=EXACT(A1,”Google”)

Change Text: REPLACE and SUBSTITUTE

While you can use the Find and Replace feature in Google Sheets, you may need to be more specific than the feature allows. For instance, you might want to change a letter in a particular spot or only a certain instance of text in a string. In this case, you can use either REPLACE or SUBSTITUTE.

Although similar, each function works slightly differently, so you can use whichever meets your needs best.

The syntax for each is REPLACE(text, position, length, new) and SUBSTITUTE(text, search_for, replace_with, occurrence). Let’s walk through a couple of examples and how to use the arguments.

REPLACE

Here, we want to replace “William H Brown” with “Bill Brown,” so we’ll use the REPLACE function and this formula:

=REPLACE(A1,1,9,”Bill”)

To break down the formula, A1 is the cell with the text, 1 is the starting position to replace, 9 is the number of characters to replace, and Bill is the replacement text.

As another example, we have phone numbers stored as text and need to change the prefix for each. Since each prefix is different, we can use REPLACE to specify the position and number of characters for the replacement. Here’s the formula:

=REPLACE(A1,5,3,”222″)

Tip: To change multiple cells in a column, you can drag the formula down to apply it to the subsequent rows as shown below.

SUBSTITUTE

For an example of the SUBSTITUTE function, we want to replace “new york” with “New York” and will add the occurrence argument to make sure we only change the first instance in our string. Here’s the formula:

=SUBSTITUTE(A1,”new york”,”New York”,1)

To break down this formula, A1 contains the text, “new york” is the text we search for, “New York” is the replacement, and 1 is the first occurrence of the text.

If you were to remove the occurrence argument in the above formula, the function would change both instances to “New York” by default as you can see here:

=SUBSTITUTE(A1,”new york”,”New York”)

Change the Letter Case: PROPER, UPPER, and LOWER

If you import data from another source or mistype during data entry, you may end up with mismatched letter cases. Using the PROPER, UPPER, and LOWER functions, you can quickly correct it.

The syntax for each is simple as PROPER(text), UPPER(text), and LOWER(text) where you can use a cell reference or the text for the argument.

To capitalize the first letter of each word in a text string, you can use the PROPER function and this formula:

=PROPER(A1)

To change the letters to all uppercase, use the UPPER function and this formula:

=UPPER(A1)

To change the letters to all lower case, use the LOWER function and this formula:

=LOWER(A1)

Remember, you can also enter the exact text for all three functions within quotes as follows:

=PROPER(“online tech TIPS”)

Obtain Part of a Text String: LEFT, RIGHT, and MID

Maybe you need to extract part of a text string. You may have data mixed in with other data or want to use a portion of a string for something in particular. You can use the LEFT, RIGHT, and MID functions to get the part you need.

The syntax for each is LEFT(string, num_characters), RIGHT(string, num_characters), and MID(string, start, length). You can use a cell reference or the text as the string argument in each.

For an example of the LEFT function, we’ll extract the first three characters from the left using the text in cell A1 with this formula:

=LEFT(A1,3)

For an example of the RIGHT function, we’ll extract the first four characters from the right using the same cell with this formula:

=RIGHT(A1,4)

For an example of the MID function, we’ll extract the name “Jane” from the text in the same cell.

=MID(A1,6,4)

In this MID example, a 6 for the start argument selects the 6th character from the left. Remember that all characters count, including spaces and punctuation. Then, a 4 for the length argument selects four characters.

Get the Length of a Text String: LEN and LENB

When you plan to do something specific with your data, such as copy and paste or export it for use elsewhere, you might need to be cautious about the character count. With LEN, you can get the number of characters in a text string, and with LENB, you can get the count in bytes.

The syntax for each is LEN(string) and LENB(string), where again, you can use a cell reference or text as the argument.

Here, we’ll get the character count for the text in cell A1 with this formula:

=LEN(A1)

With this formula, we’ll get the character count for the text in cell A1 but as bytes instead:

=LENB(A1)

Remove Extra Spaces: TRIM

If you need to clean up your data from leading, trailing, or miscellaneous extra spaces, you can use the TRIM function.

The syntax is TRIM(text), where you can use a cell reference or text for the argument.

Here, we’ll remove the spaces from the text string in cell A1 with this formula:

=TRIM(A1)

Next, we’ll remove extra spaces from the specific text “ Online Tech Tips” with this formula:

=TRIM( “Online Tech Tips”)

Tackle Your Text With Google Sheets Functions

Google Sheets offers many features for working with text. You can wrap text, change the format, hyperlink it, and more. However, when you have a lengthy dataset, the Google Sheets text functions can help you tackle your text changes quicker and more efficiently. Will you give one or two a try?

For related tutorials, look at how to use Google Sheets formulas for arrays.

The post 15+ Simple Google Sheets Text Functions first appeared on Online Tech Tips.

1. Highlight Cells

Highlighting the entire cell is pretty straightforward. You can choose to highlight only one or a group of cells. Follow these steps:

1. Select the cell or a group of cells you want to highlight.

2. Select the Fill color icon from the toolbar, and choose the highlight color you want.

3. The selected cells will automatically become highlighted with a different fill color than the others.

And that’s all there is to it. Highlighting cells might be the easiest thing to do in Google Sheets.

2. Highlight Text

If you don’t want to highlight the whole cell but only the data inside it, you can do it by changing the text color. It doesn’t matter if it’s a particular value, the whole text, or just a part of the text in a cell you want to highlight; the concept is the same.

1. Select the text within the cell or part of the text you want to highlight. Double-click the cell with your cursor to access the text to do this.

2. Select the Text color icon from the main toolbar, and choose the desired color.

3. The selected text will automatically change to the color you chose from the color palette.

Note: You can use as many colors as you want to highlight your text. But remember that some colors might be difficult to read depending on the cell background color. You can combine cell and text highlight methods to achieve different effects. You can also change the formatting style of your text to Bold, Italic, or Strikethrough to put even more emphasis on the text or a cell.

3. Conditional Formatting

For data analysis, you can format the cells based on their content. For example, you can change the background color of cells with the same value. Conditional formatting will save you time if you need to highlight several different cells with the same value and you don’t want to manually search for them to select and highlight those values.

Let’s see how to automatically highlight cells that begin, end, or contain a specific value, word, or phrase, changing only the conditional formatting rules.

Note: Conditional formatting is less powerful in Google Sheets compared to Microsoft Excel. The former limits you to 50 rules per sheet, while you can create up to 256 rules in Excel to highlight your data.

1. Highlight Text That Begins With a Specific Word

1. Select a cell (or multiple cells) that contains a specific word, phrase, or value.
2. Open the Format menu from the ribbon at the top of the screen.

3. Select Conditional Formatting from the drop-down menu. The Conditional Formatting menu will open on the right side of the Google Sheet.

4. If you want to apply Conditional Formatting to multiple cells that contain the same word, you’ll have to input the range to which the formatting will be applied. Click on the Apply to Range box.

5. When the dialog box opens, type in the range of cells where you want to apply the formatting. Look at the cell numbers and column letters and type in the range— e.g. A2:A16. This will highlight the cells that meet the conditional formatting rule .

6. In the Conditional Formatting menu, under Format Rules (or Conditional format rules), click on the drop-down menu for Format cells if…

7. Select Text starts with from the drop-down menu.

8. In the Value or formula box, type in the specific word (or a custom formula) with which the cell begins.

9. In the Formatting Style section, you can select the text and background color you want added to the text or the whole cell and text formatting options such as Bold, Italic, and Strikethrough.

10. When you select all the desired options in Formatting Style, hit the Done button.

The cells containing a specific word will be highlighted automatically (in our example”, the word is “Best”).

2. Highlight Text That Ends With a Specific Word

You can also highlight all the cells that end with a specific word. The steps are almost the same. However, when you come to the point where you need to select Format rules, You’ll choose Text ends with from the drop-down menu.

All the steps before and after this are the same as described in the previous section. Once you are ready to apply the highlight to all the cells that end with a specific word, phrase, or value, hit the Done button.

3. Highlight Text That Contains a Specific Word

You probably guessed that to highlight text that contains a specific word you have to follow the same steps as previously described in the Conditional Formatting section. But when you reach the point to select Format rules, you’ll choose Text contains from the drop-down menu.

Select the formatting styles you desire and hit the Done button to finish.

Manually highlighting text gives you precise control over the selection process, allowing you to emphasize specific data points, labels, or insights. This hands-on method is ideal for smaller datasets.

On the other hand, automatic highlighting with conditional formatting offers efficiency and scalability. It allows you to set up rules that dynamically highlight cells based on specific criteria, making it perfect for larger datasets. After using conditional formatting, you can also filter your spreadsheet data by color.

Whether you choose the manual or automatic route, highlighting the correct text enhances the clarity and readability of your spreadsheets, so keep experimenting with both methods.

The post How to Highlight Texts in Google Sheets (Manually and Automatically) first appeared on Online Tech Tips.

Here, we’ll walk through several cool Google Sheets features that might just become your fast favorites. Head to Google Sheets, sign in with your Google account, and try out some of these hidden gems.

1. Extract Data From a Smart Chip

If you’ve taken advantage of the Smart Chips in Google’s apps, then you’ll be happy to know you can do even more with them. After you insert a Smart Chip, you can extract data from it and place it in your sheet, making chips even more useful.

You can currently extract data from People, File, and Calendar Event Smart Chips. This includes name and email, owner and filename, and summary and location.

1. After you insert a Smart Chip, hover your cursor over it, select it, or right-click. Then, choose Data extractions.

2. When the sidebar opens, use the Extract tab to mark the checkboxes for those items you want to extract.

3. Use the Extract to field to enter or select the sheet location where you want the data.

4. Pick Extract and you’ll see your data display in your selected location.

If you need to refresh the extracted data, you can use the Refresh & manage tab in the sidebar.

2. Create a QR Code

QR codes are popular ways to share information, direct people to your website, and even provide discounts. By creating your own QR code in Google Sheets without add-ons or third-party tools, you or your collaborators can quickly take action.

To make the QR code, you’ll use the Google Sheets IMAGE function and a link to Google’s root URL: https://chart.googleapis.com/chart?.

Here, we’ll link to the website in cell A1 using the formula below. Place the formula in the cell where you want the QR code.

=IMAGE(“https://chart.googleapis.com/chart?chs=500×500&cht=qr&chl=”&ENCODEURL(A1))

Use the following arguments to build your formula:

• CHS argument: Define the dimensions of the QR code in pixels (chs=500×500).
• CHT argument: Specify a QR code (cht=qr).
• CHL argument: Choose the URL data (chl=”&ENCODEURL(A1)).

Then, use the ampersand operator (&) to connect the arguments.

Once you see the code, you may need to resize the row and/or column to view its full size. Then, scan the QR code to make sure it works as you expect.

You can also use optional arguments for encoding the data in a particular way or assigning a correction level. For more on these arguments, check out the Google Charts Infographics reference page for QR codes.

3. Insert a Drop-Down List

Drop-down lists are terrific tools for data entry. By selecting an item from a list, you can make sure you’re entering the data you want and can reduce errors at the same time.

Since the introduction of drop-down lists in Sheets, the feature has been enhanced to give you a simpler way to create and manage these helpful lists.

1. Insert a drop-down list by doing one of the following:
2. Select Insert > Dropdown from the menu.
3. Right-click and choose Dropdown.
4. Type the @ (At) symbol and choose Dropdowns in the Components section.

5. You’ll then see the Data Validation Rules sidebar open. Enter the location for the list in the Apply to range box and confirm that Dropdown is selected in the Criteria drop-down menu.

6. Then, add your list items in the Option boxes and optionally select colors for them to the left.

7. To display help text, pick the action for invalid data, or choose the display style, expand the Advanced Options section.

8. When you finish, select Done. Then, use your new drop-down list to enter data in your sheet.

4. Validate an Email Address

When you have a spreadsheet that contains email addresses, whether Gmail, Outlook, or something else, you may want to make sure they’re valid. While Sheets doesn’t show you if an address is legitimate, it does show you if it’s formatted correctly with the @ (At) symbol and a domain.

1. Select the cell(s) you want to check and go to Data > Data validation in the menu.

2. When the Data Validation Rules sidebar opens, select Add rule, confirm or adjust the cells in the Apply to range field, and choose Text is valid email in the Criteria drop-down box.

3. Optionally select the Advanced Options such as showing help text, displaying a warning, or rejecting the input. Pick Done to save and apply the validation rule.

You can then test the validation and options by entering an invalid email address.

5. Make a Custom Function

Are you a fan of using functions and formulas in Google Sheets? If so, why not create your own? Using the Custom Function feature, you can set up your own function and reuse it whenever you like.

1. Select Data > Named functions from the menu.

2. In the Named Functions sidebar that opens, use Add new function at the bottom to create your custom function. You can also look at an example, watch the demonstration, or find out more about the feature.

3. Enter the function name, description, and optionally argument placeholders. Enter the formula you want to use to define the function and select Next.

4. Check out the Function preview and either select Back to make changes or Create to save the new function. Notice you can also add optional arguments if necessary.

5. You’ll then see the function in the sidebar list. Enter it into a cell in your sheet to test it out.

If you need to make edits, reopen the Named Functions sidebar, select the three dots to the right of the function, and pick Edit.

6. Use a Slicer to Filter a Chart

Charts give you handy and effective ways to display your data. Using a slicer, you can filter the data that displays in the chart. This is convenient for reviewing specific portions of the chart data when needed.

Insert a Slicer

After you insert your chart, select it and go to Data > Add a slicer in the menu.

When the sidebar opens, open the Data tab, confirm the Data Range at the top, and then pick the Column to use for the filter.

You’ll see the slicer appear as a black rounded rectangle which you can move or resize as you please.

Use a Slicer

Once you have your slicer, select the Filter button on the left or drop-down arrow on the right. Then, select the data you want to see in the chart which places checkmarks next to those items.

Select OK and you’ll see your chart update immediately.

To return your chart to the original view showing all data, open the filter and pick Select all > OK.

7. Quickly Calculate Data

Sometimes you want to see a quick calculation without adding a formula to your sheet. In Google Sheets, you can simply select the values and then choose a calculation to view without any extra work.

1. Select the data you want to calculate and then look on the bottom right of the tab row. You’ll see the calculation menu in green which contains the Sum of your data.

2. Open that menu and choose the calculation you want to perform. You’ll see the new result in that menu.

3. You can also simply open the menu to see all available calculations in real-time.

If you decide to include the calculation in your sheet, keep the cell selected and choose Explore to the right of the sheet tabs.

When the sidebar opens, drag the calculation you want to use to a cell in your sheet.

8. Explore Ways to Present Your Data

Maybe you have data in your spreadsheet but aren’t sure of the best way to display or analyze it. With the Explore feature, you can see various quick ways to present your data, review details about it, and ask questions.

Select your data and pick Explore on the bottom right.

When the Explore sidebar opens, you’ll see options for your data. Type a question in the Answers section, apply color using the Formatting section, or insert a chart from the Analysis section.

After you finish, simply use the X on the top right of the sidebar to close it.

9. Request Sheet Approvals

If you use a Google Workspace account for business or education, check out the Approvals feature. With it, you can request approvals from others and keep track of what’s approved and what isn’t.

Go to File and select Approvals.

When the Approvals sidebar opens, choose Make a request.

In the pop-up window, add those you want to approve your request and optionally a message. You can also include a due date, allow the approvers to edit the sheet, or lock the file before sending your request for approval. Choose Send request when you finish.

If you haven’t shared the document with the approvers already, you’ll be asked to do so and assign the permissions.

You can then view the status by returning to the Approvals sidebar.

10. Set Up a Custom Date and Time Format

While Google Sheets provides many different ways to format your dates and times, maybe you want something in particular. You can create your own date and time format with the structure, colors, and style you want.

1. Select the cell(s) containing the date or time and go to Format > Number > Custom date and time. Alternatively, you can select the More Formats option in the toolbar and pick Custom date and time.

2. When the window opens, you’ll see the current format for your date and/or time. Select an existing element at the top to change the format or delete it.

3. To add a different element, select the arrow on the right side and choose one from the list. You can then format that element using its arrow.

4. When you finish, select Apply to use the custom date and time format and you should see your sheet update.

With these Google Sheets features, you can do even more with your data. Be sure to try one or more and see which come in handy for you.

For related tutorials, look at how to find duplicates in Google Sheets using the conditional formatting options.

The post 10 Hidden Google Sheets Features You Didn’t Know Existed first appeared on Online Tech Tips.

With two ways to switch your rows and columns, you can use whichever is most comfortable for you. This includes the Paste Special feature and the TRANSPOSE function. If you’re ready to go, here’s how to use transpose in Google Sheets.

Convert Rows and Columns With Paste Special

A quick way to convert rows and columns is using the Paste Special feature in Google Sheets. With it, you simply copy the data and then paste it as transposed.

The nice thing about using this option is that if you have formatting like bold text or a fill color in your cells, that formatting applies to the pasted cells.

1. Start by selecting the data you want to transpose. For example, we’ll convert our column of cities to a row.

2. Copy the data by doing one of the following:

• Select Edit > Copy from the menu.
• Right-click and choose Copy.
• Use the keyboard shortcut Ctrl + C on Windows or Command + C on Mac.

3. Go to the cell where you want to paste the data, making sure you have enough space so that you don’t overwrite existing data.
4. Either select Edit > Paste Special or right-click and move to Paste Special. Then, pick Transposed in the pop-out menu.

You should then see your selected cells pasted.

If you want to remove the original data after you paste it, you can delete it like any other data in your sheet.

Convert Rows and Columns With the TRANSPOSE Function

If you’re accustomed to using Google Sheets formulas, you can convert your rows or columns using the TRANSPOSE function and a simple formula.

Keep in mind that unlike the Paste Special feature above, existing text or cell formatting does not apply when you use the TRANSPOSE function.

The syntax for the formula is TRANSPOSE(range) with just one required argument for the range of cells you want to switch.

Go to the cell where you want the data transposed, making sure you have adequate space. Then, enter the following formula replacing the cell range with your own:

=TRANSPOSE(A2:G3)

Press Enter or Return and you should see your transposed data.

Again, you delete the original dataset after you use the TRANSPOSE formula to convert it if you like.

Convert Rows and Columns on Mobile

If you’re working with Google Sheets on your Android or iOS device, you can convert rows and columns there as well. While you can use the Paste Special feature or the TRANSPOSE function on Android, you can currently only use the function on iPhone.

Transpose on Android

To switch rows or columns on Android, open your Google Sheet and follow these simple steps to use Paste Special.

1. Select the cells you want to transpose. You can drag through them using the blue dot on the bottom right corner. Then, tap to display the toolbar and choose Copy.
2. Go to the cell where you want the converted rows or columns, tap, and select Paste special in the toolbar.

3. When the Paste Special options appear, choose Paste transposed.

You’ll then see your converted rows or columns.

If you prefer to use the TRANSPOSE function on Android, follow the steps below as they are the same on iPhone.

Transpose on iPhone

As mentioned, you cannot use the Paste Special feature in Google Sheets on iPhone as of this writing. You can, however, use the TRANSPOSE function to switch your rows and columns.

1. Go to the cell where you want the transposed cells.
2. Type the formula into the text box at the bottom.
3. Tap the green checkmark to apply the formula and you should see your converted rows or columns.

Converting rows to columns or the other way around is easy to do in Google Sheets. If you also use Microsoft Office, look at how to transpose data in Excel too!

The post How to Transpose Rows and Columns in Google Sheets first appeared on Online Tech Tips.

With more flexibility for working with arrays and going beyond the basic ARRAYFORMULA function, let’s look at how to use these array functions with formulas in Google Sheets.

Tip: Some of these functions may look familiar to you if you also use Microsoft Excel.

Transform an Array: TOROW and TOCOL

If you have an array in your dataset that you want to transform into a single row or column, you can use the TOROW and TOCOL functions.

The syntax for each function is the same, TOROW(array, ignore, scan) and TOCOL(array, ignore, scan) where only the first argument is required for both.

• Array: The array you want to transform, formatted as “A1:D4.”
• Ignore: By default, no parameters are ignored (0), but you can use 1 to ignore blanks, 2 to ignore errors, or 3 to ignore blanks and errors.
• Scan: This argument determines how to read the values in the array. By default, the function scans by row or using the value False, but you can use True to scan by column if you prefer.

Let’s walk through a few examples using the TOROW and TOCOL functions and their formulas.

In this first example, we’ll take our array A1 through C3 and turn it into a row using the default arguments with this formula:

=TOROW(A1:C3)

As you can see, the array is now in a row. Because we used the default scan argument, the function reads from left to right (A, D, G), down, then the left to right again (B, E, H) until complete—scanned by row.

To read the array by column instead of row, we can use True for the scan argument. We’ll leave the ignore argument blank. Here’s the formula:

=TOROW(A1:C3,,TRUE)

Now you see the function reads the array from top to bottom (A, B, C), top to bottom (D, E, F), and top to bottom (G, H, I).

The TOCOL function works the same way but transforms the array to a column. Using the same range, A1 through C3, here’s the formula using the default arguments:

=TOCOL(A1:C3)

Again, using the default for the scan argument, the function reads from left to right and provides the result as such.

To read the array by column instead of row, insert True for the scan argument like this:

=TOCOL(A1:C3,,TRUE)

Now you see the function reads the array from top to bottom instead.

Related: Having trouble with formulas? Check out our guide to troubleshooting Google Sheets formula parse errors.

Create a New Array From Rows or Columns: CHOOSEROWS and CHOOSECOLS

You may want to create a new array from an existing one. This lets you make a new cell range with only specific values from another. For this, you’ll use the CHOOSEROWS and CHOOSECOLS Google Sheets functions.

The syntax for each function is similar, CHOOSEROWS (array, row_num, row_num_opt) and CHOOSECOLS (array, col_num, col_num_opt), where the first two arguments are required for both.

• Array: The existing array, formatted as “A1:D4.”
• Row_num or Col_num: The number of the first row or column you want to return.
• Row_num_opt or Col_num_opt: The numbers for additional rows or columns you want to return. Google suggests you use negative numbers to return rows from the bottom up or columns from right to left.

Let’s look at a few examples using CHOOSEROWS and CHOOSECOLS and their formulas.

In this first example, we’ll use the array A1 through B6. We want to return the values in rows 1, 2, and 6. Here’s the formula:

=CHOOSEROWS(A1:B6,1,2,6)

As you can see, we received those three rows to create our new array.

For another example, we’ll use the same array. This time, we want to return rows 1, 2, and 6 but with 2 and 6 in reverse order. You can use positive or negative numbers to receive the same result.

Using negative numbers, you’d use this formula:

=CHOOSEROWS(A1:B6,1,-1,-5)

To explain, 1 is the first row to return, -1 is the second row to return which is the first row starting at the bottom, and -5 is the fifth row from the bottom.

Using positive numbers, you’d use this formula to obtain the same result:

=CHOOSEROWS(A1:B6,1,6,2)

The CHOOSECOLS function works similarly, except you use it when you want to create a new array from columns instead of rows.

Using the array A1 through D6, we can return columns 1 (column A) and 4 (column D) with this formula:

=CHOOSECOLS(A1:D6,1,4)

Now we have our new array with only those two columns.

As another example, we’ll use the same array starting with column 4. We’ll then add columns 1 and 2 with 2 (column B) first. You can use either positive or negative numbers:

=CHOOSECOLS(A1:D6,4,2,1)

=CHOOSECOLS(A1:D6,4,-3,-4)

As you can see in the above screenshot, with the formulas in the cells rather than the Formula Bar, we receive the same result using both options.

Note: Because Google suggests using negative numbers to reverse the placement of the results, keep this in mind if you aren’t receiving the correct results using positive numbers.

Wrap to Create a New Array: WRAPROWS and WRAPCOLS

If you want to create a new array from an existing one but wrap the columns or rows with a certain number of values in each, you can use the WRAPROWS and WRAPCOLS functions.

The syntax for each function is the same, WRAPROWS (range, count, pad) and WRAPCOLS (range, count, pad), where the first two arguments are required for both.

• Range: The existing cell range you want to use for an array, formatted as “A1:D4.”
• Count: The number of cells for each row or column.
• Pad: You can use this argument to place text or a single value in empty cells. This replaces the #N/A error you’ll receive for the blank cells. Include the text or value within quotation marks.

Let’s walk through a few examples using the WRAPROWS and WRAPCOLS functions and their formulas.

In this first example, we’ll use the cell range A1 through E1. We’ll create a new array wrapping rows with three values in each row. Here’s the formula:

=WRAPROWS(A1:E1,3)

As you can see, we have a new array with the correct result, three values in each row. Because we have an empty cell in the array, the #N/A error displays. For the next example, we’ll use the pad argument to replace the error with the text “None.” Here’s the formula:

=WRAPROWS(A1:E1,3,”None”)

Now, we can see a word instead of a Google Sheets error.

The WRAPCOLS function does the same thing by creating a new array from an existing cell range, but does so by wrapping columns instead of rows.

Here, we’ll use the same array, A1 through E3, wrapping columns with three values in each column:

=WRAPCOLS(A1:E1,3)

Like the WRAPROWS example, we receive the correct result but also an error because of the empty cell. With this formula, you can use the pad argument to add the word “Empty”:

=WRAPCOLS(A1:E1,3,”Empty”)

This new array looks much better with a word instead of the error.

Combine to Create a New Array: HSTACK and VSTACK

Two final functions we’ll look at are for appending arrays. With HSTACK and VSTACK, you can add two or more ranges of cells together to form a single array, either horizontally or vertically.

The syntax for each function is the same, HSTACK (range1, range2,…) and VSTACK (range1, range2,…), where only the first argument is required. However, you’ll almost always use the second argument, which combines another range with the first.

• Range1: The first cell range you want to use for the array, formatted as “A1:D4.”
• Range2,…: The second cell range you want to add to the first to create the array. You can combine more than two cell ranges.

Let’s look at some examples using HSTACK and VSTACK and their formulas.

In this first example, we’ll combine the ranges A1 through D2 with A3 through D4 using this formula:

=HSTACK(A1:D2,A3:D4)

You can see our data ranges combined to form a single horizontal array.

For an example of the VSTACK function, we combine three ranges. Using the following formula, we’ll use ranges A2 through C4, A6 through C8, and A10 through C12:

=VSTACK(A2:C4,A6:C8,A10:C12)

Now, we have one array with all of our data using a formula in a single cell.

Manipulate Arrays With Ease

While you can use ARRAYFORMULA in certain situations, like with the SUM function or IF function, these additional Google Sheets array formulas can save you time. They help you arrange your sheet exactly as you want it and with a single array formula.

For more tutorials like this, but with non-array functions, look at how to use the COUNTIF or SUMIF function in Google Sheets.

The post How to Use Array Formulas in Google Sheets first appeared on Online Tech Tips.

You have three ways to format the text in your cell with the wrap feature in Google Sheets on the web. In the mobile app, you can use the basic wrap format to see your text more easily.

How to Wrap Text in Google Sheets on the Web

Visit Google Sheets on the web, sign in, and open the workbook and sheet you want to use.

Select the cell containing the text you want to wrap and do one of the following:

• Pick Format > Wrapping in the menu to display the pop-out menu.
• Use the arrow next to the Text Wrapping icon in the toolbar to display the options.

Then, select Wrap.

You see the text in the cell carry down to the next line and the cell automatically lengthen to accommodate the text. You’ll also notice the height of that entire row change.

If you decide to change the column width, the length of the cell automatically adjusts. This shortens the cell so that the text string fits perfectly inside. Again, you’ll see the whole row height adjust.

Wrap All Text in a Sheet

To set all cells to wrap text you enter, use the Select All button (square between column A and row 1) to select the entire sheet.

Then, pick Format > Wrapping > Wrap or use the Wrap Text button the toolbar and pick Wrap.

Once you do this, any existing data or future data you enter into your spreadsheet will automatically wrap to a new line which is a great way to save time in Google Sheets.

Other Text Wrapping Options

In most cases, the wrap text option above best accommodates long strings of text in cells. It allows you to see all of the data without resizing or even merging cells.

However, if you find that the feature doesn’t work for your particular sheet, you can choose the Overflow or Clip option in the Wrapping menu.

Overflow: The text in the cell spills over into the adjacent cells to the right. In the screenshot below, you can see the text in cell A1 carries over to cells B1 and C1.

This is fine if the next cell to the right is blank, but if it contains data then the text in the cell to the left will be cut off as shown here.

Clip: The text in the cell is cut off at the cell border. This looks similar to the Overflow option when the cell to the right contains data.

If you go with the Overflow or Clip option, you can always see the entire cell contents by selecting the cell and looking at the Formula Bar.

If you don’t see the Formula Bar at the top of your sheet, select View > Show > Formula bar to display it.

How to Wrap Text in Google Sheets on Mobile

In the Google Sheets mobile app, you have one option to wrap text which is the same as the Wrap feature described above. The cell containing the data automatically resizes to accommodate the text which wraps to the next line.

1. Open your worksheet in Google Sheets on Android or iPhone.
2. Select the cell containing the text and tap the Format button (capital letter “A”) at the top.
3. When the Format menu appears at the bottom, select the Cell tab.
4. Turn on the toggle for Wrap text.

You can then close the Format menu and continue working in your sheet.

Make Your Text Easy to See

By wrapping text in Google Sheets, you’ll be able to view the entire contents of a cell without lifting a finger.

For similar tutorials, look at how to insert and use bullet points in Google Sheets for an organized way to format your text.

The post How to Wrap Text in Google Sheets first appeared on Online Tech Tips.

You can sort your data alphabetically on the Google Sheets website or in the mobile app, although the website gives you a bit more flexibility. If you’re ready to get to it, let’s alphabetize that data.

Sort Alphabetically in Google Sheets on the Web

If you use Google Sheets on the web, you can alphabetize your whole worksheet or a range of cells, like a column. Visit Google Sheets, sign into your Google account, and open a workbook to get started.

Alphabetize a Sheet

To alphabetize a sheet, you’ll use a specific column to sort by. The remaining columns in your sheet will update so that your data remains intact.

Choose the column that you want to sort by and do one of the following:

• Select the arrow to the right of the column header or right-click the column and pick Sort A – Z in the drop-down menu. To sort in reverse alphabetical order, pick Sort Z – A.
• Go to the Data tab, move to Sort sheet, and pick Sort sheet by column (A to Z). To sort in reverse, pick Sort sheet by column (Z to A).

Note: If you have a header row, these will be included in the alphabetization.

Alphabetize a Cell Range

To alphabetize a particular cell range instead of the whole sheet, this is also an option. Just keep in mind that your other columns will not update to stay in sync with the sorted data.

Go to the Data tab, move to Sort range, and pick Sort range by column (A to Z). To sort in descending order, pick Sort range by column (Z to A).

You’ll then see your range sorted alphabetically.

Alphabetize Multiple Cell Ranges

If you have a sheet where you want to sort by a column alphabetically but also sort additional columns of data, you can perform an advanced sort.

1. Select the data you want to alphabetize. This should include all cell ranges or columns.
2. Go to the Data tab, move to Sort sheet, and pick Advanced range sorting options.

3. In the pop-up window, check the box at the top for Data has header row to exclude this data from the sort if you like.

4. Below, you’ll see the first column to sort by. Make sure to mark the A to Z option (or Z to A to sort in reverse).

5. Select Add another sort column.

6. Choose the second column to sort by in the drop-down box that appears and mark the A to Z option next to it as well (or Z to A to sort in reverse).

7. Continue this process for all columns you want to sort by in the cell range.
8. When you finish, select Sort.

You’ll then see your data sorted alphabetically by each column, in order.

Sort Alphabetically in Google Sheets on Mobile

You can alphabetize your spreadsheet in the Google Sheets app on your mobile device in just a few taps. Although the feature is more limited, you can still sort your data alphabetically.

1. Open the Google Sheets app on Android or iPhone to the sheet you want to sort.
2. You’ll then choose a column to sort by. This then sorts the entire sheet by that specific column.
3. Tap the letter header at the top of the column to select it and then tap it again to open the small toolbar of actions.

4. Use the arrow on the right side of the toolbar to move through the actions until you see the Sort A – Z option and select it.

You’ll then see your sheet update to sort alphabetically by that column. The additional columns update just like on the website.

When you want to sort data in your Google spreadsheet from A to Z, it’s easy to do. Now that you know how to alphabetize in Google Sheets, look at our tutorial for alphabetizing in Microsoft Excel too.

The post How to Alphabetize in Google Sheets (Mobile and Computer) first appeared on Online Tech Tips.

We will show you how to add hyperlinks to your worksheet and remove links in Google Sheets that you no longer want.

Link to a Cell or Cell Range

If you have a large Google spreadsheet full of data and want to visit a single cell or cell range frequently, you can easily create a link to it.

1. Select the cell where you want to place the hyperlink. If the cell contains text, that text will be linked. You can also add the text you want when you create the link.
2. Do one of the following to insert the link:
3. Right-click and pick Insert Link.
4. Select the Insert Link button in the toolbar.
5. Choose Insert > Link from the menu.

6. When the Insert Link box appears, you’ll see the cell contents to be linked in the Text box at the top. If the cell is blank, you can enter text in that box to connect to the link.

7. Navigate to the bottom of the pop-up box and pick Sheets and named ranges.

8. Go to the bottom of the subsequent screen and pick Select a range of cells to link.

9. Either enter the cell reference or cell range starting with the sheet name in the following dialog box or use your cursor to select the cell or range to automatically populate the box.
10. Confirm the cell or range and select OK.

You’ll then see the linked text within the cell. Select the cell and use the link in the preview to head to the cell or cell range.

Link to a Named Cell Range

If you use named ranges in your sheet, this gives you another linking option.

1. Follow Steps 1 through 3 above to select the cell, open the Insert Link box, and optionally adjust or add the link text.
2. Go to the bottom of the pop-up box and pick Sheets and named ranges.
3. If necessary, scroll down the next screen in the box to the Named Ranges section and pick the range.

4. When the link to the named range appears in the cell, use Enter or Return to save it.

If you don’t see the named range in the list, it’s likely that it wasn’t named already. To do so, select the range of cells, right-click, and pick View more cell actions > Define named range.

When the Named Ranges sidebar appears on the right, enter a name for the range and select Done.

You can then follow the previous steps to link to that named range.

Link to Another Sheet

If you want to link to another spreadsheet in your Google Sheets workbook, this is a handy way to jump right to it when needed.

1. Follow Steps 1 through 3 from earlier.
2. Go to the bottom of the pop-up box and pick Sheets and named ranges.
3. Head to the Sheets section and pick the sheet name.

4. When the link to the sheet appears in the cell, use Enter or Return to save it.

Link to a Web Page

When you want to link to a website or a particular web page, you have few different ways to do so in Google Sheets.

Use the Paste Action

Probably the simplest way to insert a web link in your sheet is by copying and pasting the URL.

1. Go to the address bar in your web browser and copy the link. You can do this by selecting the URL, right-clicking, and picking Copy.

2. Return to Google Sheets and select the cell where you want the link, right-click, and pick Paste.

3. You’ll then see the hyperlink in the cell. If you want to edit the name rather than display the URL, select the cell to display the link preview and pick the Edit link button (pencil icon).

4. Enter the name in the Text box and select Apply.

Use the Insert Link Feature

Another way to link to a site or page is using the Insert Link feature described earlier.

1. Select the cell where you want the link and do one of the following:
2. Right-click and pick Insert Link.
3. Select the Insert Link button in the toolbar.
4. Choose Insert > Link from the menu.
5. Search for, type, or paste the link into the Search or paste a link box.
6. Optionally add or edit the name in the Text box and select Apply.

Use the HYPERLINK Function

If you like using functions and formulas in Google Sheets, you can also use the HYPERLINK function to insert your link.

The syntax for the formula is “HYPERLINK(url, label)”, where only the URL is required. You can use the label argument for the text to link to the URL. Be sure to include both arguments within quotation marks.

To insert a link to Online Tech Tips, you would use this formula:

=HYPERLINK(“https://www.online-tech-tips.com”)

To insert a link to Online Tech Tips that has the label OTT, you’d use this formula instead:

=HYPERLINK(“https://www.online-tech-tips.com”,”OTT”)

Remove Hyperlinks in Google Sheets

If you decide later to remove hyperlinks you’ve added or import data or a sheet from elsewhere and don’t want the included links, you can remove them.

Remove a Single Link in a Sheet

Maybe you have a particular hyperlink you want to remove. You can do this in a snap. Do one of the following to remove an individual link:

Select the cell containing the link and pick the Remove link button (broken link icon) in the preview.

Right-click the cell with the link and move to View more cell actions > Remove link.

The hyperlink is removed, but the text for the link remains in the cell.

Remove All Links in a Sheet

Perhaps you have many links in your sheet. Rather than remove them one at a time, you can take care of them all in one fell swoop.

1. Select the entire sheet using the Select All button (square in the top-left corner). You can also use the keyboard shortcut Ctrl + A on Windows or Command + A on Mac.

2. Right-click anywhere in the sheet, move to View more cell actions, and pick Remove link.

3. You should then see all hyperlinks in your sheet vanish, but any text you used for the links remain.

When you want a fast way to jump to another cell, sheet, or web page, you can add hyperlinks in Google Sheets in various ways.

For other helpful productivity tips, look at how to use find and replace in Google Sheets.

The post How to Add or Remove Hyperlinks in Google Sheets first appeared on Online Tech Tips.

Using COUNTIF in Google Sheets, you can save time and manual work from counting the items “by hand.” Simply enter the data range and criterion in the formula to get your result in seconds.

About the Google Sheets COUNTIF Function

COUNTIF is a variation of the COUNT function that lets you count cells in a dataset that meet a particular condition. As examples, you may want to count the number of students with a certain GPA, employees who’ve worked a specific number of years with your company, or even car models that begin with a particular letter. With COUNTIF, you have many possibilities to count the data you need quickly.

The syntax for the function’s formula is COUNTIF(range, condition) with these options for the condition argument:

• Use a question mark (?) as a wildcard to match a single character.
• Use an asterisk (*) to match zero or more adjacent characters.
• To match a question mark or asterisk, place a tilde (~) before it, for example, ~? or ~*.
• Use the equal sign (=), greater than (>), and less than (<) symbols to compare number equality.
• Enclose a character string in quotation marks.

How to Use COUNTIF in Google Sheets

The best way to explain how to use a function and its formula is to see them in action. Let’s look at a handful of formula examples for COUNTIF in Google Sheets.

Count Items Greater Than or Equal to a Value

Using a student grade example, we’ll count the number of students who have a GPA greater than or equal to 3.5.

Select the cell where you want the result, this is where you’ll type the formula. Enter the following formula making sure to begin with the equal sign and include a comma between the arguments:

=COUNTIF(B2:B7,”>=3.5”)

To break down the formula, B2:B7 is the range of cells and “>=3.5” is the condition for greater than or equal to 3.5.

As you can see, we receive a result of 3 which is correct. There are three students that have a GPA of 3.5 or higher.

Related: Having trouble with formulas? Check out our guide to troubleshooting Google Sheets formula parse errors.

Count Items Less Than a Value

In this next example, let’s count the number of employees who have worked for us less than 10 years.

Select the cell where you want the results and enter the following formula:

=COUNTIF(B2:B10,”<10″)

To break down the formula, B2:B10 is the data range and “<10” is the condition for less than 10.

We receive 5 as our result which is correct. Notice that Steve Stone has worked for us for 10 years, but he is not part of the result because 10 is not less than 10.

Count Items That Start With a Letter

For another example, let’s count the number of makes of cars, not models, that start with the letter H.

Select the cell where you want the formula result and type the following:

=COUNTIF(A2:A9,”H*”)

To break down this formula, A2:A9 is our range and “H*” is the condition for the first letter H and the asterisk wildcard for any following letters.

Here, we receive a result of 4 which is correct; we have four car makes that start with the letter H.

Count Items Matching Cell Values

Maybe the condition you want to match already exists in another cell. You can use this by placing the string in quotation marks, adding an ampersand (&), and entering the cell reference.

Here, we’ll count the number of times that the value in cell A15 (600) appears in our dataset.

Select the cell where you want the result and enter this formula:

=COUNTIF(A2:D13,”=”&A15)

Breaking down the formula, A2:D13 is the data range, “=” is the operator (string) in quotes, and &A15 is the value we want to match in cell A15.

We receive 3 as our result which is correct, we have three items matching 600.

Count Items Matching Text Values

For one final example, you might want to count the number of cells with specific text. Let’s count the total number of expenses for Fuel.

Select the cell where you want the result and enter this formula:

=COUNTIF(A2:A8,”Fuel”)

To break down this formula, A2:A8 is the data range and “Fuel” is the condition to match.

We receive our result of 3 which is correct. Note: COUNTIF is not case sensitive, so you could enter “FUEL” or “fuel” for the text string and receive the same result.

When you need to count a number of items but only those that meet your criterion, the COUNTIF function in Google Sheets works like a dream.

If you want to count items that match multiple criteria, you can use the COUNTIFS function. Check out our tutorial for using COUNTIFS along with similar functions in Microsoft Excel and apply the same formula structure in Google Sheets.

The post How to Use COUNTIF in Google Sheets first appeared on Online Tech Tips.

With four different methods for inserting bullet points, you can use whichever works best for your spreadsheet. These allow you to create lists within cells or add a bullet point in each separate cell to make an easy-to-read list.

Use a Keyboard Shortcut to Insert a Bullet

This method is ideal if you want to create a bullet list within a single cell. You’ll use keyboard shortcuts to insert the list. The keys you press depend on whether you use Windows or Mac.

Insert Bullets on Windows

1. To add your first bullet point within a cell on Windows, select the cell and press Enter to display the cursor or go to the Formula Bar at the top.
2. Use the key combination Alt + 7 with the 7 being on your numeric keypad.

3. When you see the bullet point, you can insert your list item or add a space before the item if you like.

4. Use the key combination Alt + Enter to drop down to a new line within the cell.

5. Then, follow Steps 2 and 3 above to enter another bullet point.

6. Continue this process until you complete the list. Then, press Enter.

Insert Bullets on Mac

1. To add your first bullet point within a cell on Mac, select the cell and use the key combination Option + 8.

2. When you see the bullet point, you can insert your list item or add a space before it.

3. Use the key combination Control + Return to add a line break within the cell.

4. Then, follow Steps 1 and 2 above to enter another bullet point.

5. Continue this process until you finish your list. Then, press Return.

Copy and Paste a Bullet

This next option also works if you want a bullet list in a cell but prefer something other than the default black dot, like an emoji or checkmark. You can also use this method to add bullets to individual cells if you like. You’ll simply copy and paste the symbol from another app.

1. Select the symbol you want to use as a bullet from another spot on your computer. Then copy it using the menu or the keyboard shortcut Ctrl + C on Windows or Command + C on Mac.

2. Choose the cell where you want to paste the symbol. Select Edit > Paste from the menu, right-click and pick Paste, or use a keyboard shortcut. Use Ctrl + V on Windows or Command + V on Mac.

3. When the symbol appears in the cell, add your list item, with or without a space before it.

4. If you only want that one item in the cell, press Enter or Return and you’re set.
5. If you want to add another point within the cell, use Alt + Enter on Windows or Control + Return on Mac to add a new line.
6. Then, paste the symbol again and enter your next list item.

7. When you complete the list, press Enter or Return.

Enter the CHAR Function and Formula

Another way to insert bullets is by using the CHAR function and its formula in Google Sheets. The syntax for the formula is CHAR(number).

Use the Bullet List Number

If you simply want the default black dot for the bullet, the number is 9679. You then include the list item in the CHAR formula within quotes or using a cell reference. Let’s look at a couple of examples.

Here, we’ll use the text in cell A1 as our list item without a space using this formula:

=CHAR(9679)&A1

To break down the formula, you have CHAR(9679) for the bullet point, an ampersand to add to the string, and A1 for the text.

For another example, we’ll insert the bullet point and include a space with “abc” as our list item using this formula:

=CHAR(9679)&” “&”abc”

To break down this formula, you have CHAR(9679) for the bullet point, an ampersand to add to the string, a space enclosed in quotation marks, another ampersand, and the text within quotes.

Use a Unicode Character

You can also use the Unicode number for the symbol you want in the formula. You can obtain the number from the List of Unicode Characters on Wikipedia or your own source.

As an example, we’ll use the Latin Letter Bilabial Click, which is number 664.

We’ll then add a space and “abc” as our list item with this formula:

=CHAR(664)&” “&”abc”

Create a Custom Number Format

This final method we’ll explain is perfect if you want to format existing cells with bullet points. You can create a custom number format that you then apply to any cells you like. Just note that it may affect the existing number formats you use for the cells.

1. Select Format > Number and choose Custom number format at the bottom of the pop-out menu.

2. When the Custom Number Formats window opens, enter the symbol you want to use in the box at the top. You can use an asterisk, a dash, or paste a special symbol as described earlier.

3. If you want a space between the bullet and text, enter a space.
4. Then, add the @ (At) symbol.
5. Select Apply to save the format and apply it to the current cell.

6. When the window closes, you won’t see the symbol you included (if the cell is blank) until you type within the cell and press Enter or Return.

7. To apply the new format to other existing cells, select the cells, go to the Format menu, move to Number, and choose the format you created from the list.

You’ll then see your selected cells update to use your custom bullet format.

You can also access and use the same custom format in other worksheets or Google Sheets workbooks when you sign in with the same Google account.

With these simple ways to insert bullet points in Google Sheets, you can easily create a list just like in Google Docs. For related tutorials, look at ways to use a checkmark in Microsoft Excel.

The post How to Insert and Use Bullet Points in Google Sheets first appeared on Online Tech Tips.

You can include task descriptions and color-coding. Then, view your timeline by week, month, quarter, or year for the best picture. Here’s how to set up and use the Google Sheets project timeline.

Timeline View Availability

To use the Timeline View, you should have one of the Google Workspace editions. These include Essentials, Business Starter, Standard, and Plus, Enterprise Essentials, Starter, Standard, and Plus, Education Fundamentals, Standard, and Plus, and Frontline.

Set Up the Project Data

While there’s no required way to set up your data to use the Timeline View, there are recommended columns and you do need to have at least one column with dates.

If you’re using Google Sheets formulas to determine start or end dates, just make sure the results are formatted as dates.

To make the most of the timeline, try to include the following columns:

• Task: Include the task or name of it.
• Start date: Add start dates for each task.
• End date: To view the full duration of a task on the timeline, include end dates.
• Description: Optionally include further details about each task.
• Duration: Add the length of time between the start and end dates for the project tasks. You can use days or hours, minutes, and seconds.

For help pulling in data from another spreadsheet, check out our tutorials for importing data into your sheet or for converting your Excel workbook to Google Sheets.

Create the Timeline

Once you have your data set up, you can create the timeline. If you want to make adjustments to the data, you can still do so, and the timeline will update automatically.

1. Select the data you want to use for the timeline, including headers for the above columns.
2. Go to the Insert tab and choose Timeline.

3. When the Create a Timeline window opens, confirm or edit the data range and select OK.

You’ll then see a new sheet added to your workbook labeled “Timeline 1” which looks similar to the Gantt chart.

From there, you can use different views, color-code the cards, and group the timeline tasks.

Use the Timeline View

When the timeline opens, you should see the Settings sidebar open on the right at the same time. If not, choose Settings on the top right. Here, you can select the required columns and optional fields.

Pick the columns for the Start date, End date or duration, and Card title.

You can also select columns for the optional fields at the bottom of the sidebar:

• Card color: If you want to color-code your cards, choose a column to base the color on.
• Card detail: You can choose your Description column here to display the task details.
• Card group: Group your tasks by a column like start, end, or duration if you like.

Timeline Views

As mentioned, you can view your timeline by various timeframes. At the top, use the drop-down menu to pick from days, weeks, months, quarters, or years.

To the right, use the next drop-down boxes to pick between a comfortable or condensed view or zoom in on the timeline.

Card Details

Depending on the view you choose to see your timelines, you may not see all details on the task cards. Simply select a card on the timeline and you’ll see the Card details sidebar open on the right.

From there, you’ll see the data in each of the columns. To color-code a card, choose a color from the drop-down box. To use your own color, pick Customize to open the palette.

If you need to make changes to the details for a card (task), select Edit data at the bottom of the sidebar.

You’ll then be directed to the task in your spreadsheet. Make your changes and the timeline will update in real-time.

You can also adjust your data on the sheet at any time to see the updates on your timeline.

Staying on task and up to date is easier with the Google Sheets project timeline view. You can see your project milestones and tasks, and then share the project schedule with team members or stakeholders.

For more, look at our step-by-step guide for sorting or filtering by color in Google Sheets.

The post How to Use Google Sheets Timeline View to Manage Projects first appeared on Online Tech Tips.

To do that, you’ll have to first learn how to set the print area in Google Sheets.

Set the Print Area to Only Print Select Cells

If you have your data only in specific cells in your worksheet, you can configure an option so that Google Sheets only prints those select cells.

1. Launch a web browser on your computer, access Google Sheets, and open the workbook you’d like to print.
2. Select the cells you want to print in your workbook.

3. Choose File > Print from Google Sheets’ menu bar. Alternatively, press Ctrl + P (Windows) or Command + P (Mac).

4. Select Selected cells from the Print dropdown menu on the right. This ensures only the selected cells are printed.

5. Select Next at the top of the screen.
6. Follow the standard print process to print your cells.

Set the Print Area to Print the Entire Sheet

If your workbook has multiple worksheets, and you’d like to print a specific worksheet, you can get Google Sheets to do that.

1. Open your workbook with Google Sheets in a web browser.
2. Choose the worksheet you’d like to print at the bottom of your workbook.

3. Select File > Print from Google Sheets’ menu bar.
4. Choose Current sheet from the Print dropdown menu on the right. You’ll see your current worksheet’s preview on the left.

5. Select Next at the top and follow the standard print wizard to print your worksheet.

Set the Print Area to Print the Entire Workbook

Sometimes, you may want to print your entire Google Sheets workbook, which could include multiple worksheets. Google Sheets has an option to do that, too.

When you print an entire workbook, make sure your printer has enough ink to print your data. Also, feed enough paper into your printer’s paper tray to ensure there’s no interruption when you print your workbook.

1. Access your workbook with Google Sheets in a web browser.
2. Choose File > Print from Google Sheets’ menu bar.
3. Select Workbook from the Print dropdown menu on the right.

4. Choose All sheets from the Selection dropdown menu.
5. You’ll see a preview of your entire workbook on the left. If this looks good to you, select Next at the top-right corner.
6. You’ll then select your printer, choose the paper size, and other options to finally print your workbook.

Set the Print Area to Print Headers on Each Page

If your Google Sheets data is spread across multiple pages, you may want to include the headers on each page that you print. This makes reading the data easier as you know exactly what column is for what data on each page.

To print column headers on each page, you’ll first freeze the header row:

1. Open your workbook and access the worksheet you want to print.
2. Select View > Freeze > 1 row from Google Sheets’ menu bar to freeze the first row in your worksheet.

3. Choose File > Print to configure the print options for your worksheet.
4. Select Headers & footers and enable Repeat frozen rows on the right.

5. Customize any other options if you’d like. Then, select Next at the top-right corner.

Customize the Print Area With Custom Page Breaks

You can add custom page breaks to your Google Sheets worksheets to let your printer know where to stop printing a page and start a new page.

To add a page break in your worksheet:

1. Select File > Print from Google Sheets’ menu bar.
2. Choose Set Custom Page Breaks in the sidebar on the right.

3. Drag the blue dotted line in your worksheet to specify a page break. Then, select Confirm Breaks at the top-right corner.

4. If you’re not happy with the specified page breaks, select Reset at the top to reset the page breaks.
5. Select Next at the top-right corner to continue to print your worksheet with your custom page breaks.

It’s Easy to Customize and Set the Print Area in Google Sheets

Google Sheets offers you the flexibility to print whatever part of your workbook you want. This way, you can focus on the main data that you’d like to print while leaving everything else out. We hope this guide helps you out.

The post How to Set the Print Area in Google Sheets first appeared on Online Tech Tips.

Let’s look at 10 of the most helpful formulas in Google Sheets that are designed to save you time.

Setting Up a Formula

Using a formula on Google Sheets is easy. In a text box, just type “=” followed by the formula you want to use. There should be a little question mark box that pops up as you type a formula. Select that box to learn more about using that formula.

For example, if we type in “=add(“ we’ll learn more about how to use this function. Sheets tells us that it returns the sum of two numbers and that we can format it by adding value 1, a comma, then value 2 within the parenthesis.

That means that typing “=add(1,3)” should return the answer to 1+3.

Instead of typing “1” and “3”, we can also refer to cells on Sheets. By adding the cell name (denoted by the column followed by the row), Sheets will automatically perform the formula for those given cells.

So, if we type in “=add(B3,C3)”, the values from cells B3 and C3 will be added. It gives us the same answer. 

This approach works for most of the formulas in the following sections.

10 Helpful Formulas in Google Sheets to Save You Time

The following are 10 of the most helpful formulas in Google Sheets to save you time. There are plenty of hidden Sheets formulas that can help you, but these are ones that the everyday user can use and benefit from.

1. Ditch the Calendar with TODAY()

It feels like we catch ourselves asking “what’s today’s date?” every single day. In Sheets, it becomes an even bigger issue — tracking deadlines and counting down dates requires a daily update. That is, unless you use the TODAY() function.

After putting it in a cell, you’re left with today’s date. It updates every day and doesn’t require you to do anything.

Excel has a whole range of built-in date and time formulas. This makes it easy to find a difference between two dates. With the TODAY() function, you can have a constantly changing second variable.

2. COUNTIF() Makes Conditional Counting Easier

Sheets also lets you do a little bit of coding using what’s known as “conditional counting”. This is when you only count an item if it falls within certain criteria.

For example, if you want to know how many of your survey participants own two or more cars, you can use a COUNTIF() statement. 

The condition would be a value that’s greater than or equal to two.

Without using this formula, you would have to scan through the data by hand and find the number you’re looking for. 

There are other IF functions like this you can use as well such as SUMIF, AVERAGEIF, and more.

3. Add Cells with SUM()

One of our favorite functions is the SUM() function. It adds together all the quantities within a selected range. A sum is just a bunch of numbers added together. For example, the sum of 2, 3, and 4 is 9.

You can use SUM() to add a whole column together. 

As you keep adding new rows, the SUM() function will automatically update and add the new item. It can be used for positive, negative, or zero-value numbers.

Want to make a quick budget? SUM() will be your best friend.

4. Put Text Together with CONCATENATE()

If you want to add strings of text together, you can’t just use addition. You’ll need to use a programming term called concatenation. This term refers to the idea of adding text together. 

You can concatenate multiple separate cells together and create a single cell that includes all of the text. Using CONCATENATE() is the automatic way of performing this process.

Without this formula, you would have to copy and paste text separately into a single cell. This process takes a lot of time when you’re looking at files that contain multiple rows.

This formula is great if you want to combine people’s first and last names or create personalized messages.

5. TRIM() Removes Unwanted Extra Spaces

Whenever blocks of text are copied and pasted from other sources, formatting becomes a nightmare. Many times, you’ll have unwanted spaces that ruin the overall format.

You can either manually delete every space, or you can use the TRIM() command in Google Sheets. This command reduces the number of spaces in a message and leaves you with only one space between words.

This could also be helpful for Sheets that are set up to accept written responses from users. It removes the human error of double-spacing by accident and results in a document that looks really clean.

6. TEXT() Converts Values to Other Formats

One of the big headaches in Google Sheets is reformatting values that are put into a cell. Sheets will try to guess which format you want, but you could be left changing a whole section of cells.

Alternatively, you can try the TEXT() function. This formula will change the formatting from general text, strings of words, values, dollar amounts, percentages, dates, and a number of other options.

If you’re copying and pasting a chunk of data that you need to turn into dollar values, for example, you can use this formula to do it. You can also change the number of decimals shown after the value.

7. Built-In Translation with GOOGLETRANSLATE()

Sheets can also expedite your translations. If you have a column of phrases in a language you know, you can create multiple columns of a translated phrase into different languages.

For example, we can translate common phrases from English to Spanish, French, and Japanese in a single document. This can help an international team understand the same document.

This formula is courtesy of Google’s translation services. You can also use this formula to find out what language a certain text is by copying and pasting it into Sheets. 

You can keep the source language as “auto”, and Sheets will determine what language it is, then translate it into the selected language you can understand.

8. Quickly SORT() Data

Maintaining a sorted list of data can be a headache. As new data is inputted or data is changed, your hand-sorted list will get disrupted.

Rather than manually sorting the data every time it’s inserted, you can use the built-in SORT() command. This automatically generates a list of ascending or descending values. 

After running, it compiles the sorted list in its own row or column. You can then use this range to take additional steps in your Sheet.

9. Swap Rows and Columns with TRANSPOSE()

Did you accidentally swap the row and column of your sheet when you were first making it? Or maybe a client sent you over data that you’d like to add to your document, but the rows and columns need to be switched.

Whatever the case, you can try the TRANSPOSE() command.

Here’s how it works: The rows and columns are flipped around. The corresponding data in the table is also changed to ensure it’s in the correct place after the flip.

As the old data is changed, the transposed table also updates and changes.

10. Quickly Find the AVERAGE()

Using the AVERAGE() command in Sheets will spit out the mean of a given set of numbers. If you don’t remember, the average is when you add up all the numbers and divide by how many numbers there are.

This is a useful math command. Without the AVERAGE() formula, you’ll be left solving it with paper and a calculator.

Just select the range of numbers you want to find an average for, then Sheets will do the rest.

Use Formulas to Improve Your Google Sheets Efficiency

Time to open Sheets and try out these 10 helpful formulas you just learned. They’re designed to save you time and make your life easier. Try them out on your own and see how easy they are.

Have a favorite formula that we missed? Leave a comment and let us know.

The post 10 Helpful Formulas in Google Sheets to Save You Time first appeared on Online Tech Tips.