http://adamhgrimes.com/blog/how-do-you-calculate-volatility-in-excel/

I received a question from a reader who asked, “Can you calculate volatility in Excel?” The answer is, yes you can, but there are a few things you need to know. Without going into too much detail here, there are many ways to calculate volatility. Two of the most common measures are implied and historical (also called realized or statistical) volatility. It is fairly simple to calculate historical volatility in excel, and I will show you how in this post. Calculating implied is quite a bit more complicated. You technically can do it in excel, but you have to impute it from an option price. In addition, there’s actually a volatility surface, or different values of implieds for different strike prices and maturities. That’s a topic for another day; today let’s just look at how to calculate a simple historical volatility in Excel.

4. So far, the procedure has been straightforward: calculate a return series, and then calculate the standard deviation of that series. There is one more step, which is perhaps the only part of this that is conceptually a little bit complicated. You have calculated the standard deviation of the returns for whatever the time interval of your data is. If you have daily data, you have calculated a daily standard deviation, and so on for hourly, weekly or any period. Historical volatility is the annualized standard deviation of returns. We must multiple the standard deviation by an annualization factor, which is the square root of how ever many of your periods are in a year. This example is daily data; there are 262 trading days in a year, so we multiply the standard deviation by SQRT(262). If you are using weekly data, the annualization factor is SQRT(52), etc.