Conduct an ISO 27001:2022 Gap Analysis

Build a portfolio-ready ISO 27001 gap analysis workbook for a SaaS company.

Introduction

30 Second Summary

Every company that handles sensitive data eventually faces the same question: are we actually secure, or do we just think we are? An ISO 27001 gap analysis is how organizations answer that question with evidence instead of assumptions.

In this project, you will conduct a full ISO 27001:2022 gap analysis for a fictional SaaS company called TechFlow Solutions. Working entirely in Google Sheets, you will build a professional workbook that assesses their security posture, scores risks, and produces a prioritized action plan.

What You'll Build

Imagine opening the finished workbook in front of a hiring manager, clicking through tabs that show a company's security gaps scored by risk, and landing on a dashboard that answers the first question any executive asks: what do we fix first?

By the end of this project, you'll have:

A clause-by-clause assessment and Statement of Applicability that scores TechFlow Solutions against ISO 27001's management system requirements and Annex A controls, with clear ratings you can click through for every requirement.
A risk-scored gap register that ranks every finding from Critical to Low using a likelihood-by-impact matrix, so you can sort by severity and immediately see which gaps to fix first.
A phased remediation roadmap and executive dashboard you could present to a CISO or show in a portfolio interview, complete with compliance percentages, risk distribution, and top priorities at a glance.
Secret Mission: Map your ISO 27001 findings to NIST CSF functions, showing cross-framework coverage that demonstrates your ability to work across multiple standards.

Are there any prerequisites?

This project assumes familiarity with information security frameworks like NIST CSF. You will also need a Google account to access Google Sheets.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. A clear picture of the end goal keeps every tab, formula, and rating you create in the next hour anchored to a real deliverable you can present to a hiring manager or a CISO.

Set Up Your Gap Analysis Workbook

A gap analysis lives or dies by its structure. Without a well-organized workbook and a clearly defined company scenario, your findings scatter across tabs with no thread connecting them, and no one can act on the results.

In this step, you will create the workbook that holds your entire ISO 27001:2022 assessment. You will set up six purpose-built tabs, populate the fictional company scenario, and prepare the first assessment matrix for scoring.

In this step, get ready to:

Create a Google Sheet with six assessment tabs.
Populate TechFlow Solutions' company profile and ISMS scope.
Set up the Clause Assessment tab with headers and pre-populated clause references.

Create the workbook and tab structure

Each tab in your workbook maps to a distinct phase of the gap analysis workflow. Setting up all six now means you will always know exactly where each piece of the assessment lives.

Open Google Sheets by navigating to sheets.google.com in your browser.
Sign into your Google account if prompted.
Click Blank spreadsheet to create a new sheet.
Click the title in the top-left corner (it says Untitled spreadsheet) and rename it to ISO 27001 Gap Analysis - TechFlow Solutions.

Your sheet starts with one tab called Sheet1. You need six tabs total.

Double-click the Sheet1 tab at the bottom of the screen and rename it to Company Profile.
Click the + icon next to the tab to add a new tab. Rename it to Clause Assessment.
Repeat to add four more tabs named: Statement of Applicability, Gap Register, Remediation Roadmap, and Executive Dashboard.

Look at the bottom of your sheet. You should see six tabs in a row: Company Profile, Clause Assessment, Statement of Applicability, Gap Register, Remediation Roadmap, and Executive Dashboard.

What does each tab do?

Company Profile defines the organization you are assessing. Clause Assessment scores TechFlow against the mandatory management system requirements (Clauses 4-10). Statement of Applicability records which Annex A controls apply and whether they are implemented.

Gap Register consolidates every finding and scores it by risk. Remediation Roadmap sequences fixes into phased timelines. Executive Dashboard gives stakeholders a one-page view of certification readiness.

🙋‍♀️ Tabs not showing?

If you do not see all six tabs, scroll the tab bar at the bottom using the small arrows on the left side. Google Sheets hides tabs that do not fit on screen.

Check for typos in tab names by double-clicking each tab to edit it.

Help me set up my Google Sheets tabs for the gap analysis workbook.

Build the TechFlow Solutions company profile

Before you can assess anything, you need to define what you are assessing. ISO 27001 Clause 4 requires understanding the organization, its context, interested parties, and the scope of the ISMS. The Company Profile tab captures all of this in one place.

What is an ISMS?

An Information Security Management System (ISMS) is the set of policies, processes, and controls an organization uses to manage information security risks. ISO 27001 provides the framework for building, operating, and improving an ISMS.

Click the Company Profile tab at the bottom of your sheet.
In cell A1, type Organization Overview as a section header. Bold it using Cmd+B (macOS) or Ctrl+B (Windows).
Starting from row 2, enter the following label-value pairs. Type the label in Column A and the value in Column B:

Row 2 — Column A: Company Name, Column B: TechFlow Solutions.
Row 3 — Column A: Industry, Column B: SaaS / Cloud-based project management.
Row 4 — Column A: Company Size, Column B: 50 employees.
Row 5 — Column A: Location, Column B: Fully remote (no physical office).
Row 6 — Column A: Founded, Column B: 2019.
Row 7 — Column A: Annual Revenue, Column B: $5M ARR.
Row 8 — Column A: Primary Cloud Provider, Column B: AWS (us-east-1).

Leave a blank row, then type Team Structure in Column A as a new section header. Bold it.
Enter each team on its own row with the team name in Column A and headcount in Column B:

Column A: Engineering, Column B: 20.
Column A: Product, Column B: 8.
Column A: Sales & Marketing, Column B: 10.
Column A: IT / Security, Column B: 5.
Column A: Operations & Admin, Column B: 7.

Scroll up to review your work. You should see the organization overview (company name, industry, size) followed by a team breakdown totaling 50 employees.

Below the team structure, add a bold section header: Infrastructure & Customer Data.
Enter the following rows:

Column A: Primary Cloud Provider, Column B: AWS (us-east-1 region).
Column A: Database, Column B: PostgreSQL on Amazon RDS.
Column A: Customer Count, Column B: ~200 B2B customers.
Column A: Data Types Held, Column B: Customer PII, financial data, project data, usage analytics.
Column A: Architecture, Column B: Multi-tenant SaaS platform.
Column A: Backup Configuration, Column B: Daily automated snapshots via AWS RDS.

Add a bold section header: Existing Security Measures.
Enter each existing security measure on its own row in Column A:

AWS IAM roles configured for team-based access.
MFA enabled on AWS management console (not on other internal tools).
SSL/TLS encryption on all customer-facing endpoints.
Daily automated database backups via RDS snapshots (never tested for restoration).
AWS security groups configured as network firewalls.
Annual security awareness email sent to all employees (no formal training program).
Information security policy document exists (last updated 2 years ago).
Company-issued laptops for all employees (no MDM or endpoint protection).

Continue entering the remaining two security measures:

AWS CloudTrail enabled for API activity logging (logs not reviewed regularly).
Role-based access control on the SaaS platform for customers.

Scan through the security measures you just entered. You should see 10 items describing a mix of partial controls and missing processes. This is TechFlow's current reality: some technical safeguards in place, but significant governance gaps.

Why document existing security measures?

These measures become your evidence baseline. When you assess each ISO 27001 clause and control in later steps, you will compare TechFlow's current state against what the standard requires. Gaps are the difference between what exists here and what ISO 27001 demands.

Add a bold section header: ISMS Scope.
In the row below, enter this scope statement in Column A:

Type this scope statement into the cell:

The ISMS scope covers all information assets, processes, and personnel involved in the development, delivery, and support of TechFlow Solutions' cloud-based project management platform, including AWS-hosted infrastructure, customer data processing, and remote employee operations.

Add a bold section header: Interested Parties.
Enter each interested party in Column A with their expectation in Column B:

Column A: Customers, Column B: Data protection, service availability, contractual SLAs.
Column A: Employees, Column B: Job security, clear security responsibilities, safe working tools.
Column A: Regulators, Column B: Compliance with data protection laws (GDPR, SOC 2 expectations).
Column A: Board of Directors, Column B: Risk visibility, business continuity, certification readiness.
Column A: Cloud Service Providers (AWS), Column B: Shared responsibility model compliance.

Scroll through the entire Company Profile tab from top to bottom. You should see five bold section headers (Organization Overview, Team Structure, Infrastructure & Customer Data, Existing Security Measures, ISMS Scope, and Interested Parties) with data filled in beneath each one.

Profile looking messy or hard to read?

Widen Column A by dragging the column border to the right so labels are fully visible. You can also widen Column B for the longer values like the ISMS scope statement.

Make sure every section header is bolded. This visual separation makes the tab scannable when you reference it during the assessment.

Help me organize my Company Profile tab in Google Sheets.

Set up the Clause Assessment tab structure

Clauses 4 through 10 are the mandatory management system requirements of ISO 27001. These are what an auditor checks to determine whether your ISMS meets the standard. You need a structured matrix to rate each sub-requirement and record evidence.

Click the Clause Assessment tab at the bottom of your sheet.
In row 1, enter the following headers across Columns A through F:
Bold the entire header row by selecting row 1 and pressing Cmd+B (macOS) or Ctrl+B (Windows).

Column A: Clause Reference.
Column B: Requirement Summary.
Column C: Rating.
Column D: Evidence.
Column E: Gap Description.
Column F: Notes.

You should see six bold headers spanning Columns A through F. The Rating column is where you will score each requirement in the next step. For now, leave Columns C through F empty.

Now pre-populate Columns A and B with every sub-requirement from Clauses 4 through 10. This reference data gives you a row-by-row checklist to score against.

Starting in row 2, enter these Clause 4 (Context of the Organization) and Clause 5 (Leadership) references. Type the clause number in Column A and the requirement summary in Column B:

Column A: 4.1, Column B: Understanding the organization and its context.
Column A: 4.2, Column B: Understanding the needs and expectations of interested parties.
Column A: 4.3, Column B: Determining the scope of the ISMS.
Column A: 4.4, Column B: Information security management system.
Column A: 5.1, Column B: Leadership and commitment.
Column A: 5.2, Column B: Information security policy.
Column A: 5.3, Column B: Organizational roles, responsibilities, and authorities.

You should see 7 rows of data below the header, covering Clauses 4.1 through 5.3.

Continue with Clause 6 (Planning) references:

Column A: 6.1, Column B: Actions to address risks and opportunities.
Column A: 6.1.2, Column B: Information security risk assessment.
Column A: 6.1.3, Column B: Information security risk treatment.
Column A: 6.2, Column B: Information security objectives and planning to achieve them.
Column A: 6.3, Column B: Planning of changes.

Add the Clause 7 (Support) and Clause 8 (Operation) references:

Column A: 7.1, Column B: Resources.
Column A: 7.2, Column B: Competence.
Column A: 7.3, Column B: Awareness.
Column A: 7.4, Column B: Communication.
Column A: 7.5, Column B: Documented information.
Column A: 8.1, Column B: Operational planning and control.
Column A: 8.2, Column B: Information security risk assessment (execution).
Column A: 8.3, Column B: Information security risk treatment (execution).

Finish with Clause 9 (Performance Evaluation) and Clause 10 (Improvement) references:

Column A: 9.1, Column B: Monitoring, measurement, analysis, and evaluation.
Column A: 9.2, Column B: Internal audit.
Column A: 9.3, Column B: Management review.
Column A: 10.1, Column B: Continual improvement.
Column A: 10.2, Column B: Nonconformity and corrective action.

Why Clauses 4-10 and not 1-3?

Clauses 1 through 3 cover scope, normative references, and terms/definitions. They set the stage for the standard but contain no certifiable requirements. Clauses 4 through 10 are the management system requirements that auditors assess during certification.

Before you move on, count the data rows in your Clause Assessment tab. How many sub-requirements do you expect to see below the header row?

You should count 25 rows of clause references (rows 2 through 26), spanning from 4.1 through 10.2. Columns C through F should be empty, ready for you to score in the next step.

Row count does not match 25?

Check that you included both 6.1 and its sub-clauses 6.1.2 and 6.1.3 as separate rows. Clause 6 has the most sub-requirements and is the easiest to miss entries in.

Also check that Clauses 8, 9, and 10 each have their sub-requirements entered as separate rows (not combined into a single row per clause).

Help me verify my Clause Assessment tab has all the correct sub-clause references.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your workbook. Google Sheets auto-saves, but you can confirm by checking for the All changes saved in Drive message at the top of the screen.

ⓧ I'd like to double check the full setup

Walk through this checklist to confirm everything is in place:

Your sheet title reads ISO 27001 Gap Analysis - TechFlow Solutions.
Six tabs exist at the bottom: Company Profile, Clause Assessment, Statement of Applicability, Gap Register, Remediation Roadmap, Executive Dashboard.
The Company Profile tab has six sections with bold headers: Organization Overview, Team Structure, Infrastructure & Customer Data, Existing Security Measures, ISMS Scope, and Interested Parties.
The Existing Security Measures section lists 10 items.
The Interested Parties section lists 5 parties with their expectations.
The Clause Assessment tab has 6 bold headers in row 1 (Clause Reference through Notes) and 25 data rows from 4.1 through 10.2.
Columns C through F in the Clause Assessment tab are empty, ready for scoring.

Your workbook is structured and your scenario is defined. In the next step, you will work through each clause and score TechFlow's current state against what ISO 27001 actually requires. That is where the gaps start to surface.

Assess TechFlow Against Clauses 4-10

Your workbook is structured and TechFlow's company profile is documented. Now it is time to put that assessment framework to use and find out where TechFlow actually stands.

ISO 27001 certification is not just about implementing security controls. Clauses 4 through 10 define the management system requirements that auditors evaluate. Without a functioning ISMS across these clauses, no amount of technical controls will get TechFlow certified.

In this step, get ready to:

Assess Clauses 4 and 5 to evaluate TechFlow's organizational context and leadership commitment.
Assess Clauses 6, 7, and 8 to uncover gaps in planning, support, and operational controls.
Assess Clauses 9 and 10 to evaluate performance monitoring and continuous improvement.

Assess Clauses 4 and 5 (Context and Leadership)

Clause 4 covers how well an organization understands its own context, its stakeholders, and the boundaries of its ISMS. Clause 5 asks whether leadership is genuinely committed to information security or just paying lip service.

Switch to the Clause Assessment tab in your workbook.
For each sub-clause row below, fill in three columns: the Rating in Column C, what evidence TechFlow has (or lacks) in Column D, and what is missing in Column E.

Start with Clause 4. Work through each sub-clause using TechFlow's scenario from the Company Profile tab:

For row 4.1, set the Rating to Partially Compliant. TechFlow has an informal understanding of their market, but no one has documented the internal and external issues that affect information security. Note this in the Evidence column and describe the missing documentation in the Gap Description.
For row 4.2, set the Rating to Partially Compliant. Some customer contracts mention security expectations, but there is no formal register of interested parties and their requirements. Note this gap.
For row 4.3, set the Rating to Partially Compliant. The ISMS scope you documented in the Company Profile tab exists, but TechFlow has not formally documented interfaces and dependencies between in-scope and out-of-scope systems.
For row 4.4, set the Rating to Non-Compliant. No formal ISMS exists at TechFlow. This is the most significant finding in Clause 4.

Look at your Clause 4 ratings. You should see three Partially Compliant rows and one Non-Compliant row. TechFlow has pieces in place, but the formal structure is missing.

What does Partially Compliant actually mean?

Partially Compliant means the organization is doing something that addresses the requirement, but the approach is informal, incomplete, or undocumented. An auditor would acknowledge the effort but flag it as insufficient for certification.

Non-Compliant means nothing meaningful exists. These are the certification blockers that need the most attention.

Now move to Clause 5. This clause evaluates whether leadership treats information security as a genuine organizational priority:

For row 5.1, set the Rating to Partially Compliant. The CEO mentions security in all-hands meetings but does not participate in formal security governance. No evidence of management review or resource allocation decisions exists.
For row 5.2, set the Rating to Partially Compliant. An information security policy exists but was last updated 2 years ago. It does not reference the current organizational context.
For row 5.3, set the Rating to Partially Compliant. The IT/Security team handles security tasks, but roles and responsibilities are not formally documented or communicated organization-wide.

Clause 5 should show all three sub-clauses rated Partially Compliant. Leadership at TechFlow is not ignoring security entirely, but none of it is formalized.

Not sure which rating to pick?

If you are torn between Partially Compliant and Non-Compliant, ask yourself: is TechFlow doing anything that addresses this requirement, even informally? If yes, it is Partially Compliant. If there is genuinely nothing in place, it is Non-Compliant.

Help me decide between Partially Compliant and Non-Compliant for a specific clause.

Assess Clauses 6, 7, and 8 (Planning, Support, Operation)

Clause 6 is the planning backbone of an ISMS. It covers risk assessment, risk treatment, and security objectives. This is where most organizations preparing for certification have their biggest gaps.

Work through each Clause 6 sub-requirement:

For row 6.1, set the Rating to Non-Compliant. No formal risk assessment methodology exists at TechFlow. No documented risk assessment has ever been conducted.
For row 6.1.2, set the Rating to Non-Compliant. No documented risk assessment process exists. Clause 6.1.2 specifically requires a defined process for identifying, analyzing, and evaluating risks.
For row 6.1.3, set the Rating to Non-Compliant. No risk treatment plan exists. No Statement of Applicability has been created.
For row 6.2, set the Rating to Non-Compliant. No formal information security objectives have been documented at TechFlow.
For row 6.3, set the Rating to Partially Compliant. Changes to infrastructure are handled through engineering tickets, but there is no formal change management process tied to security impact assessment.

Scroll back and review your Clause 6 ratings. You should see four Non-Compliant rows and one Partially Compliant row. This is the most alarming clause area so far.

Why is Clause 6 so critical?

Clause 6 is where ISO 27001 requires you to define your risk assessment process, build a risk treatment plan, and produce a Statement of Applicability. These outputs feed into almost every other clause.

Without a risk assessment process, an organization cannot systematically identify threats. Without a risk treatment plan, there is no basis for choosing controls. A certification auditor would flag these as fundamental blockers on day one.

Now move to Clause 7, which covers the supporting infrastructure: resources, competence, awareness, communication, and documentation:

For row 7.1, set the Rating to Compliant. TechFlow's security team of 5 people exists with adequate tooling budget. This is one of the few areas where TechFlow meets the requirement.
For row 7.2, set the Rating to Partially Compliant. Team members have relevant experience but there is no formal competence assessment or training plan in place.
For row 7.3, set the Rating to Partially Compliant. TechFlow sends an annual security email, but there is no formal awareness program with testing or knowledge assessment.
For row 7.4, set the Rating to Non-Compliant. No formal communication plan for security matters exists.
For row 7.5, set the Rating to Partially Compliant. Some policies and procedures exist but there is no formal document control process covering version control, review dates, or approval workflows.

Clause 7 should show a mixed picture: one Compliant (resources), three Partially Compliant, and one Non-Compliant. TechFlow has the people and the budget. What is missing is the formal structure around them.

Finish this substep with Clause 8, which covers how the organization executes its risk-based plans:

For row 8.1, set the Rating to Partially Compliant. Some operational controls exist (firewalls, SSL) but they are not systematically planned or documented against a risk treatment plan.
For row 8.2, set the Rating to Non-Compliant. No risk assessments have been performed at planned intervals.
For row 8.3, set the Rating to Non-Compliant. No risk treatment plan has been implemented.

Why do Clauses 8.2 and 8.3 echo Clause 6?

Clause 6 requires you to plan a risk assessment and risk treatment process. Clause 8 requires you to actually execute them at planned intervals. Since TechFlow never planned the processes in the first place, the execution clauses are automatically Non-Compliant too.

Assess Clauses 9 and 10 (Performance Evaluation and Improvement)

Clause 9 asks whether the organization monitors and evaluates how well its ISMS is actually working. Clause 10 asks whether the organization improves when things go wrong. These are the clauses that keep an ISMS alive after certification.

Work through Clause 9 first:

For row 9.1, set the Rating to Partially Compliant. Basic uptime monitoring exists in AWS, but there is no formal measurement of ISMS effectiveness or security control performance.
For row 9.2, set the Rating to Non-Compliant. No internal audit program for information security exists. Clause 9.2 requires audits at planned intervals to verify the ISMS conforms to its own requirements and the standard.
For row 9.3, set the Rating to Non-Compliant. No formal management review of information security has been conducted. Clause 9.3 requires top management to review ISMS results, audit findings, and improvement opportunities at planned intervals.

Now complete the final clause. Clause 10 covers how TechFlow handles things when they go wrong and how it improves over time:

For row 10.1, set the Rating to Partially Compliant. Issues are fixed when found, but there is no formal nonconformity tracking or root cause analysis process.
For row 10.2, set the Rating to Non-Compliant. No formal continual improvement process for information security exists at TechFlow.

Before you review the full picture, take a moment to predict: across all of Clauses 4 through 10, how many clause areas do you think have significant gaps? Significant means the clause has multiple Partially Compliant or Non-Compliant ratings.

Now scroll through the entire Clause Assessment tab from top to bottom. Count the results by rating type across all rows.

You should find that roughly 4 clause areas have significant gaps. Clause 6 (Planning) is almost entirely Non-Compliant. Clauses 8, 9, and 10 each have multiple Non-Compliant ratings. Meanwhile, Clauses 4 and 5 are mostly Partially Compliant, and Clause 7 is the brightest spot with one Compliant rating for resources.

What does this pattern tell you?

TechFlow's biggest certification blockers are not a lack of people or budget. They are a lack of process. The company has security resources (Clause 7.1 is Compliant) and some technical controls in place, but the formal planning, documentation, and review processes that ISO 27001 requires are almost entirely absent.

This is the most common pattern in pre-certification organizations. Startups solve problems reactively and skip governance. The gap analysis makes that invisible risk visible.

✔️ My assessment matches this pattern

Your Clause Assessment tab is complete. Make sure your workbook is saved in Google Drive before moving on.

ⓧ I'd like to double check my assessment

Here is the expected rating for every sub-clause. Compare each row against your entries:

Clause 4:

4.1: Partially Compliant (informal market understanding, no documented analysis).
4.2: Partially Compliant (some contracts mention security, no formal register).
4.3: Partially Compliant (scope exists, interfaces and dependencies not documented).
4.4: Non-Compliant (no formal ISMS).

Clause 5:

5.1: Partially Compliant (CEO mentions security, no formal governance).
5.2: Partially Compliant (policy exists, 2 years outdated).
5.3: Partially Compliant (team exists, roles not formally documented).

Clause 6:

6.1: Non-Compliant (no risk assessment methodology).
6.1.2: Non-Compliant (no documented process).
6.1.3: Non-Compliant (no risk treatment plan or SoA).
6.2: Non-Compliant (no formal security objectives).
6.3: Partially Compliant (engineering tickets, no formal change management).

Clause 7:

7.1: Compliant (security team and budget exist).
7.2: Partially Compliant (experience, no formal competence assessment).
7.3: Partially Compliant (annual email, no formal program).
7.4: Non-Compliant (no formal communication plan).
7.5: Partially Compliant (some policies, no document control).

Clauses 8, 9, and 10:

8.1: Partially Compliant (some controls, not systematically documented).
8.2: Non-Compliant (no risk assessments at planned intervals).
8.3: Non-Compliant (no risk treatment plan).
9.1: Partially Compliant (uptime monitoring, no ISMS measurement).
9.2: Non-Compliant (no internal audit program).
9.3: Non-Compliant (no formal management review).
10.1: Partially Compliant (ad-hoc fixes, no formal tracking).
10.2: Non-Compliant (no continual improvement process).

Ratings look different from the reference?

Double check that you are referencing TechFlow's Company Profile tab when making each rating decision. The scenario data you populated in Step 1 is the single source of evidence for every assessment.

If a specific sub-clause feels ambiguous, review whether TechFlow is doing anything at all to address it. Any informal effort means Partially Compliant. A complete absence means Non-Compliant.

Help me understand why a specific TechFlow clause rating should be Partially Compliant versus Non-Compliant.

You now have a complete first picture of where TechFlow stands against the management system requirements. Next up, you will build a Statement of Applicability to assess which Annex A controls apply to TechFlow and whether they are implemented.

Build a Statement of Applicability

Your Clause Assessment tab now paints a clear picture of where TechFlow stands against the management system requirements in Clauses 4-10. You know which clauses are Compliant, Partially Compliant, and Non-Compliant.

Clauses tell you whether TechFlow has the right processes in place. But they do not tell you which specific security controls are missing. That is what the Statement of Applicability covers. It is one of the most scrutinized documents in an ISO 27001 audit, where you declare which Annex A controls apply to your organization, whether they are implemented, and why any are excluded.

In this step, get ready to:

Set up the Statement of Applicability structure with headers and a curated set of Annex A control references.
Assess each control for applicability and implementation status using TechFlow's scenario.
Review the completed SoA and identify the emerging gap pattern across your workbook.

Set up the SoA structure and populate control references

ISO 27001 Clause 6.1.3 requires organizations to compare their necessary controls against the Annex A reference set and document their decisions in the Statement of Applicability. For every control, you record whether it applies and justify why.

Switch to the Statement of Applicability tab in your workbook.
In Row 1, enter the following headers across Columns A through G:

Column A: Control Reference.
Column B: Control Title.
Column C: Applicable? (Yes / No).
Column D: Justification.
Column E: Implementation Status (Implemented / Partially Implemented / Not Implemented).
Column F: Implementation Evidence.
Column G: Gap Description.

Your header row should now span seven columns from A to G.

Annex A in ISO 27001:2022 contains 93 controls organized into four themes: Organizational (A.5), People (A.6), Physical (A.7), and Technological (A.8). A full assessment would cover all 93. For this project, you will assess a curated subset of approximately 15 controls that are most relevant for a SaaS company like TechFlow.

Starting in Row 2, populate Column A (Control Reference) and Column B (Control Title) with the following 15 controls:

A.5.1 - Information security policies.
A.5.2 - Information security roles and responsibilities.
A.5.3 - Segregation of duties.
A.5.9 - Inventory of information assets.
A.5.15 - Access control.
A.5.19 - Information security in supplier relationships.
A.5.23 - Information security for cloud services.
A.5.24 - Information security incident management.
A.5.30 - ICT readiness for business continuity.
A.7.1 - Physical security perimeters.
A.8.1 - User endpoint devices.
A.8.2 - Privileged access rights.
A.8.3 - Information access restriction.
A.8.5 - Secure authentication.
A.8.15 - Logging.

Your tab should now have a header row plus 15 data rows, each with a control reference in Column A and a control title in Column B. Columns C through G are still empty.

Why only 15 controls out of 93?

This subset focuses on Organizational (A.5.x) and Technological (A.8.x) controls because those are the most broadly applicable for a SaaS company. You are also including one Physical control (A.7.1) specifically to practice documenting a Not Applicable exclusion with justification.

The structure you are building works for all 93 controls. After this project, you could expand it to cover the full set independently.

🙋‍♀️ Not sure how to add the rows quickly?

Type the first control reference (A.5.1) in cell A2, then press Tab to move to B2 for the title. Press Enter to drop to the next row. Repeat for each control.

If you do not see 15 rows with references and titles, scroll down to make sure none were skipped. Help me set up my SoA tab structure in Google Sheets.

Assess control applicability and implementation for TechFlow

For each control, you need to answer three questions: Does this control apply to TechFlow? If yes, has TechFlow implemented it? If there is a gap, what exactly is missing?

You will work through the controls in three batches. Start with the first three organizational controls to build your assessment rhythm, then move through the remaining organizational and technological controls.

Fill in Columns C through G for the first three controls using the guidance below:

A.5.1 (Information security policies): Mark as Yes for Applicable. Set Implementation Status to Partially Implemented. In Evidence, note that a policy exists but was last updated 2 years ago and has not been reviewed. In Gap Description, note that the policy does not reflect current organizational context and lacks a review schedule.
A.5.2 (Information security roles and responsibilities): Mark as Yes for Applicable. Set Implementation Status to Partially Implemented. In Evidence, note that the IT/Security team exists but roles are not formally defined or documented. In Gap Description, note that organization-wide security responsibilities are not communicated.
A.5.3 (Segregation of duties): Mark as Yes for Applicable. Set Implementation Status to Not Implemented. In Evidence, note that developers currently have direct production database access. In Gap Description, note there is no separation between development and production environments.

What does the Justification column capture?

The Justification column (Column D) explains why a control applies or does not apply. For controls marked Yes, write a short reason like "Applicable: TechFlow handles customer data and needs formal access policies."

For controls marked No, this column is critical. Auditors will examine every exclusion justification closely, so it needs to be specific and defensible.

Check your first three rows. Each should have all seven columns filled in, with A.5.1 and A.5.2 as Partially Implemented and A.5.3 as Not Implemented.

Continue filling in Columns C through G for the remaining organizational controls:

A.5.9 (Inventory of information assets): Yes, Partially Implemented. An informal asset list exists but it is not maintained or complete.
A.5.15 (Access control): Yes, Partially Implemented. MFA is enabled on the AWS console but not on all internal tools. No formal access control policy exists.
A.5.19 (Information security in supplier relationships): Yes, Not Implemented. No supplier security assessment process exists.
A.5.23 (Information security for cloud services): Yes, Partially Implemented. The AWS shared responsibility model is understood informally but not documented.
A.5.24 (Information security incident management): Yes, Not Implemented. No documented incident response plan exists.
A.5.30 (ICT readiness for business continuity): Yes, Partially Implemented. Backups are configured but have never been tested for restoration.

