We frequently need to carry out some challenging mathematical computations while using Excel. The majority of these intricate mathematical calculations can be handled by Excel’s excellent computing capabilities. One of them is calculating the factorial of large numbers. However, when computing the factorial of large numbers, even Excel has a limitation. In this article, we will learn **two **convenient methods to calculate the factorial of a large number in Excel. After learning these two methods, we will not only be able to calculate the factorial of a large number but also be able to overcome the limitation. So, let’s start with this article and explore these methods.

## Download Practice Workbook

## 2 Convenient Ways to Calculate Factorial of Large Number in Excel

In this section of the article, we will learn **two **convenient methods to calculate the factorial of a large number in Excel. Let’s say we have the following **Numbers **as our dataset. Our goal is to calculate the factorial of these numbers.

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.

### 1. Calculating Factorial of Number Less Than 171

In the first method, we will learn to calculate the factorial of numbers that are **less than 171**. You may ask, “What is so special about this **171**?” Let’s clarify. In Excel, the maximum possible number that we can use is **9.99999999999999E+307**. The factorial of **171 **is **1.24101807 E+309**, which exceeds the limit of Excel. So, if we want to calculate the factorial of **171 **or any higher number, Excel will return a **#NUM! Error**. We will see this later in this section. Now, let’s follow the steps mentioned in the following section.

__Steps:__

- Firstly, use the following formula in cell
**C5**.

`=FACT(B5)`

Here, cell **C5 **represents the first cell of the **Number **column. Then the **FACT function** will return the factorial of **100 **in cell **C5**.

- After that, press
**ENTER**.

As a result, you will have the following output on your worksheet.

- Finally, use the
**AutoFill**option of Excel to get the remaining outputs as demonstrated in the following image.

__Note:__* As we discussed earlier, you can see that factorial 171 returns a #NUM! Error. *

### 2. Computing Factorial of Number Greater Than or Equal to 171

So, can we calculate the factorials of numbers that are larger or equal to **171 **in Excel? We can definitely do it. To do this, we need to create a **user-defined function** using the **VBA Macro** feature of Excel. In the following section, we will learn the detailed steps to do it.

__Steps:__

- Firstly, go to the
**Developer**tab from**Ribbon**. - Following that, choose the
**Visual Basic**option from the**Code**group.

Subsequently, the **Microsoft Visual Basic** window will open on your worksheet.

- After that, go to the
**Insert**tab from the**Microsoft Visual Basic**window. - Then, choose the
**Module**option from the drop-down.

- Now, write the following code in the newly created
**Module**.

```
Function large_factorial(number_1 As Integer) As String
Dim i As Integer
large_factorial = "1"
For i = 2 To number_1
large_factorial = str_mult_f(large_factorial, CStr(i))
Next i
End Function
Function StrAdd(string_1 As String, string_2 As String) As String
Dim remaining As Integer
Dim sum_value, interim_value As String
remaining = 0
count_value = 1
For i = Application.Max(Len(string_1), Len(string_2)) To 1 Step -1
If Len(string_1) >= count_value Then
Str1Add = Val(Mid(string_1, Len(string_1) - (count_value - 1), 1))
End If
If Len(string_2) >= count_value Then
Str2Add = Val(Mid(string_2, Len(string_2) - (count_value - 1), 1))
End If
sum_value = Str1Add + Str2Add + remaining
Str1Add = 0
Str2Add = 0
count_value = count_value + 1
If sum_value > 9 Then
remaining = 1
sum_value = sum_value - 10
Else: remaining = 0
End If
StrAdd = Trim(Str(sum_value) & StrAdd)
Next i
If remaining Then StrAdd = Str(remaining) & StrAdd
StrAdd = Trim(StrAdd)
End Function
Function str_mult_f(number_1 As String, number_2 As String) As String
Dim multi_1(), multi_2(), multi_3(), answer As String
Dim chunk_value As Integer
Dim step_number As Integer
number_1 = Trim(number_1)
number_2 = Trim(number_2)
chunk_value = 7
step_number = 1
If Len(number_1) / chunk_value <> Int(Len(number_1) / chunk_value) Then
number_1 = String(chunk_value - (Len(number_1) - _
(Int(Len(number_1) / chunk_value) * chunk_value)), "0") & number_1
End If
If Len(number_2) / chunk_value <> Int(Len(number_2) / chunk_value) Then
number_2 = String(chunk_value - (Len(number_2) - _
(Int(Len(number_2) / chunk_value) * chunk_value)), "0") & number_2
End If
ReDim multi_1(Len(number_1) / chunk_value)
ReDim multi_2(Len(number_2) / chunk_value)
For i = Len(number_1) To 1 Step -chunk_value
multi_1(step_number - 1) = Trim(Mid(number_1, Len(number_1) - _
(step_number * chunk_value - 1), chunk_value))
step_number = step_number + 1
Next i
step_number = 1
For i = Len(number_2) To 1 Step -chunk_value
multi_2(step_number - 1) = Trim(Mid(number_2, Len(number_2) - _
(step_number * chunk_value - 1), chunk_value))
step_number = step_number + 1
Next i
step_number = 0
ReDim multi_3(UBound(multi_1()) * UBound(multi_2()))
For i = 1 To UBound(multi_1())
For j = 1 To UBound(multi_2())
multi_3(step_number) = multi_1(i - 1) * multi_2(j - 1) & _
String(((i - 1) + (j - 1)) * chunk_value, "0")
step_number = step_number + 1
Next j
Next i
answer = "0"
For i = 0 To UBound(multi_3()) - 1
answer = StrAdd(answer, (multi_3(i)))
Next i
str_mult_f = answer
End Function
```

- After writing the code, click on the
**Save**icon as marked in the following picture.

- Next, press the keyboard shortcut
**ALT + F11**to open the worksheet from the**VBA**editor. - After that, use the following formula in cell
**C5**.

`=large_factorial(B5)`

Here, cell **B5 **indicates the first cell of the **Number **column.

- Then, press
**ENTER**.

Subsequently, you will get the factorial of **200 **in cell **C5 **as shown in the following image.

- Lastly, apply the
**AutoFill**feature of Excel to get the remaining outputs.

## Practice Section

In the **Excel Workbook**, we have provided a **Practice Section **on the right side of the worksheet. Please practice it yourself.

## Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to **calculate the factorial of a large number in Excel**. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, **ExcelDemy**.