This Proven System Works For Anyone, Any Age, Even If You Have Absolutely NO Computer Skills

Peasie · Expert

Excel training course

Shamus · Forum Moderator & Expert

Hi Peasie,

you Scots sticking together again (lol).

Don't use trainig providers for Excel as half the time they don't understand it themselves. (had a real fun time at one bank where during a slack period we were sent on an advanced Excel training course and what we considered basics he was trying to put accross as advanced elements of Excel (lookups, pivot tables etc.)... We all ended up walking out of that one and when we reported back it transpired that several others had also walked out on different trainers on similar courses).

For an excellent book recomendation try Excel 2010 data analysis and business Analysis by Microsoft press (it's just a rehash of the 2007 version of the book).

See here for a look inside version :

http://www.amazon.co.uk/Microsoft-Excel-2010-Analysis-Business/dp/0735643369/ref=sr_1_3?ie=UTF8&qid=1321904597&sr=8-3

you can move through it like a workbook or dip in and out of it to pick up the bits that you need.

All the best,

Shaun.

kjmcculloch83 · Expert

We have to some places Shaun (not here of course, such a friendly place).

I agree with Shaun. I find the best way is to get a project, like the one you had today, break it down into bite size steps and use a combination of your old friend google and the paperclip (where did he go in later versions). The best way to learn is by doing.

Kris

Peasie · Expert

I think I'd really need a book to be reading from rather that Google. It's a lot easier reading from a book and working at a PC than going backwards and forwards on the screen. I suppose two monitors may solve that or even a laptop but I'm strapped for cash just now.

I'm afraid I don't consider pivot tables to be the basics. I suppose it is when you consider just how much Excel can do. But there are different levels of basics.

Haler · Veteran Member

Hi to all,

I thought I knew a thing or two about Excel. I even designed a custom built financial project management tool with IF statements and what I thought was fancy advanced formula techniques. However, after looking at the syllabus of the book shaun recommended I know feel like a choir boy in amongst the three tenors!! Yikes I am not even close to calling myself proficient in the use of Excel.

Anyways, as I lick my wounds, I just thought I would mention that I have been using Lynda.com for training in Excel. It seems to be a good starting point for anyone wanting to learn Excel.

Hope it helps.

Sylvia · Veteran Member

I went on two Excel courses at our local agriculture college a few years ago. I was lucky that my boss paid. The first was too basic for me as I had some experience of spreadsheets from University but the second Level 3 was useful.

I have the book Shaun recommends and it is very good but I wouldn't recommend it as a starting point. A lot of the more basic skills are very useful. I suggest you look for something that covers basic formulas, copying formulas and pivot tables. If you have a local library I suggest you get some books from them, you will quickly work out what you don't know and you will not need the basic book for long, then follow with the book Shaun recommends.

Have fun, Sylvia

-- Edited by Sylvia on Tuesday 22nd of November 2011 09:18:04 AM

ICBUK · Guru

Hello,

There is an online course on Excel which ICB Members get a discount: http://excelwithbusiness.com/icb.aspx From an Excel lover's point of view I thought it was very good.

Peasie · Expert

James - I was aware of that course. I looked at it again recently but I went ahead and bought a book instead for £19.95. It was one of those "people who bought.....also bought......." recommendations from Amazon after looking at the link from Shaun. It has a CD with all the examples on it.

ICBUK · Guru

Hi Peasie,

Name and link? I might give it a try. I always thought of myself as an experienced Excel user, then our new finance guy comes along and makes me feel like I'm still in Excel kindergarten

Peasie · Expert

http://www.amazon.co.uk/Excel-2010-Bible-John-Walkenbach/dp/0470474874/ref=pd_sim_b_3

This probably is more for the basics but it does run to 1000 plus pages so there should be a lot of new stuff for me.

kjmcculloch83 · Expert

Let me know how you get on with it Peasie, I might have a look at it if it's any good.

Kris

Shamus · Forum Moderator & Expert

Hi Peasie,

John Walkenbachs regarded along demi god lines amongst serious Excel people so that one's going to be a good call (sorry, not got the book myself so I'm just going on the author).

There used to be an Excel course over on Accountingweb but I can't find the link to it at the moment and from what I remember that one was for Excel 2003 anyway (which most of us prefer to that damned ribbon that gets in your way more than helps... Give me drop downs any day of the week).

On the CD front the book that I recommended also comes with a CD (or at least the 2007 version did. making sweeping assumptions here!).

The book that I used back when I first started was the Que Excel for Windows special edition (1300 pages) but it's well out of date now (even predates Microsoft putting the year on the various versions of Excel) so no point even giving you the ISBN for that one.

semsley · Guru

Hey Shaun, I've got the QUE special edition for Microsoft Office version 4.2 !!!! Oldie but goodie.

Shamus · Forum Moderator & Expert

Great books weren't they Sheila.

Seems wierd that they could produce a book that covered all of the office products that was about the same size as the books for any individual product that still covered all of the same material in the same font size.

Got a few of the Que special editions for Excel, Access, Project,Visual Basic and Office... If ever I came to sell the Access ones I could actually advertise them as new as they've never been opened! When it clicked that Excel can do everything that Access does but better that software pretty much fell by the wayside for me. (for that matter I could say the same about Project but some sites that I've worked at have needed me to use that rather than do my planning in Excel).

I should have known when I took to Excel like a duck to water too many years ago to mention that I would have ended up in this line of work!

hope that your well,

have a good one,

Shaun.

Wella · Expert

Ordered Microsoft Excel 2010: Data Analysis and Business Modeling yesterday. Due for delivery tomorrow. Can't wait (sad ).

I am a big Access fan though, and am just getting to grips with 2010 version.

This is a link to the AWeb Excel Zone

http://www.accountingweb.co.uk/excel

Bill

kjmcculloch83 · Expert

Like you Bill, I was a big Access fan. Like Excel most users only do a tiny amount of what the software is capable of. Access was one of the first easy(ish) to use relational databases, but I now like mySQL and knowing access made this a lot easier. Most of my website is built on a mySQL backend, the site is just one big database.

Kris

Sweetpea · Veteran Member

Hi Peasie,

I did the best Excel course you could ever find.

Inspire Business Training is the company called. Call David Kevill on 07980 972367 or email him at david@inspirebusinesstraining.co.uk
I believe his course is the cheapest I found and the best .

HTH

Laura

Shamus · Forum Moderator & Expert

kjmcculloch83 wrote:
Like you Bill, I was a big Access fan. Like Excel most users only do a tiny amount of what the software is capable of. Access was one of the first easy(ish) to use relational databases, but I now like mySQL and knowing access made this a lot easier. Most of my website is built on a mySQL backend, the site is just one big database.

Kris

Actually...

By definition Excel can be defined as a relational database.

Going back C.J. Date's definition of what a relational database is, it's data thats stored in tables as columns and rows. (as opposed to heirarchic databases which have preset dependancies rather than implied dependancies reintroduced by referential integrity).

Excel will work with SQL but that's not something that I've used it for myself as I'm very much a horses for courses boy and my SQL queries stay firmly on the DB2 side of the fence.

because people use the products interchangabley based on personal preferences, using Access for spreadsheets and Excel for databases there has been talk for years of Microsoft biting the bullit and merging the two products (which if rumours are to be believed should see it's first release in Office 2013.... But as the world ends next year of course we'll never see it!).

Personal view is that it would be a dark day for accountants everywhere as knowing Microsoft they will not make the transition of either group of users particularly easy with the end result ending up as some monstrous hybrid that nobody actually wanted or asked for but will apparently be what we need! (call me a cynic).

It was actually spreadsheets (first version of Lotus 1-2-3) that got me into using tables to store data and from there it was a natural progression to relational databases. Up to that stage banking had been all parent and child segments which is one of those things that I can honestly say that I don't miss at all.

I think that if you think in any of the products, Excel, Lotus 1-2-3, Access, DB2, Oracle, Sybase, or any of a few dozen others. Then picking up one of the other products is a lot easier transition than coming from other database types (heirarchic, KSDS, RRDS, ESDS, etc) or fresh to the subject.

One issue that I do find though is that SQL is different between the products which really doesn't help. Lets start a campaign for SQL to be one single set of commands in one format that is universal amongst all products rather than seemingly unique to each with some comminality.

That was a fun conversation. knew the day job would come in useful on the site at some stage or other!

Talk in a bit,

Shaun.

kjmcculloch83 · Expert

While I accept that Excel can be used for databases, the biggest issue is if you use it on a network rather than locally. Excel doesn't seem to support any type of record locking therefore I suppose your options are either sheet locking meaning only one person can edit the sheet at one time whereas in Access two people can edit 2 different records at the same time.

With SQL I think in a web development environment mySQL is coming out as the VHS right now.

Laura - I had a look at the site you suggested, but as was discussed earlier there are so many opinions on advanced in Excel, and I don't think their version of advanced is the same as mine. Maybe good for beginners though.

Kris

ICBUK · Guru

@Shaun: got to dissagree with you there (can't believe it) "By definition Excel can be defined as a relational database" " it's data thats stored in tables as columns and rows"

Relational Databases are ones that have a structure of multiple items or data sets that are linked via a relationship. A common example would be Customer and Supplier, one structure for each, but multiple instances linked to eachother.

You can set Excel up as a pseudo one by using linked worksheets, but I would not call Excel a relational database.

@Kris: You can use Google Apps to store your Excel worksheets (they convert it into Google version) then you can have a good platform for multiple users accessing and editing the same file. Then export it back to Excel when done.

Shamus · Forum Moderator & Expert

Hi James,

feel free to disagree. I know that I'm not right all the time (still aspiring to just being right for most of it!).

I think that I should have placed more emphasis on the word "can" in that sentence as just realised that it reads as tough I'm stating the Excel is a relational database rather than merely indicating that if you saw fit it could be used as one (which is how I intended it to read).

The thing that I don't think that you can do... Although could very possibly be wrong is set up referential integrity rules (delete cascade, delete restrict, delete set null etc.) without actually programming it in... Then again I don't think that you can do that with Access either without a bit of firking around and even DB2 didn't have it incorporated from the get go.

By definition of course incorporating inter table dependancies reintroduces hierarchic dependancies which necessitates denormalisation of the data.

I think that the real advantage that Access used to have over Excel was that Excel was pretty restricted on size of a single table where access didn't suffer with the same restraints (the size for Excel if now 1,048,576 rows by 16,384 columns where it used to be 65,536 rows by 256 columns so that restriction is now history).

The actual definition of a relational database per Dr Codd (the IBM mathematician who came up with the whole concept) is in concept a very simple one. "A relational database is a database that is perceived by it's users a collection of tables".

Back in the day this was a huge leap forwards over databases where the concept was a series of indexed master records (parents) which child segments holding transactions, addresses, etc. The data was interlinked but was slow and clumbersome to access.

Having data in what was basically a spreadsheet with columns and rows where inter relationships of data could be defined on the fly via SQL in order to access the information quickly was an absolute godsend.

Going back to Excel, You could set worksheets up as a collection of tables which surely makes the workbook a relational database.

Worth mentioning here is that in a relational database environment, not all tables are related even though they may exist within the same database. For example, there may be no direct link between (say) daily exchange rates table and customer addresses but both tables exist within the same database. Both are considered relational as they are tables even though they have no relationship to each other.

I'm sure someone will now come up with a relationship between those two!

nice to have this sort of debate on the site.

Peasie · Expert

I now have a sore head from this thread.

The words are English. They appear to be in the correct order. But they don't make any sense to me.

I'm beginning to think I'm not even a beginner to Excel. I've a long, long way to go.

Hopefully my book will be here on Friday and can start on that.

>>>>>
"By definition of course incorporating inter table dependancies reintroduces hierarchic dependancies which necessitates denormalisation of the data."
>>>>>

That noise you may have heard was my brain exploding - it just couldn't cope.

EDIT : Deleted the smiley at the end - I didn't realise it represented a yawn. I'm not implying that. I just couldn't immediately see an emoticon for an exploding brain.

-- Edited by Peasie on Wednesday 23rd of November 2011 08:19:27 PM

kjmcculloch83 · Expert

and back to the calm forum.... Shaun, is there any topic you don't know about?

Kris

Shamus · Forum Moderator & Expert

kjmcculloch83 wrote:
and back to the calm forum.... Shaun, is there any topic you don't know about?

Kris

Mmmm... Dentistry... Sage! (lol)

Actually there are loads of subjects that I wouldn't dream to attempt to answer. Just people keep picking subjects that I do know a bit about... Still waiting for threads on

Living in Thailand.

Crisp Sandwiches

Songs by Sisters of Mercy

Horses

Nightlife in Houston, Texas

... Actually this could end up a long list.

On the Sage thing, I generally glance at the Sage questions but if they're really about Sage rather than accountancy problems where someone just happens to be using Sage then I leave them to others to reply to.

Sylvia · Veteran Member

I am enjoying this discussion, it takes me back 15+ years to University - I really enjoyed all the database work we did and was sorry not to use it again. I agree with James about Excel not being a relational database, but the idea of normalising the data and then setting up linked worksheets is interesting. Paradox was my preferred desktop database.

Peasie, I can understand why you find this a foreign language, in normal small office life it is just not needed or used!

Sylvia

Wella · Expert

Did some one mention Access?

Shamus · Forum Moderator & Expert

You're not an Access bod are you Bill?

Why when Excel can give you so much more!

Wella · Expert

Hi Shaun

I am a fan but I am also almost addicted to Excel. Yesterday I received the book you recommended earlier, and am already half way through it. I mainly got it for the pivot tables section. I like to use both in conjunction, as raw/ prime data almost always seems to arrive as an excel wookbook.

I am one of those users (excel & access) that started off self taught. I fibbed at an interview that I could use both, when in reality I had only ever opened the programmes and had a play . Turned out I still knew more than my colleagues (I got the job), who must have fibbed too smile . Once in the door I managed to get on every training course possible. Only problem is with courses, you very rarely go back to work and put into practice what you learned, and it soon fades away.

Still consider myself a power user but not an expert, and I still get a buzz when I find a function that does what I want.

Bill

Spamkebab · Expert

Well Bill maybe you can help me out with a question i have? If a job is advertising for an Accounts Assistant who must be an Excel expert, what in your opinion are they looking for (apart from someone over experienced for the pittance they are offering lol)

Ta

Neil

Wella · Expert

Hi Neil

If you can make columns and/ or rows add up I'd go for it biggrin

Bill

Shamus · Forum Moderator & Expert

Hi Neil,

if I were taking someone on the level of Excel skill that I would expect as a minimum from a finance professional would be (draw your own conclusions from the order in which I think of these) :

The ability to turn CSV files into Excel spreadsheets.

The ability to reformat spreadsheets adding and moving columns

The ability to incorporate conditional arguements (if statements) in the spreadsheet.

The ability to create a table and associated lookup facilities

Gneral formatting skills including Date formatting and getting negative figures to be displayed in red in brackets.

The ability to use the autofill function (dragging a calculation down or across).

When using autofill being able to use anchor values (all cells referring to the value in a single cell, possibly in another spreadsheet).

Being able to total on one sheet values from several other sheets.

Basic knowledge of Pivot Tables (much easier to use with later versions of Excel than with Excel 2003 and before)

Being able to split a single value over several columns

Being able to merge the character values of several columns into a single column.

Being able to calculate the difference between two dates in a days format.

Being able to calculate the difference between two times in a minutes or seconds format.

Being able to show todays date and use the date in calculations

Being able to set auto update on and off

Being able to display the spreadsheet without zero values or gridlines

That I would say is the basic start point for considering yourself to be using Excel professionally. Anything above and beyond the above scores brownie points.

Right, back to deferred tax again! (I'd rather be Exceling (Ooh, double meaning on a word!)).

All the best,

Shaun.

Peasie · Expert

Wella wrote:
Still consider myself a power user but not an expert, and I still get a buzz when I find a function that does what I want.

I remember how I felt when I found the SUMIF function. I thought who needs sliced bread when you have the SUMIF function.

Started reading this book. It may be over 1000 pages but when you factor in a diagram on most pages there isn't really all that much.

Having said that I'm beginning to think I'm not even a beginner. Only at Page 15 and I found out how to justify cells using the keyboard. I had for years wanted something simple to do that, like "Ctrl R" in Word but now I find out I can do it by "Alt H AR". I'm still amazed that "Alt 0189" gives me ½.

Spamkebab · Expert

Thanks for that list Shaun I'd say that i am proficient with about 90% of it. Mind you i'd have to take Bills job offer as it seems so much more relaxed lol. Anyway that list has given me quite some confidence in my ability, i should have asked a lot sooner. I reckon it wouldn't take too long to complete the rest, Excel seems to be a program that explains itself better when you stumble upon things or so i find.

Cheers you two

Neil

BTW the kaplan text for Spreadsheets covers quite a lot on your list Shaun.

-- Edited by Spamkebab on Saturday 26th of November 2011 04:15:00 PM

Peasie · Expert

Shamus wrote:
The ability to turn CSV files into Excel spreadsheets.

General formatting skills including Date formatting and getting negative figures to be displayed in red in brackets.

I am able to do most of the things in that list. Some I haven't needed yet so would need to learn.

The gridlines thing I cheat with and have a button on my Quick Access Toolbar to turn them on and off.

I think I must use an unusual format for dates as I like it to be "26 Nov 2011" or occasionally "Sat, 26 Nov 2011" - although if I'm saving a file using a date format it will be "2011-11-26_Filename.xlsx".

The red brackets thing puzzled me as I couldn't find how to do it in Excel but had to do it in my Regional settings.

The CSV files into spreadsheets has me stumped though. All my CSV files open in Excel. I'd have to right click on them and "Open With" some other program for them not to open in Excel - what am I missing? The only thing I have to do with them is change to date and number formats.

Peasie · Expert

Spamkebab wrote:
Excel seems to be a program that explains itself better when you stumble upon things or so i find.

Because I have large hands with big fat fingers I've been known to hit the wrong key by mistake. I've found a lot of useful things this way.

And you know what they say about men with big hands - they need big gloves in winter.

Shamus · Forum Moderator & Expert

Hi Neil,

you would however be surprised at how many people consider items on that list to be advanced level!

Sounds as though your on top of it espechially as you've identified the "Stumble on" effect... The number of afternoons I've lost by stumbling on something then spending half a day trying to understand (a) how to use it and (b) what on earth would I ever in a thousand years need to use it for...

If you demonstrated such ability to me I would have no qualms about taking you on as the lists an excellent foundation to build on.

No employer expects employee's to know absolutely everything about a product (even though when we apply for roles we expect that they do). All that they really expect is someone pertaining to be an expert to know more than they do (and also know where to look for the things that you may need to know but don't yet).

Anyhoo, next stage after the above is learning how to program Macro's to automate common tasks but even some advanced Excel users never really venture too far down that path.

Shamus · Forum Moderator & Expert

Hi Peasie,

for the red brackets (i.e. accountancy format negative values). Click on the column, columns or indeed entire spreadsheet (Cntl +A) that you want to reformat then :

Right click

Format Cells

Custom

In the entry box type this : 0.00;[red](0.00)

That basically says what the format should be if positive ; what the format sould be if negative.

You might actually prefer #,##0.00;[red](#,##0.00) as that would also suppress leading zeros.

you can put in any colour you want but red is traditional and really emphasises the figures

Peasie · Expert

Shamus wrote:
Hi Peasie,

for the red brackets (i.e. accountancy format negative values). Click on the column, columns or indeed entire spreadsheet (Cntl +A) that you want to reformat then :

Right click

Format Cells

Custom

In the entry box type this : 0.00;[red](0.00)

That basically says what the format should be if positive ; what the format sould be if negative.

You might actually prefer #,##0.00;[red](#,##0.00) as that would also suppress leading zeros.

you can put in any colour you want but red is traditional and really emphasises the figures

I do have the format now for the various types.....number, currency, accounting - but it meant changing things in Regional Settings.

Spamkebab · Expert

The red brackets was one i "stumbled upon" using 2007 at the time. for the life of me i can't understand why it's not included in the list? Basically i just played with the formats that were available until i got the hang of them.

no2pe · Newbie

This Proven System Works For Anyone, Any Age, Even If You Have Absolutely NO Computer Skills

Obtain a higher paying job or get a promotion with your newly acquired Excel abilities...
Pass that college exam with flying colors (your professor doesn't need to know how you did it!)
Hone your skills on one of the most in-demand business applications in use today - Microsoft Excel!

For the first time ever, you can obtain BOTH VOLUMES of the Fast Track to Excel home study course, for one low price. This is an incredible offer, available only for a limited time.

Visit Following Link To Avail This Offer For Limited Time:
tinyurl.com/7pzbfpt

Spamkebab · Expert

Can anyone smell spam? or do i need another bath?

kjmcculloch83 · Expert

It is quite subtle though

Spamkebab · Expert

LOL!

Shamus · Forum Moderator & Expert

The other posts been now been deleted for advertising infringement.

I thought of deleting this one too but can't think of a much better way of putting off spammers than unleashing Neil, Kris and Peasie when he reads this on them...

Cry havoc and let slip the dogs of Spam!

Spamkebab · Expert

I hope Peasie is ok, i haven't seen many posts from him for a few days. Hope he isn't lay spreadeagled and trapped beneath a thousand Cornflakes boxes and a few milk cartons of nuts and bolts.

Don Tax · Expert

Yeh hope he's ok and I wanted to say I'm using excess charity clothing bags as kitchen bin liners.

Shamus · Forum Moderator & Expert

Don Tax wrote:
I'm using excess charity clothing bags as kitchen bin liners.

Me too, saving a fortune on buying black bags from Tesco's biggrin

Don Tax · Expert

Yeh, I don't know how many clothes they think I've got.

Now the Mrs, that's a different story, but a skip might be more appropriate (ducks)

Shamus · Forum Moderator & Expert

Excellent Suggestion Tim.

Any clothing recyclers out there reading this skips are a really great idea and I definitely won't be filling it full of the tree that I lopped down last week biggrin

Spamkebab · Expert

Instead of lopping down the tree you should have just sat underneath it and read one of your study texts aloud.

We are now on our third dog since i started studying.