Personal Accounts - Layout 2

Hi People

Been a while, but let's get down to it, you will remember that I left you with the task of playing with the formatting for the Title.  The merged block of cells at the top of the spreadsheet.

Following on from the basic formatting, let's sort out the formulae (plural of formula i.e. calculation).

Initially we want a balance to start the proceedings. So we will put the initial value of 0 in G5 (or you can put your own value in here). And  dont forget to format the cell as currency.

The Spreadsheet so far showing the starting balance of £0.00 (after formatting the cell for currency)

Now the cell E6, will contain a formula which will be unique to that cell, as it will be referring to the G5 cell, where as the remaining column calculations will be based on the cell above (e.g. the calculation in E7 will refer to E6 as the balance).

OK the calculation for cell G5 is:

=if(D6="";"";D6+G5)

The cell below will contain the formula

=if(D7="";"";D7+D6)
See below

This is the spreadsheet with the formulae entered (which would be blank without any values entered)

Looking at the picture above I have put 2 entries into the spreadsheet, showing  the calculated values, which as you can see, incorrectly calculates the cash debit on row 7. 

 The value is added to the balance from D6 instead of reducing the balance.  

Clearly we need to do something about this.  

Think about it and we will deal with this issue next time

That's all for now

Thank You

Prometheus1618

We now have a FREE SPYWARE REPORT available, yours to keep and share if you wish. Fill in your details below and get yours now

eMail address:
First Name:

Personal Accounts - Layout 1

Hi People

Let's do more with the Personal Accounts spreadsheet we started last time.

First though, you may have noticed that when you start a new spreadsheet, the program always starts with 3 sheets.  This is a situation which requires a new spreadsheet to correct but we will deal with that another time.

The extra sheets can be removed in the current spreadsheet by RIGHT clicking on the tab you which to remove (the tabs are at the bottom left hand side of the spreadsheet)

A Tab at the bottom of the spreadsheet, normally there are three

Select 'Delete sheet...' from the menu and 'yes' when prompted.

Sheet gone and job done.

Also you can 'Rename' the sheet from the same menu (as I have done in the above picture).

Continuing with your spreadsheet, currently it has a very basic layout, so let's change the appearance a bit.

Highlight row 5 (left click on the 5 on the left side of the spreadsheet) and click the centre alignment button on the Formatting Tool Bar (This is the bar with the 'Default' , 'Arial' font and '10' font size boxes at the left of the  toolbar - see below)

The left hand part of the 'Formatting' Toolbar, showing the 'Default'  format setting, the 'Arial' font setting and the '10' font size'

The 'Centre text horizontally' button

Now your headings are all centred.

To make a bit more of the top of the spreadsheet, how about a nice big heading in a merged block of cells.  To merge the cells, highlight the block from B2 to F3 ( that is two rows and four colums), now press the 'Merge Cells' button

The 'Merge Cells' button

The range will now be a merged block.  Click inside the merged block and set the font size (see the picture above) to 18.  Now create you own heading or you can stick with 'My Accounts' as I have done. 

You can also set the title as centred. and let's add a bit of colour, by right clicking in the merged block of cells and selecting 'Format Cells....' from the list.  

The 'Background' tab is the one you want and select a colour of your choice. 

Also play around with perhaps making the headings bold and adding a coloured background.

Have a go and we will have a look at it next time.

Well that's it for now.

Thank You for your attention

Regards

Prometheus1618

We now have a FREE SPYWARE REPORT available, yours to keep and share if you wish. Fill in your details below and get yours now

eMail address:
First Name:

Personal Accounts Spreadsheet

Hi People

Following on from the basic Mileage spreadsheet, and with no suggestions as yet as to what you would like to see as a spreadsheet project, I felt a Personal Accounts spreadsheet, might be of use.

I would however, ask you to look over ALL the first tutorial as I will be making some assumptions on what you know, based on information from the earlier tutorial.  So please review the last tutorial fully, before getting into this one.

OK that being said (also I would like to add, that I cannot ANY responsibility for your use, misuse or abuse of this information or any errors which may creep in), let's get going.

Open OpenOffice Calc and  setup the following  in row 5 (these are the headings)

Date (B5) means put the title 'Date' in cell B5

So

Date (B5), Transaction (C5), Type (D5), Amount (E5), Balance(F5)

These will do for now we may add more columns later.

If you remember how to set Formats for the individual columns (check back to the last tutorial for more information), set the column B as a Date format, set columns E and F as currency

For now that's it

See you next time
Prometheus1618



We now have a FREE SPYWARE REPORT available, yours to keep and share if you wish. Fill in your details below and get yours now

eMail address:
First Name: