Google Sheets may not be as advanced as Excel, but it offers a very affordable alternative to Microsoft’s spreadsheet tool and is free to use. As part of the Google Drive package, 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-based version of Excel, Google Sheets still allows you to manipulate data in several ways, including applying unique formatting to different sets of values.
What is conditional formatting?
Conditional formatting is a Google Sheets feature that allows you to apply custom formatting to multiple data sets. 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 Format.
- Select Conditional format.
- Go to the single color tab below Conditional formatting rules menu.
- Click the table icon located below the Apply to range eyelash.
This allows you to select the column for which you want to highlight the highest value. When you finish, click OK.
- In it Format cells yes drop-down list, select the The custom formula is option.
- Use the following formula: =$B:$B=max(B:B) and click Made.
B represents the column in which you want to find the highest value.
- Click Format.
- Select Conditional format.
- Go to the single color tab below Conditional formatting rules menu.
- Click the table icon located below the Apply to range eyelash.
- Click on the Format cells yes drop-down menu and select the The custom formula is option.
- Use this formula instead of the previous one: =$B1>=large($B$1:$B,3).
What this formula does is highlight the top three values in column B. Replace B with any other column letter you want.
Conditional formatting for lowest value
No matter what data you are looking at, when you want to find the highs it is also worth looking at the lows to better understand the data sheet.
Conditional formatting can also be used to highlight low values, if you use the correct formula.
- Follow the steps mentioned above to reach the The custom formula is option.
- Then, write the following formula: =$B:$B=min(B:B). If you want to highlight the lowest N values, modify the formula in the example above: =$B1>=large($B$1:$B,3) where the three highest values stand out: =$B1.
Format options
You are also in charge of how you want the values to be highlighted in your spreadsheet. After you provide 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 launch the function and highlight the values you are looking for.
What can conditional formatting be used 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 less than a certain percentage on a test.
Example to highlight qualifications
- Open a test score spreadsheet.
- Click on Format > Conditional format.
- Click the table icon located below the Apply to range tab to select the range of cells.
- Select Less than under the Format cells yes eyelash.
- Check if any rule exists, if it exists click it, if not click Add new rule.
- Then add Less than and click on the Value or formula option.
- Get into 0.8, 0.6, 0.7 etc to highlight values lower than 80%, 60%, 70%.
This particular formula should be very useful for teachers or even students who want to know the percentile in which they have scored.
Other areas you can apply conditional formatting to include sales, purchasing, and virtually any other area where you need to filter data.
Use third-party applications
If you find that Google Sheets isn’t complex enough for you, you can improve things by using third-party apps or extensions that let you get the most out 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 allows you to do this for inpidual rows, one at a time. Although you may not need power tools or the like to highlight the highest values in a spreadsheet, it’s good to know that you can get more out of this web-based application than you might think.
Excel made easy
If you can’t afford to use Microsoft Office, Google Sheets covers most of your spreadsheet needs. Although most businesses do not use the web-based application and prefer a more professional solution, many freelancers and regular users turn to Google Sheets to record and analyze data.
Let us know how often you turn to Google Sheets to manage information and how well-versed you are in Google Sheet features. Many people claim that they are a bit difficult to learn. Do you agree?