Scroll through Rows 2 through 9 to confirm all organizational controls now have ratings, evidence, and gap descriptions filled in.

Now fill in the technological controls and the one physical control:

A.7.1 (Physical security perimeters): Mark as No for Applicable. In Justification, write: "TechFlow is fully remote with no physical office. All infrastructure is cloud-hosted. No company-controlled physical premises exist." Leave Implementation Status, Evidence, and Gap Description blank.
A.8.1 (User endpoint devices): Yes, Partially Implemented. Company laptops are issued but there is no MDM or endpoint protection policy.
A.8.2 (Privileged access rights): Yes, Partially Implemented. Some admin accounts exist but there is no formal privileged access management process.
A.8.3 (Information access restriction): Yes, Partially Implemented. Role-based access control exists on the SaaS platform but not on internal systems.
A.8.5 (Secure authentication): Yes, Partially Implemented. MFA is enabled on AWS but not enforced on all systems.
A.8.15 (Logging): Yes, Partially Implemented. AWS CloudTrail is enabled but logs are not reviewed regularly.

Why does A.7.1 get a different treatment?

Annex A is a reference set of controls, not a mandatory checklist. When a control genuinely does not apply to your organization's scope, you exclude it. The key is that your justification must be defensible. "We don't need it" is not enough. "We have no physical premises because we are fully remote and all infrastructure is cloud-hosted" is specific and verifiable.

Scroll through all 15 rows in your Statement of Applicability tab. Every row should have Columns C through G filled in, except for A.7.1 where Implementation Status, Evidence, and Gap Description are intentionally blank because the control does not apply.

Missing data in some rows?

Check that you filled in all seven columns (A through G) for each applicable control. A common miss is leaving the Justification column empty for controls marked Yes. Even when a control applies, you should note why it applies to TechFlow's scope.

Help me check my Statement of Applicability for missing fields.

Review your SoA and identify the gap pattern

Your Statement of Applicability is complete. Before moving on, step back and look at what you have built across the entire workbook so far.

Count how many controls in your SoA are Implemented, Partially Implemented, and Not Implemented.
Write these counts at the bottom of your SoA tab for reference.

You should find zero controls that are fully Implemented. The majority (around 11) are Partially Implemented, and 3 are Not Implemented. One control is Not Applicable.

Now switch to your Clause Assessment tab and look at your Non-Compliant and Partially Compliant ratings from Step 2.
Switch back to the Statement of Applicability tab and look at your Not Implemented and Partially Implemented controls.

Here is a question to consider: if your CISO asked you right now, "Which gap should we fix first?", could you answer confidently?

You probably cannot. Both tabs give you flat lists of findings. A gap in segregation of duties (A.5.3) sits right next to a gap in asset inventory (A.5.9). They look equally important in a spreadsheet row. But in reality, one of them could lead to a data breach tomorrow, and the other is a documentation task that can wait.

Why can't a flat list drive remediation?

A gap analysis without risk scoring treats every finding as equally urgent. In practice, organizations have limited budgets and staff. They need to know which gaps expose real business risk and which ones are documentation improvements. That is the difference between a compliance checklist and an actionable risk register.

You have identified the gaps. Now you need a way to rank them. In the next step, you will build a risk-scored gap register that transforms these flat findings into a prioritized action list.

Score Your Gaps with a Risk Matrix

Your Statement of Applicability is complete, and you have gaps scattered across two tabs. Right now, a gap in segregation of duties for production databases and a gap in your documented communication plan appear in the same flat list, looking equally important.

They are not equally important. Risk scoring transforms your findings from a flat checklist into a prioritized register that tells your fictional CISO exactly what to fix first based on actual business impact.

In this step, get ready to:

Set up the Gap Register with risk scoring headers and formulas.
Consolidate and score all findings from the Clause Assessment and SoA into a single register.
Validate the risk distribution to confirm your highest-risk items surface to the top.

Set up the Gap Register with risk scoring columns

The Gap Register consolidates every finding from your Clause Assessment and SoA into a single tab. It adds two new dimensions that your earlier tabs do not have: Likelihood and Impact scores that multiply together into a Risk Score.

Switch to the Gap Register tab in your workbook.
In Row 1, type the following headers across Columns A through J:

Column A: Gap ID.
Column B: Source.
Column C: Gap Description.
Column D: Current State.
Column E: Target State.
Column F: Likelihood (1-5) where 1 = Rare and 5 = Almost Certain.
Column G: Impact (1-5) where 1 = Negligible and 5 = Severe.
Column H: Risk Score (this will use a formula).
Column I: Risk Level (this will also use a formula).
Column J: Priority.

Now add the two formulas that do the heavy lifting. The Risk Score multiplies Likelihood by Impact, and the Risk Level categorizes the resulting number into a severity band.

Click cell H2 and enter the Risk Score formula:

=F2*G2

Click cell I2 and enter the Risk Level formula:

=IF(H2>=16,"Critical",IF(H2>=10,"High",IF(H2>=5,"Medium","Low")))

What do these formulas do?

The Risk Score formula in Column H multiplies Likelihood (Column F) by Impact (Column G), producing a number from 1 to 25.

The Risk Level formula in Column I reads that score and assigns a severity label. Scores of 16-25 are Critical, 10-15 are High, 5-9 are Medium, and 1-4 are Low. This 5x5 matrix is a common practitioner convention for risk scoring. ISO 27001 requires a risk assessment process but does not prescribe a specific methodology.

Both formulas will show 0 and Low right now because Columns F and G are empty. They will calculate properly once you start entering data in the next substep.

Seeing an error in your formula cells?

Check that you used the correct cell references. Column H should reference F2 and G2. Column I should reference H2.

Make sure the formula starts with an equals sign. If Google Sheets treats it as plain text, delete the cell content and retype it starting with =.

help me fix my Risk Score or Risk Level formula in Google Sheets

Consolidate and score all findings

Every finding rated Partially Compliant or Non-Compliant in the Clause Assessment, and every control rated Partially Implemented or Not Implemented in the SoA, needs to land in this register. You will score each one for how likely it is to cause a problem and how severe the impact would be.

Start with the two foundational gaps that carry the highest possible risk score. These are certification blockers that affect everything else.

In Row 2, enter GAP-001 with these values:
In Row 3, enter GAP-002 with these values:

GAP-001 values:

Column A (Gap ID): GAP-001.
Column B (Source): Clause 6.1.2.
Column C (Gap Description): No formal risk assessment process or methodology exists.
Column D (Current State): No documented risk assessment has been conducted. Risks are addressed ad hoc by the IT/Security team..
Column E (Target State): Documented risk assessment methodology with defined criteria for identifying, analyzing, and evaluating information security risks.
Column F (Likelihood): 5.
Column G (Impact): 5.
Columns H and I will auto-calculate from the formulas. Column J (Priority): Critical.

GAP-002 values:

Column A (Gap ID): GAP-002.
Column B (Source): Clause 6.1.3.
Column C (Gap Description): No risk treatment plan or Statement of Applicability exists.
Column D (Current State): No documented risk treatment decisions. Controls are implemented without formal justification..
Column E (Target State): Risk treatment plan with documented control selection rationale and a complete Statement of Applicability.
Column F (Likelihood): 5.
Column G (Impact): 5.
Columns H and I will auto-calculate from the formulas. Column J (Priority): Critical.

Check Columns H and I in both rows. You should see a Risk Score of 25 and a Risk Level of Critical for both GAP-001 and GAP-002. Your formulas are working.

Why are these both scored 25?

Clause 6.1.2 (risk assessment) and Clause 6.1.3 (risk treatment and SoA) are the backbone of the entire ISMS. Without a risk assessment process, you cannot justify which controls to implement. Without a Statement of Applicability, an auditor has no basis for evaluating your control decisions. Both are virtually guaranteed to be flagged as major nonconformities in a Stage 1 audit.

Now copy the formulas down before entering the remaining gaps. This saves you from retyping them for each row.

Select cells H2:I2.
Drag the small blue square at the bottom-right corner of the selection down to Row 13 (you will have 12 total findings).

The empty rows will show 0 and Low until you fill in their Likelihood and Impact values. Continue entering the remaining Critical findings.

In Row 4, enter GAP-003: Source A.5.24. No documented incident response plan. Likelihood 4 (incidents are likely for any SaaS company). Impact 5 (unmanaged incidents cause data breaches). Risk Score: 20, Critical.
In Row 5, enter GAP-004: Source A.5.3. Developers have production database access with no segregation of duties. Likelihood 4. Impact 4 (unauthorized changes to customer data). Risk Score: 16, Critical.
In Row 6, enter GAP-005: Source Clause 9.2. No internal audit program for information security. Likelihood 5. Impact 4. Risk Score: 20, Critical.
In Row 7, enter GAP-006: Source A.8.5. MFA not enforced on all systems. Likelihood 4. Impact 4. Risk Score: 16, Critical.

Check Rows 4 through 7. Every Risk Level cell should read Critical. You now have six Critical findings entered.

Why does the internal audit gap score so high?

Clause 9.2 requires organizations to conduct internal audits at planned intervals. Without an audit program, TechFlow has no mechanism to verify that its own controls are working. An auditor will flag this as a major nonconformity because it means the ISMS cannot self-correct.

Now enter the two High-risk findings. These are serious gaps but not certification blockers on their own.

In Row 8, enter GAP-007: Source A.5.30. Backups configured but never tested for restoration. Likelihood 3. Impact 5 (a backup that cannot restore is no backup at all). Risk Score: 15, High.
In Row 9, enter GAP-008: Source A.5.19. No supplier security assessment process. Likelihood 3. Impact 4. Risk Score: 12, High.

Check Rows 8 and 9. Both Risk Level cells should read High.

Finally, enter the four Medium-risk findings. These are documentation and awareness gaps that need attention but are lower urgency.

In Row 10, enter GAP-009: Source Clause 7.3. Security awareness program inadequate (annual email only, no formal training or assessment). Likelihood 3. Impact 3. Risk Score: 9, Medium.
In Row 11, enter GAP-010: Source A.5.1. Information security policy outdated and not reviewed. Likelihood 3. Impact 3. Risk Score: 9, Medium.
In Row 12, enter GAP-011: Source Clause 7.4. No formal communication plan for security matters. Likelihood 2. Impact 3. Risk Score: 6, Medium.
In Row 13, enter GAP-012: Source A.5.9. Asset inventory incomplete and not maintained. Likelihood 3. Impact 3. Risk Score: 9, Medium.

Check Rows 10 through 13. All four Risk Level cells should read Medium.

Where did you pull each gap from?

Some gaps come from the Clause Assessment tab (like GAP-001, GAP-002, GAP-005, GAP-009, GAP-011). Others come from the Statement of Applicability tab (like GAP-003, GAP-004, GAP-006, GAP-007, GAP-008, GAP-010, GAP-012). The Gap Register is where these two sources merge into one unified view.

For the Current State and Target State columns in Rows 4 through 13, use the evidence notes from your Clause Assessment and SoA tabs. Describe what TechFlow currently has (or lacks) in Column D, and what ISO 27001 requires in Column E.

Fill in the Priority column (Column J) for each row by matching it to the Risk Level: Critical priority for Critical findings, High priority for High findings, and so on.

✔️ Awesome, I've got all 12 entries!

Nice work. Make sure every row from 2 to 13 has values in all columns, including Current State, Target State, and Priority. The formulas in Columns H and I should be auto-calculating for every row.

ⓧ I'd like to double check my entries

Scroll through your Gap Register and verify these key values match:

GAP-001 and GAP-002 both show Risk Score 25 and Risk Level Critical.
GAP-003 shows 20 Critical, GAP-004 shows 16 Critical, GAP-005 shows 20 Critical, GAP-006 shows 16 Critical.
GAP-007 shows 15 High, GAP-008 shows 12 High.
GAP-009 shows 9 Medium, GAP-010 shows 9 Medium, GAP-011 shows 6 Medium, GAP-012 shows 9 Medium.
You should have exactly 12 rows of data (Rows 2 through 13) with no empty formula cells.

Risk Level showing the wrong category?

Double check your Likelihood and Impact values for that row. A Risk Score of 15 should show High, not Critical. If the formula is wrong, click the Risk Level cell and confirm it matches the nested IF formula from the setup substep.

If a row shows 0 in the Risk Score column, make sure you entered the Likelihood and Impact values in Columns F and G (not in different columns).

help me troubleshoot my gap register risk scoring formulas

Validate the risk distribution

A gap register is only useful if it tells a clear story. Sorting by Risk Score reveals where TechFlow's biggest certification blockers are concentrated.

Count your findings by Risk Level. You should have 6 Critical, 2 High, 4 Medium, and 0 Low findings.

What does this distribution tell you?

Half of TechFlow's findings are Critical. That concentration in risk management (Clause 6) and access control areas tells a clear story: TechFlow's biggest certification blockers are the absence of foundational risk management processes and weak access controls, not missing paperwork.

The Medium findings (awareness, documentation, communication) still need fixing, but they will not block certification on their own. This is exactly the kind of prioritization insight that a flat list could never give you.

Before you sort the register, think about this: which gaps do you expect to appear in the top three rows?

Select all your data (Columns A through J, Rows 1 through 13).
Click Data in the top menu bar.
Click Sort range, then select Advanced range sorting options.
Check the Data has header row checkbox.
Set Sort by to Risk Score and select Z to A (descending).
Click Sort.

Your top rows should now show GAP-001 and GAP-002 (both scored 25), followed by GAP-003 and GAP-005 (both scored 20). The Critical risk management and access control gaps have surfaced to the top, exactly where they belong.

Why does sorting matter this much?

Compare this sorted register to the flat lists you started with. In the Clause Assessment tab, Clause 7.4 (no communication plan) appeared next to Clause 6.1.2 (no risk assessment) with nothing to distinguish their urgency. Now, the risk assessment gap sits at the top with a score of 25, and the communication plan gap sits near the bottom with a score of 6. That difference is what makes this register actionable.

🙋‍♀️ Sort did not change anything?

Make sure you selected the full data range including all columns before sorting. If you only selected Column H, the scores will sort but the rest of the row data will stay in place, scrambling your register.

Also verify you checked Data has header row. Without this, your header row may have been sorted into the middle of your data.

help me sort my gap register by risk score in Google Sheets

Your gap register now ranks every finding by real business risk. Next up, you will turn this prioritized list into a phased remediation roadmap and build an executive dashboard that answers the question every CISO asks: how far are we from certification, and what do we fix first?

Build a Remediation Roadmap and Executive Dashboard

Your gap register now sorts TechFlow's findings by actual risk. You can see at a glance that a missing risk assessment process (score 25) matters far more than an incomplete asset inventory (score 9).

But a sorted list still does not answer the executive question: "What do we fix first, who owns it, and when is it done?" In this step, you will turn your risk-scored findings into a phased remediation roadmap and an executive dashboard that a CISO can read in 30 seconds.

In this step, get ready to:

Build a phased remediation roadmap sequencing all 12 gaps across 30, 90, 180, and 365-day horizons.
Create an executive dashboard with compliance metrics, risk distribution, and top priorities.
Apply conditional formatting across the entire workbook so every tab is scannable at a glance.

Set up the Remediation Roadmap headers

The remediation roadmap turns your flat gap register into a project plan. Each gap gets an owner, a timeline, and an effort estimate so that TechFlow's leadership knows exactly who is responsible for what and by when.

Switch to the Remediation Roadmap tab in your workbook.
Enter the following headers in row 1:

Column A: Gap ID.
Column B: Remediation Action.
Column C: Owner.
Column D: Phase (30 / 90 / 180 / 365 days).
Column E: Effort Estimate (Low / Medium / High).
Column F: Dependencies.
Column G: Status (Not Started / In Progress / Complete).

You should now see seven column headers spanning columns A through G in your Remediation Roadmap tab.

Populate Phase 1 and Phase 2 of the roadmap

Sequencing remediation follows two principles: Critical and High risks come first, and foundational controls come before the controls that depend on them. Phase 1 tackles the two items that block everything else.

Enter the following two rows for Phase 1 (30 days), starting in row 2:

GAP-001: Establish risk assessment methodology. Owner: Security Lead. Phase: 30. Effort: High. Dependencies: None. Status: Not Started.
GAP-002: Create Statement of Applicability. Owner: Security Lead. Phase: 30. Effort: Medium. Dependencies: GAP-001. Status: Not Started.

Why does GAP-002 depend on GAP-001?

The Statement of Applicability documents which Annex A controls apply and why. You cannot make defensible applicability decisions without first completing the risk assessment that identifies which threats are relevant. That is why a real certification timeline starts with the risk methodology.

You should see two rows under your headers. Notice that GAP-002 lists GAP-001 as a dependency in Column F.

Now enter the following four rows for Phase 2 (90 days). These are the Critical operational gaps that unblock certification readiness:

GAP-003: Develop and test incident response plan. Owner: Security Lead. Phase: 90. Effort: High. Dependencies: None. Status: Not Started.
GAP-004: Implement segregation of duties for production access. Owner: IT Manager. Phase: 90. Effort: Medium. Dependencies: None. Status: Not Started.
GAP-005: Establish internal audit program. Owner: Security Lead. Phase: 90. Effort: High. Dependencies: None. Status: Not Started.
GAP-006: Enforce MFA on all systems. Owner: IT Manager. Phase: 90. Effort: Medium. Dependencies: None. Status: Not Started.

Count the rows in your Remediation Roadmap. You should now see 6 rows of data (2 for Phase 1, 4 for Phase 2) covering all six Critical-rated gaps.

Populate Phase 3 and Phase 4 of the roadmap

Phases 3 and 4 cover the High and Medium risk items. These are still real gaps, but they can wait until the foundational processes from Phases 1 and 2 are in place.

Enter the following four rows for Phase 3 (180 days):

GAP-007: Test backup restoration procedures. Owner: IT Manager. Phase: 180. Effort: Low. Dependencies: None. Status: Not Started.
GAP-008: Create supplier security assessment process. Owner: Security Lead. Phase: 180. Effort: Medium. Dependencies: None. Status: Not Started.
GAP-009: Implement formal security awareness program. Owner: HR Lead. Phase: 180. Effort: Medium. Dependencies: None. Status: Not Started.
GAP-010: Update information security policy. Owner: Security Lead. Phase: 180. Effort: Low. Dependencies: None. Status: Not Started.

Enter the final two rows for Phase 4 (365 days):

GAP-011: Establish communication plan. Owner: Security Lead. Phase: 365. Effort: Low. Dependencies: None. Status: Not Started.
GAP-012: Complete and maintain asset inventory. Owner: IT Manager. Phase: 365. Effort: Medium. Dependencies: None. Status: Not Started.

Count your rows. You should see 12 data rows (rows 2 through 13), one for every gap in your Gap Register. Every gap now has an owner, a phase, and an effort estimate.

Missing a gap or see only 11 rows?

Cross-reference Column A of your Remediation Roadmap against Column A of your Gap Register tab. Every Gap ID from GAP-001 through GAP-012 should appear exactly once in the roadmap.

help me check that my Remediation Roadmap covers all 12 gaps from the Gap Register

💡 Why this phasing order?

Phase 1 contains the two items ISO 27001 auditors look for first: a documented risk assessment methodology and a Statement of Applicability. Without these, every other control decision is unjustified.

Phase 2 tackles operational gaps (incident response, access controls, internal audit, MFA) that directly reduce the risk of a real-world breach. Phases 3 and 4 address important but lower-urgency items like backup testing and documentation improvements.

Build the Compliance Summary and Risk Distribution sections

The Executive Dashboard is the one-page view a CISO reads before asking questions. It pulls numbers from the tabs you have already built, so every metric updates automatically if you revise a rating or score.

Switch to the Executive Dashboard tab.
In cell A1, type the heading Compliance Summary.

Section 1 pulls compliance counts from your Clause Assessment and Statement of Applicability tabs using COUNTIF formulas. Set up the following rows below the heading:

In Column A, enter the labels: Compliant, Partially Compliant, Non-Compliant / Not Implemented, and Overall Readiness Score in separate rows.
In Column B, enter COUNTIF formulas that count matching values from your assessment tabs. Enter the Compliant count formula in the cell next to your Compliant label:

=COUNTIF('Clause Assessment'!C:C,"Compliant")+COUNTIF('Statement of Applicability'!E:E,"Implemented")

What does this formula do?

This COUNTIF formula counts every row rated Compliant in the Clause Assessment tab (Column C) and adds every row rated Implemented in the Statement of Applicability tab (Column E). Together, these give you the total count of fully satisfied requirements across both tabs.

Enter the Partially Compliant count formula in the next row:

=COUNTIF('Clause Assessment'!C:C,"Partially Compliant")+COUNTIF('Statement of Applicability'!E:E,"Partially Implemented")

Enter the Non-Compliant / Not Implemented count formula in the next row:

=COUNTIF('Clause Assessment'!C:C,"Non-Compliant")+COUNTIF('Statement of Applicability'!E:E,"Not Implemented")

For the Overall Readiness Score, enter a percentage formula that divides the Compliant count by the total requirements assessed. Replace B2, B3, and B4 with the actual cell references where your three count formulas live:

=B2/(B2+B3+B4)

Format the readiness score cell as a percentage by selecting the cell, clicking Format in the menu bar, then Number, then Percent.

You should see actual numbers populating next to each label. The readiness score should be a low percentage, reflecting TechFlow's significant gaps.

Now add Section 2 below the Compliance Summary. Leave a blank row for spacing, then enter the heading Risk Distribution.

In Column A, enter the labels: Critical, High, Medium, and Low in separate rows.
In Column B, enter a COUNTIF formula for each risk level that counts matching values from the Gap Register. Enter the Critical count formula:

=COUNTIF('Gap Register'!I:I,"Critical")

Enter matching COUNTIF formulas for High, Medium, and Low in the rows below, following the same pattern but changing the search string.

Check the numbers. You should see 6 Critical, 2 High, 4 Medium, and 0 Low findings. These should match what you see when you sort the Gap Register tab by Risk Level.

Seeing 0 for every risk level?

