EXCEL 2000 TO THE LIMIT (MS Excel 2000)

Member Name: VirtualIce

Product:	MS Excel 2000
Date:	21/06/02 (381 review reads)
Rating:

Advantages: depth of product

Disadvantages: auto database is poor

I work On IT projects, I have been demonstating and training people, and am fast learning some basic rules over the years.

Use the most widely marketed products for whatever problems you need to solve. Microsoft Office 2000 is such a product (and of course Office XP). Within this the most versatile element is the spreadsheet Excel. This one product is capable of many varied tasks. I have had full versions of Lotus Millenium offered to me gratis, but I have left it standing at the side. No other group product comes close to these. Use microsoft because everyone else does! That makes it compatable, you can share skills and information, and microsoft plough so much money into Office, that the products are screaming with features.

That innocent little grid of squares can transform to self running applications, spreadsheets, database, word processor, graphics editor, web sheet design. It can connect to the microsoft wordprocessor WORD and the microsoft database ACCESS.

TRY THIS AT HOME
A.
a main feature of Excel is the ability to convert information in clear stages to complete a task which you want like a business invoice for example.

info note where I say put "= HI" in cell type = space HI in the box, not "= space HI"

"maybe you have a table showing a clients "order of products" from your company...

your first column is headed "products" in cell C10, and each cell under it describes each product type in turn.

Your second column is headed "quantity" in cell D10, and has a number in each cell below it representing a quantity of that product type.

the third column is headed "vat" in cell E10, and under it you write "yes" if that product type is considered vat taxable.

the fourth column is headed "price each exvat" in cell F10, and under it you write your price per product exclusive of vat for eac
h product type in the list.


the fifth column is headed "total exvat" in cell G10, and has formula 1 written in it. formula 1 is a statement written in cell G11 "=D11*F11". now select cell G11 with your mouse and goto edit and copy. then select G12 with your mouse. on your keyboard hold your shift button down while pressing your down arrow key to select all the cells from G12 to the bottom of your column. then release your fingers away leaving the cells still highlighted. then go to edit and paste. The formula you typed in G11 is now automatically brought down for the rest of that column.

the sixth column is headed "total inclvat" in cell H10 and has formula 2 written in it. Formula 2 is a statement written in cell H11 "=IF(E11="yes",G11*1.175,G11)" . now select cell H11 with your mouse and goto edit and copy. then select H12 with your mouse. on your keyboard hold your shift button down while pressing your down arrow key to select all the cells from H12 to the bottom of your column. then release your fingers away leaving the cells still highlighted. then go to edit and paste. The formula you typed in H11 is now automatically brought down for the rest of that column.

in cell I8 write "totals". In cell F8 write the formula "=sum(F11:F30)". The F30 assumes that your lowest product is on row 30, change to suit your table. now select cell F8 with your mouse and goto edit and copy. then select G8 with your mouse. on your keyboard hold your shift button down while pressing your right arrow key once to select 2 cells from G8 to H8. then release your fingers away leaving the cells still highlighted. then go to edit and paste. The formula you typed in F8 is now automatically brought accross for those cells.
are you still with me, ok yeh, great!


Now make it look better. first notice that you have several sheets with tabs at the bottom, then think in terms of fo
rmatting each page in tools:options and by selecting a group of cells (clickdown,drag mouse accross, clickup) and format those format:cells. These are very powerfull options and used skillfully can create dramatic effects. insert:worksheet adds a new sheet and rightclicking the worksheet tab then select rename to change the tab name to whatever you want. cool eh!
eg change the font colour, and the background colour, and create borders.
eg select accross cells in alignment and click mergecells and wrap.
eg format your cells as text so that anyone typing a tel number in that box doesnt lose the first "0" (01233467892 automatically is changed to 1233467892)
eg format your cells to produce £ sign or $ etc
The combinations in this section are limitless, this is just a start.

Tables of information
You can create a database in excel by simply writing a table straight on the sheet, this is followed by some commands which instruct excel that your table exists and that you wish to addto or modify it. That is a process I have used to good effect.
Excel does have an internal automatic database within it allowing you to create an active list or table, but this method is limited in size so don't be tempted unless your table is small.
The developers true option is linking to other software. With a little patience you can master this and have an Access database linked to your spreadsheet.
Word has an automated assistant to help you link excel to it for doing mailing lists which either run from an excel table or an access table. Ie you have a list of names and addresses in excel and word will automatically run your letter with updated name address etc for each name on the list. this could be thousands. can do this for envelopes to.

Gadgets & macros

there is much much more to excel 2000 as you start to enter into macro territory and VB programming. I have used fairly complex techniques to make full grown
applications which work manipulate and update member records and perform analysis on each member at the click of a button. It then at other click commands which I control, will print many different combinations of reports to suit the business concerned, where the reports have active information which changes with each individual client. Each client can be fed into complex analysis which can show analysis of their status with respect to your company, for example show the multiple elements of their complex finances as their finances progress through time. eg loans, mortgages, life insurance, credit cards etc through say 30 years period described in month by month detail.

all this from microsoft Excel 2000, neat eh!

Summary: