With linked data types, you can easily pull food nutrition facts and data right into Excel. To do this, you’ll first convert text into the Foods data type and then insert the facts you want into your workbook. This article will teach you how.
Note: The Foods data type is only available to Microsoft 365 Personal, Microsoft 365 Family, and EDU subscribers. For more information about requirements see the Linked data types FAQ and tips.
How to convert to and use the Foods data type
-
In a cell, enter the name of a generic food, food type, food group, or nutrient. For example, you might enter “Blueberries”, “Blueberry Pancakes”, or “2 eggs”. If you’re entering multiple, make sure each is in its own cell in a column.
Tip: You can include serving size or quantities when using the Foods data type. See the section on clarifying portions below.
-
Select these cells and go to Insert > Table to create a table. Add a header column if desired.
Note: While creating a table isn’t required, we recommend putting your text in a table for the best experience.
-
With the table selected, go to Data > expand the Data Types dropdown gallery > select Foods.
-
Excel will convert the text to a Foods data type that matches our online sources. You’ll know it’s converted if the Food icon appears in the cells.
Note: If you see a question mark icon in the cell, this means that Excel needs your help to clarify a match. See the section on using the Data Selector below.
-
To add data to your table, select one or more converted cells and the Add Column button will appear. Select that button and select any of the fields to add those facts to the table. For example, selecting “calories, total” will insert a column with the approximate total number of calories for the portion sizes of the foods in the table.
-
Once you’ve got the data you want in your workbook, you can use your favorite Excel features, like formulas, to reference and work with the data. See How to write formulas that reference data types for more information.
Use the Data Selector to specify a Foods data type match.
Use these tips below to help you figure out the correct match for your text. If you need help on how to use the Data Selector, see Use the Data Selector to specify data types
What to do if I get multiple results for the same food
You may see multiple results of the same food with different calorie counts. This means there are options that you can choose from.
In the example to the right, converting “Tomato Soup” will give you multiple options to choose from.
To find the result you need you can:
-
Refine your results by entering a more specific term in the text box of the Data Selector.
Tip: So instead of “Tomato Soup” you might enter “Tomato Basil Soup” which will give you only one option. Selecting this will also update the text in your table.
-
You can also look at details for each result so you can make an informed choice on which result to select. To learn how, see below.
To view details of a food result in the Data Selector:
-
Select the image of a result to open the detail view.
-
Scroll down to the related foods section and check if it’s similar to what you want.
-
If it is, Select it to choose this result and convert it. Otherwise, select the back arrow to check the other results.
In this example, this “tomato soup” result shows that its nutritional information actually represents a creamy tomato bisque.
Clarifying portions and quantities for food
If you convert general food like “blueberries”, you may wonder what quantity is represented by the calorie count. By selecting the data type icon to open the card, you can check the consumed description field to see what quantity the nutrition facts represent.
You can easily refine foods with modifiers for quantity to get a better representation of what you ate. This can be done before converting or after by simply re-entering the text in the cell.
For example, you can enter “1/2 cup blueberries”, “60 g blueberries”, “20 blueberries”, “10 oz blueberries”, or even “1 large bowl blueberries”.
See also
Linked data types FAQ and tips
What linked data types are available in Excel?
How to write formulas that reference data types
FIELDVALUE function
#FIELD! error