Beach Profiles in Excel
Producing 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.
You should be able to use the resources to draw profiles like the one below:
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.
The Resources:
Download and use. Let me know if there are problems with these...
Beach Profile Spreadsheet (20 rows) (updated to solve a problem 11 November 2020)
Beach Profile Spreadsheet (30 rows) (added 19 January 2024 as adding rows by hand is slightly tricky)
Excel 2019 Profile Drawing Instructions – the most up to date version of Excel. You will need to use the desktop app, not the online version
Excel 2016 Profile Drawing Instructions
Excel 2007 Profile Drawing Instructions – should work in Excel 2007, 2010 and 2011 (Mac version).
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!
If you have any problems downloading the spreadsheet, let me know and I can send it directly to you.
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... If you're going to need to add rows because you have a super-wide beach, you'll need to unhide the columns to be able to drag the formulae down the sheet.
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.