The Excel CONVERT Function Conversion Toolkit

The Excel CONVERT Function Explained

The Excel CONVERT function is both a powerful workhorse and a cumbersome beast. With this Excel CONVERT Function Toolkit we have attempted to tame the beast for you so that you can get more out of the workhorse.

The Excel CONVERT Function Toolkit is free to download as a .xlsx workbook and it's features are outlined for you below: CONVERT Toolkit Features.

Download Free CONVERT Function Toolkit

What is the Excel CONVERT function?
The Excel CONVERT Function Shortcomings
The Excel CONVERT FUNCTION Toolkit Features
The Excel CONVERT Function Compatibility
The Excel CONVERT Function Conclusion

What is the Excel CONVERT Function?

The Excel CONVERT function first appeared with the launch of Excel 2007 and it gives you the power to convert one unit of weight or measurement into another unit by calling the CONVERT function from within a formula. At its core CONVERT is a really simple function to master.

The syntax for the CONVERT function is as follows:

=CONVERT(number,from_unit,to_unit)

So for instance, if I wanted to find out how many feet were in 10 metres I would create a formula as follows:

=CONVERT(10,"m","ft")

The first operator ("m") is the unit we are converting from (metres) and the second operator ("ft") is the unit that we are converting to (feet).

You need to remember to put your units inside double quotation marks in order for it to work here.

You can only convert units from like-for-like categories. So from one energy unit to another energy unit or from one distance unit to another distance unit. You will obviously generate errors if you try to perform conversions between different categories (converting cubic feet to Fahrenheit for example).

The Excel CONVERT Function Shortcomings

Convert FUNCTION Cross Referencing

The CONVERT function can make use of over 100 different units and unit prefixes and each one has an associated operator which you need to call from within your formula in order to perform the conversion. That's an awful lot to remember, and even if you have a cross reference source such as that provided by Microsoft's support documentation its an awful lot of going back and forth to check your references.

Convert FUNCTION Omissions

Whilst the unit operators available to the CONVERT function are extensive, they are by no means comprehensive and there are some limitations. For instance, the Distance category would really benefit from some additional unit measurements of typography such as EMs and Pixels in addition to PICAs, Points (PICA 1/72 inch) and inches. There are also some UK specific measurement units ocassionally missing (see Ambiguities).

Convert FUNCTION Inconsistent Case Sensitivity

What's more, most of the operators that you will use in the CONVERT function are case sensitive but some aren't such as the unit BTU (British Thermal Unit) for which you can either use the uppercase operator "BTU" or the lowercase operator "btu". Then there are other operators for units which are only partially case sensitive such as for Electron Volts for which you can use either "eV" or "ev" in your formula, but not "EV". Its very easy to get bogged down, even with a reference guide and Microsoft have definitely not gone all out on standardisation in order to make the CONVERT function more user-friendly.

Some units also have multiple operators which do the same thing. For example, for the Imperial hundredweight unit you can call any of the following three operators: "uk_cwt", "lcwt" or "hweight".

Convert FUNCTION Ambiguities

Also, the unit operators that are used by the CONVERT function can sometimes be a little North-America centric and are not always clearly documented as such in Microsoft's support documentation. For instance, Fluid Ounces ("oz") uses the United States Customary fluid ounce but does not distinguish it from the British Imperial fluid ounce which is both different and omitted from the available units to choose from. The same is the case for the Freight Ton which silently goes by the US measurement.

Convert FUNCTION Errors

There's even a spelling mistake in there which could confound many people, since "deka" is written as "dekao" in the support documentation.¹ There's no such thing as "dekao" and a Google search for a "dekao unit" back in 2021 pretty much only gave you results for Excel's CONVERT function and its documentation and not for any unit of measurement that actually exists.² If not for Excel CONVERT this would nearly have been a GoogleWhack, and yes even Microsoft make mistakes sometimes!

In 2024, the spelling error persists in the documentation and a few Online Excel tutorials on the CONVERT function have even adopted the "dekao" mispelling in their tutorials.

What this indicates is that, since this error has presumably been sitting there for at least a decade, the CONVERT function is pretty dormant in terms of extension, update or innovation. On the plus side that means its quite stable, but on the downside it means that we need to workaround any shortcomings it has for ourselves.

Overall, using Excel's CONVERT function without checking your units and your formulas carefully represents a big opportunity for inconsistency and error within your spreadsheets. As you can see, if you are starting from scratch with Excel's CONVERT function (and you are going to use it a lot) then you could have a lot of work in front of you.

Or you would have done, but with our Excel CONVERT function toolkit we have done a lot of the heavy lifting for you.

1) In weights and measurements "deka" is the prefix denoting a multiple of the value by 1000.

2) Since we've mentioned Google its worth pointing out that the Excel CONVERT function isn't the only show in town. Google Sheets also has a very good CONVERT function too. See the documentation here: Google Sheets Convert Function Documentation.

The Excel CONVERT FUNCTION Toolkit Features.

We have attempted to put together a CONVERT function solution that goes some way to compensating for the obstacles generally faced when using Excel to perform weight and measurements conversions. The CONVERT function and its unit operators become much more user-friendly once you take them out of static reference sources and document them in Excel itself.

With our Excel CONVERT function toolkit we have taken the units and operators that you need out of static external reference sources and put them right into a dynamic Excel spreadsheet, so that you can use them right away by just calling up their unique cell reference. Where there are multiple unit operators which do the same thing we have chosen just one for the sake of consistency and simplicity.

For each weight and measurement category this downloadable Excel workbook provides you with an almost complete reference list of Excel CONVERT operators which you can add to your own formulas just by using the cell reference without ever having to remember the operators, typing them out or having to worry about the various ways in which different ones use different combinations of capitalisation and lowercase.

Calling cell references to get the unit operators in this way also means that you don't need to type out double quotation marks around your from_units and to_units every time, as we did on the first example of the CONVERT function on this page.

This alone gives you a real headstart for working with the CONVERT function. Plus, for each category of weight and measurement we've created 36 readymade Excel CONVERT formulas which enable you to perform a lot of the calculations that you might need out of the box.

The following Feet to Inches CONVERT function conversion is just one example:

=CONVERT(E11,C15,C14)

Excel CONVERT Function with CONCATENTATE for Prefixes

Where unit operators can also make use of a multiplier prefix (e.g. the Metre operator "m" can make use of the Kilo prefix "k" to perform conversions upon kilometres) we have provided you with a working sample formula which makes use of Excel's CONCATENATE function to add the prefix to the operator.

=CONVERT(E136,C12,(CONCATENATE(C142,C11)))

Excel convert function using CONCATENATE for prefixes

Excel CONVERT Function with CONCAT for Prefixes

For forwards compatibility we have also given you a version of the formula which uses CONCAT, in anticipation of the CONCATENATE function becoming deprecated at some point in the future.

This particular example converts terabytes to gigabytes by calling up the quantity to convert in Cell E138 and then prefixing the byte unit in Cell C122 with the tera operator in Cell C139 for the from_unit and lastly prefixing the byte unit in Cell C122 with the giga operator in Cell C140 for the to_unit.

=CONVERT(E138,(CONCAT(C139,C122)),(CONCAT(C140,C122)))

Excel convert function using CONCAT for prefixes

Excel CONVERT Function Disambiguation and Clarification of Standard Units

Where clarification might be needed we have added a citation explaining the units in the notes.

Where disambiguation might be required we have provided information in square brackets with the unit to help distinguish it from a similar unit. For example, Microsoft's support documentation for the CONVERT function lacks a clear distinction between mechanical horsepower and metric horsepower which you ordinarily might not be able to differentiate without doing some research and then testing the function to prove which one is which. We've done much of this work for you, but it still pays to be vigilent.

All the operator values in Column C needed to perform your Excel CONVERT calculations have been protected so that they can't be altered, meaning that you are in a good place to avoid errors in your formulas. The password used is MyPassword, if you decide that you do want to edit the unit operators in the future.

Excel CONVERT Function with Multiple Worksheets

In addition to the convert_references worksheet, in Sheet2 you will find an additional readymade formula which enables you to call the unit operators needed to perform CONVERT function calculations from any other worksheet in the workbook.

=CONVERT(C5,convert_references!$C$14,
convert_references!$C$15)

With this example formula we used the CONVERT function and called the operators we needed from the convert_references worksheet to use here in our formula in Sheet2.

We used absolute cell references for columns and rows so that we can then use the Fill Handle cursor to apply the formula to the cells below D5 without the source cells incrementing and causing incorrect results.

Excel convert function using abolsute cell references

This CONVERT function statement works as follows

C5 tells Excel to look for the unit quantity to convert in cell C5 in this worksheet.
The convert_references! part tells Excel to look for the measurement system to use in the worksheet called convert_references. This needs to end with an exclamation mark in order to work.
The first dollar sign ($C) creates an absolute cell reference which tells Excel to only look for the measurement system to use for the from_unit in Column C.
The second dollar sign ($14) creates an absolute cell reference which tells Excel to only look for the measurement system to use for the from_unit in Row 14.
The same logic used in points 2-4 also applies to the to_unit.

Now that we have a formula that works between worksheets you can even insert these worksheets into an existing workbook allowing you to perform a multitude of weight and measurement conversions upon your existing data with minimum effort.

Excel CONVERT Function Compatibility

Because the Excel CONVERT function only first appeared in Excel 2007 it won't work at all in any versions prior to that. With our downloadable .xlsx Excel CONVERT function toolkit we have passed it around a bit between Excel 365 and Excel 2010. When doing this we discovered that Excel 2010 only recognises 50 percent of the unit operators available to 365 so these will have been added since Excel 2010 and the worksheet is not fully backwards compatible.

At present there appears to be no documentation as to which units were added in which Excel versions.

We recommend using this toolkit with Excel 2019, Excel 2021 and Excel 365 for best performance.

Excel CONVERT Function Conclusion

When making any unit conversions using Excel's CONVERT function you should always perform your own checks and undertake your own due diligence before making any commercial decisions based upon the results. Weight and measurement conversions need to be both laser accurate and reference exactly the right standard units in order to be dependable. Mistakes can be costly.

If you enjoyed this tutorial you might also like:

We come to you: Our regional, mobile Microsoft Excel trainers cover most locations of mainland UK for on-site visits including the English regions of the North West, North East, Yorkshire and the Humber, Greater London, the East of England, West Midlands, East Midlands, and also some parts of the South West of England (including Wiltshire, Bristol area and Gloucestershire) and South East of England (including Buckinghamshire, Oxfordshire, Berkshire, Hampshire and Surrey). Virtual classroom courses are available from anywhere via live video conferencing.

We really ❤ love helping organisations to master Microsoft Excel.

Our only question is: Will it be yours?

Our Excel Trainers are:

Specialist educators with a wealth of experience and expertise, proven track records and excellent feedback.

Our Closed Excel Courses are:

Flexible instructor-led courses catering to YOUR specific learning needs and training requirements.

Education is Our Passion:

Over 18,000 students trained across almost every industry, sector and background.

Call 0844 493 3699

Or email info@foursquaretraining.co.uk

Excel Training Reviews

An excellent, clear and patient tutor who guided me through the course seamlessly.
M Menzies (Online Excel training 365 (virtual classroom))

Can I just pass on my thanks to your team for the training you provided in York. I've had lots of positive feedback from the staff involved.
J Whiley, NHS York and Selby (Excel training York)

Really useful. Really knowledgeable trainer.
A Ward, Aeromet (Advanced Excel Training Worcester)

The trainer explained everything really well. I found the course easy to follow.
V Broxham, Baywa-re Operational Services (Intermediate Excel training Milton Keynes)

Happy with everything.
M Aston, Bierce Surveying Ltd (Advanced Excel Training Aylesbury Buckinghamshire)

We have had a few people over the years but the way the trainer picked up our business and requirements so quickly – all I can is you have a diamond there.
S Jenkins, Concorde Glass (Intermediate Excel Training Grimsby)

We all thoroughly enjoyed the course, we learned a great deal and thought Sara was a brilliant trainer, very patient with a fantastic way of teaching us, so thank you for that.
C Waldock, Darlington Borough Council (Intermediate Excel training Darlington, County Durham)

☆ ☆ ☆ ☆ ☆ Five Star Review

We had some really great feedback from the last two training sessions,
on the quality of the content as well as the delivery from the trainer.
Excel Training London

