Contextures

Excel Food Tracker Workbook

Use this free workbook to analyze the data from your LoseIt food tracker app. How to get your data, and create custom reports.

Introduction

If you use the LoseIt food tracker app on your iPhone or iPad, you can see daily and weekly summaries of your data. To get more control over the reports, you can export that data to Excel. Follow the steps below, to see how to do that, then analyze your LoseIt food tracker data in Excel, instead of being limited to the preset options in LoseIt.

You can set up your own data analysis workbook in Excel, or download the free workbook and paste your own data into it.

Getting Started

To export the weekly stats from LoseIt, log in to the online version, at LoseIt.com.

NOTE: I use the free version of LoseIt, so things might look different in the premium version.

To see your weekly data:

  1. Click the Insights button at the top of the screen.
  2. In the list at the left, click Weekly, in the Summaries section.

 

Choose a Week

At the top of the Weekly Summary , there is a drop down for the "Week of", and by default it shows the Monday of the current week.

NOTE: Weeks in LoseIt start on a Monday, and I don't see any way to change that setting.

  • Click the left and right arrows to move forward or back a week at a time.
  • Or, use the down arrow to open the monthly calendar.

Export the Data

To export the Weekly Summary data for the selected week, click the "Export to Spreadsheet" button at the top right of the page.

Depending on which web browser you use, you should see a dialog box that lets you choose where to save the data file. I change the number in the file name, so it shows the year, month and day.

Open the Data in Excel

After you're finished exporting the data, log out of LoseIt, and open one of the files that you exported. The files are in CSV format, and should automatically open in Excel.

Each file contains a list, with all the food that you logged for the selected week, and the nutrition information for each item.

Create a Master Workbook

Since you can only download one week's data per report, you'll need to create a master file, to collect all the data in a single workbook.

To create a Master file:
  1. Open a new blank workbook in Excel
  2. Select all the data, and the heading row, from the weekly download that you opened
  3. Copy the selected cells, and paste into the blank workbook. I pasted in cell A3, to leave a couple of rows for a heading
Format the Table
  1. Select any cell in the pasted data, and on the Home tab, click Format as Table
  2. Click on a Table Style, then click OK to create the table – leave the check mark in the box for "My table has headers"
Sort and the Master Table
  1. Optional – Delete the Steps records at the bottom of the data, if you don't need those. Or, cut those rows, and paste them onto a separate sheet, for Steps data.
  2. Sort the list by the Date column, in ascending order – the date is downloaded with the newest data at the top
  3. Close the downloaded data file, without saving any changes.
  4. Then, save the Master workbook, with a name and file location that you choose

Add New Fields

This step is optional, but I added fields to the downloaded data. These will make it easier to summarize the data in the pivot tables.

At the right side of the Food Data table, add two new fields, to use in reports.

  • In the first column to the right of the data, put a heading of "Year", and the formula:
    =YEAR([@Date])
  • In the next column, add the heading "Week", and the formula: 
    =WEEKNUM([@Date],2)

The 2nd argument in the WEEKNUM formula is 2, so the week starts on Monday, to match the LoseIt statistics.

Add More Data

If you downloaded more than one week's data, you can add the data from those files too.

  • Open a downloaded data file
  • Copy all the data (not the heading row). Don't include the Steps rows at the bottom of the data, if you don't want to track those.
  • Paste at the end of the existing data in the Master Workbook
  • Sort the list by the Date column, in ascending order.

Repeat those steps every time you download data.

Food Tracker Reports

After all the data is added to the Master Workbook, you can create some pivot tables, to summarize the data.

To create a pivot table:
  1. Select any cell in the Food Data table, and on the Ribbon, click the Insert tab, and click Pivot Table
  2. Leave the default settings, and click OK to create a pivot table on a new worksheet.
  3. Add Year to the Columns area, add Week to the Rows area, and add Calories to the Values area
To create a pivot chart
  1. Select any cell in the pivot table, and on the Ribbon, click the Insert tab
  2. In the Charts group, click the arrow for Line Charts and Area Charts, and select one of the 2-D Line Charts.
  3. Move the chart, and resize it if necessary, so you can see the pivot table and pivot chart.

Make More Reports

You can create more pivot table and pivot charts, to show other information about your food data. For example:

  1. Copy the first pivot table, and paste it onto a new blank sheet
  2. Remove Week from the Rows area, and put Name there instead.
  3. Sort by the grand total column, to see where most of your calories come from

In my master workbook, there are 3 reports:

  • Total calories per week
  • Average nutrients per daily meal
  • Top 5 foods, based on total calories

Download the Food Tracker Workbook

Download the Food Tracker workbook, and other related Excel workbook, for health and fitness tracking.

Food Tracker Workbook
Calorie Counter With Recipe Calculator
Weight Loss Tracker

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

__


Custom Search

30 Excel Functions in 30 Days

 

 

 

 

excel tools

 

 

 

 

 

 

 

Update Your Excel Skills

 

 

30 Excel Functions in 30 Days

 

 

 



Last updated: February 22, 2017 1:55 PM