Calculate Daily Fat Calories in Microsoft Excel 2000
Updated: April 14, 2004
Curriculum Areas: Language arts/math/social studies/science
Grade Level: 4 and up
Application: Microsoft Excel 2000
Tip: Calculate daily fat calories and the percentage of calories from fat using an Excel spreadsheet.
Description: The U.S. Department of Agriculture recommends a diet that provides no more than 30 percent of total calories from fat. (See the United States Department of Agriculture's Dietary Guidelines for Americans.) This activity shows students how to create an Excel spreadsheet and use Excel functions to keep track of the food they eat each day, as well as the total calories and grams of fat. Students also learn to calculate "fat calories" and the "% of calories from fat" as they learn more about healthy eating.
This activity can be used when studying nutrition with your class or even as part of a math lesson. The activity can last for just one or two days with younger students or for a week or so with older students.
Below is a sample daily food log, starting with breakfast.
How to:
1. | Start Microsoft Excel and make sure a new, blank spreadsheet is open. |
2. | Near the top of the spreadsheet, enter the date you have chosen as the day on which you're going to keep track of what you've eaten. Widen the A column so that the foods aren't overlapping into the next column by dragging to the right the column width marker between the A and B column headings. |
3. | In the A column, enter the foods you ate for breakfast (see sample above). Include the serving sizes, too. |
4. | In the B and C columns, enter the amount of calories and grams of fat that the foods contain. To determine these amounts, use resources available to you in school or online resources listed below, such as "The Fat and Calorie Counter." This site is unique in that it lets you select what you ate for each meal and then lists the calories and fat contents for you. Online health resources: |
5. | Next, calculate the fat calories in the D column by creating a formula that multiplies the number of fat grams by 9, because there are 9 calories per gram of fat. For example, in the sample log, the formula for cell D13 is =C13*9. |
6. | Finally, calculate the percentage of calories from fat in the E column by dividing the number of fat calories by the number of calories. For example, =D13/B13 would divide the fat calories in D13 by the number of calories in cell B13. |
7. | Copy the formulas down the D and E columns (to fill all the rows that have food listed). To do this, selecting the two cells, and then drag the AutoFill handle (the bottom-right corner of the cell) down as far as necessary. |
8. | Save and then print the spreadsheet. |
For a Challenge:
| • | A summary section can be added to the top of the spreadsheet to keep track of items such as: | • | Total Calories and Total Grams of Fat for the whole day, using a SUM formula. For example, in the sample above, the SUM of all calories in the B column would be calculated as =SUM(B13:B30) if the list of food went down to row 30. Have the students use the resources in textbooks or on the Internet to determine the number of calories they should eat a day in order to stay healthy. They should see how close they get to that number without going over it excessively. | | • | Fat Calories for the entire day (see step 5 above). | | • | % of Calories from Fat (see step 6 above). Try to keep the percentage lower than 30 percent, as recommended by the USDA. |
|
| • | More than one day's worth of food can be tracked, either by using a different sheet (Sheet2 for example), or just scrolling to the right of the first day's log and entering the next day's food further over. |
| • | Younger students can insert pictures of food into the spreadsheet instead of typing the names of the foods. |
| • | Students can also track the food group that each food is from, using school resources such as textbooks and the Internet. |
| • | Students can turn their findings into a PowerPoint presentation (report) about how healthy their diet is on a daily basis. |
