The recent update to conditional formatting in Google Sheets allows you to turn any data set into a coloured heat map with just a couple of simple steps. This is great for identifying trends and patterns and cuts out the process of having to create a separate chart or graph.
A data heat map highlights each cell with a different colour depending on the figure. So you can set the colour to a light shade for low figures and a darker colour for high figures. A great example is using the heat map to identify trends in your sales report, allowing you to see what months have been the most successful.
To create your own heat map, follow the simple steps below:
- Select your data in the Google Spreadsheet.
- Go to Format – Conditional Formatting (or Right-click). This will open a Conditional format rules panel.
- Select the colours for your Minpoint, Midpoint and Max point
- Adjust your Midpoint to a Percentile
To easily identify your data even further, you can select your data and click add a new rule. If you want to see your top figures (e.g. above £1000) select greater than in Format cells if… add your Value into the box and you can choose to have these figures in bold for example.
2. Turn on colourful rows or columns via this neat trick
In Google Sheets, the Conditional Formatting feature provides the ability to format cells if they meet certain conditions, such as if they contain a certain word or a number.
e.g. if a cell contains the string “Yes” make the background green, or “No” make the background red.
To access Conditional Formatting in a Google Sheet:
- Click Format > Conditional formatting (A toolbar will open to the right)
- Create a rule via Add a new rule
- Use Apply to a range to select the range to be formatted
- Under the Format cells if… drop down select Custom formula is (the last option)
- In the box Value or formula use one of the following formulae:
| Formatting | Formula |
| Apply to Even Rows | =ISEVEN(ROW()) |
| Apply to Odd Rows | =ISODD(ROW()) |
| Apply to Even Columns | =ISEVEN(COLUMN()) |
| Apply to Odd Columns | =ISODD(COLUMN()) |
Then select the Formatting style such as a colour.
3. Say goodbye to spelling mistakes in Google Sheets
For the majority of us fast typers Spell Check plays an important role spotting our mistakes as we type.
Luckily for us Spell Check has finally been added to the new Google Sheets. Spell Check with Google Sheets behaves just the same as it does with Docs or Slides. You can choose to check your entire Sheet, individual worksheets or even columns and cells. Here’s how to spell check your Sheet:
- Highlight the cell range or columns you wish to check
- Click the tools tab and select Spelling
- Google will identify any mis-spellings
- Click change, ignore or add to dictionary
If you are working with a Sheet created in the old version of Google Sheets, Spell Check is also available, in a slightly different format. After you choose Spelling, a pop up box will open – simply click on the identified mis-spelling and choose from a list of suggested corrections.
Hope you liked the above article. We do some amazing & unique workshops that can help you Scale up & Automate business using Free Technology that is already in front of you. Find our more using links below:
