That Blue Square Thing

Beach Profiles in Excel

Updated with instructions for Excel 2016

Dunwich boardwalkProducing beach profiles in a spreadsheet is tricky. But, with a little formula magic, it can be done.

What I'm providing here is a spreadsheet to do the calculations for you (it will also do the cliff height calculation for you as a bonus). And there's a set of instructions for how to draw the profile.

Why haven't I done the spreadsheet so that it draws the profile automatically? I think you should learn how to do that bit yourself. There's lots of useful Functional ICT skills tied up in graph drawing and it's really not the hard bit.

The spreadsheet sometimes won't download properly, so there's a zipped version to try to avoid the problems some users have experienced.

You should be able to use the resources to draw profiles like the one below:

Beach profile diagram

I think you could take a diagram like this and add some useful annotations in textboxes (where's the storm beach?) and some photos to show the beach itself as well. That sounds like advanced ICT data presentation to me...

The same sheet has been used by a school in Oman to draw a 1.6km cross section across the Sharqiya Sands at an Outward Bound base built in the desert. So I guess it could be used in other ways if needed as well.

Dune profile diagram

Use this scatter graph!The Resources:

Download and use. Let me know if there are problems with these...

MS Excel iconBeach Profile Spreadsheet (updated to solve a problem 6 November 2018)

pdf iconExcel 2016 Profile Drawing Instructions

pdf iconExcel 2007 Profile Drawing Instructions - should work in Excel 2007, 2010 and 2011 (Mac version).

zip file iconZipped version - this might overcome the persistent download issues that people have been having. The folder will need unzipping on a Windows system (right click > extract all). The spreadsheet and the instructions are included in one download, as well as a version of the spreadsheet with 40 rows for adding data. School networks can sometimes dislike zip files - you may need to talk to your system admin (and give them chocolates, cake or beer!).

Make sure you choose the right scatter graph! My choice would be the Scatter with Straight lines and Markers.

And do make sure you include the zeros in the graph so that the profile goes all the way down to the sea!

You might also be interested in the Kite Diagram Spreadsheet if you need to do things with vegetation cover transects.

Technical Notes:

The spreadsheet is an Excel 2011 version. It should work in previous versions of Excel without a problem - but please let me know if it doesn't!

There are hidden columns on all the sheets. These hide the working out. You can unhide them if you want to and look at how they work - you can also accidentally delete them of course...

There are locked cells on the second and third sheets. This should stop people accidentally deleting stuff they don't need to delete. There's no password protection in place so full open-source ability to tweak as much as you want to.

The maths involved isn't that complex - it's simply using Sine, Cosine and/or Tangent rules from basic trigonometry. The complex bit is that Excel requires that angles are in Radians rather than Degrees before it will apply Sine rules and so on. I didn't realise that - which is why it took me about an hour to get this sorted!

The screenshots were done on a Mac I'm afraid so might not quite tally up. But you should be able to cope with that.