How to do a Fast Fourier Transform (FFT) in Microsoft Excel

Fast Fourier Transform (FFT) is the easiest way to show the dominant frequencies in a signal. FFT is an efficient algorithm to compute the discrete Fourier Transform and in its inverse. There are many distinct FFT algorithms which involves a wide range of mathematics, which starts from simple complex-number arithmetic to group theory and number theory too. You also need Microsoft Excel with the Data Analysis package.If you don’t have this you can install this under Tools or Add-ins.

In this article you will see how to perform a FFT graph in Microsoft excel.

Step 1:

First and foremost open a blank spreadsheet and then follow the following titles to the first cells in column A, B, C, D and E of your spreadsheet: Time, Data, FFT Frequency, FFT Complex, and FFT Magnitude.

Step 2:

You need to add your data to the “Data” column. It will be helpful for you to make a note of the number of data points that is samples in your list and taking into consideration the sampling rate at which your data is taken. Do not forget o write and label these two numbers somewhere in your spreadsheet.

Step 3:

In the “Time” column, you need to determine the time at which each point was taken. The easiest way to do this is to divide the total time by the number of data points.

Step 4:

Next you need to perform a Fourier Analysis of your data which can be either Data/Data Analysis/ Fourier Analysis. You have make sure that the input range is your entire data column and the output range is you entire FFT Complete column. This column is known as FFT Complex column because the Fourier Analysis function outputs a complex number.

Step 5:

In the very first cell of the “FTT Magnitude” column, you need to type the following equation: =IMABS (E2)

Now you need to drag this equation down so that it fills every cell in this column. This will turn all the complex numbers in the “FTT Complex” column into a real number so that we can use the dominant frequencies.

STEP 6:

Now we need to calibrate the axis A of the graph to show the dominant frequencies. It may prove beneficial if we create a separate column showing zero through the number of data points minus one (N-1). It will also help to create a separate cell with the following function:

=(S/2)/ (N/2) over here S is a sampling rate and N is the number of samples.

In the “FTT Frequency” column you need to type the following function:

=F2*SG$4

Here F2 is the appropriate number from the number column, and $G$4 denotes the function dividing the sampling rate by the number of samples. Now drag this function down to half of your data of your data points.

Step 7:

Now create a graph of “FTT Magnitude” column (Y axis) versus the “FTT Frequency” column (X axis). If all you have done is right then you should get a graph with peaks where the dominant frequencies are.

