Just one way of using Excel spreadsheets to keep track of money owed by customers and for sending out invoices. For people who have never used spreadsheets before.

Before reading this check out the example first.

spreadsheet%20example.xls

Then work through it with an empty spreadsheet open 

Open up excel

It will open a Book of plain spreadsheets , usefully there are 3 spreadsheets linked together in this book, sometimes more in the later versions of excel.

Sheet 1, 2 and 3 are accessed by clicking on the link down in the bottom left of the screen.

So back to sheet 1,

     The columns may not be wide enough for customers names, so place the cursor at the top of the column where it is lettered A B C  and hover over the vertical dividing line after the letter, the cursor changes to a black cross with arrows, left click and drag the line to make the column wider.

The example given shows the date, the customers name/hours and then the amount owed.

These two top rows on the spreadsheet should be frozen so that they are always visible when scrolling up and down.

 To do this, click on the side where it is numbered 3, the whole row will now be highlighted, now open the drop down box labelled window (next to help, at the top of the screen) and click on freeze panes. Now scroll up and down the page to check.

Note,

While a row line is highlighted as above, it is possible to insert another row above this one by clicking on insert at the top of the screen and then clicking on rows. The rows will still be numbered the same, so you can only notice this if you have written something in the rows.  Like testing etc.

The same can be done for the vertical columns by clicking on the letters instead of the numbers.

Now in column A, row 3, type in the date - but make it the 1^st of the month. Like 01/05/11

Left Click on this date (cell) , the cell will be highlighted, hover over the bottom right hand corner of the cell until the cursor changes into a black cross. Now hold the left click down and run the curser down the page to the very bottom of the screen. A small box will appear with the dates whizzing past.

 When you get to the end of the financial year release the left click, the column will then fill up with the years dates.

You will need a row of dates like this for each customer, but you will not need to go through this procedure each time. This time simply click on the letter A in the first column, it will highlight the dates, now right click on the selected column and then click on copy in the opened box.

Now left click on the letter E which is the third column and the right click on the column, and now click on paste. The years dates will now be in this column,  do this for each customer  - every three columns.

I prefer to have gaps between the months, usually about three rows, so

Scroll down to the end of the month and highlight  1^st of the next month by left clicking on the number to the left of that date. Now open insert and add rows.

I usually do this as I get to each month rather than working right through the year.

Now to get it to add up each customers monthly invoice. Or whatever time span you want.

In the three clear rows that you have created at the bottom of the month, type in Total

in the customers name column, then left click the cell below this, this cell is now highlighted and will be where the total amount is finally displayed.

Now click on the auto sum icon on the top tool bar – it looks like a strange capital  E . The cell that you highlighted will now have =sum etc etc in it, - ignore this.

 Scroll to the top of the month and now hold the left button down on the Amount column on the 1^st of the month row. While holding the left mouse button down, scroll down the row to the end of the month, highlighting all those cells.

 Now on the row above the Letters near the top of the screen there will be a list of all those cells selected. To the left of that if you hoover over the cell a tick and a cross appears, click on the tick unless you have made a mistake then x obviously.

This varies on later and earlier Excel spreadsheets slightly - different spot and slightly different yes/no cell.

The amount for the month should now appear in the highlighted cell. Any other figures now placed in the amount column will automatically be added to the total.

This auto sum calculation will take about six seconds to do when you have had a bit of practice, - just timed myself.

You can use the auto sum to scroll down the whole year for each customer should you so wish, but do not add any other figures to the amount column like paid 500 or owes 300.

The total for all the customers for each month can also be calculated using auto sum.

 Highlight a suitable cell – perhaps on the right side of the spreadsheet after the last customer.

 Now click on auto sum , scroll across the totals. And click on the tick again.

If you need to add up figures that are not in rows or columns or if there is writing in the way preventing you from scrolling across .

Then select auto sum, now hold down the Ctrl button on the keyboard and while it is held down click on the individual cells that you want to add up.

Then click on the tick again.

 All these names and dates and amounts can easily be copied onto the clipboard and then pasted out onto an invoice template which can be made up on sheet 2 or 3 of the spreadsheet book.

 This can then either be printed out or copied and emailed to the customer.

Sometimes it is quicker to set up a few customers then highlight all those cells and copy paste the details next to the original ones, changing the names if copied.

If the cells show dates when you type in numbers or the opposite, the highlight those problem cells, right click on them and choose format cells from the opened box.

Then select dates or numbers or general.