Google Sheets may not be as advanced as Excel, but it offers a very accessible alternative to Microsoft's spreadsheet and is also free. As part of the Google Drive suite, Google Sheets can be used to create, edit, and share spreadsheets.
It can be used in any browser and the spreadsheets created are compatible with Microsoft Excel. Even as a more simplistic web version of Excel, Google Sheets still lets you manipulate data in different ways, including applying unique formatting to different sets of values.
What is conditional formatting?
Conditional formatting is a feature of Google Sheets that lets you apply custom formatting to various sets of data. This is done by creating or using existing conditional formatting rules. One of the most common uses of this feature is to highlight specific values in a spreadsheet for easier identification.
Conditional formatting for highest value
- Click on 'Format'.
- Select "Conditional Formatting".
- Go to the "Unique Color" tab in the "Conditional Format Rules" menu.
- Click the table icon located under the "Apply to Range" tab.
This allows you to select the column from which you want to highlight the highest value. When finished, click “OK”.
- In the "Format cells if" drop-down list, select the "Custom formula is" option.
- Use the following formula '=$B:$B=max(B:B)'. Click "Done"
B represents the column in which you want to find the highest value.
It's quite simple, but what if you need more than highlighting the highest value. What if you need to see more values, say the top three values out of five? You can use the conditional formatting method to do this. This method consists of using the same path but a different formula.
- Click on 'Format'.
- Select "Conditional Formatting".
- Go to the "Unique Color" tab in the "Conditional Format Rules" menu.
- Click the table icon located under the "Apply to Range" tab.
- When the "Format cells if" list drops down, select the "Custom formula is" option.
- Use this formula instead of the previous one '=$B1>=large($B$1:$B,3)'
This formula highlights the first three values in column B. Replace B with any other column letter you want.
Conditional formatting for lowest value
Whatever data you are looking at, when you want to find the highs, it is also worth looking at the lows to better understand the datasheet.
Conditional formatting can also be used to highlight low values, if you use the right formula.
Follow the steps mentioned earlier to reach the option “The custom formula is”. Type the following formula '=$B:$B=min(B:B)'. If you want to highlight the lowest N values, modify the formula in the previous example '=$B1>=large($B$1:$B,3)' which highlights the three highest values to '=$B1<=small($B$1:$B,3)'.
Formatting options
You are also responsible for how you want the values to be highlighted in your spreadsheet. After giving the conditional formatting formula parameters, you can choose a custom formatting style and change the appearance of the text.
You can bold it, italicize it, underline it, and even change the color. After customizing the font, click Done to start the function and highlight the values you are looking for.
What can you use conditional formatting for?
Conditional formatting can be used with a variety of custom formulas. You can also highlight high values below a certain threshold. For example, you can use conditional formatting to show who scored below a certain percentage on a test.
Example of note highlighting
- Open a test results spreadsheet.
- Click on 'Format' then on 'Conditional formatting'.
- Click the table icon under the "Apply to Range" tab to select the range of cells.
- Select "Less Than" under the "Format Cells If" tab.
- Search for any existing rules.
- If there is one, click on it, no, click on 'Add a new rule'.
- Then add “Less than”.
- Click on the 'Value or Formula' option.
- Enter 0,8, 0,6, 0,7 etc. to highlight values below 80%, 60%, 70%.
This particular formula should be very useful for teachers or even students who want to know the percentile in which they scored.
Other areas you can apply conditional formatting to include sales, purchases, and just about any other area where you may need to filter data.
Use of third-party applications
If you find that Google Sheets isn't complex enough for you, you can take things up a notch by using third-party apps or extensions that let you make full use of spreadsheets. An application like Power Tools will allow you to use a function similar to the AutoSum function in Excel.
What is AutoSum? It is an Excel function that allows you to obtain the sum of different rows. Google Sheets only lets you do this for individual rows, one at a time. While you may not need power tools or the like to highlight the highest value(s) in a spreadsheet, it's good to know that you can get more out of this web application than it seems.
Excel with ease
If you can't afford to use Microsoft Office, Google Sheets has you covered for most of your spreadsheet needs. Although most businesses don't use the web app, preferring a more professional solution, many freelancers and regular users turn to Google Sheets to record and analyze data.
Tell us how often do you use Google Sheets to manage information and how familiar are you with Google Sheet functions? Many people claim that they are a bit difficult to learn. Do you agree?
