How to convert Celsius to Fahrenheit with Google Sheets

Below is a simple formula to convert Celsius to Fahrenheit and vice versa in Google Sheets; assuming A1 contains the temperature you are converting. Copy our practice sheet to create a dynamic CONVERT formula and see all available conversions built into Google Sheets’ CONVERT function.

=CONVERT(A1,"C","F")

=CONVERT(A1,"F","C")

But we can make our spreadsheet more user friendly. Copy our practice sheet to make a temperature conversion sheet with dropdown menus and dynamic formulas. Also, included in the practice spreadsheet is a dynamic conversion sheet that holds every available conversion variable within Google Sheets.

Expanding Our Temperature Converting Formula

The above formulas work great. But, to make the above formulas more user friendly and understandable we are going to expand our formula and add data validation dropdown menus to our sheet. To accomplish this we will need 3 columns.

1. Temperature Column
2. Conversion Column
3. Converted Temperature Column

The temperature column is straight forward, we need a column so a user can enter the starting temperature they would like to convert.

Next we will add a data validation dropdown menu using “list of items” criteria to specify the conversion, either “Fahrenheit to Celsius” or “Celsius to Fahrenheit”. This will allow a user to easily understand which temperature conversion they have selected.

• Navigate to the data validation menu. Data Data validation
• Select your cell range, the cells we will apply the data validation to
• Select List of items for the criteria and add the two options, separate the options with a comma
• Ensure Show dropdown list in cell is checked
• Click save

Now users will be able to select, from a dropdown, their desired conversion.

Creating Our Temperature Formula

Below is the formula we will use to either convert the temperature from Celsius to Fahrenheit or Fahrenheit to Celsius. Again, column A holds the starting temperature and column B holds the data validation dropdown menu we created in the previous step.

=IF(OR(ISBLANK(A2),ISBLANK(B2)),, IF(B2="Fahrenheit to Celsius", CONVERT(A2,"F","C"),CONVERT(A2,"C","F")))

Formula Explained

Functions Used

• CONVERT
• IF
• ISBLANK
• OR

Formula Goal

The goal of our formula is to either run the CONVERT function to convert temperatures from Fahrenheit to Celsius or Celsius to Fahrenheit. To accomplish this goal we will use the IF function.

=IF(B2="Fahrenheit to Celsius", CONVERT(A2,"F","C"),CONVERT(A2,"C","F"))

With this formula we are running the CONVERT function to convert Fahrenheit to Celsius, if our dropdown cell equals “Fahrenheit to Celsius”. If B2 doesn’t equal “Fahrenheit to Celsius” then the value_if_false formula equation will run which is the CONVERT function to convert Celsius to Fahrenheit.

At this point we have provided a formula for our goal, but to help keep our spreadsheet data neat and organized we should consider expanding our formula to only run if all the necessary variables have been entered.

Ignoring Blank Cells

To ignore blank cells or rows where the necessary data/variables has yet to be entered we can utilize the IF function again. This is referred to as a nest IF formula.

=IF(OR(ISBLANK(A2),ISBLANK(B2)),, IF(B2="Fahrenheit to Celsius", CONVERT(A2,"F","C"),CONVERT(A2,"C","F")))

In the above formula you can see we are first evaluating if our variables cells are blank. We do this with the ISBLANK function. This function will return a TRUE statement if the referenced cell is empty. Because we need to evaluate multiple variable fields we can utilize the OR function. This way if either the corresponding cells in column A or B are blank the OR function will return a TRUE value. If a TRUE value is returned our IF function has been set to leave the cell blank, but if a FALSE value is returned we will run the formula we built in the previous step.

A user can enter a temperature and select from a dropdown menu which temperature conversion they would like to complete. We have a user friendly spreadsheet that is dynamic and flexible.

Bonus: All Available Conversions

You will notice two additional tabs in our practice sheet. One tab to hold variables, this sheet is hidden. The other tab will display the acceptable variables for the CONVERT function depending upon the selected category. Enter a numerical value in cell A2, then you can select your category, start unit, and end unit. We will return the converted value, as well as, also generate a text based formula to return the same value. We do this so you can see exactly how to set up a formula for that specific conversion and the necessary variables.

The convert function can convert your data input based on 13 different categories. Keep in mind you can only convert units between variables in the same category.

• Weight
• Distance
• Time
• Pressure
• Force
• Energy
• Power
• Magnetism
• Temperature
• Volume
• Area
• Information
• Speed

You can use this tab to convert temperature into Kelvin and other units.

Subscribe to our newsletter to receive great Google Sheets tips directly in your inbox.