The search string inside your COUNTIF formula must match the exact text in your Gap Register's Risk Level column (Column I). If your nested IF formula in the Gap Register outputs critical in lowercase, your COUNTIF must also search for lowercase. Check both tabs and make sure the strings match.

help me troubleshoot why my COUNTIF formulas return 0 when referencing the Gap Register tab

Add the Top 5 Priorities and Remediation Timeline

Sections 3 and 4 give the reader the "so what" view. The Top 5 tells them where to focus. The timeline tells them how long the full journey takes.

Leave a blank row below Risk Distribution, then enter the heading Top 5 Priority Items.
Create three column headers below it: Gap ID / Description, Risk Score, and Phase / Owner.
Enter the top 5 gaps sorted by Risk Score descending:

GAP-001: No risk assessment process. Score: 25. Phase 1 (30 days), Security Lead.
GAP-002: No Statement of Applicability. Score: 25. Phase 1 (30 days), Security Lead.
GAP-003: No incident response plan. Score: 20. Phase 2 (90 days), Security Lead.
GAP-005: No internal audit program. Score: 20. Phase 2 (90 days), Security Lead.
GAP-004: No segregation of duties. Score: 16. Phase 2 (90 days), IT Manager.

Why does GAP-005 appear before GAP-004?

Both GAP-003 and GAP-005 share a Risk Score of 20, which is higher than GAP-004's score of 16. When two gaps tie on score, either order is acceptable. The key point is that both score-20 items rank above the score-16 item.

Leave a blank row, then enter the heading Remediation Timeline.
Create three column headers: Phase, Gaps Addressed, and Key Deliverables.
Enter the following four rows:

30 days: 2 gaps. Key deliverables: Risk assessment methodology, Statement of Applicability.
90 days: 4 gaps. Key deliverables: Incident response plan, segregation of duties, internal audit program, MFA enforcement.
180 days: 4 gaps. Key deliverables: Backup testing, supplier assessments, awareness program, policy update.
365 days: 2 gaps. Key deliverables: Communication plan, asset inventory.

Your Executive Dashboard tab should now have four distinct sections stacked vertically: Compliance Summary, Risk Distribution, Top 5 Priority Items, and Remediation Timeline.

Apply conditional formatting across the workbook

Conditional formatting turns raw text ratings into instant visual signals. A stakeholder scanning the workbook can spot problem areas without reading a single word.

Switch to the Gap Register tab.
Select the Risk Level column (Column I), from the first data row down to the last.
Click Format in the menu bar.
Click Conditional formatting.
A panel opens on the right side of the screen. Under Format rules, set the condition to Text is exactly and enter Critical.
Set the fill color to red.
Click Done, then click Add another rule.

Repeat for the remaining three levels using the same Text is exactly condition:

High = orange fill.
Medium = yellow fill.
Low = green fill.

Your Gap Register's Risk Level column should now light up in color. You should see red cells for the six Critical gaps, orange for the two High gaps, and yellow for the four Medium gaps.

Switch to the Clause Assessment tab. Select the Rating column (Column C) and apply conditional formatting with the same steps:

Non-Compliant = red fill.
Partially Compliant = yellow fill.
Compliant = green fill.

Switch to the Statement of Applicability tab. Select the Implementation Status column (Column E) and apply conditional formatting:

Not Implemented = red fill.
Partially Implemented = yellow fill.
Implemented = green fill.

Switch to the Remediation Roadmap tab. Select the Status column (Column G) and apply conditional formatting:

Not Started = red fill.
In Progress = yellow fill.
Complete = green fill.

Click through each tab to confirm the colors appeared. The Clause Assessment and SoA tabs should show a mix of red, yellow, and green. The Remediation Roadmap Status column should be entirely red since every row is Not Started.

Colors not showing up on a tab?

Make sure you selected the correct column range before opening Format > Conditional formatting. The Apply to range field at the top of the panel shows which cells the rule targets. Also confirm that the text in your condition matches the exact cell values, including capitalization.

help me troubleshoot conditional formatting rules that are not applying color to my cells

Verify your complete workbook

Before you look at the finished dashboard, think about what you expect to see. Based on the data you entered across all five tabs, what percentage of requirements do you think TechFlow is fully compliant with? Which risk level should have the highest count?

Switch to the Executive Dashboard tab and review all four sections.

You should see a compliance percentage in the Compliance Summary, a risk distribution showing 6 Critical, 2 High, 4 Medium, and 0 Low findings, the top 5 priority items with their scores and owners, and a timeline table showing which remediation phase covers how many gaps. This is the tab you would present to a CISO or show in a portfolio.

What does this dashboard tell a CISO?

In under 30 seconds, a CISO can see that TechFlow has significant gaps in risk management and access controls (the Critical cluster), that the first 90 days are the heaviest lift, and that the Security Lead owns the majority of Phase 1 and Phase 2 work. That is enough to make a resource allocation decision on the spot.

Secret mission

Map Your Findings to NIST CSF

Organizations rarely work with just one security framework. In this mission, you will map your ISO 27001 gap findings to the NIST Cybersecurity Framework's five functions. This cross-framework mapping demonstrates the kind of fluency that sets senior GRC practitioners apart and reduces audit duplication across standards.

Clean Up Your Resources

This project runs entirely in Google Sheets, so there are no ongoing costs or processes to shut down. Decide whether to keep your workbook for your portfolio, or delete it if you no longer need it.

Resources you used:

Google Sheet: ISO 27001 Gap Analysis - TechFlow Solutions (7 tabs: Company Profile, Clause Assessment, Statement of Applicability, Gap Register, Remediation Roadmap, Executive Dashboard, NIST CSF Mapping).

Keep everything running

No action needed. Choose this if you plan to expand the workbook, reference it in interviews, or use it as a template for a real gap analysis.

Your Google Sheet stays in your Google Drive at no cost.
Consider starring the file in Google Drive so you can find it quickly when you need it for portfolio reviews or interviews.
If you want to expand the Statement of Applicability to cover all 93 Annex A controls, this workbook is already structured for it.

Pause - I'll come back to this later

Since this project lives in Google Drive, there is nothing to pause or shut down. Your workbook stays accessible whenever you return.

Bookmark the Google Sheet URL in your browser so you can return to it later.
Your workbook will remain in Google Drive at no cost until you decide to delete it.

Delete - I don't want to use this again

Remove the workbook from your Google Drive entirely.

Open Google Drive at drive.google.com.
Find the file named ISO 27001 Gap Analysis - TechFlow Solutions.
Right-click the file and select Move to trash.
To permanently delete it, click Trash in the left sidebar, right-click the file, and select Delete forever.

Nice Work!

Nice work! You just conducted a full ISO 27001:2022 gap analysis for TechFlow Solutions, turning a messy security posture into a structured, risk-scored action plan ready for a CISO's desk.

You've learned how to:

Assessed a fictional SaaS company against all seven ISMS clause areas (Clauses 4-10) and built a Statement of Applicability documenting which Annex A controls apply, their implementation status, and justification for any exclusions.
Applied risk-based scoring with a likelihood-by-impact matrix to transform a flat list of gaps into a prioritized gap register ranked from Critical to Low.
Built a phased remediation roadmap sequencing fixes across 30, 90, 180, and 365-day horizons, and created an executive dashboard showing compliance posture, risk distribution, and top priorities at a glance.
Secret Mission: Mapped ISO 27001 findings to NIST CSF functions, demonstrating cross-framework fluency and the ability to reduce audit duplication.

Ready to quiz yourself?