Excel RAG Analysis: Build a Red-Amber-Green Tracker

How-to basics · Updated November 21, 2026

Excel RAG analysis usually means tracking status with red, amber, and green indicators: red signals a serious issue, amber a warning, and green an acceptable or on-track result. In Excel for Microsoft 365 on Windows, set clear thresholds, calculate each status with an IF formula, then use conditional formatting to display the colors.

What RAG analysis means in Excel

RAG is short for red, amber, green. Teams use the colors to make exceptions and progress easier to scan in a project plan, service report, risk register, sales dashboard, or workload tracker. A RAG analysis is not one special Excel feature; it is a reporting method built from ordinary cells, formulas, and formatting.

The colors need agreed meanings. For example, a project could be green when it is on schedule, amber when a milestone is at risk, and red when the delivery date is likely to be missed. A customer-support team might instead apply the colors to response time, while a budget report might use them for spending against a plan. The same color can mean different things in different organizations, so document the definitions beside the report.

In this context, RAG does not mean retrieval-augmented generation, a term used in some discussions of AI systems. If someone asks for RAG analysis in a spreadsheet or project report, they usually mean a red-amber-green status view. Confirm the intended meaning when the surrounding work is about AI rather than performance reporting.

Choose what each color means

Before building the workbook, decide what is being measured and whether a higher or lower value is better. A score of 90 might be good for completion percentage but bad for days overdue. Reversing that assumption is a common cause of misleading status reports.

For a measure where higher is better, a simple set of rules could be:

  • Green: the score is at or above the agreed green threshold.
  • Amber: the score is below green but at or above the amber threshold.
  • Red: the score is below the amber threshold.

Choose thresholds that reflect actual policy, service commitments, or project tolerances rather than picking convenient numbers. Define whether boundary values count as amber or green, and use the same rule across comparable items. If different departments use different targets, keep the thresholds visible for each department instead of quietly applying one universal cutoff.

Set up the worksheet

A straightforward tracker can use one row per item, with columns for the item name, measured value, and calculated status. Keep threshold cells in a clearly labelled area, and include the measurement date and owner if they matter to decisions. A small example layout is:

  • A1: Work item; B1: Score; C1: Owner; D1: Amber threshold; E1: Green threshold; F1: RAG status.
  • Row 2 and later: one work item per row. In a simple model, put the same amber and green thresholds in D2 and E2, then fill those threshold values down as needed.

For instance, a score might be a completion percentage from 0 to 100, with 60 as the amber floor and 80 as the green floor. Label the units clearly: a value of 80 could mean 80 percent, 80 days, or a score on a different scale. If the data comes from another workbook or a recurring export, preserve a clear distinction between source data and calculated status.

Calculate the RAG status with a formula

With the score in B2, the amber threshold in D2, and the green threshold in E2, enter this formula in F2 and copy it down. It returns a blank if B2 is blank; otherwise, it returns Green, Amber, or Red. For B2 equal to 72, D2 equal to 60, and E2 equal to 80, the result is Amber.

=IF(B2="","",IF(B2>=$E$2,"Green",IF(B2>=$D$2,"Amber","Red")))

The dollar signs in $D$2 and $E$2 keep the threshold references fixed when you fill the formula down. This example assumes a numeric score and that higher scores are better. The comparisons are inclusive: a score exactly equal to the green threshold is Green, while a score exactly equal to the amber threshold is Amber. If your rule treats a boundary differently, adjust the comparison operators and explain the change to users.

For a lower-is-better measure, such as days overdue, the logic must be reversed. Do not reuse the higher-is-better formula just because the columns look the same. Write down the desired outcomes for values below, at, and above each limit, then check the formula against those examples before filling it through a report.

Apply conditional formatting

The status words are useful even without color: they remain understandable in a printed report and are more accessible than color alone. To color the status cells in Excel for Microsoft 365 on Windows, select the range, such as F2:F100, and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains. Create a rule for Green, choose a green format, and repeat for Amber and Red with distinct formats.

Choose readable text and fill colors with enough contrast. Avoid relying on a faint background shade that disappears when printed. If the workbook uses icons, labels should still be present so a reader who cannot distinguish the colors can understand the status. For broader conditional-formatting options, use Home > Conditional Formatting > New Rule and select the rule type that fits the range and workbook layout.

