Clean Google Sheets data | New Tips

Clean Google Sheets data

Google Sheets are used to entering datas in the spreadsheet cells. The google workspace offers office automation tools for all business users. Google sheets are used to create and format spreadsheets online. Sheets can be used to work anywhere and collaborate online and offline. Data entry can be made in spreadsheets to work on big datas. Datas piled up in the sheets every time you enter datas. In sheets, you can reduce the data entry errors, remove the spaces, remove duplicate data, and combine two or more sheets into a single sheet. Clean the google sheets data using the below tips.

 

Email Address Validation in Sheets

Google sheet is used to enter the datas in the sheet. If you are entering the email address in the sheets, reduce the entry errors by validating the email format in the sheets.

1. Go to google sheets.
2. Select the column that contains the email address.
3. Click the data.
4. Go to Data Validation.
5. Select Text.

 

 

6.Hit the option “Contains”.

7.Enter ‘@’Show warning/ reject input (If someone enters an invalid option)

 

 

8.Choose validation help text

9.Enter a message.

10.Click save.

Reduce Data Entry Errors by Limiting choices in Sheets

You can reduce the data entry error by limiting the choices in spreadsheets. In the status of project data, give choices like In Progress, Not started, or Done. Specify the options displayed as a drop-down list in each cell in the column.

1. Open a spreadsheet.
2. Select the column where you want to add the options.
3. Click Data.
4. Hit Data validation.

 

 

5.Select the List of items.

6.Enter the valid options separated by commas.

 

 

7. Make sure the show drop-down list in the cell box is checked.

 

 

8. Choose Show warning / Reject input to specify what happens if someone enters an invalid option.

 

 

9. (Optional) To show a message to assist with validation, check the Show validation help text box and enter a message. Ex: Please enter a valid value (Done, In Progress, Not Started).

10.Click Save.

11.To see the choices click the arrow in a cell under the column.

 

Combine multiple sheets of Data into a Single sheet

If you have data in a separate spreadsheet, copy it from one sheet to another.

1. Go to sheets.
2. In an empty cell, enter =IMPORTRANGE.
3. In the Parenthesis bracket, add the URL of the spreadsheet.
4. Copy and paste the URL from a spreadsheet including the data you want to import.
5. Add the sheet name and range of cells to import.
6. Press enter.
7. Click Allow access to connect the two spreadsheets.

 

Remove duplicate data and extra space

While dealing with big datas, duplication error is most common. Use google sheets to remove unwanted and complicated data. Normally the cells with the same values but with a different formula, letter case, and formatting are considered duplicates. When you copy and paste big data, extra space is added to the data. While searching for data strings, extra space causes many problems while searching. Using the sheet, remove extra, leading, trailing, or excessive spaces in a spreadsheet.

 

Remove duplicate data in the sheet

1. Open a google sheet.
2. Select the data range that you want to remove duplicate data.
3. Click Data
4. Go to ‘Remove duplicates’.

 

 

5. Select which columns to include and whether the data has headers.

6. Click Remove duplicates.

7. In the status window, click OK.

 

 

Remove extra space in a sheet

1. Open a spreadsheet.
2. Select the data range that you want to remove extra space.
3. Click Data.

 

 

4.  Find Trim whitespace.

 

 

5. In the status window, click OK.

 

 

6. Trim workspace.

7. Click ok.

 

 

The list of service available with us are listed below.

• Google workspace
• Web development
• Web design
• Digital Marketing

 

Google workspace a online business application service offered by us. Features about google sheets are available here for the users. To know more about the  services provided by us contact us.