Tips to format & clean up data

On this page

• Add your logo to an invoice
• Add checkboxes to spreadsheets
• Color-code your tabs
• Color-code data updates
• 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

               

Want advanced Google Workspace features for your business?

Try Google Workspace today!

 

 

Expand all  |  Collapse all

Add your logo to an invoice

Make your spreadsheets look consistent and professional by putting your organization’s logo at the top of invoices. Just add the image to the top row. Set the row to "freeze" so it remains visible as you scroll. 

Learn how

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

Learn more at the Google Docs Editors Help Center

 

Add checkboxes to spreadsheets

Use checkboxes to make Google Sheets more interactive. You can use checkboxes with charts, filters, pivot tables, and formulas.

• Check off items in a to-do list
• Track project tasks
• Take attendance
• Create dynamic charts

Learn how

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 Checkbox.
4. To remove checkboxes, select the checkboxes you want to remove and press Delete.

Learn more at the Google Docs Editors Help Center

 

Color-code your tabs

 If you create multiple sheets in your spreadsheet file, you can change the color of each sheet's tab to quickly tell them apart.

Learn how

1. On a sheet tab, click the Down arrow .
2. Click Change color and select a color.
3. (Optional) To choose a custom color, click Custom and select a color using the sliders or by entering a color number.

 

Color-code data updates

Create a list of options for entering data in a cell or range of cells. Then assign a color for each option so you can get a quick visual summary at a glance.

• If your sales team is tracking their revenue in a spreadsheet, set up rules to change the cell color when they reach their target.
• Add status markers to project trackers. For example, if a task is marked “Complete,” the background color can automatically change to green.
• Assign a color for each person on your team so they can quickly see all the tasks they’ve been assigned.

Learn how

Use conditional formatting

1. In Google Sheets, highlight the cell or range of cells where you want to add a list of options.
2. Click DataData validation.
3. Click Add rule.
4. Under Criteria, select Dropdown.
5. Enter your options. 
6. (Optional) To assign color, at left, choose a color for each option.
7. (Optional) To add more options, click Add another item.
8. Click Done.

 

Standardize data entries with lists

Reduce the chance of data-entry errors by limiting choices in Sheets. For example, if you have a status column, you can give choices, such as Done, In Progress, and Not Started. You specify the options and they appear in a drop-down list in each cell in the column.

Learn how

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 DataData 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

If your data involves entering email addresses, reduce entry errors by validating the email format in Sheets.

Learn how

Use data validation for email addresses

1. In Sheets, open a spreadsheet.
2. Select the column that will contain the email addresses.
3. Click DataData validation.
4. Next to Criteria, select Textcontains.
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 [e-mail].
8. Click Save.

 

Split data into columns

You can split clearly defined data, such as text separated by commas, into separate columns in Sheets. For example, a single column with Last name, First name data can be split into 2 columns: Last name and First name.

Learn how

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.

2. At the top, click Data Split text to columns.
3. To change which character Sheets uses to split the data, next to "Separator" click the dropdown menu.
4. To fix how your columns spread out after you split your text, click the menu next to "Separator"  Detect automatically.

Note: After you paste the data, you can click Paste Split text to columns.

 

Swap rows and columns

If you want to rotate what you have in columns to rows, or vice versa, you can do that using the TRANSPOSE function in Sheets. For example, you might want to swap column headings with row headings.

Learn how

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.

For example:

To transpose rows 1 and 2 of columns A through E, you enter: =TRANSPOSE(A1:E2).

 

Space rows & columns evenly

In Sheets, you can resize all rows, or all columns, to use the same amount of space.

Learn how

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 FormatWrapping, 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

Duplication errors and spaces are a common problem that can be costly and easy to miss, especially in big data. 

• Remove unwanted, duplicate data. Cells with identical values but different letter cases, formatting, or formulas are considered duplicates.
• Remove extra leading spaces, trailing spaces, or excessive spaces from your data. This helps reduce problems when searching for data strings.

Learn how

Remove duplicate data

1. In Sheets, open a spreadsheet.
2. Select the data range that you want to remove duplicate data in.
3. Click DataRemove 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 DataTrim whitespace.
   Nonbreaking spaces aren’t trimmed.

 

Format currencies in your spreadsheet

If you’re working with international teams or clients, you can add international currencies to your financial spreadsheets in Google Sheets.

Learn how

1. On your computer, open a spreadsheet in Google Sheets.
2. Highlight the data you want to format.
3. Click Format 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.

You can also change a few properties about the currency (for example, how many decimal places to show) by clicking the drop-down menu in the right corner of the input box and choosing a desired option.

Related topics

• Tips to analyze data
• Tips for finance 

 

_{Google, Google Workspace, and related marks and logos are trademarks of Google LLC. All other company and product names are trademarks of the companies with which they are associated.}