To remove the alternating row colors that have been applied with conditional formatting, go to the Format menu then click on the Conditional formatting option. Sumit Last updated November 10, 2020 . Click on it to remove it from the selected range A1:K20. It is a perfect tool for automating day-to-day tasks using the JavaScript-based cloud platform. To apply color to all even columns, type =ISEVEN (COLUMN ()). If you insert new rows or columns before or after the formatted range, Google Sheets will also automatically apply the alternate colors settings. First, select the cells you want to apply the formatting to and click the Formatmenu and select Alternating colors. If the row is even, it will return the logical output TRUE and get the formatting applied, else it will return FALSE and get no formatting. Let's take a look at some easy steps below to color alternate rows using the Google sheets built-in method first. Click on the Extensions menu and select the Apps Script option. Also, you want the color to change dynamically reflecting the data changes. It's almost the equivalent of of tables in Excel. This will apply a basic alternate color scheme to each row of your data set and open the Alternating Colors panel on the right, so you can make further changes. Lets take a look at how to color alternate rows using conditional formatting! To apply a background color to alternate rows, go to conditional formatting. Like with changing the color of cells, when changing text color you can select a single cell, a range, a row, or a column and then change the color to anything that you want. Open the sheet, access Conditional formatting, select a range, and input =ISEVEN(COLUMN()) as a custom formula. Hit Done and make sure that your document is saved (Google Docs automatically save unless youre offline). A menu will pop up on the right side of your screen. Here are a few simple notes you should know about coloring alternate rows or columns in Google Sheets: Thats all! Now, lets take a quick look at the syntax of the formula and what does each of its parts means to better understand how it works! If youd want to color the odd rows, too, you should create another rule, with the =ISODD function instead. One thing Google Sheets lack is the ability to add table objects to store your data. Select an array you want to apply an alternating pattern. Below that, youll see the . The most common formulas entered are =ISEVEN(ROW()) for coloring even rows and =ISODD(ROW()) for odd rows. To apply alternating colors with conditional formatting, use any of the 4 formulas below, in the "Format cells if" options, under the "Custom formula is" drop-down selection: Conditional formatting is an amazingly useful tool that allows you to format cells based on their contents, and the following is just one of the many ways that you can use conditional formatting in Google Sheets. To remove this type of alternating color that is applied with conditional formatting, you can do either of the following: Changing the color of text in your spreadsheet is almost the exact same as changing cell background color, except that you must click a different menu to begin with. For this guide, I'll choose the range A1:H20. Click on the Run button to execute this script. With these steps, you will be able to alternate rows or columns in colors on Google Sheets and Excel. But what if you need to color every third or fourth row or would want to color alternate columns, instead? sheet.getRange(row, column).setBackground(linecolor)} column++;} row++;}} Reply. Alternating row color is a format that will remain even if you click "Reset" in the color menu. Since leaving the classroom, he's been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloudwards.net. In this example, the range is A1:K20. For testing, you can use the same above values (fruit data) as my explanation below will be based on that. Use the Custom option if you want to choose your own colors or you can even use a hexadecimal color code to select colors. If you want, feel free to add the header row as well in this selection. We will select a formatting style and . Once you dont need alternating colors in your sheet, you can easily remove them, as well. Click on Format from the top menu and then click on Alternating Colors. You can apply an alternate color scheme to rows in your Google Sheets spreadsheet directly using the Alternating Colors formatting feature. =ROW returns the row number of the cell, while =ISEVEN returns TRUE for the even row numbers and FALSE for the odd row numbers. Also, you can change the number to vary the number of shaded columns or rows. How to Shade Alternate Rows in Google Sheets 1. For those using Microsoft Excel, there are a few steps to alternate row colors. Here are the steps for more individual formatting. Hit the "Alternating colors" section near the bottom of the drop-down menu, and choose your style and colors. Subscribe for awesome Google Sheets videos . In our example, both, returns the row number of the cell, while, for the odd row numbers. In the next step, label cell E1 as "Helper 1" and cell F1 as "Helper 2". Templates Click on it to remove the selected format rule from your sheet. For this example, our data is in Column A. 7. You can click on the Fill color icon in the main toolbar, which will open a menu that allows you to select the fill color for the cells in your spreadsheet. After the layout is applied and alternating rows are colored, your data will look more readable. Change it to Classic, then on the next dropdown, select 'Use a formula to determine which cells to format'. Set the range of cells to format. Click Alternating Colours on the Format tab. Click on 'Alternating' colors. With this method you will be able to alternate row colors, or if needed you will also be able to alternate column colors. Then, there are four basic rules you can create here to dictate how and where you'd like color applied to your sheet. At first, prepare or choose your datasheet on which you wish to have color alternate rows. Note: This process is the easiest way to add color to alternate rows in Excel. Follow us on social media to stay up to date with the latest in Google Sheets! Choose the color of the fill (the paint bucket icon) and hit Save. The formatting will stay on the selected cells, and Google will automatically populate added rows with the same format. This article will teach you both ways. The ROW function returns the row number of the current cell, and the ISEVEN function then checks if that row is even or not. Using Excel or Google Sheets can greatly increase your productivity and organization. If you were only looking for where the options are found then you can stop right here. section. For example, the first row will always be unshaded, following that in the picture below, there is a new date "2021-03-19" in rows 2 and 3, hence they need to be shaded. Come write for us! To change border color in Google Sheets, you must first select the cell or range of cells that you want to modify, then open the "Borders" menu in the toolbar, then open the "Border color" menu, and then you must apply the type of border that you want to see. Click the paint bucket icon to change the color. Click here to read more about me and Spreadsheet Class. Many businesses and organizations utilize Google Sheets instead of Excel. You are free to add a color of your choice to the header row as well. In the box below, type the following formula: Then, select the color, font, and formatting styles you want to apply in the Formatting Style box. I am going to use a few helper columns to make you understand the logic of the formula (Formula # 1) that highlights every set of 5 alternate rows from row # 1 in Google Sheets. If you are new to Apps Script, then check out our Google Apps Scripts guide for beginners. In the Conditional Format Rules pane, click on the 'Format cells if' drop down and select 'Custom Formula is'. Note: When you add rows to the bottom or insert rows, you will need to reapply this formatting manually. Column F: ROW Formula. Click the Format Button and select the specific formatting you wish to apply (I just chose a fill color of gray) Click OK Button. This that we are commenting on is preferable, in most cases, to when all cells are the same color, mostly for display issues. 3. Here's an example: ii. Look at the top menu bar, and find format. Ben Stockton is a freelance tech writer from the United Kingdom. How To Highlight Every Other Row In Google Sheets - Related Questions Steps to Sort Out Filter Issue Associated with Alternating Colors for Groups in Google Sheets. If you used Alternating colors, simply select any formatted cell, go to Format > Alternating colors, and there youll have the Remove alternating colors option. Most likely you will want to do this to simply change the shade of grey/black of borders, but in this example I have used the color red to make the lines stand out. The entire table will automatically be selected in the (1) Apply to range box. Another option to remove any alternating row colors from your data is to clear all the formatting from the selected range! After your data is selected, click Format > Alternating Colors. Date Foo Bar (Row color) 2016-07-13 4 0.2 Solution: Alternating colors If you want two alternating colors, you can set the following two rules (or only one if white is fine for the second color): Apply to range: Custom formula is: Second rule ( (Notice that only 4 cells are selected here, as opposed to a whole row being selected which we will go over in the next example). Try it yourself, and see what youve learned today: Apply conditional formatting along with a wide range of other Google Sheets formulas that will help you improve the readability of your sheet . Repeat steps 1-7 for the secondary color. You can see the Explore button at the extreme right of the Status Bar. 6. You can, however, also add alternating colors after every second, third, or nth row in your sheet using Conditional formatting. Weve mentioned there is more than one way to color alternate rows in Google Sheets, so you must be wondering what those ways are? This feature only colors the alternate rows, and if you want to color the specific Nth rows, there is still no better option than conditional formatting. Select the Alternating colors option. With rows selected, navigate select the Format menu and click on the Conditional formatting option. 2. These formulas determine whether a row or column is even or odd numbered, and then applies the color accordingly. You can also select whether or not you want there to be a special color for the header/footer. When referring to changing the color of a cell itself, we are talking about changing the background color of that cell. Or give you a pivot layout readymade based on the data provided? Step 1: Highlight your data table. Copyright 2022 OkSheets. When styling alternating colors, you can select a default style, or you can also specify which colors that you want to use. We select and review products independently. Read on to learn how to select non adjacent, How to Alternate Row Color in Excel in Table Settings, How to Alternate Row Color in Excel with a Formula, How to Alternate Row Color in Google Sheets, How to Alternate Row Colors in Google Sheets with Conditional Formatting, 11 Best Web Design Proposal Templates 2022, Top 13 Business Proposal Templates to Take Your Business Higher in 2022, 16 Top Distance Learning PowerPoint Design Templates, How to Add Error Bars in Excel & Google Sheets, How To Select Non Adjacent Cells In Excel & Google Sheets, How To Make A Table Of Contents In PowerPoint and Google Slides, Excel Quick Analysis Tool Explanation & How-To, How to Make a Box Plot in Excel & Google Sheets, SUMIF in Google Sheets Explained & How-To, Untitled Document Create & Import from Google Docs, How to Alternate Row Color in Excel and Google Sheets, Untitled Presentation Create & Import from Google Slides. A textbox will appear below it. Go to the "Format" tab "Alternating colors". My name is Corey, and I created this website to help people learn how to use Google Sheets. This cannot be done with the Alternating colors option. And you want to add a row color there so that you could identify it more quickly? If youd want to color the odd rows, too, you should create another rule, with the, function instead. Go to FILL COLOR option and choose the color you want. Following that, next date, 2021-01-01 does not need to be and so on. In order to format only the alternating rows, you will need to use conditional formatting based on a formula. To do this click on the letter "D" on the top of column D to select the entire column, open the "Fill color" menu, and then select the color that you want. Google Sheets has come a long way. However, the old colors are still there, and as soon as you remove the current row color formatting, the previous one will become visible. Select the range of rows you want to apply alternating colors to. To apply color to alternate columns, type this formula: =MOD (COLUMN (),2)=0. For years, the only way to add alternating colors to the rows in Google Sheets was the Conditional Formatting. Next, select all the cells in that range and click on Format Cells. This is a great trick to know in order to increase visibility on a busy spreadsheet that contains duplicates. Google Sheets has a built-in formatting option that allows you to apply two different colors to two rows and repeat the colors to the next rows below them. You can also change 0 in the formula, so your formula will look like =MOD(ROW(),4)=3. You should now see that the system has colored every fourth row in the selection. To choose from the default styles, choose a style under the heading Default styles. Click on remove alternating colors option. Here's an example of alternating row bands applied to a checklist in Google Sheets: How To Alternate Colors In Google Sheets Rows. Follow the same steps as before (or just click the . This means that a certain condition needs to be evaluated, and if the condition is met, then a corresponding action will be applied to the selected range. Search. You can do this manually, or by selecting a cell, and then pressing Ctrl+A to select the data. Inside the Value of formula box, add the above formula. Today, you can do this more easily, with a simple built-in feature. What about coloring a set of 3 alternate rows in Google Sheets? G. I. Inside it, make the following amendments. fjs.parentNode.insertBefore(js, fjs); Find the Format tab and click on Conditional formatting. It is right above Alternating colors that will lead you to the proper place to input your formula and personalize the cells on your spreadsheet. Select all the rows you want to add the alternate colors. There is another way to add alternating row colors manually through the Fill Color tool. To do so, select your data set in your Google Sheets spreadsheet. It is free, accessible on any computer, and has many of the same features and functions. Google Sheets also has two separate ways to alternate the column or row color: the preset way and conditional formatting.