An Excel-based Depth-of-Field Calculator.

 

Excel-based Depth-of-Field Calculator

First some definitions are in order. In photography, depth-of-field (DOF) is loosely defined as the region in focus for a given photograph. The main factors affecting DOF are the size of the camera sensor, the lens focal length and aperture, and the distance to the subject. Here's how this works. With a given camera, if what you want is to get as shallow DOF as possible (i.e. a blurred background as commonly seen in portraits) then you want to:

- Get closer to your subject

- Use a wider aperture (e.g. f/2 is better than f/8)

- Use a long lens (e.g. 100 mm is better than 24mm)

And of course the reverse actions are true if you need a wide DOF (say, in a landscape shot).

This is the most basic knowledge photographers need to have to control DOF. As you become experienced, these tradeoffs become intuitive, and you pretty much know how to adjust your camera for the DOF you need in a given situation. In the beginning though, and also for some critical situations, it is useful to have a calculator giving you the expected DOF for a given setting. I understand there are some very good online calculators and even smart-phone Apps that do this very well. I happen to use an iPhone App myself. But, if nothing else, for educational purposes, it's fun to have a tool you can run on Excel and can perform some interesting plots. It's for this reason that I developed this freely available tool. So let's have a look.

The DOF Calculator Interface

Figure 1 shows the Calculator "Interface".

Figure 1 - Spreadsheet Calculator

In the "INPUTS" region you simply enter your parameters like the camera sensor, the lens focal length, aperture and subject distance. The spreadsheet automatically calculates the closest distance that is still sharp and the farthest distance that is still sharp. Units are selectable between feet and meters. The total DOF (i.e Far Distance - Near Distance) is diplayed in units of m (of ft), cm and inches for convenience. The tool also plots this in a bar graph showing the areas in-focus behind and in-front of the subject. As the subject distance gets larger, the DOF behind the subject keeps increasing. When the so-called called "hyperfocal distance" is reached, pretty much everything behind the subject becomes in-focus. The calculator signals this by showing "Infinity" on the far distance calculation and "000" in the bar graph.

I also added two auxiliary plots to the spreadsheet that I believe add something I haven't seen in other calculators. The top plot, "DOF Variation with Aperture (Given Subject Distance)", shows how the total DOF  changes if you adjust the Aperture and keep everything else the same.

The bottom plot, "DOF Variation with Subject Distance (Given Aperture)", shows how the total DOF changes if you just change your subject distance while keeping everything else the same (including the aperture selected).

Downloads

Download links are provided below.

DOF Calculator, Rev 3

And here's an Excel 2003 compatible version as some users can't open the latest excel formats.

DOF Calculator, Rev 3 (Excel 2003 compatible)

 

The detailed math I implemented in this spreadsheet is described in this Wikipedia article http://en.wikipedia.org/wiki/Depth_of_field. While I did my best to avoid bugs, and tested the tool by comparing results against other calculators, it's possible some bugs remain. If you find any bugs, or have suggestions for improvement, please contact me and I'll do my best to address them. 

Comments, questions, suggestions? You can reach me at: contact (at sign) paulorenato (dot) com

 

• Prev
• Next