COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
Excel MasterClass: creating a worksheet and chart to track your weight
It's fairly easy to create a simple Excel worksheet to chart your weight, but this master class shows you lots of tips and tricks to make the final result quicker to create and easier to use.

Posted by Andy Brown on 14 October 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Excel MasterClass 1: Creating a Diet Worksheet

So you're worried about your weight, and want to track it in Excel?  This master class (the first in what I hope will become a series) shows how to create a chart which will automatically update when you type in new measurement.

This master class assumes that you already know how to use Excel - it's designed to show quick and innovative ways to do things, not to teach the basics.

Step 1 - Create the raw data

Create a new worksheet and type in some basic data:

Raw data

It's a good idea to weigh yourself once a week (here it's every Monday, although that will only become clear when we change the date format).

 

Select the date cells, and format them:

Formatting the date cells

Select these cells, and press CTRL + 1 to bring up the Format Cells dialog box.

 

Type in a custom number formatting code:

Number formatting code

The more of each letter you type in, the more information the date will display. Try it!

 

Double-click on the column, which now isn't wide enough:

Widening the column

Double-click where shown to widen column A.

 

To give yourself some sample data, select the cells and use the mouse pointer shown to click and drag down:

Drag cells down

Position the mouse pointer exactly as shown, and click and drag down to extend the range.

 

The nice thing about this is that the date formatting is copied, and you get one weight per week:

One weight per week

Excel adds a 7-day interval for each new date, extrapolating the interval between the first two dates.

 

Step 2 - creating a table

The easiest way to make your chart extend automatically when you add a new row to your data is to turn the data into a table.  First select any single cell:

Selecting a single cell

Which cell? That's up to you, but make sure it is just one cell. When you create a table (as shown below) Excel will then push up, down, left and right until it hits either blank rows or columns or the edge of the worksheet.

 

Press Ctrl + T to create a table:

Creating a table

Excel will show this dialog box. Your block of data will be selected automatically - all that you need to do is to select OK.

 

Excel applies formatting to the table:

Formatted table

When you add rows to the table, the internal range name will be automatically extended downwards to accommodate them.

 

Step 3 - creating a chart

There are obviously many ways to create a chart, but here's one of the easiest.  First select the data:

Selecting the data

Click on any single cell, then press Ctrl + A to select the block.

 

Now press Alt + F1 to create a chart on the same worksheet:

Creating a chart

Excel will chart the data shown selected.

 

Excel will create your default chart type, which you can then change:

Changing chart type

Right-click on the background of the chart and choose the option shown to change its type.

You could if you like spend some time formatting your chart:

Formatted chart

I haven't included how to format the chart, but it's easy enough to work most things out I hope.

Step 4 - adding a trend line

You may have put on a pound (or kilogram) or two this month, but this might be just seasonal variation (it's Christmas, and you ALWAYS eat a whole tin of Quality Street at Christmas).  To iron out this variation, add a trend line:

Adding a trend line

Right-click on your line chart, and choose to add a trend line to it.

 

Set this trend line to be a 52 week moving average (although you won't be able to do this until you've accumulated at least 53 weeks of data):

Moving average trend line

Choose to create a moving average, and set the period to 52 weeks.

 

You should now have a chart which automatically expands when you add another row to your table of weights!

Final chart

This (non-imaginary) trainer has a clear seasonal pattern: he stuffs himself at Christmas and takes family holidays involving exercise in the summer.  The red moving average line smooths out these seasonal variations, and shows an unmistakable - and very welcome - slight downward trend.

This blog has 0 threads Add post