Learn what conditional formatting is and how it can be used to emphasize the important data in a spreadsheet. We will discuss a variety of the built-in options you can use to apply conditional formatting rules to your data. 

A primer on conditional formatting

Conditional formatting

Additional rule

Conditional formatting of top and bottom values

Greater than

Weight range

Auditing an expense account

Conditional formatting using dates

Before last week

All dates except June

Dates

A Primer on Conditional Formatting

In this chapter, you will learn how to apply conditional formatting in more flexible ways. We'll discuss a variety of functions you can use to create conditional formatting rules with custom formulas.

Primer on custom formulas

Amount variance

Percentage variance

Working with AND / OR

Positive and negative

Both amount and variance

Quality control

Quality control further analysis

Highlighting an entire row

Formatting an entire row

Custom formula

Custom Application of Conditional Formatting

Learn tricks to use conditional formatting in unique ways! In this chapter, you will learn more functions, build your own searches, and make interactive task lists with checkboxes.

Highlight duplicate values

COUNTIF practice

Finding more duplicates

Conditionally formatting duplicates

Cleaning it up

Building a search box

SEARCH Function

Highlighting search

Highlight top values

SMALL function

Highlighting SMALL

Conditional formatting hacks

Cross off your to dos

Long term career goals

Ranking your goals

Crossing them off

Conditional Formatting Hacks

In this chapter, you will use everything you have learned about conditional formatting to analyze a company's payroll. You will be working with dates, looking for duplicates, and checking for errors to create your report.  

Working with payroll dates

ISDATE function

Correcting your report

Checking for duplicates

Checking a payroll register

Paid time off totals

Pay allowed range

Moving to part-time

Overall payroll analysis

Year-to-date

Positive variances

Negative variances

Congratulations!

Putting It All Together

スプレッドシートは、データが多すぎて活用しにくくなることがあります。追加のレポートを作らずに、データに隠れたストーリーを伝えたいときは、条件付き書式が役立ちます。たとえば、カラースケールで在庫の古さを強調したり、年次比較の財務データで差異の大きい部分を目立たせたりできます。条件付き書式には、複雑なコードなしで使える組み込みオプションが用意されています。既存のデータに対して動作するため、並べ替えやフィルターの代わりとしても使えます。学習の最後には、条件付き書式を使って自社の給与データを分析するレポートを自分で作成できるようになります。

Data Analysis in Google Sheets

組み込みオプションやカスタム数式で条件付き書式をデータに適用する方法を学ぶ。

Google スプレッドシートの条件付き書式

組み込みオプションとカスタム数式で、データに条件付き書式を適用する方法を学びましょう。

Crossing them off

Google スプレッドシートの条件付き書式

演習の手順

実践的なインタラクティブ演習