Nibelung The Edges Of MS Excel (MS Excel 2000)

Member Name: Nibelung

Product:	MS Excel 2000
Date:	26/03/01 (194 review reads)
Rating:

Advantages: Very powerful tool for monitoring data

Disadvantages: Possibly over-equipped in some aspects

I have been using MS Excel both corporately and privately for about 8 years, starting with version 2.

I would describe myself as a heavy user, but perhaps not a “power” user, i.e. the management reports I designed and ran were large rather than pioneering in any statistical way.

It would be easy for me to list some of the features of Excel 2000 that I think are really neat, but it would be equally easy for someone to point out that this or that facility has existed since version 1, or, worse still, that Lotus 1-2-3 does it better!

This has been the whole trouble with MS Office upgrades all along – did we really do any more than scratch the surface of the old version before Uncle Bill decided that we needed a newer one to use up all the disc space and RAM to which we had just upgraded?

“In answer to consumer demand” always seems to be the reason given for an upgrade – which consumers? That’s what I would like to know.

Anyhow, that’s got my “I hate Bill” bits out of the way. I still happen to think that Excel is the de facto market leader in spreadsheets. Whether it deserves the accolade is another matter

Rather than delve into the nitty-gritty detail of whether Excel is bug-ridden or prone to viral attack, which has been opined elsewhere anyway, I thought that I would give you some idea of the uses to which I have put it, now that I am semi*-retired. You never know, it might help you get a tighter control on your finances, or you could REALLY depress yourself by keeping a check on how much your car costs per mile.

(*my wife refuses to let me call it retired, since she can see no light at the end of her particular tunnel).

Excel As a Bean-Counter
~~~~~~~~~~~~~~~~~~~~~~~

Firstly, here is a lightning tour of Excel for complete novices. Open any new spreadsheet, and you are met with a blank stare from what looks like graph pap
er. The rectangles are called cells, starting (usually) with A1 at top left (that’s column A, row 1). Any cell can be filled with one of three things.

1.Text –

However much of a maths “whiz” you are, you are going to need to label something sooner or later.

2.Numerical data –

By numbers, I mean anything from a date to a percentage, currency to ordinary numbers with God-knows how many decimal places (if you want)

3.Formulae -

The “engine” of any spreadsheet – without these, it’s just a piece of on-screen squared paper. Not sure what I mean by a formula? Here’s a quick and dirty example. Let’s say that cell A1 has £3.50 typed in, and next to it, we want to show how much extra you will pay including VAT. We don’t reach for our calculators to work out 17.5 % of £3.50, and then type it in. We type in a formula instead. These always start with “=” to differentiate it from text or numbers. In this case, we would input in cell B1, “=A1* 17.5%”. (The asterisk being Excel’s nearest known symbol to “times”)

Any alterations to A1 would immediately reflect in B1. Now lets put in the total gross amount next to B1, (in C1). Our formula here could be “=Sum(A1:B1)” – I say COULD because you could also put “=A1+B1”.

So now you’ve got three cells all telling you something, A1 – the net price of a widget, B1 – how much the VAT is, and C1 – the gross cost of a widget. Now for the neat part - type a new price into A1 and watch your other two cells recalculate themselves.

Voila, you’re now a spreadsheet user. Tart it up a bit with some labels, and you’ve got an invoice. Obviously, any “power” user of Excel will view this as a travesty of oversimplification, since the power of Excel lies in the vast array of commands
(like SUM) that it can use, including manipulating dates (e.g. how working days between x and y?).

Here are some of the items I now track with a spreadsheet – withhold any judgements as to just HOW sad I am until the end please!

Utility Meter Readings.
~~~~~~~~~~~~~~~~~~~~~~

I use Excel here to identify any shortfall or overpayment in my monthly debits to Gas, Electricity and Water Companies using Excel’s graph facilities – very easy to use, just identify the data to be used, martial it to one contiguous rectangle, and follow the “wizard”. I also used it to carry out several “what-ifs” using the tariffs quoted by the vast array of companies vying for my affections. I now know that moving to Npower from BG and Southern Electricity still costs in, but only just, by 16p a week. When I first made the change, this was before BG dropped their standing charge and threw the whole issue back into the melting pot.

Recording weekly meter readings has also enabled me to prove the gas saved by installing a condensing boiler (much more efficient).

You COULD set up your own home banking sheet, but most people use MS Money of Quicken for this.

Financial
~~~~~~~~~

Several years ago, when the threat of redundancy first reared its ugly head (funny how a THREAT like that becomes a PROMISE of early retirement instead, the longer you hang on in there!) I decided it was time to monitor what I was worth.

I gathered all my insurance policies, latest statements for PEPs, ISA’s etc together and started a month-by-month plot of what they were worth based on the latest data available. I have updated this monthly ever since, and I have found it very reassuring. Having set myself a target of being worth £xyz by the time my wife retires, I adjust how much money needs to go into savings each month to hold to that target. This is proving difficult at the moment, since on
e of my major holdings is BTshares! However, at least I know what I USED to be worth!

Having retired, obviously my income has taken a serious “hit”, so I am monitoring my outgoings as a proportion of my income, taking into account such things as when the mortgage is paid up, projected pay-rises etc. To be honest, I only use Excel to provide the tabular data here. I use my other “toy”, MS Access database to manipulate this data, but only because I want to keep my hand in and develop my skills in this area**. This set of reports has helped me identify just how much cheaper a pay-and-go cell phone has been (for me that is), thereby trimming down another overhead.

** Once you get in deep with Access, you soon find yourself designing modules in Visual Basic too, to make up for its shortcomings. Yet another inadvertent string to the bow!

Excel As a Database
~~~~~~~~~~~~~~~~~~~
Well, sort of. There is limited database function within Excel that allows for one data table, known as a flat file. This is limited to 32 columns wide and something like 16,550 records deep. The 32 columns can be a limitation as I discovered once at work. Having set up a 28-column database to monitor customer survey results, some bright spark revised the questionnaire to need 50 columns without checking on the consequences. Enter one very rapid self-teach exercise in learning Access instead! (By rapid, I mean by the time next month’s figures were wanted!)

What do I use this for now? Why, keeping tracks of my DOOYOO opinions of
course!

The on-screen format of DOOYOO opinions only stays chronological till you revise one, after which it plonks itself at the top of the list.

Having a separate database that can be sorted into any one of several orders is very
useful when it comes to updating it. I suppose this could be extended to show
average reads, and/or income per opinion, which months were yo
ur best period etc. Who needs a Community Centre like CIAO’s. Make your own!

Setting up the database is a “doddle” – so much faster than a “real” one. The pay-back comes when you try to extract statistical data (e.g How many opinions are rated Very Useful?) In a “real database” you’d only have to write a query. In Excel, you have to write a “Criteria” which shows which data you are looking at, in this case the ratings column, filtered down to “Very Useful” followed by a formula that specifies that you want to count how many items meet the criteria. Messy, but once written, it doesn’t need adjusting.

Verdict
~~~~~~~
As a home user, is this really any use? Well, personally, I think it is, but then I’m a “steam-hammer to crack a nut” person. It also stops the brain addling, since it’s rarely straightforward, especially when integrating Excel and Access. I’m tempted to make my MS Access address database integrate with Word, but I’ll leave that for another time! Another positive, is that keeping au fait with MS Office certainly seems to buck up your chances of finding general office work – when was the last time you saw an ad saying “Knowledge of Wordstar an advantage”?

Summary: