How to Clean Google Sheets data?
Google Sheets are used to enter 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 pile 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 tips below.
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.
- Go to Google Sheets.
- Select the column that contains the email address.
- Click the data.
- Go to Data Validation.
- 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.
- Open a spreadsheet.
- Select the column where you want to add the options.
- Click Data.
- Hit Data validation.
5. Select the List of items.
6. Enter the valid options separated by commas.
7. Check the show drop-down list in the cell box.
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.
- Go to sheets.
- In an empty cell, enter =IMPORTRANGE.
- In the Parenthesis bracket, add the URL of the spreadsheet.
- Copy and paste the URL from a spreadsheet including the data you want to import.
- Add the sheet name and range of cells to import.
- Press enter.
- Click Allow access to connect the two spreadsheets.
Remove duplicate data and extra space.
While dealing with big datas, duplication errors are 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. Extra space causes many problems when searching for data strings. Using the sheet, remove extra, leading, trailing, or excessive spaces in a spreadsheet.
Remove duplicate data in the sheet
- Open a Google sheet.
- Select the data range that you want to remove duplicate data.
- Click Data
- 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
- Open a spreadsheet.
- Select the data range that you want to remove extra space.
- Click Data.
4. Find Trim whitespace.
5. In the status window, click OK.
6. Trim workspace.
7. Click ok.
Navohosting is one of the best Google Workspace reseller in India. We offer digital services to our clients.
The list of services available with us are listed below.
- Google workspace
- Domain and Hosting
- Web Design
Features to enhance Google Sheets visibility
Google Sheets Beginners Tips & Guide
for the users. To know more about the services provided by us contact us.
