Crunching FFTs with Microsoft Excel
IntroductionFast Fourier Transforms (FFTs) are a powerful tool for evaluating the dynamic performance of analog-to-digital converters (ADCs). Numerous programs are available to process ("crunch") the output code of an ADC into its FFT components. Many of these programs are custom applications written by a C programmer. While these custom programs are handy, the C source code is not always available. Other FFT crunching programs use MATLAB®. MATLAB is very popular in the industry; however, MATLAB is not as ubiquitous as Microsoft Excel, and in 2004 Mathworks was charging approximately $3000 (USD) for its basic MATLAB software, along with the signal processing toolbox and annual licensing agreement. This application note introduces an Excel spreadsheet that crunches the output code of an ADC into its FFT components.
The "Crunching_FFTs_Excel-2003" and "Crunching_FFTs_Excel-2007" spreadsheets have been automated using macros for ease of use. They provide a formatted single-tone FFT plot from the user's data. The user is simply required to enter three variables and the ADC data record.
Using Excel to crunch FFTs has its benefits. Excel records all the intermediate steps from raw ADC data to a FFT plot, which the user can then explore by analyzing the equation for each spreadsheet cell. For simplicity, however, the default setting of the Crunching FFTs spreadsheets hides the intermediate detailed calculations. If you wish to analyze the details of the FFT calculation, simply click on the "Show_Detailed_Calculations" bubble.
The major drawback of using Microsoft Excel to perform Fourier analysis: ADC data record is limited to 4096 data points.¹ Another shortcoming of the Crunching FFTs spreadsheet is that it requires coherent sampling. See Maxim's application note 3190 "Coherent Sampling Calculator (CSC)" for more information.²
Step-by-Step Instructions for Using the "Crunching FFTs" Spreadsheet
- Starting with cell A2, replace the existing ADC data record in column A with a new ADC data record. The number of data points in the ADC data record must be a power of 2 (128, 256, 512, 1024, 2048...).
- Enter the three required input variables into cells B2, C2, D2. Remember that you must adhere to coherent sampling.
fSAMPLE is the sampling frequency of the ADC under test.
NRECORD is the number of data points used to create your FFTs. This value must be a power of 2.
- Click on the macro button titled "Click Here After Changing fSAMPLE."
- Click on the macro button titled "Click Here After Changing NRECORD."
- Click on the macro button titled "Click Here to Crunch FFT."³
- Enjoy using your Single-Tone FFT Plot.
Theoretical ExamplesThe Example Data Records spreadsheet is provided as a fun, simple way to practice using the CrunchingFFTs spreadsheet. These example data records were created from a software model of an ADC. This software model only accounts for quantization error and thermal noise, therefore harmonics are not present in the FFTs for these data records.
MAX1420 ExampleThe Crunching_FFTs_MAX1420_fs60_fin2p1_4096_Excel-2003 and Crunching_FFTs_MAX1420_fs60_fin2p1_4096_Excel-2007 spreadsheets show the FFT for data taken from the 12-bit MAX1420EVKIT with a 60.0489984MHz sampling frequency and a 2.125758MHz input tone at -0.5dBFS. Unlike the data discussed in the "Theoretical Examples" section above, the MAX1420 FFT shows harmonics due to device nonlinearities. The second harmonic can be seen at 4.0251516MHz and the third harmonic at 6.377274MHz. See Figure 1.
Figure 1. The 12-bit MAX1420 FFT created with the Crunching_FFTs spreadsheet.
MAX12553 ExampleThe Crunching_FFTs_MAX12553_fs65_fin30_4096_Excel-2003 and Crunching_FFTs_MAX1420_fs60_fin2p1_4096_Excel-2007 spreadsheets show the FFT for data taken from the 14-bit MAX12553 device while mounted on the MAX12555EVKIT with a 65.00352MHz sampling frequency and a 30.01017MHz input tone at -0.6dBFS. See Figure 2. Comparing Figure 1 to Figure 2, one can easily see that the 14-bit device (Figure 2) result is a lower noise floor than the 12-bit device (Figure 1).
Figure 2. The 14-bit MAX12553 FFT created with the Crunching_FFTs spreadsheet.
Excel VersionsThis application note was originally created using Excel 2003 in July 2004. In February 2010, it was updated to include Excel 2007. Other versions of Excel have not been tested. The major difference between Excel 2003 and 2007 is that the function call for the Fourier analysis changed from "ATPVBAEN.XLA" to "ATPVBAEN.XLAM." Additionally, Excel 2007 changed the menus structure.
Tips for Excel 2003 UsersIf you get a runtime error '1004' that says ATPVBAEN.XLA could not be found, you must install the Analysis ToolPak - VBA. The macro linked to the "Click Here to Crunch FFT" button requires the "Analysis ToolPak - VBA" to run successfully. This ToolPak is free and part of your Excel software. To install the TookPak from Microsoft Excel, pull down the "Tools" menu and select "Add-Ins." From the Add-Ins window, check Analysis ToolPak - VBA. You may need your Microsoft Office installation CD to add this ToolPak. Once the TookPak is added, click the macro button "Click Here to Crunch FFT."
Tips for Excel 2007 Users
- When opening "Crunching_FFTs_2007.xls," you may get a security warning. If so, click on "Options" and then select "Enable Macros."
- If you get a runtime error '1004' you need to install the Analysis ToolPak - VBA. To install the "Analysis ToolPak - VBA" in Excel 2007, click the "Microsoft Office Button" in the upper left of the window, click "Excel Options" in the lower right of the window, and then click "Add-Ins." On the bottom of the "Excel Options" window, select "Manage Excel Add-ins" and then click "Go...". On the "Add-Ins" window, check "Analysis ToolPak - VBA" and then click "OK."
- Advanced users may want to study the visual basic code and macros. To more easily work with visual basic and macros, it is recommended that you show the "Developer" tab in the ribbon. To show the "Developer" tab, click the "Microsoft Office Button" in the upper left of the window, click "Excel Options," and then click "Popular." Check the third box that says "Show Developer tab in the Ribbon" and then click "OK."
¹A 4096 data-point ADC record is acceptable to verify the accuracy of an ADC. Maxim, however, typically uses MATLAB software and longer ADC data records to characterize an ADC's dynamic parameters such as SNR, SINAD, THD, and SFDR. Longer ADC data records produce more consistent results from FFT plot to FFT plot.
²It is certainly possible to add additional functionality to the Crunching FFTs.xls spreadsheet that would allow window sampling. See Maxim's application note 1040, "Coherent Sampling vs. Window Sampling" for more information. If a reader succeeds in adding window sampling functionality, we would certainly be interested to see the results.