Excel for Mac has conditional formatting through the Home tab, although the placement and appearance of commands can vary by release. Excel for the web also supports conditional formatting, but some dialog options and editing controls differ from the Windows desktop app. If a rule is important to a shared report, open the workbook in the version your team uses and verify the displayed result.

Handle measures where lower is better

Suppose a support team tracks resolution time in hours. A short time is desirable, so a result at or below a green maximum should be green; a result above that limit but within an agreed amber maximum should be amber; and a still larger value should be red. This is the opposite direction from a completion score.

Be precise about what the thresholds describe. A threshold might be a maximum number of hours, a minimum percentage, a deadline date, or a category selected by a reviewer. Dates and durations also need consistent units. Comparing a date serial to a number of days, for example, is not meaningful just because both values appear numeric in Excel.

For subjective assessments, a manually selected status may be more honest than a formula. A project manager may know that a dependency is at risk even when the schedule metric has not crossed a numeric limit. In that case, use a validated list of allowed statuses and record a short reason, owner, or next action. The RAG label should summarize evidence, not conceal the judgment behind it.

Summarize status without hiding detail

A useful report shows both the overall picture and the items that need attention. A portfolio can display counts of red, amber, and green items, while a detail table shows the owner, measure, date last updated, and explanation for each status. A single overall color can be misleading if it hides a serious red item among many green ones.

Decide how an overall status is derived. It could be the worst status among a set of measures, a status based on a weighted score, or a human-reviewed summary. These methods answer different questions. A weighted average can make many strong results offset one critical failure; a worst-status rule is more cautious but may make a portfolio appear uniformly poor because of one minor exception. State the aggregation rule in the report.

Keep definitions close to the summary, perhaps in a legend or a small methodology note. Include the reporting period and refresh date too. Without them, a green status may look current even if its underlying data was last updated weeks ago.

Common RAG reporting problems

  • Unclear thresholds: Different readers interpret amber differently. Publish the cutoff values and boundary rules.
  • Wrong direction: A formula assumes higher is better when the metric is days late or defects. Test known examples before copying the formula.
  • Blank data shown as red: Missing information is not necessarily failure. Decide whether to show blank, a separate Not reported status, or a clearly flagged data-quality issue.
  • Stale updates: A status can be correct for the last update and wrong today. Include a date and define how often the data must be refreshed.
  • Color-only communication: A colored cell without a word, icon, or explanation is harder to interpret for some readers and in print.
  • Unexplained overrides: Manual changes may be needed, but record who changed the status, why, and when so the report remains auditable.

Also check for numbers stored as text, inconsistent units, and accidental blanks in the source data. A formula can produce a plausible-looking status from incorrect input. Compare several calculated rows with the original evidence, especially after adding new rows or changing thresholds.

When a simple Excel tracker is enough

A worksheet is often a sensible choice for a small team, a one-off review, or a report whose data is already maintained in Excel. It is easy to inspect the thresholds, adapt the columns, and share a familiar file. Keep the workbook structured, avoid merged cells in the data table, and use one row per record so sorting and filtering remain practical.

A shared workbook can become fragile when many people edit the same status, when data arrives from several systems, or when the organization needs controlled approval and a permanent change history. At that point, consider a managed list, project-management platform, or reporting system as the source of record, with Excel used for analysis or export. The right tool depends on governance, collaboration, data volume, and audit needs—not on the colors themselves.

Frequently Asked Questions

What does RAG mean in Excel?
It usually means red, amber, and green status reporting. Excel does not have a dedicated RAG-analysis command; users create it with data, formulas or manual labels, and conditional formatting.

Can Excel automatically color a RAG status?
Yes. Select the status range and use Home > Conditional Formatting to create rules for the words Green, Amber, and Red. Keep the text labels so the meaning is not dependent on color.

How do I decide the amber threshold?
Base it on an agreed tolerance, target, or escalation policy. Document the rule and decide whether the exact threshold value belongs to amber or to the next category.

Can a RAG status be entered manually?
Yes. Manual status is useful for qualitative risks or expert judgment that a single metric cannot capture. Use consistent allowed labels and record the reason and date for an override.

Does RAG mean retrieval-augmented generation?
It can in AI discussions, but in Excel trackers and project reporting, RAG commonly means red-amber-green. Use the surrounding context to tell which meaning is intended.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.