How to Do Factorial in Excel (6 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

The product of all positive integers less than or equal to n in mathematics is known as the factorial, indicated by the symbol (n!). In other words, the product of n and the subsequent smaller factorial also equal the factorial of n. So how will you calculate the factorial value of a number? Well, you can calculate those using your calculator, or you can do it by following some methods in Excel. Guess what today’s topic is? That’s right! How to do factorials in Excel.

Let’s assume we have a dataset, namely “Factorials of Consecutive Integer Numbers”. You can use any dataset suitable for you.

How to Do Factorials in Excel

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.


1. Using FACT Function

To calculate the factorial value of a number, the FACT function is the first method that everyone wants to work with because of its workability, and all you need to do is put a simple command in the formula editor.

📌 Steps:

  • To begin with this method, enter the following formula in cell B5.
= FACT(B5)

Here, B5 refers to the Number column.

  • Now see the output given below.

Using FACT Function how to do factorials in excel

  • Drag the Fill Handle tool to get the other value.

Using FACT Function

Read More: How to Calculate Factorial of Large Number in Excel


2. Applying FACTDOUBLE Function

Using the FACTDOUBLE function is another way to do factorials in Excel. To learn more about it, follow the discussion we are going to provide below.

📌 Steps:

  • Write the following formula in cell C5.
=FACTDOUBLE(B5)*FACTDOUBLE(B5-1)

The factorial is calculated by multiplying the two double factorials, which are obtained by multiplying the double factorial of the number in cell B5 and the double factorial of the number in cell B5 minus 1.

  • Press Enter to see the output.

Applying FACTDOUBLE Function how to do factorials in excel

  • To get the other value, drag the Fill Handle tool from C5 to B10.

Applying FACTDOUBLE Function how to do factorials in excel


3. Utilizing SEQUENCE Function

The SEQUENCE function allows you to have a list of sequential numbers in an array. As the factorial of a number means the product of the number with its subsequent lower values, we can incorporate the SEQUENCE function to calculate the factorial of a number. See the description given below on how to do factorials in Excel

📌 Steps:

  • To begin with this method, enter the following formula in cell B5.
=PRODUCT(SEQUENCE(B5))

Formula Breakdown

  • SEQUENCE(3) → returns a sequence of numbers. Here, 3 is the rows
    • Output → {1;2;3}
  • PRODUCT(SEQUENCE(3)) multiply those numbers and return its product value.
    • Output → 6
  • Hit the Enter button to see the output.

Utilizing SEQUENCE Function how to do factorials in excel

  • Lower the cursor to the C10 cell to enable AutoFill.

Utilizing SEQUENCE Function how to do factorials in excel


4. Utilizing LAMBDA Function

You can add your own functions to the native Excel function library and make it unnecessary to copy and paste frequently used formulas by creating a function for them. The LAMBDA function can also be used by non-programmers because it doesn’t require JavaScript or VBA macros.

📌 Steps:

  • First and foremost, click Formulas > Name Manager.

Utilizing LAMBDA Function how to do factorials in excel

  • This will open a prompt named New Manager.
  • Press the New button.

Utilizing LAMBDA Function

  • Add factorial as a name into the Name input.

  • Paste the following formula into the Refers to box.
=LAMBDA(n,LET(integer,INT(n),IF(integer<2,1,integer*factorial(integer-1))))
  • Press the Close button afterward.

  • Now type the following function in cell C5.
=factorial(B5)
  • Press Enter to see the output.

  • To get the other value, drag the Fill Handle tool from C5 to C10.

Utilizing LAMBDA Function


5. Incorporating Power Query Feature

With the use of the Power Query feature, you can do factorial in Excel. Though you need to go through some extra steps to accomplish this, once you learn the process, there will be no burden that we can tell you.

📌 Steps:

  • Go to the Data tab > From Table/Range command.

Incorporating Power Query Feature how to do factorials in excel

  • Thus, a prompt will appear named Create Table and ask you to create a table. Select your data area, $B$4:$B10$ for instance.
  • Press OK.

Incorporating Power Query Feature how to do factorials in excel

  • Now, double-click on the region denoted by 1 and name the sheet where you want your output to appear.
  • Click on the Number column if your data is not selected already.
  • Then, from the Transform tab, navigate to Scientific > Factorial.

  • Subsequently, your input value will be transformed into factorial value as described below.
  • Now go to the Home tab and select the Close & Load option.

  • Now, see the output given below.


6. Applying VBA Code

If you wish to create a customized function on your own and do factorials with it, then you can do this by incorporating a simple VBA code, which I am going to describe below.

📌 Steps:

  • Press Alt + F11 to open your Microsoft Visual Basic.
  • Then press Insert > Module to open a blank module.

Applying VBA Code

  • Now, write the following VBA code in your Module1.
Function FactorialUsingForLoop(val As Long) As Long
Dim MyInput As Long, MyFactorialValue As Long
Let MyFactorialValue = 1
For MyInput = 1 To val
MyFactorialValue = MyFactorialValue * MyInput
Next
FactorialUsingForLoop = MyFactorialValue
End Function

Applying VBA Code

⚡ Code Breakdown

Now, we will explain how the given VBA code works. The code is divided into two steps.

  • In the first portion, there are some variables that we need to use to run the code.
  • In the second portion, it will run a For…Next statement from 1 to your given cell value, which is referred to by Val, and do multiplication each time to get a final factorial value.
  • Close your VBA
  • Now click on the C5 cell and type the following formula.
=FactorialUsingForLoop(B5)

As you have already understood, the function we have written above was created by the given VBA code.

  • Now press Enter to see the output.

  • To get the other value, drag the Fill Handle tool from C5 to C10.


Things to Remember

  • It is worth mentioning, that you can only calculate the factorial value of an integer number. Thus, all of the methods we have described above will be applicable only to integer numbers. You will not get any output otherwise.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it yourself.

Do it yourself


Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.


Conclusion

Throughout this article, we tried to show you 6 methods to do factorials in Excel. Further, if you have any queries, feel free to comment below.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mohammad Shah Miran
Mohammad Shah Miran

Miran is a highly motivated individual with a strong educational background in engineering. He is interested in technology and passionate about creating engaging and informative content. After graduation, Miran decided to pursue a career in content development and has been working in the field for some time. He is eager to continue learning and growing as a professional.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo