# How to Perform Bootstrapping in Excel (with Easy Steps)

Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. This article will show you how to perform bootstrapping in Excel with easy steps.

## Introduction to Bootstrapping

Bootstrapping is a method of constructing a confidence interval for a statistic. It follows some characteristics.

• The sample size is small.
• The distribution is unknown.

## How to Perform Bootstrapping in Excel: with Easy Steps

This is the dataset for todayâ€™s article. I have an original dataset and have to prepare some samples from this data.

I will use this dataset and samples to perform bootstrapping.

### Step 1: Prepare Samples

The first step is to prepare bootstrapping samples from the original dataset. To do so, I will use a combination of the INDEX, ROWS, and RAND functions.

• Go to D5 and write down the following formula
`=INDEX(\$B\$5:\$B\$34,ROWS(\$B\$5:\$B\$34)*RAND()+1)`
• Then, press ENTER to get the output.

Formula Breakdown

• RAND() â†’ Returns a random number greater than 0 and lesser than 1.
• Output: The output will vary every time you refresh the file.
• ROWS(\$B\$5:\$B\$34) â†’ Returns the number of rows in the given range.
• Output: 30
• INDEX(\$B\$5:\$B\$34,ROWS(\$B\$5:\$B\$34)*RAND()+1) â†’ Returns you the result
• Output: The output will vary every time you refresh the file.
• After that, use the Fill Handle to AutoFill up to M5.

• Similarly, AutoFill downward till sampling 30.

### Step 2: Calculate Mean of Means

Now, I will calculate the mean of all the means of these 30 samples. But before that, I have to calculate the mean of each sample. The function that is going to be handy is the AVERAGE function.

• To do so, go to N5 and write down the following formula
`=AVERAGE(D5:M5)`
• Then, press ENTER to get the output.

• After that, AutoFill the formula up to N34.

• Now, I will calculate the mean of means. To do so, write down the following formula in Q4.
`=AVERAGE(N5:N34)`
• Then, press ENTER to get the output.

### Step 3: Measure Confidence Interval

In the following step, we will measure the confidence interval. The function to be used is the PERCENTILE.EXC function. But before that, I will require a cut-off value.

• To calculate the cut-off value, go to Q5 and write down the following formula
`=Q4*0.05`
• Then, press ENTER to get the output.

• I have assumed a 90% confidence interval. Thatâ€™s why I have multiplied the mean of means with 05.
• Then, I will calculate the lower bound. For this, go to Q6 and write down the following formula
`=PERCENTILE.EXC(D5:M34,0.05)`
• Then, press ENTER to get the output.

• In a similar way, I will calculate the upper bound. The formula will become
`=PERCENTILE.EXC(D5:M34,0.95)`
• Then, press ENTER to get the output.

## Conclusion

In this article, I have demonstrated how to perform bootstrapping in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.

## Related Articles

<< Go Back to Excel for StatisticsÂ |Â Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF