Microsoft's Excel spreadsheet provides a helpful tool for manipulating data, but sometimes its formatting capabilities don't cut it. Although Excel can display data in scientific format, for example, 6.023E23, you can't get it to produce exponents only in multiples of three, such as E-3, E-6, E-9, and so on. Think millivolts, microvolts, and nanovolts, and you get the idea.
Nor will Excel's built-in formats display exponents as superscripts, which may make interpreting data a hassle. Who wants to look at 60.45E-3 when they can see 60.45x10-3 instead? I grew frustrated at these limits and wrote an Excel macro, EngUnits, which not only produces information in engineering format, but it also lets you set the number of significant figures you want in the results. The macro, written in Excel's built-in Visual Basic, operates on a block of contiguous numeric values
I stressed the macro converts numeric information into text that presents the information properly, so users can readily export the data as text to a Word, PowerPoint, or other documents. The VB macro includes comments, so anyone with a bit of programming experience can change the macro to format information differently. Instead of trying to first learn VB from a book, I suggest engineers record a macro to perform a task and then look at the equivalent VB code Excel produces. The macros teach you a lot about how VB works on Excel information. The EngUnits macro is distributed under the terms of the General Public License (GPL): www.gnu.org/licenses/gpl.txt.
Get Real Exponents Real Fast
To use the EngUnits macro, first ensure Excel is set to run macros. Then, simply copy--but do not yet paste--numeric data you want to convert. Highlight 'destination' cells for the data and use Excel's Edit-->Paste Special... menu selection. Click on 'Values' in the Paste Special window. Next, format the pasted data cells as Number or as Scientific and choose as many decimal places as needed to represent your information. With the pasted data still highlighted, you can run the EngUnits macro: Under Excel's Tool menu, select Macro and then select Macros. The list of macros should include EngUnits. Run it on your numeric data.
The distributed Excel file comes with a set of sample data that includes large and small values. The tables below show before and after views of portions of the test data. The macro will operate on numeric data in any contiguous cells:
Numeric Values
|
Engineering-format Text
|
-2.00 |
-2.0000 |
4.00 |
4.0000 |
-32.00 |
-32.000 |
64.00 |
64.000 |
1024.00 |
1.0240x103 |
-2048.00 |
-2.0480x103 |
4096.00 |
4.0960x103 |
-8192.00 |
-8.1920x103 |
1048576.00 |
1.0486x106 |
6.02E-23 |
60.200x10-24 |
|
-6.02E-23 |
-60.200x10-24 |
More Numeric Values
|
Engineering-format Text
|
-0.000002 |
-2.0000x10-6 |
0.000064 |
64.000x10-6 |
-0.000128 |
-128.00x10-6 |
0.000256 |
256.00x10-6 |
0.004096 |
4.0960x10-3 |
-0.008192 |
-8.1920x10-3 |
0.016384 |
16.384x10-3 |
Download Jon's complete macro instructions
Download Excel file with the commented EngUnits macro and some test data
You need this trick if: You need to convert Excel values to engineering format that provides exponents in multiples of three and in superscript form. You can drop converted data into other software, such as Word, PowerPoint, a Web-page editor, or a desktop publishing program.
Got a cool software trick? Send details, including any documentation and supporting code, to kfield@reedbusiness.com. If we publish your trick, we’ll send you a super cool Design News t-shirt.
Tweet This
3 