The content covered the exact content required after the beginners course. The information will allow me to improve the spreadsheets currently in use.
L Powell, Dudley Building Society (Intermediate Excel Training Dudley)

Great course!
V Franklin, E-Leather (Excel Beginners Training Peterborough)

The training approach and style was great. Incredibly informative and supportive.
L Shier, One YMCA (Introduction to Excel Training Watford, Hertfordshire)

Great teacher!
J Smith, Fisher German (Intermediate Excel Training Canterbury, Kent)

Its an excellent course. I was impressed by the discovery of so many tools that will facilitate our working life. The trainer is excellent, everything was perfect.
S Green, HW Martin (Beginners Excel Training Cambridgeshire)

Very happy to learn so much.
O Chawishly, LM Information Delivery (Intermediate Excel Training Oxfordshire)

Covered all objectives. Wish We'd had two days.
B Hough, Homebase (VBA for Excel Training Milton Keynes)

The course was excellent, covering more in a single day of training than I anticipated would be possible with a high degree of clarity.
Unsigned, New Economy, (VBA for Excel Training Manchester)

Very informative, easy to follow. Trainer was clear with his explanations
S Nottingham, Denney O'Hara (Microsoft Excel training Leeds, West Yorkshire)

Excellent day!
L McLachlan, Grange School (Excel training Rochdale, Greater Manchester)

Brilliant Training, went at the right speed [and] was made relevant to work
Kathryn Strong, Tennants Distribution (Excel training Manchester)

Fantastic course, not too much information overload. Explained simple. Exactly what I needed. Many thanks.
K Stobbs, Depuy Spine - A Jonson and Jonson Company (Introductory Excel training Leeds, West Yorkshire)

The flexibility of the course was ideal.
S Walsh, Progress Rail (Advanced Excel training Edinburgh, Midlothian)

The guys who came to the course this week were very impressed with the whole set up and are now eager to attend the next level up.
E Dixon, Connaught PLC (Beginners Excel training Leeds, West Yorkshire)

The course has improved my knowledge of Microsoft Excel and was a good pace so easily understandable. Gerry was very patient and helped each of us where we needed it.
L Muir, Filtronic Broadband (Microsoft Excel training Newcastle Upon Tyne)

Although a total beginner on Excel, I found the course to be extremely helpful.
S Antonie, Filtronic (Excel training Shipley)

Made what I thought was going to be completely foreign to me easy to understand. I can actually see where I can use Excel in my day job now. Max was very good and took the time to make sure we understood. [...] Nothing could be improved.
C Elsmore, Elmwood Design (Excel training Leeds, West Yorkshire)

Very Useful.
J Collins, Cooper Research Technology Ltd (Advanced MS Excel Training Ripley, Derbyshire)

Enjoyable and informative.
N. Bell, Cosalt PLC (Excel advanced training Grimsby, Lincolnshire)

The training session went very well and I have had a lot of good feedback from the participants.
P Ogden, InterfaceFLOR (Microsoft Excel Training Halifax West Yorkshire)

Course exceeded expectations.
K. Richards, Ashfield Homes (Microsoft Excel Training Sutton-in-Ashfield, Nottinghamshire)

The day was very well constructed and presented.
D. Coulthand, Crossgates Shopping Centre, Leeds (Intermediate Microsoft Excel Training Leeds, West Yorkshire)

Training very clear. Never felt rushed or pressured to complete tasks. Each item explained and demonstrated very well. Easy to follow
S. Hope, Lear Corporation (Advanced Microsoft Excel Training Coventry, West Midlands)

Good course, good information, good trainer and at a nice tempo
A. Webster, BE Aerospace (Advanced Microsoft Excel Training Hinckley, Leicestershire)

Excellent, the best Excel course I have ever been on. Trainer gave time and patience to us all.
D. Gill, Persimmon Homes (Beginners Microsoft Excel Training Malmesbury, Wiltshire)

A very robust day's training.
T. Camp, Ideal Shopping (Beginners Microsoft Excel Training Peterborough, Cambridgeshire)

The course in Edinburgh was incredible
C. Rowley (Beginners Microsoft Excel Training Edinburgh, Midlothian)