27 Comments

  1. Hi,

    I came across your notes, while I was searching for a recipe to do FFT analysis on a data set using excel.

    as easy as they seem, I am having a bit difficulty to follow the instructions.

    is G4 equal to (S/2)/(N/2) where S is the sampling frequency, and N is the number of points. Why is it (S/2)/(N/2)? why did not you write S/N? How is it any different?

    also why fft frequency equal to F2*G$4? is this the definition of fft frequency?

    Thank you very much,

    Oscar

  2. It is unfortunately a poor copy/past of a document explaining how to do a FFT with Excel. Why a “poor copy/past”, because it’s a copy/past but adding some mistakes such as inverting the FTT Magnitude column and the FTT Complex leading to a “=IMABS (E2)” in the … E2 case.
    Thanks anyway for sharing knowledge 🙂

  3. This seems to be a transcription of a longer answer from San Francisco State University School of Engineering (“Frequency Domain Using Excel”) by Larry Klingenberg
    but this one has a few typos.

  4. Robin

    You have no idea how greatly I was looking for these steps. I was trying to find online solution and luckily I ended up here. I have bookmarked this page to refer to the steps whenever I want and I have even shared it with my brother who often trouble me with Microsoft word and excel issues. TechGenie is a good platform to look for details like this

  5. My coder is trying to convince me to move to .
    net from PHP. I have always disliked the idea because
    of the expenses. But he’s tryiong none the less. I’ve been using Movable-type on several websites for about a year and am concerned about switching to another platform.

    I have heard fantastic things about blogengine.

    net. Is there a way I can import all my wordpress posts into
    it? Any help would be really appreciated!

  6. Techie Justin

    Every time I try to perform a FFT graph in Microsoft excel, I get stuck at the fifth step where we need to type the equation. Performing a FFT graph is not easy but thanks to the DIY solution here, I can now perform it easily. TechGenie has helped me to resolve many technical issues like this before.

  7. Reenie

    I always end up doing something wrong while performing a FFT graph in Microsoft excel. I usually get confused at the equation step but the steps here are easy to follow and next time I will be able to do it on my own. I have a request that you please continue to give such DIY’s as it is of great help to the technically challenged people like me. TechGenie is a big hope for me and I always look forward to the information given here

  8. Thank you for this quick tutorial, I got a bit confused when attributing frequencies to magnitudes.

    Just for clarity – the zero value (N=0) is the offset, the first value has a period equal to the length of the sample file (Full Period = number of samples x sample period), the second value (n=2) has a period of half the full period (Full Period/2), n=3 is a third i.e. FP/3, n=4 is FP/4, therefore the period of value n = (number of samples) * (sample period) / n. Finally turn the period into frequency by inverting it or directly freq = n/[(number of samples)*(sample period)].

    Please correct me if I’m wrong.

  9. step 1 says to fill out cells A->E…

    later in the other steps, you call out cells F and G, amongst others, which are not well defined. you are inferring a lot of hidden details in making the other cells beyond E. according to you listing, column E will be FFT magnitude. running IMABS (E2) through column is self-defeating as it has supposedly ‘collected’ the data from the column D which is the ‘complex’ [not complete] column. excel does not accept such circular programming…

    while i figured out all the hidden details it should be noted that many of these steps would be clearly defined with screen shots of the behavior one wants to enlist, per step…

  10. Harmeet,

    Your explanation is not clear enough.

    what is “SG$4$”?

    Did you mean

    =F2*S*G4 ?

    Could you provide an example?

    Also, it would be good if you mentioned that the number of points has to be a power of 2. I tried using 30 points so it didn’t work, so I had to use only 16. Or am I missing something?

    Please be more clear.

    Thanks

  11. Rob

    It is assumed that the labels are all in row 1.
    With regard to columns:
    A = Time
    B = Data
    C = FFT freq
    D = FFT complex
    E = FFT Magnitude
    F = ?, probably some numerical index

    The author made a typo. Where it says

    “Step 5:
    In the very first cell of the “FTT Magnitude” column, you need to type the following equation: =IMABS (E2)”

    It should have read (D2). This is because you are interested in the amplitude of the FFT’d signal. The FFT gives two important bit: amplitude and phase, but it *gives* the info as a complex number (ie real and imaginary components). One must break it down into its useful parts. That is my imabs is for:getting the amplitude.

    Even after you have the amplitude ‘FFT MAGNITUDE’ (call it the Y axis), you somehow need to get the right values for the X axis-to match. This is where you are determining the scaling for the x-axis.

    How do we do this? Well, the Nyquist theorem says the maximum frequency you resolve is half your sampling frequency in the time domain. It is for this reason that the frequency should only extent to +/- your sampling frequency (in the time domain) divided by two. After all why should we favor positive frequencies over negative ones? They are both will fit right over the top of a time domain signal-ie. cosine (for example) Now that you have the boundaries of your x axis, you need to divide that x-axis into N samples so that your x axis is the same size as your y axis. That is all the fancy ratio stuff that step 6 was trying to do. So your x axis would look like: {(-Fs/2), (-Fs/2)+ delta,
    -(Fs/2)+2delta,…0,…,(Fs/2)-2delta,(Fs/2)-delta,(Fs/2)}

    In this case Fs is the sampling frequency in the time domain. We could solve delta by thinking about breaking -fs/2 to fs/2 into N of delta or we could think about ranging from 0 to fs/2 (~half as many) divided by N/2 to obtain delta.

    but if you really want to learn a lot about fourier techniques, I like the book “FFTs in NMR, Optical, and Mass Spectroscopy” Francis and Verdun (if I recall).

  12. Dwell

    I had gone through the write-up and tried following the steps very carefully. But every time I try, I do not get a FFT graph. Please can you suggest what may be wrong on my application? I have some urgent work to do using excel and am not able to proceed. Please help!

  13. Melona

    Microsoft Excel has never been easy for me. Never have I been able to perform the formulas and equations. But when searching for some solution on how to do a FFT in Microsoft Excel I ended up here on this page. I faced some issues in the beginning but after trying for 2-3 times I understood and performed the task. Would like to thank for the solution.

  14. Dwell: There are some typos in the article. The equation you need in Step 5 is: =IMABS(D2). You should also realize that the data in the article is real, but is allowed to be complex. Also you need to know that Excel’s FFT function has a requirement that N, the number of data points be equal to 2 to some integer power, less than or equal to 12. This means the maximum value of N is 4096.

  15. Dwell: There are typos in the instructions. The equation (to be entered without the quotes) in Step 5 should be “= IMABS(D2))”. You should also know that the FFT function in Excel requires that N, the number of samples must be equal to 2 raised to an integer power between (and including) 1 and 12.

  16. Maggie

    Working on Microsoft Excel has always been tough for me, creating FFT graph was beyond my skills. But the other day I was looking for steps for creating FFT Graph and luckily I came across this page. Though, I was unable to create a FFT graph in the first attempt. But after several attempts I could easily create FFT graph.

  17. I have always faced difficulties while working on Microsoft Office Excel. Creating a graph was something I could never get through. When I came across the step by step tips, I realized how simple it is to use FFT graph in Microsoft excel. Now I can easily make FFT graphs without nagging my colleagues and friends.

  18. Here is what I added to make it work:
    Developer > Add-ins > Analysis ToolPak
    References to first cell is row 2
    Step 1: include Column F titled Number
    Step 2: number of points must be an exact power of 2 and no more than 4096
    Step 3: A2 is 0, A3 is =A2+(1/(samplerate)), then copy A3 down
    Step 4: If Data Analysis is missing then you don’t have the add-in
    Step 5: E2 is =IMABS(D2), then copy down
    Step 6: G4 is =(samplerate)/(number_of_points),
    F2 =ROW()-1 & copy down

Leave a Reply

Your email address will not be published. Required fields are marked *