The RACI matrix in Excel
- Apr. 4, 2021
|Example files with this article:|
RACI matrix in Excel
Wikipedia has a.o. this regarding the responsibility assignment matrix like for instance the RACI matrix: A responsibility assignment matrix), also known as RACI matrix or linear responsibility chart, describes the participation by various roles in completing tasks or deliverables for a project or business process. RACI is an acronym derived from the four key responsibilities most typically used: responsible, accountable, consulted, and informed.
In the matrix we see:
- on the horizontal axis the names of persons or functional roles or departments
- on the vertical axis the activities, tasks, processes, results, to be performed
In Excel we could have it like this for example:
On the left hand side we see indeed see the activities and tasks. At the top we see the persons and teams. The table/matrix specifies for each specific combination:
|R||Responsible||Responsible for the task execution, reporting to the Accountable person|
|A||Accountable||Only 1 per task. Determines the details and contents of the task. Needs to be aware of the status of the task at all times.|
|C||Consulted||Gives advice and direction, support; back and forth|
|I||Informed||Receives information where appropriate, but does not influence; information stream in 1 direction only|
18 tasks and 19 persons: this is starting to become quite a lot of information. We already have a kind of sorting (for tasks and persons), however I firmly believe that there is a demand for more filtering options. How do we see the persons linked to a certain taks - by letter (R/A/C/I) ? How do we see the tasks linked to a person - also by letter (R/A/C/I) ? I don't find this easy in the matrix.
Excel functions to the rescue
Using our newest dynamic functions in Excel this gives clarity:
We have functions like FILTER and SORT. I prepared this for all of you: put an "x" in the column to the left of the person OR task. In the table to the right you will see, depending on where the "x" is:
- if an "x" to the left of a person: the overview of tasks, by letter
- if an "x" to the left of a task: the overview of persons for this taks, by letter
Everything is dynamic ! Please use this template as a start. When you change the names of persons and tasks for your organisation, the matrix is ready for data entry and consultation. I don't want to underestimate your Excel abilities by explaining conditional formatting and the functions like COUNTIF so I will definitely not do this.
Bonus tip: add the email address in a (hidden) row and retrieve it with the filters instead of the names. Using the Excel function TEXTJOIN you create a concatenated list of email addresses linked to 1 task: paste it into Outlook and you gain time by not searching or typing !
Happy Easter to all of you !