Magazine Archive

Home -> Magazines -> Issues -> Articles in this issue -> View

Using Spreadsheets as Song Planners

Do you use a computer to help you make music? Then why not go the whole hog and use it to keep track of your songs? Vincent Taylor shows that there's more to computer spreadsheets than merely adding up figures.

It was a source of great amusement to the resident engineer and my fellow musicians when I first turned up at the recording studio with a 2ft by 4ft sheet of stout cardboard with a one-inch grid drawn on, three large sheets of acetate, and a set of overhead projector pens. Amusement turned into annoyance as the masking tape came out and numbers and lines of every hue began to appear on the acetate sheets, while everyone else was unpacking the gear and setting up the drum kit.

I had decided - somewhat unilaterally - that we were no longer going to find ourselves scrabbling around in the middle of the session looking for "the bit where the bass does that run" or "that drum break with the paradiddle - I think it's the one just before the guitar solo, or was it after the third verse?" What I had done was to set up a simple song planner and a song plan for each of the songs we were about to record. They showed which instruments would be recorded onto each track and at what stage in the song each would begin and stop playing.

The song plans helped us to record better in three ways. They incorporated the usual engineer's track/instrument list so that we knew where everything was and could eventually scrap the guide tracks and record over them. We could ensure that we had left nothing out (how many times before had we got to what we thought was the end, only to realise we still had the tambourine part or the third vocal harmony to do?). They also allowed us to use the limited number of tape tracks to full effect by dropping in our embellishments on the blank sections of already recorded tracks. This last little trick does have its drawbacks, however. Dropping in cymbal crashes onto an existing backing vocal track can mean you have to be very quick on the draw readjusting the equalisation or levels when mixing.

But the song plans really came into their own during mixdown. We drew vertical arrows, in a different colour to the track/instrument lines on each song plan, to indicate fader level changes for each track at the point at which they occurred in the song. Thus, not only could we fine-tune the mix during practice runs and keep a complete record of it (in much the way automated desks do nowadays), but also we could stand each plan in front of us during the final mixdown and accurately place any level changes.


Structured song plans won't guarantee you a hit single, but they do save time, money and heartache when recording. Surprisingly, they are probably needed now more than ever. With the growth of home recording - though some homes are looking more like professional studios, while studios try to look more like homes - and the increasing popularity of multitrack sequencing via MIDI, there is a lot more to keep track of.

Technology has widened the number of options and made for much greater control over the song development process, but this has forced an unprecedented requirement on the musician to become organised. In a field of endeavour where disorganisation has been, in the past, a respected code of practice, this is a difficult pill for some musicians to swallow.

Consider how songs used to be produced in the bad old days. The songwriter sat on the end of the bed with an acoustic guitar and wrote the basic song. It would then be arranged and rehearsed with the band in a rehearsal room or studio. The band then saved up and booked into a recording studio, coming out with a stereo master tape.

Today, it appears, songs are written on MIDI keyboards, often over a rough, looped drum pattern, arranged on computer using a multitrack sequencer, then mixed and recorded onto a small multitrack tape machine using MIDI-to-tape synchronisation. Sometimes, only the main vocal line is left un-sampled and un-synthesized. And all of this takes place in the relative comfort of your own spare room!

There are fewer people involved and much less moving about. This creates an illusion that less organisation is needed. In fact, I'd argue, you need more. Writing and editing on a computer-based sequencer means you are composing, arranging and determining the performance parameters all at once. Advances in technology have compressed what took many hours and several brains (not to mention digits and limbs) to produce, into something which one person can undertake alone and, incidentally, is far less unsettling for the neighbours.

We mustn't forget, though, that the creative and organisational input to writing, arranging and performing is now being provided by a single brain instead of several. Those of you familiar with micros will be aware of the benefits of extra memory and, after all, isn't the brain where memory originated?

Figure 1. Example of a traditional, hand-drawn, simple Song Plan.


Like good systems analysts we start by trying to break down the song producing system into its component parts or tasks. However, there is a problem. As I have already mentioned, composing, arranging and performing take place concurrently when using a MIDI sequencer. Multitrack tape recording, mixing and stereo tape recording are usually the second and third stages. As the process does not break down easily into several distinct tasks which take place at different times any more, we have to concentrate upon where the music data resides at each stage. So what we are doing is defining our information requirements. Let's split the data into MIDI and audio first.

The MIDI data is held in the multitrack sequencer (though some people prefer to use the sequencer built into their synth for the very early stages of composition). We enter the data via a keypad, MIDI keyboard or other instrument, computer keyboard, or (occasionally) as a system dump from tape or disk. As the song develops we need to keep track of various items: which 'instrument' (and by that I mean musical oart) is recorded on which sequencer track and on which MIDI channel; which MIDI output device (synth, sampler, drum machine, etc) is going to play it; which voice it will use (ie. the name and location on disk, cartridge or internal memory), and any other relevant program information. We also need to know where each 'instrument' or part starts, stops or restarts. This introduces the dimension of time in musical bars or measures, minutes and seconds, and sometimes the units known as MIDI 'ticks'.

Inspection of sequencer tracks will tell us exactly, to the beat or tick, where a part begins or ends, but most sequencers do not provide a good overview of an entire track and certainly not of a complete song. Standard computer screens are just too narrow, and the resolution too low. Within the overview we need, the start or end bar of a part is sufficient to help us edit or later record. Also, wouldn't it be useful to know how far into the song we were in minutes and seconds at each stage, in order to prevent the intended three minute single becoming the 12" extended mix by default?

The MIDI data is converted into audio signals by adding the sampled or synthesized sounds during playback. At this stage we might want to make a note of the effect settings, mixer channels, and the equalisation and volume levels on the way to the tape recorder. We will certainly want to note what we are putting on each track. As audio tracks tend to cost more per unit than MIDI sequencer tracks, you will probably have more of the latter at your disposal and will want to plan the order of recording and bouncing down onto the multitrack tape machine. Only by planning your bounces can you minimise the number of generations of tape recording, and so keep the tape noise as low as possible.

Finally, any effects settings, equalisation, and stereo panning should be noted from the final mixdown to 2-track tape in case a re-mix is needed.

Figure 2. Example 'MIDI Section' from a computer spreadsheet Song Plan.


The MIDI data section of your song plan should be a grid showing time, in song sections, bars, minutes and seconds, or ticks (or all four), increasing to the right and tracks, channels and instruments listed down the left-hand side (see Figure 2).

The audio section splits into three parts: mixer in, multitrack tape, and mixdown. The mixer section should cover your own mixer's control options and become a record of the basic settings for each instrument or part as it is recorded (Figure 3). Similarly, the multitrack tape section should indicate the options on the machine you are using. Figure 4 shows a useful, adaptable table I have devised for planning the recording of several instrument parts onto a 4-track tape, using bouncing down techniques and including a MIDI-to-tape synchronisation track.

Figure 3. Example 'Mixer Section' from a computer spreadsheet Song Plan.

Remember: if you only have a couple of output devices (rack or keyboard synths, sampler, etc), try to ensure that they are each playing a part every time you record a track, in order to keep down the number of generations of audio signal.

Lastly, the mixdown section would look similar to Figure 3, noting all of the relevant settings used on the way out to the stereo mastering machine. In order to time fader level changes I found it useful to go back to the MIDI section (Figure 2), enter the fader settings as numbers or a simple instruction (eg. fade in, fade out) in the relevant bar, and to utilise the printout during mixdown.

Figure 4. Example 'Multitrack Tape Section' from a computer spreadsheet Song Plan.


Pen and paper is the most obvious way of doing all this, though a pencil is better for editing. In fact, it is the huge amount of editing of parts, positions and settings which prompted me to begin thinking of other ways of making a record of a song's parameters in the first place.

Large acetate sheets overlaid on a grid of tracks or channels and song section or bar markings mean that you do not have to re-draw the grid every time you edit the song structure. This format can be made large and clear, and water soluble pens allow you to edit freely.

Another option is the use of a computer spreadsheet. Essentially, a spreadsheet program gives you a grid of 'cells' on screen into which you can place words or numbers. Columns are named alphabetically (A, B, C, etc) and the rows are numbered (1, 2, 3, etc). Each cell is uniquely identified by its column letter and row number. So, cell C4 is in the third column along and fourth row down.

The great strength of the spreadsheet, which is based upon accountants' ledger paper, is that it will perform calculations upon the contents of selected cells automatically. For instance, it can add up a column of figures and place the total in another cell. If one of the numbers in the column is altered later, it will automatically recalculate the total, saving a huge amount of time on figure work.

A spreadsheet can perform quite complex mathematical operations, such as calculating loan repayments, raising the contents of a cell to a power, stripping off all the numbers before the decimal point to give the fractional part of a number only, etc.

Almost all micros now have relatively inexpensive spreadsheet packages available for them, and some even come bundled 'free' with the computer. As an alternative, a worthwhile purchase is an 'integrated' package. This will usually include a word processor, spreadsheet, and database facility all in one. Moving from one application to another is made very quick and simple, and the packages can even cost much less than a dedicated word processor, spreadsheet or database, because the latter tend to include a very sophisticated range of functions, many of which you may never use. I developed my song plans on Migent's Ability Plus (an inexpensive integrated package) and tested them also on Computer Associates' SuperCalc/4 (a very powerful, but expensive package) both running on an IBM PC compatible, and found no problems. Neither package was by any means stretched by the application and both, usefully, have a sideways printing facility.

This is one feature which is very useful, if not essential, in turning your spreadsheet into a song planner. On screen you will be able to view areas of the song plan well off to the right, towards the end of the song. However, unless you have a printer that can handle A3 size paper, or a plotter, it is unlikely that the whole song will fit across a standard A4 page. By turning an image of the song plan through 90 degrees and printing out the plan down the page instead of across, the program lets you have hard copy of the complete song. If you are using continuous paper, this means that it can handle a song of any length.

It is relatively easy to set up all of the song plan data sections (MIDI, mixer in, multitrack tape, and mixdown) on a computer spreadsheet. You enter the text or numbers in the appropriate column, as shown in Figures 2 to 4. Spreadsheets allow you to insert, delete, copy or move rows or columns of data at will, so editing the whole layout is possible at any stage of developing the song plan. I created a column for each bar (see Figure 2), and set the width of each column to four characters. Most spreadsheets usually allow you to set column widths for a range of columns at once, rather than each one individually, which saves time. The track, channel, instrument part, voice/pattern, and output device column widths can be tailored to suit the names or abbreviations for your own set-up.

Once you have designed your song plan, but before you enter the information relating to a particular song, save the spreadsheet file and use it as a master 'template' each time you want to create a song file, by making a copy of it, naming the copy after your song, and entering the data relating to your song into its own new song plan file. That way you avoid having to duplicate entering the layout of the song planner for each song.

Entering and altering the track, channel or instrument listings is simple. You just enter the numbers or names in the appropriate cells. I have used arrows to indicate the start and finish of a part in terms of the bar when it comes in, drops out, or comes in again (Figure 2). These are just made up of a '<' character and three hyphens (ie. <--- ) to signify a start bar, a series of four hyphens (----) to signify that the part continues for the bar shown, and a reversed arrow symbol ( -> ) to show where a part drops out or ends. Having created these symbols, I copied them into the columns marked 'Bar: 1,2, 3, etc' to form the long arrows on each row, which show where each part starts and stops in the song.

Moving an entire part from, say, track 3 to track 8 is also easy. You mark off the data in the row corresponding to track 3 as a 'range' of cells (eg. A11 to Y11) and tell the program to 'move' the entire marked range to its new location (eg. A16, or row 16 of the spreadsheet). In fact, this process is very similar to moving marked sections of a sequencer part between tracks, only much faster.

Any alterations you may make to effects, equalisation, volume or pan settings, or to which part goes on which tape track, can be noted on your paper printout as you record and edited on the spreadsheet afterwards to maintain a neat, accurate update of the song as it develops. It is this clear printout of your song that is invaluable, as it gives you that overview which you can't obtain otherwise. Ideally a multi-tasking micro (such as the Amiga), two micros, or a program such a Microsoft Windows, which allows you to flip between sequencer and spreadsheet, would give you the best control for immediate updating of the song plan as you develop it, but we'll leave that one for the studios.

Is this all just a lot of fuss? Obviously, a spreadsheet song planner is going to take time to set up and time to edit to keep the song plan up to date with your song as it exists on your sequencer, multitrack tape, or stereo tape. I have included many bits of information about each song, some of which you might want to keep a record of. Your song plan should only be as complex as it is useful. Any of the four sections described can be left out, or others added.

But what about all this time spent away from actually composing, performing, arranging or recording? It is very tempting to spend all of your time on the more creative or interesting parts of producing a piece of music, but how many wasted hours have we all spent trying to create that perfect sample or synthesizer voice to no avail, or come back to a sequencer song file or half-finished multitrack tape having hardly a clue as to what state you left it in or where anything is?

Using a song planner can give you more effective time for actually making music. You will have a handle on the complexities of a piece, which you may find refreshing in a world where the degree of control you can exercise over your music is tens of times greater than it was just five years ago.


Spreadsheet song planners are not the ultimate solution by any means. It is highly likely that MIDI sequencer programs will begin to incorporate much more comprehensive song planning tools than at present, or that complementary packages will be developed akin to voice librarians and editors. There exist specialist software programs called 'project planners', designed originally to cope with managing major construction projects, which produce horizontal bar (or Gantt) charts showing start and end dates for all the tasks which make up a project: design; demolition; foundations; main structure; roof; fittings; decoration, etc. The structure of these programs is very similar to what a good song plan should be. Perhaps some enterprising software house might be interested in copying their design to produce a dedicated song planner and song library program.

In the meantime, try dusting off that spreadsheet program you received when you first bought your micro. Turn it into a song planner, and see the wood as well as the trees for once.


A computer spreadsheet provides you with a matrix or grid of 'cells' on screen into which you can place words, numbers, and/or mathematical formulae. These grid columns are named alphabetically (A, B, C, etc) and the rows are numbered (1, 2, 3, etc). Thus each cell is uniquely identified by its column letter and row number. For example, cell C4 is in the third column along and fourth row down.

The great strength of the spreadsheet, which is based upon accountants' ledger paper, is that it will perform calculations upon the contents of selected cells automatically. For instance, by specifying the appropriate formula, a spreadsheet can be made to add up a column of figures and place the total in another cell. If one of the figures in the column is altered later, it will automatically recalculate the total, saving a huge amount of time on figure work.

A spreadsheet can perform quite complex mathematical operations, such as calculating loan repayments, raising the contents of a cell to a power, stripping off all the numbers before the decimal point to give the fractional part of a number only, etc. They are most often used by companies to forecast financial budgets.

Almost all micros now have relatively inexpensive spreadsheet packages available for them, and some even come bundled 'free' with the computer. The most famous spreadsheet of them all is probably Lotus 123, for IBM PC compatibles, though there are equally good (if not better) spreadsheets available for the Atari ST (eg. VIP Professional) and Macintosh (eg. Excel).


G8 Bar No. 1
G43 Tempo 120 (beats per minute)
G44 Time Signature 3,4, etc (beats per bar)
G45 Beats +G44
G46 Time Elapsed +(G44/G43)
G47 Minutes +INT(G46)
G48 Seconds +INT((G46-G47)*60)
G49 Ticks +G45*120

G51 Counter Reading (1.0139+0.5195*(G46*60+$D$52)

D51 Initial Reading Value of initial tape reading
D52 Equals seconds elapsed 1.86844666*D51+0.00122561*D51^2

H8 Bar No. +G8+1
H43 Tempo +G43
H44 Time Signature +G44
H45 Beats +G45+H44
H46 Time Elapsed +G46+(H44/H43)
H47 to H52 Copy across form column G

Enter your own values for G8, the first bar (if different from 1); G43, the tempo in beats per minute; G44, the number of whole beats per bar; D51, the initial counter reading that locates the song on the tape, having zeroed the counter at the start of the tape.

Enter the formula in the appropriate cells as shown. Note that '*' means multiply, and ^ means raise to the power. Be careful to place the brackets and the $ signs exactly as shown. The very long strings of zeros have been shortened in the above formula by using scientific notation, where the number is expressed as a number between 1 and 10 multiplied by 10 to a power (eg. 0.00012991 = 1.2991 x 10^-4).

Column G, rows 47 to 52, can be copied in a block to the same rows in column H. Then the whole of column H (rows 43 to 51) can be copied to columns I, J, K, etc, onwards for as many bars as you wish.


If you need to know how far into a song you are at any point, in terms of musical beats, MIDI 'ticks', minutes and seconds, or tape counter readings, you can utilise the power of the spreadsheet to perform many repetitive calculations to great effect. The formulae you need are given in the separate Table, and should be inserted in the appropriate spreadsheet cells to give values such as those shown in Figure 2. When you have entered them for columns G and H as shown, you can copy them across to all of the columns from H to the end of the song, in one go. The spreadsheet will then automatically adjust the column names in the formulae.

The number of beats is the cumulative sum of the time signatures for each bar, as shown. This allows for any time signature changes part way through the song. The total number of minutes to the end of the current bar is the cumulative sum of the time signature divided by the tempo. To separate this out into minutes and seconds, set row 47 to equal the integer part of row 46 (using a built-in spreadsheet function, usually called INT, or similar). Row 47 will now equal the number of complete minutes. Row 48 is then set to equal the difference between row 46 and 47, multiplied by 60 to give seconds; using the INT (integer) function rounds them down to avoid the odd 2 minutes 60 seconds cropping up. Make sure you type those brackets in the right place!

The number of MIDI 'ticks' - ie. the unit of resolution of your sequencer - is normally a set fraction of a beat. So the TICKS row is just the number of beats multiplied by that fraction. The formula shown uses the 120 ticks per beat (ie. pulses per quarter note) of Personal Composer System/2, though several sequencers have finer resolution. Replace the 120 by the value for your sequencer.

You can also use the spreadsheet to make your tape counter count actual time elapsed. Tape counters that measure the actual length of tape which passes the head, and therefore the amount of time elapsed, are generally expensive. Most small multitrack recorders are only fitted with a standard tape counter, which is linked to the number of revolutions of the leading spindle, rather than the length of tape passing the head. This means that they are a very poor guide to the number of seconds elapsed during playback.

Usually, the counter starts off quickly at the beginning of a tape and gradually slows down as more and more tape builds up on the spool, requiring fewer revolutions to take up the tape passing the head at constant speed. The graph shows this effect for a 45-minute cassette, with the actual readings producing a curve compared with a straight line estimate.

The trick is to find a formula which estimates the curve of the graph. For this, I used a statistical curve fitting technique and found that the counter reading (zeroed at the start of the tape) on a Tascam Porta One using a 45-minute cassette tape was approximated by the following formula:

Reading = 1.0139 + 0.5195 x SEC - 0.00012991 x SEC2 + 0.000000033444 x SEC3 - 0.000000000003927 x SEC4

where SEC equals the number of seconds elapsed since the start of the tape. This formula predicted my counter reading to within one unit on the display at any point on the tape - not bad when you consider that, using the mechanical tape counter as an estimator, the number of counter readings divided by the time taken for the whole tape to run would mean that your estimate could be out by up to 90 counter units in the middle section of the tape! You may notice that the formula's predictions are slightly out at the start of the tape, beginning with a value of just over one, rather than at zero, but this really doesn't matter when accurate timings for the middle of a song or middle of a tape are what we seek.

To convert this formula for use on another cassette-based 4- or 8-track machine, you firstly need to know the ratio of tape counter units to revolutions of the leading spindle on your machine. Attach a small piece of blu-tack or a thin strip of masking tape to the leading spindle (ie. the one that engages the cassette spool which takes on tape as the cassette plays). Set your tape counter to zero. Press the Play button on the recorder and count a given number of revolutions (say, 50). Stop the tape as close as you can to the exact number of revolutions and note the reading on the tape counter. Divide the counter reading (eg. 40) by the number of revolutions (eg. 50) and you will obtain your required ratio (0.8 in this example).

The formula given above is for a machine with an approximate ratio of 0.75 counter readings to each revolution of the leading spindle. So, you would have to adjust the entire formula up by 0.8/0.75 (=1.067), in this example, to allow for your own machine's counter. Putting brackets around the entire right-hand side of the formula gives us the new estimate of the counter reading:

Reading = 1.067 x (1.0139 + 0.5195 x SEC - 0.00012991 x SEC2 + 0.000000033444 x SEC3 - 0.000000000003927 x SEC4)

Secondly, you need to adjust for the tape speed (in inches per second) your machine uses. The above formula is for a tape running at 1⅞ips, so if your tape runs at twice the speed (ie. 3¾ips), simply multiply the entire right-hand side of the formula by 2.

So, having adjusted the formula for your own tape machine's counter and tape speed, you need to enter the formula into the spreadsheet. You will have to allow for songs which begin part way through the tape, so in the accompanying Table Of Formulae (see panel) I have designated cell D51 to be the place where you can enter the value of the initial counter reading. You will need to run the tape back to the start, zero the counter, fast forward to the place where the song starts and note the reading to find this. It assumes that you will then zero the counter again to start the song. You have to tell the formula whereabouts on the tape it is starting from in order that it can estimate the counter reading accurately.

Below that I have entered a formula into cell D52 to calculate the number of seconds into the song, which relates to the initial counter reading; ie. how many seconds of tape have been wound onto the leading spool. By using curve fitting techniques again to find the reverse of the above formula, we get:

Seconds into Tape = 1.86844666 x Initial Reading + 0.00122561 x (Initial Reading)2

Similarly to the above, you will have to adjust the formula for your own machine's ratio of counter readings to spindle revolutions or tape speed. This time, however, you divide the entire right-hand side by the value of your ratio over 0.75 (ie. the 1.143 given in the example), and also divide it by your tape speed over 1⅞ips.

The formula in cell G51, which estimates the counter reading at any point in the song, now allows for the point in the tape where your song starts. You simply let SEC in the first formula equal the sum of the entry in G46 (multiplied by 60 to give seconds) and the value contained in cell D52 to give the total time elapsed since the very start of the tape up to that point in the song, and deduct the contents of cell D51 (the counter reading at the start of the song) to give the counter reading zeroed at the beginning of your song.

For a 30-minute cassette, which uses thicker tape and so the counter slows down more quickly as the tape is wound onto the leading spindle, the new formula is as follows:

Reading = 0.3775 + 0.5197 x SEC - 0.00020888 x SEC2 + 0.0000000846447 x SEC3 - 0.000000000015395 x SEC4

and the reverse formula for cell D52 is given by:

Seconds into Tape = 1.86794139 x Initial Reading + 0.00198860 x (Initial Reading)2

Simply use the new values and adjust for your machine's own ratio and tape speed as described above.


Graph showing inaccuracy of tape counter readings for a 45-minute cassette tape running at 1⅞ips.

0000 0000 0000
0060 0032 0020
0120 0062 0040
0180 0091 0060
0240 0119 0080
0300 0146 0100
0540 0248 0180
0600 0273 0200
1020 0427 0339
1200 0486 0399
1740 0652 0579
1800 0670 0599
2100 0753 0699
2340 0816 0779
2400 0831 0799
2460 0846 0819
2520 0862 0838
2580 0877 0858
2640 0892 0878
2700 0906 0898
2760 0921 0918
2783 0926 0926

Previous Article in this issue

Roland W30 Workstation

Next article in this issue

DACS MIDI Patchbay Competition

Sound On Sound - Copyright: SOS Publications Ltd.
The contents of this magazine are re-published here with the kind permission of SOS Publications Ltd.


Sound On Sound - Jun 1989

Donated by: Rob Hodder



Feature by Vincent Taylor

Previous article in this issue:

> Roland W30 Workstation

Next article in this issue:

> DACS MIDI Patchbay Competiti...

Help Support The Things You Love

mu:zines is the result of thousands of hours of effort, and will require many thousands more going forward to reach our goals of getting all this content online.

If you value this resource, you can support this project - it really helps!

Donations for November 2020
Issues donated this month: 2

New issues that have been donated or scanned for us this month.

Funds donated this month: £43.00

All donations and support are gratefully appreciated - thank you.

Please Contribute to mu:zines by supplying magazines, scanning or donating funds. Thanks!

Monetary donations go towards site running costs, and the occasional coffee for me if there's anything left over!

Small Print

Terms of usePrivacy