Recent Searches

No recent searches

    Popular Articles

      Articles
      View all
        Topics
        View all
          Tickets
          View all
            no results

            Sorry! nothing found for

            Tips to format & clean up data

            Modified on Mon, 22 Sep at 1:11 PM

            On this page

            • Add your logo to an invoice
            • Generate data analysis & insights with Gemini
            • Add checkboxes to spreadsheets
            • Standardize data entries with lists
            • Validate email addresses
            • Split data into columns
            • Swap rows and columns
            • Space rows and columns evenly
            • Remove duplicate entries & spaces
            • Format currencies

            Add your logo to an invoice

            Add an image to a spreadsheet

            1. On your computer, open a spreadsheet in Google Sheets.
            2. Click the cell where you want to add an image.
            3. Click Insert and then Image. 
            4. Select to place your image in the cell or over the the cells. Cells with an image cannot also have text.
            5. Choose an image or take a snapshot.
            6. Click Open or Select.

            Generate data analysis & insights with Gemini

            1. On your computer, open a spreadsheet from Google Sheets.
            2. At the top right, click Ask Gemini .
            3. In the side panel, create your own prompt with references from your sheet or generic cell names. Examples of prompts:
              • "Identify trends in this table."
              • "How can I show regression and prediction of this data?"
              • "What analysis can you help me perform with this sheet?"
              • "Help me understand month-to-month food prices."

            Add checkboxes to spreadsheets

            1. On your computer, open a spreadsheet in Google Sheets.
            2. Select the cells you want to have checkboxes.
            3. In the menu at the top, click Insert and then Checkbox.
            4. To remove checkboxes, select the checkboxes you want to remove and press Delete.

            Standardize data entries with lists

            Create a list for data validation

            1. In Sheets, open a spreadsheet.
            2. Select the column where you want to add the options.
            3. Click Dataand thenData validation.
            4. Next to Criteria, select List of items.
            5. Enter the valid options separated by commas.
            6. Make sure the Show dropdown list in cell box is checked. 
            7. Select Show warning or Reject input to specify what happens if someone enters an invalid option.
            8. (Optional) To show a message to assist with validation, check the Show validation help text box and enter a message. For example: Please enter a valid value (Done, In Progress, Not Started).
            9. Click Save.
            10. (Optional) To see the choices, click the arrow in a cell under the column.

            Validate email addresses

            Use data validation for email addresses

            1. In Sheets, open a spreadsheet.
            2. Select the column that will contain the email addresses.
            3. Click Dataand thenData validation.
            4. Next to Criteria, select Textand thencontains.
            5. In the text box next to contains, enter @.
            6. Select Show warning or Reject input to specify what happens if someone enters an invalid option.
            7. (Optional) To show a message to assist with validation, select Show validation help text and enter a message. For example: Please enter a valid email address, such as xxx@xxx.xxx.
            8. Click Save.

            Split data into columns

            1. On your computer, open a spreadsheet in Google Sheets.
            • If the data’s already in the sheet, select the cells you want to split.
            • If the data isn’t in the sheet yet, paste it.
            1. At the top, click Data and then Split text to columns.
            2. To change which character Sheets uses to split the data, next to "Separator" click the dropdown menu.
            3. To fix how your columns spread out after you split your text, click the menu next to "Separator" and then Detect automatically.

            Swap rows and columns

            1. In Sheets, open a spreadsheet.
            2. In an empty cell, type =TRANSPOSE.
            3. In parentheses, enter the references to the rows or columns that you want to transpose.
            4. Press Enter.

            Space rows & columns evenly

            Evenly space rows & columns

            1. On your computer, open a spreadsheet in Google Sheets.
            2. Select the rows or columns you want to resize.
              Tip: To select all rows and columns in the sheet, click the button in the top left corner of the sheet.
            3. To resize rows:
              1. Point to a row border on the numbered row bar. The pointer changes to a vertical arrow.
              2. To change the height of selected rows, drag the row border in the row bar.
            4. To resize columns:
              1. Point to a column border on the column-heading bar. The pointer changes to a horizontal arrow.
              2. To change the width of selected columns, drag the column border in the heading bar.

            Automatically expand cells to show content

            In Sheets, you can format cells to automatically expand to show content. You can also set content to flow into adjacent empty cells without expanding cell size.

            1. On your computer, open a spreadsheet in Google Sheets.
            2. Select the cells you want to format.
            3. Click Formatand thenWrapping, then select an option:
              • Overflow—Allow content to flow into adjacent empty cells. Overflow saves vertical space and shows as much content as possible.
              • Wrap—Automatically expand cells to show all content. Wrap lets you view all content in selected cells.
              • Clip—Hide content that doesn't fit in existing cells. Clip shows only the content that fits in the cell.

            Remove duplicate data & spaces


            Remove duplicate data

            1. In Sheets, open a spreadsheet.
            2. Select the data range that you want to remove duplicate data in.
            3. Click Dataand thenRemove duplicates.
            4. Select which columns to include and whether the data has headers.
            5. Click Remove duplicates.
            6. In the status window, click OK.

            Remove extra spaces

            1. In Sheets, open a spreadsheet.
            2. Select the data range that you want to remove extra spaces in.
            3. Click Dataand thenTrim whitespace.
              Nonbreaking spaces aren’t trimmed.

            Format currencies in your spreadsheet

            1. On your computer, open a spreadsheet in Google Sheets.
            2. Highlight the data you want to format.
            3. Click Format and then Number.
            4. Click Custom currency.
            5. Search in the menu text box to select a format. You can also add your own custom currency format into the text box.
            6. Click Apply.

            Was this article helpful?

            That’s Great!

            Thank you for your feedback

            Sorry! We couldn't be helpful

            Thank you for your feedback

            Let us know how can we improve this article!

            Select at least one of the reasons
            CAPTCHA verification is required.

            Feedback sent

            We appreciate your effort and will try to fix the article

            Preview
            0 of 0