# Google Sheets: conditional formatting explained When there are hundreds of entries, tables can quickly look overwhelming and cause confusion. And before you can even access the most important bits of information, time is being wasted. Spreadsheet software make it easier to understand important aspects thanks to **user-defined formatting**. For example, one could highlight all the values that are greater than 500 in a table. Or perhaps you’re looking to format only cells that contain an entry of a future date. [Conditional formatting in Excel](https://www.ionos.co.uk/digitalguide/online-marketing/online-sales/conditional-formatting-in-excel/ "Conditional formatting in Excel") or in Google Sheets lets you automatically highlight values that match pre-defined conditions. ## Areas of application for conditional formatting in Google Sheets The **areas of application** for conditional formatting are as varied as the available formatting options. **Accountants** can use conditional formatting, for example, to calculate overviews of monthly profits and losses by highlighting negative values in red. **Project managers**, on the other hand, may format their ongoing **budget overviews** to flag any values exceeding available budgets. Conditional formatting makes it easier to view and comprehend complex tables. By using colour **highlighting**, specific elements and values in a table are more easily viewed based on individual values or specific rules or formulas. ## Values as the basis for conditional formatting in Google Sheets The simplest way to highlight specific information is to compare and relate individual entries to each other. Formatting options such as “Colour scale,” for example, are useful to highlight high values in a darker colour than lower values. Google automatically suggests a scale of green tones, but users can choose their own colours. To format highlighting options in your Google Sheets project, follow these steps: 1. **Mark** the desired **range of values.** 2. Right-click on the selected area and choose “**Conditional formatting.**” 3. In the lower right side menu, select “**Colour scale.**” You can now choose to continue with the green colour scale or select your preferred colour. Click on “**Done**” when you’re finished. [![Image: Google Sheets: Menu to format colour scale](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/b/5/csm_google-sheets-menu-to-format-color-scale_1faedc7d95.webp "Google Sheets: Menu to format colour scale")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/google-sheets-menu-to-format-color-scale.jpg) If necessary, you can select individual colours for minimum and maximum values and optionally also for mid-range values. ## Google Sheets: conditional formatting based on rules If you’re working with an extensive profit-loss table and need an overview of the months during which the business performed particularly well, conditional formatting can be defined based on rules and exact values. The following example is based on a table of revenue and outgoings over a period of 12 months. Here, we want to highlight cells that meet the following conditions: **profits over $1,000** are to be automatically **highlighted in green**, and **losses** will be **shown in red**. 1. **Mark** the range of values in the spreadsheet you want to apply the conditions to. Right-click anywhere in the selection and select “**Conditional formatting.**” [![Image: Google Sheets: Table with selected range of values](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/3/a/csm_google-sheets-table-with-selected-range-of-values_a4c549cf15.webp "Google Sheets: Table with selected range of values")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/google-sheets-table-with-selected-range-of-values.jpg) The option “Conditional formation” is shown after right-clicking on the highlighted area. 2. The dialogue window “**Conditional format rules**” will open up in which conditions and formatting can be further defined. Select “**Format cells if…**” from the drop-down menu and choose “**Greater than.**” In the field below, enter the value “**1000.**” All values within the selected range that are greater than 1,000 will be automatically highlighted in green. [![Image: Google Sheets: Conditional formatting (rules)](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/5/c/csm_google-sheets-conditional-formatting_e0525f962e.webp "Google Sheets: Conditional formatting (rules)")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/google-sheets-conditional-formatting.jpg) Cells that contain values which fulfil the predefined conditions will be automatically formatted using Google Sheets. 3. By selecting “**Add another rule,**” you can specify additional conditions. In this example, we’re trying to highlight all cells with values smaller than “0” in red signifying losses. Add another rule and choose “**Smaller than**” and enter “**0.**” Change the formatting colour to red. Hit “**Done**” when you’re ready. [![Image: Google-Sheets: table with two formatting rules](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/6/d/csm_google-sheets-table-with-two-formatting-rules_52cb66d013.webp "Google-Sheets: table with two formatting rules")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/google-sheets-table-with-two-formatting-rules.jpg) Negative values are instantly recognisable. Values greater than 1,000 are highlighted green. If you add new values to the range or change some of the values in the selected range, the formatting rules will still be automatically applied. ## Conditional formatting based on formulas Aside from conditional formatting via defined rules for values, text, and dates, you can also specify your own formulas to format data. A **user-defined formula** allows you to highlight cells which meet the conditions of your formula. This could be useful to highlight future dates, for example, as shown in this example: 1. **Mark** the range of values in the spreadsheet you want to apply the conditions to. Right-click anywhere in the selection and select “**Conditional formatting.**” [![Image: Google Sheets: table with selected range](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/5/b/csm_google-sheets-table-with-selected-range_236974804d.webp "Google Sheets: table with selected range")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/google-sheets-table-with-selected-range.jpg) The “Conditional formatting” option can be selected for individual cells or selected ranges by right-clicking with your mouse 2. In the settings window “**Conditional format rules”** select “**Format cells if**” and choose “**Custom formula is**” from the drop-down menu. Enter the desired formula. In this example, the formula is “**=B4>TODAY()**”. Then select your preferred formatting. In the example below, all future dates will be marked in red. [![Image: Google Sheets: Rules for conditional formatting](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/b/6/csm_google-sheets-rules-for-conditional-formatting_e8c7666c7b.webp "Google Sheets: Rules for conditional formatting")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/google-sheets-rules-for-conditional-formatting.jpg) By using the formula =B4>TODAY(), all cells from B4 which contain a future date will be marked in red. 3. Confirm your conditional formatting by clicking on “**Done.**” If you right-click into one of the conditionally formatted cells and click on the “**Conditional format rules,**” you can see the active formatting in the selected cell. If necessary, you can also add another rule. [![Image: Google Sheets: Active formatting rules for the selected cell range](https://www.ionos.co.uk/digitalguide/fileadmin/_processed_/7/b/csm_google-sheets-active-formatting-rules-for-the-selected-cell-range_449a7a98f9.webp "Google Sheets: Active formatting rules for the selected cell range")](https://www.ionos.co.uk/digitalguide/fileadmin/DigitalGuide/Screenshots_2020/google-sheets-active-formatting-rules-for-the-selected-cell-range.jpg) All active conditional formatting rules and formulas can be made visible. In this example, the formula used to conditionally format the cell is shown. More information on conditional formatting in Google Sheets and some other interesting examples are shown in this **YouTube tutorial**: --- ### Tip Google Sheets is available as part of the [Google Workspace package for enterprises](https://www.ionos.co.uk/office-solutions/google-workspace "Google Workspace for enterprises") from IONOS. The full package includes a business Gmail account and additional cloud storage as well as optimised Google support. --- This is a markdown version of: [https://www.ionos.co.uk/digitalguide/online-marketing/online-sales/google-sheets-conditional-formatting/](https://www.ionos.co.uk/digitalguide/online-marketing/online-sales/google-sheets-conditional-formatting/) for AI/LLM consumption.