The AHP Excel template works under Office Libre and Excel version MS Excel 2013. The workbook consists of 20 input worksheets for pair-wise comparisons, a sheet for the consolidation of all judgments, a summary sheet to display the result, a sheet with reference tables (random index, limits for geometric consistency index GCI, judgment scales) and a sheet for solving the eigenvalue problem when using the eigenvector method (EVM). Latest version: 2022-07-08.
Alternative for complex AHP projects: AHP free online software.
Excel Template
-
Within the input worksheets (questionnaires) priorities are calculated using the row geometric mean method (RGMM).
-
Three consistency indices (the consistency ratio CR, the geometric consistency index GCI and overall dissonance Psi) are calculated. The level of consistency needed (α) is implemented as a variable input field in the summary sheet, and can be set between zero and one.
-
If CR exceeds α, the top 3 inconsistent pair-wise comparisons on the input sheets are highlighted, to allow the participants an adjustment of their judgments. The judgment resulting in lower inconsistency is proposed.
-
Final priorities are shown in a summary sheet; their calculation is based on the eigen vector method (EVM). For the solution of the eigenvalue problem the power method algorithm is applied with a fixed number of 20 iterations.
-
Different judgment scales are implemented.
- Errors of the EVM and RGMM are show beside the calculated priorities.
-
Either individual participants, or an aggregation of individual judgments (AIJ) based on the weighted geometric mean of all participants’ judgments can be selected.
Limitations
- The template does not include the hierarchy of the decision problem and the final aggregation of weights, i.e. it is only suitable for finding the weights in each category or sub-category. For the definition of a hierarchy and evaluation of alternatives see here.
- Sensitivity analysis of the final result is not included.
How to use the template?
A detailed description (pdf) is attached in the download file.
Reference
When you use the template for your research, please make a reference to the author’s paper.
Please cite:Klaus D. Goepel, (2013). Implementing the Analytic Hierarchy
Process as a Standard Method for Multi-Criteria Decision Making In
Corporate Enterprises – A New AHP Excel Template with Multiple Inputs, Proceedings of the International Symposium on the Analytic Hierarchy Process, Kuala Lumpur 2013. DOI: https://doi.org/10.13033/isahp.y2013.047
Download
Download AHP Excel Template Version 2022.07.08 (zip)
Please consider a donation, it will help me to maintain the website and program. An explanation of AHP (Analytic Hierarchy Process) is given in my video here. For terms of use please see our user agreement and privacy policy.
Your comments are always welcome!
How can i do to compare 27 alternatives in 7 criteries? In file, i find only a way to compare 2 alternatives A and B.
Thank you.
A and B is for the pair wise comparisons to find the weights of your 7 criteria. Evaluation of alternatives is not part of the template. You have to do it on an extra sheet, using the weights of your 7 criteria. An example, how to evaluate the alternatives, is shown in my AHP video on youtube. You find the link on my site.
Hello Klaus,
Thank you for making my life easy. Question – I have three main criteria and eight sub-criteria. How to show all (main and sub) criteria in the excel sheet? Thank you.
Hi Ashish,
Use one template for the three main, and another for the eight sub criteria. Calculation of global priorities has to be done on an extra sheet manually.
Regards, Klaus
i have 13 variables in my research, can you accommodate 13 instead of 10?
Hi Peter,
sorry,but I have no plan to extend to more than 10 criteria. Maybe you can break down your 13 into 2 subcategories?
Dear Dr. Goepel,
thank you for the magnificent template.
Great job. Is there a way you can link the template to Arcview 3.3
Hi Collins,
I’m not sure about Arcview; have no experience with it. Thanks for your feedback.
Dear Dr. Goepel,
Thank you for excellent tool. I just finished calculations – ranking of different stakeholder preferences in forest management planning for my PhD thesis.
Your excel sheet works perfect.
Tell me, please, how do you prefer to quote you? Your name, AHP Excel template, version, your web-site, or something different?
Thank you again.
Best regards from Serbia,
Dejan
Dear Dejan,
You might make a reference to this paper:
Klaus D. Goepel, (2013). Implementing the Analytic Hierarchy Process as a Standard Method for Multi-Criteria Decision Making In Corporate Enterprises – A New AHP Excel Template with Multiple Inputs, Proceedings of the International Symposium on the Analytic Hierarchy Process 2013.
Download here
Thanks and regards,
Klaus
Dear Klaus,
Thank you VERY MUCH for this free template. I am a mac user, and installing ExpertChoice software in my old macbook would be an agony. So, thank you very much 🙂
Regards,
Nova
Hi Klaus
Is there any limitation for the minimum number of the participants in AHP method?
Regards
Marjaneh
Dear Marjaneh,
You need at least one participant; that’s the minimum.
Regards, Klaus
Thank you for the template Klaus , well in my case my research includes 25 criterias to compare, i’ve seen that your template is limited to 10, do you know a way to use it in my case? Thanks again!
Hi Chris,
You are welcome.
25 criteria means 300 (!) pair-wise comparisons. I don’t believe that many can do it in a consistent way. In my projects, people already had difficulties to do 10 criteria with CR < 0.1. My proposal would be, to try to break down in further subcategories.
I am in need of a template that can input data for 60 participants. is there anyone with such a template. I am currently doing my research and attempting to use data from 60 farmers. can some one help me. I have tried to follow the steps to add new participant but I am stuck. I am in urgent need of a template with that capacity.
regards,
Elroy Wilson
Elroy,
as a quick solution:
Use 3 of my original templates (3 x 20 = 60 participants).
Transfer/translate the values of the consolidated matrices of each subgroup (summary sheet of each of the three templates with selected participant “0”) as input into a 4th template (3 participants = 3 subgroups, sheet In1 to In3).
The consolidated result of the 4th template is the group result of all 60 participants.
I can help you with the last step if necessary.
Regards, Klaus
Hi Klaus,
I am interested in using the method above for calculating the results for a large number of participants. I have entered the data on several of your templates as suggested, but I am unsure of how to enter the data from the summary page into sheets In1 – In(x) as the template appears to only accept integer values and not 1.25 etc. as might be required. Further advice on this would be greatly appreciated.
Hi Phil, are you using the latest version? I changed from integer to decimal on the input sheets in version 12.08.13. Please let me know if you have problems with it.
Regards, Klaus
Thanks for the reply Klaus – I was using a previous version. New version works great – much appreciated.