The Mapping Electric Potentials activity from our lab manual Advanced Physics with Vernier — Beyond Mechanics (PHYS-ABM) is set up for students to collect electric potential measurements and enter them into an Excel spreadsheet provided with the lab manual (and attached to this article). Excel then uses that electric potential data to create a 3D plot of the electric field.

Some Excel settings can prevent the spreadsheet from interpolating electric potential correctly. A common error warns of “circular” references.

We believe the issues arise from using a (relatively) old Excel file with a newer versions of Excel. As Excel as evolved over the years, some settings have changed as well. When using the Excel spreadsheet to plot your data, do the following:

1) Be careful that the cells are populated only by numbers, formatted as such or as “general” cells. It appears that if you mangle even one cell – say, type 2,35 instead of 2.35 and it gets formatted as a text cell – it propagates the #VALUE error across the spreadsheet, replacing the zeros in the interpolated cells. (And, it appears, the “undo” button won’t undo it. In that case, open a new template file and copy/paste your data into it.)

2) Check the “Enable iterative calculation” box. It can be found in the File >Options >Formulas menu. This feature gets you past the circular errors message, since it allows Excel to perform the calculation successive times until it reaches a solution. We believe this feature was “on” by default in earlier versions of Excel but isn’t necessarily in later versions.

If you continue to have problems with the Excel spreadsheet, please contact Vernier Technical Support for additional help.