Basic Aesthetics
Table of Contents
Alternating colors
This is a helpful way to make your spreadsheet clearer, more visually appealing, and help you distinguish between tabs or other documents with similar information.
Highlight the cells that you want to add alternating colors to. Your selection will be highlighted in blue.
2. From the menu on the top of the screen, select the “Fill Color” icon that looks like a paint bucket (circled in green below).
3. Then, select “Alternating Colors” from the bottom of the menu (circled in green below).
4. A menu pane will appear on the right side of the screen where you can select the colors. Select the color you want (or add your own under “Custom Styles”), then click “Done” (circled in green below).
Note: Here, you can also toggle on and off “Header” and “Footer” to add or remove a darker shade of the color in the top and bottom cells of your selection.
5. You’re done! You have now added alternating colors to your selection. To remove the alternating colors, follow the above steps to pull up the Alternating Colors menu pane on the right side of the screen, then, on the very bottom of that section, click “Remove alternating colors”.
Cell Borders
By default, each cell in Google Sheets will have a light gray border. You can change the color, thickness, or pattern of the border or remove the borders altogether to change the look of your spreadsheet.
Highlight the cells that you want to use to add/change a border. Your selection will be highlighted in blue.
2. From the menu on the top of the screen, select the “Borders” icon (circled in green below).
3. From the menu, select any option you want (from the section circled in green below) to add borders to that part of the cell. The option in the top left will add borders everywhere in the cells you selected, the others will only add borders to parts of the cell (such as the left side, right side, top, etc.). If you want to change the color of your borders, select the pencil icon (circled in blue below). If you want to change the weight (thickness) or pattern of your borders, select the line icon (circled in purple below).
4. You’re done! You have now added/edited borders. In my example, I selected the top left border option from the menu and didn’t change the color or weight of the lines. See the example below. To remove the borders you added, select the option in the bottom right of the menu shown in the previous step.
5. If you want to hide all of the borders from the tab (even the default light gray gridlines), select “View” from the top menu (circled in green below), then select “Show” (circled in blue below), then uncheck “Gridlines” (circled in purple below).
The most common reason why I hide the gridlines is when I am making a dashboard and want it to look clean. I wouldn’t recommend removing gridlines from a document that you will have to edit a lot, because it can get confusing to know where to click without the gridlines. When I remove gridlines, I do it after I have everything set up.
6. You’re done! You have now hidden the gridlines from the tab. If you want to show them again, follow the above step to reselect “Gridlines”
Color coding tabs
This is a helpful way to keep a document organized.
Look in the bottom left corner of the document for the tabs. With your mouse, right click on the tab you want to color code, in this case “January Party”.
2. From the menu, select “Change color” (circled in green below) then select your desired color. Repeat for other tabs as necessary.
3. That’s it, you have now changed the color of the tab(s). You can change the color at any time by following these steps. To remove the color, follow these steps, then select “Reset” instead of a color.
Conditional formatting
Conditional formatting will take your spreadsheets to the next level. It can help you more quickly interpret data and it will make your documents look more polished. There are a lot of different ways to use conditional formatting, including adding formula logic, but I‘m just going to cover these 3 common uses:
1. Color scale
2. Basic conditional formatting
3. Highlight duplicates
In this example I will be tallying votes from a film club’s 2024 Oscar Best Picture prediction vote. All vote totals were randomly assigned (using the function =randbetween(1,10) as a Google Sheets bonus function for you).
Open Conditional Formatting Menu
(these first 4 steps are how you start, no matter which type of conditional formatting you want to do)
Highlight the cells (or column) where you want to apply conditional formatting. In this case, B2:B12.
2. Then, click on the paint bucket tool in the top menu (circled in green below). From the bottom of that menu, select “Conditional formatting” (circled in blue below).
3. The conditional formatting menu will appear on the right side of the screen. Here’s what it looks like:
1. Color scale
4. After following the above steps to open the conditional formatting menu, I’m going to apply a color scale. I want to be able to more quickly interpret the results, so I’m going to add a color scale where the lowest votes are in white, the highest are in yellow, and everything in the middle is on a gradient between the 2 colors.
5. On the conditional format rules menu, select “Color scale” (circled in green below).
6. This will toggle you to customize your color scale. By default, it will highlight the lowest numbers in green and the highest numbers in white. I want to change the scale so that the lowest numbers are in white and the highest are in yellow, so click on the color gradient boxes in the middle of the menu (circled in green below)
7. For this example, I’m going to select the center option that starts with white on the left and ends with yellow on the right (circled in green below). You can select any of the color scales, or even click “Custom color scale” on the bottom of this menu to make your own.
8. After making your selection, click “Done” in the bottom right corner of the menu (circled in green below).
If you want to customize your colors or gradient, feel free to play around with the minpoint, midpoint, and maxpoint options below the color scale you set.
9. Now you’ve added a color gradient to the vote total! We can see that 9 is the most votes that any movie received and that went to Barbie (I know Barbie is on the list twice, stay tuned for highlighting duplicates below).
The colors will update automatically if you increase or decrease any of the numbers.
2. Basic conditional formatting
4. After following the above steps to open the conditional formatting menu, I’m going to play around with some basic conditional formatting options.
If you are continuing on to try this way of conditional formatting after trying one of the other methods, you may want to remove the conditional formatting that you have already applied so that it doesn’t get mixed up with the new one you’re applying. See instructions at the bottom of this section on how to remove formatting. In practice, you can absolutely have multiple overlapping conditional formatting rules, it just might be confusing in this case.
5. I want to turn the text red for any vote total of 5 or less so that those movies can be removed from the list for the second round of voting. From the conditional formatting menu, click to open the “Format Cells If” dropdown menu (circled in green below) it will say “is not empty” by default.
6. In this example, we’re going to scroll down on that menu and click “Less than or equal to” (circled in green below). You may want to play around with this and try selecting different options from this menu.
7. Then, you will need to enter the value as it relates to the formatting rule you have selected. In this case, we want to format all numbers 5 or below, so I typed 5 into the box (circled in green below) below the rule I selected.
8. If I do nothing else and just click “done”, then all cells in the range I selected will be highlighted in green if they are 5 or below. I want to change it so that the text will turn red instead. So I’m going to click in the “Formatting Style” section where it says default (circled in green below).
9. In this case, I’m going to select the option with red text and a white background (circled in green below), but you can select any of the 6 preset options or click “Custom format” on the bottom to make you brown.
10. After making your selection, click “Done” in the bottom right corner of the menu (circled in green below).
11. Now you’ve added conditional formatting to the vote total! This made it easier to tell which movies need to be removed from the film club’s second ballot.
12. The “less than or equal to” formatting rule that we used is one of 18 built-in options. All of those rules follow the same process and are pretty logic-based. Once you know how to do one, you could be able to figure out most of the others. I encourage you to test out other rules from that dropdown.
3. Highlight duplicates
4. After following the above steps to open the conditional formatting menu, I’m going to highlight duplicates. Some housekeeping notes before we start–
This is the hardest of the 3 conditional formatting options in this guide. Highlighting duplicates is the one area where Excel beats out Google Sheets, in my opinion. So if these steps aren’t working for you, you could try doing it in excel instead if you have access to that program. Here are steps to highlight duplicates in the excel desktop app.
One difference from the above steps on how to open the conditional formatting menu: make sure to select cells A2:A12 instead of B2:B12 since this time we’ll be working with movie titles instead of the vote totals.
If you are continuing on to try this way of conditional formatting after trying one of the other methods, you may want to remove the conditional formatting that you have already applied so that it doesn’t get mixed up with the new one you’re applying. See instructions at the bottom of this section on how to remove formatting. In practice, you can absolutely have multiple overlapping conditional formatting rules, it just might be confusing in this case.
5. Let’s get started. In the conditional formatting menu, click to open the “Format Cells If” dropdown menu (circled in green below) it will say “is not empty” by default.
6. Then, scroll to the very bottom and select “Custom formula is” (circled in green below).
7. We’re going to write a formula based on the Countif function described below. It’s great if you understand what this formula is doing, but it’s also ok if you don’t yet know all the ins and outs. The formula, especially in this use case, is very copy and paste friendly.
8. Custom formulas here always need to start with an equal sign. Then type (or paste) the following formula: =countif(A:A,A2)>1
See the Countif section below for more detail on this function. All you need to know for now, is that the formula is counting how many times each movie appears on the list. It is going to highlight every movie that appears on the list more than 1 time.
If you copy this formula anywhere else, be sure to update the range and reference since you may want to select a different group of cells.
9. Optional: After entering the formula, you may choose to customize the formatting style (I did pink here because Barbie is the duplicate). See more details on how to customize formatting style above in the previous section “2. Basic conditional formatting”.
10. Then, click “Done” in the bottom right corner of the pane (circled in green below).
11. Now you’ve added conditional formatting to the movie list to highlight duplicates. The color formatting will update automatically, so if you delete one of the Barbies, or change the text of one (even just adding a space at the end) the pink will go away.
Highlighting duplicates in a list of 11 movies wasn’t super practical, but knowing how to highlight duplicates comes in handy when you’re merging lists or working with long contact lists. In those situations, being able to quickly find duplicate names, emails, etc. is more helpful.
12. For more information, see the official instructions from Google Sheets on conditional formatting here.
Removing Conditional Formatting
FYI - If you ever want to remove conditional formatting, highlight the cells where you applied the formatting. Then, select the paint bucket tool and click “conditional formatting” to open the conditional formatting menu. From there, hover over the formatting that you want to delete until the trash icon appears (circled in green below). Click on the trash icon to delete the conditional formatting rule.
Fill color
To change the look of your spreadsheet or draw attention to a particular cell or group of cells, you can change the fill color.
Select the cell(s) that you want to change the color of. Your selection will be highlighted in blue. In this case, I want to highlight the “Invitation web page text” cells in Yellow to help me remember to ask for a template on the next team call, so I selected A5:C5.
2. From the top menu, select the paint bucket icon (circled in green below), then select your desired color.
3. You’re done! You have now changed the fill color of the cell. To remove the color, click the paint bucket icon (circled in green above), then select “reset” on the bottom of the color selection menu.
Paint format tool
This is a helpful tool to copy formatting (text size, font, bold, color/cell color, etc.) from one cell to another so you don’t have to manually change the settings on each cell. This tool exists in google docs (word) and other similar documents as well.
Start by formatting one cell in the way you would like to copy to other cells (see the Text formatting and Fill color instructions for specifics). When it’s formatted how you want it, click on the cell. In this case, I formatted the “Done?” cell (B1) to change the font, text color, and fill color and want to copy that formatting to the “Deadline” cell (C1).
2. Then, select the “Paint Format” icon from the far left side of the top menu (making sure you have already clicked on the cell whose format you want to copy). The icon looks like a roller paint brush (circled in green below).
3. Then, click on the cell(s) where you want to paste the formatting. You could select an entire document, or row, or cell if you wanted. In this case, I clicked on the “Deadline” cell (C1).
4. You’re done! You have now copied the formatting to a new cell(s).
Text formatting
On the top menu, there is a section with options to bold, italicize, strikethrough, or change the color of your text. You can also change the fill color of any cell or cells you want. Since these formatting options are pretty standard across commonly used products like Google Sheets, Microsoft Word, Powerpoint, etc., I won’t be going into much detail.
For more information, see Google’s guide to editing and formatting a document here.
Text wrapping
Sometimes, the text in a cell will be longer than the cell can fit. In those cases, text wrapping is a helpful feature to format your spreadsheet in a clean and clear way. There are only three steps required to wrap text, so most of this section will cover the formatting options.
First, highlight the text or row that you want to edit. Highlighted cells will be highlighted in blue. In my example, I’m working on a task list document and want to wrap the text in column C which is the “Notes” column (C:C). As you can see in the example, my notes start in column C and extend way over to the right side of the screen.
Note: You could always resize the column (see how below), to make it wider to contain more text in a single line; however, sometimes you may want the text to go over multiple lines or you may have additional data to the right of the column that would make resizing it too wide impractical, so it is still useful to know how to wrap text.
Resizing option 1: hover your mouse on the line between columns C and D on the top of the spreadsheet where the column letters are listed and click and drag column C to the right
Resizing option 2: Highlight the row or column you want to resize, right click with your mouse, select “resize” from the menu, then enter a number and click “ok”.
2. After highlighting the row or column that you want to use for text wrapping, select the “Text Wrapping” icon from the menu on the top of the screen (circled in green below).
3. Then, select your desired formatting option: Overflow (circled in green below), Wrap (circled in blue below), or Clip (circled in purple below).
Overflow: This is the default option where the text continues past the boundary of the existing cell. See an example below.
Wrap: This will keep all of the text in its cell and, when the text is longer than the cell, it will make the row taller so the text can show on multiple lines within the cell. See an example below.
Clip: This will prevent the text from extending past the borders of the cell and keep the cell its original size. When the text is clipped, all of your original text is still in the cell, but you will only be able to see as much as can fit in the cell boundaries. See an example below.
4. You’re done! You have now wrapped or clipped the text! To undo this change, follow the above steps and select “Overflow” as the formatting option.
Deleting & adding rows & columns
This is more personal preference than any of the topics. There are a few things that I almost always do when I start working in a new Google Sheets document. I bold all heading text, freeze the top row, and delete the extra rows and columns. Stylistically, I don’t like when there are hundreds of extra rows/columns. I prefer a cleaner look, especially for dashboards. It’s a quick process to delete rows and columns and most people use it to delete data they don’t need, but not to delete the blank excess space.
1. Create a rough outline of how you want to set up the spreadsheet so that you know how much space you will need. You can always add back rows/columns later, so don’t worry about getting it perfect. In this example, I’m using a (fake) staff list for an upcoming event. Currently I have data in the following range: A1:F19. I want to delete everything outside of this range.
2. First, we’re going to delete the columns. Click on the letter of the first column that you would like to delete, in this case G.
3. Without clicking on anything else in the spreadsheet (other than the bar on the bottom if needed to drag) scroll all the way to the right until you see the last column. In this case the last column is Z.
4. Hold down the shift key on your keyboard and click the letter Z in the column label. All columns in your selection, in this case G through Z should be selected in blue now.
Double check to make sure you aren’t accidentally selecting any columns with data that you want to keep.
Note: the screenshot below only shows columns N through Z because I couldn’t get G through M to fit on screen, but they are selected too.
5. Right click on a column label in the blue selection and click “Delete Column” (circled in green below) from the menu.
You can always delete 1 or a few columns by selecting the column(s), right clicking, and selecting “delete columns” if you just want to remove a few from a specific place in the document.
6. The columns you selected (G:Z) are now gone!
7. Next, we’re going to delete the extra rows below the table. This will follow the same steps as deleting the columns, but I have a shortcut since there are so many rows.
8. In the top left corner of the spreadsheet near the menu and formula bar you will see the name of the cell or range that you have selected. In this case, I clicked on cell A1, so that’s that it shows in that box (circled in green below)
9. That box usually shows you what you have selected, but it can also work in reverse. If you type a cell or range name in that box, it will select it. In this case, my table has data in rows 1:19. So I want to delete everything starting with row 20 and going to the bottom of the spreadsheet. I’ll start by typing the row number 20 followed by a colon so it looks like this: 20:
10. I know that new Google Sheets have 1000 rows and 26 columns (A-Z). However, usually while I’m setting up the initial table I delete a row leaving 999. So, in this case, I’m going to type 20:999 into the selection box and hit enter on my keyboard. When I hit enter, it will automatically select rows 20 through 999.
The reason I don’t try 1000 as the upper limit here is that, If I at some point deleted a row (something I usually do), then I’ll get an error message and have to start the selection over. So this may be a personal quirk. Feel free to select 1000.
11. Now, like with the columns, right click anywhere in the blue area and select “Delete rows 20-999” from the menu.
If you end up with any extra blank rows on the bottom, just select them manually, right click, and delete.
12. Now you have a cleaner and more focused spreadsheet.
13. Finally, if you ever need to add a column or row, highlight the row(s) or column(s) next to where you want to add blank cells. Right click and select either “Insert 1 column left” or “Insert 1 column right”, (circled in green below) depending on where you want the new cells to go.
14. Another option if you want to add a lot of rows to the bottom of the spreadsheet, just type the number of rows you want into this box in the bottom left corner of the screen and click “Add”.
15. Advanced tip:
If you’re ever making a complicated dashboard or anything where you want to have certain sections blend in to the gray background color on the screen when cells are gone, the hex code for the background is currently #f8faf8. If you make that the cell fill color and hide gridlines (select “View” on the top menu, then “Show” then uncheck “Gridlines”), it will give the appearance of removed cells.
16. For more information, see the official instructions from Google Sheets on deleting, adding, or moving columns & cells here.