How important are Excel forum's?

[My Aswer Is This]

My first experience with Spreadsheets was while I was in the Air force in the late 80's. I moved into a new job in Maintenance control. I was interested in computers and noticed this female airman using Lotus 123. But I also noticed she had a hand held calculator in her hand. I asked what the hand held calculator was for and she said someone had erased all the formulas in the spreadsheet and no one knew how to put them back in so she was doing the calculations by hand and then putting the results into the report. I found myself a new job as the office computer guy. Had to buy a Lotus 123 manual from the base bookstore because no one had a manual. I would read the manuals from cover to cover.</SPAN>

 

[SydneyGeek]

I've worked in corporate finance for many years. I've always been quite concerned about the lack of Excel coverage in accounting degrees / qualifications. Do you think Excel should be covered better in accounting studies (and other numeracy based qualifications)? I notice qualified accountants often quickly climb the corporate ladder yet never achieve a good level of Excel skills. In my experience I notice that the effect is that very often management don't appreciate the importance of Excel and don't have a good expectation in terms of what to expect from their staff. As a training provider the effect can be seen when I approach companies and offer my services as a trainer. I typically hear 1 of 2 things.

1. "We already have all the Excel skills we need internally!"
or
2. "Can you teach my accountants all of the relevant skills in two days?"

Given that a typical accountant will use Excel for 60-80% of their working hours, how can Excel be so terribly under estimated?

There is no concept out there (for most corporates anyway) of what constitutes advanced Excel, and what it takes to get there. I trained others for years using commercially available courseware, and the Advanced course covered VLOOKUP, nested IF, and a couple of others, then pivot tables (very basic), recorded macros, and Goal Seek / Solver. Oh, and some stuff that I have seldom seen used in the wild like Reports, Views, Scenarios.

At least it had the capacity to take people to "Competent", but "Advanced" is a definite misnomer. Most commercial courses just scratch the surface, and because the executives generally don't have the skills they don't know what they don't know.
Makes it a hard sell...
However, if you can show them the difference between pre-baked courses and the stuff you teach, you can make a difference. And I assume that the attendees get heaps out of it, because here in front of them is a person who understands and can stretch them. You can't teach them everything in 2 days, but you can open their eyes.

Which makes me think that more relevant Excel experience, in the degree, would be a huge benefit to financial types and engineers. The question is, could you find people wit the desire and the skills to teach the courses?

Denis

 

[Jon von der Heyden]

You can't teach them everything in 2 days, but you can open their eyes.

That's my mission!

The question is, could you find people with the desire and the skills to teach the courses?

I'd say that our research team are doing a fab job of getting the right delegates. My last group was amazing - each had a real passion! I hope to see some of them contributing here (I go out of my way to promote MrExcel). The problem is - there are far too few with the passion; because they haven't yet scratched the surface.

Where lies the root of the problem? How do we fix it?

 

[BiocideJ]

Where lies the root of the problem? How do we fix it?

I think like SydneyGeek said, people "don't know what they don't know".
The last company I worked for had an advanced Excel skills assessment for all applicants.
Every applicant who got through to taking the test claimed to have advanced Excel skills, but of the 50-60 that took it while I was there, I know of only two who got more than 6 of the 10 questions correct. Most only got 2-3 right.

Either people were lying about their skills or they just had no idea what actually constitutes an advanced Excel user.
As far as how to fix it, I've got nothing. It's difficult to convince someone they need something they don't see the need for in the moment.

 

[Jon von der Heyden]

For me, being an advanced in Excel is more than just about knowing how the functions and tools work. It's about knowing what to use, and when. Through interactions here we learn many design concepts. Concepts that just aren't covered sufficiently in books and courses. I've seen many Excel assessments. I've never seen one that can be used to determine if an individual is capable of building a good model that meets a range of criteria. For me, those being:

Easy to use:
- Logical order of worksheets
- Navigation, is there means to navigate around the workbook without having to scroll endlessly (i.e. use of hyperlinks, grouping, custom views etc)
- Clearly marked inputs (using e.g. Styles)

Flexible:
- Avoid unnecessary use of constants. Does it easily support structural changes? E.g. if a column is inserted, will VLOOKUP column index expand to accommodate the change?
- Dynamic references, do formula and pivot table ranges span arb row counts, or are they referencing dynamic ranges? Appropriate use of Tables (ListObjects)
- Date flexibility is another important one to me. Are dates hard-coded? Are date functions being used appropriately?

Robust:
- Are appropriate measures taken to ensure that worksheet and workbook structures cannot be tampered with (worksheet and workbook protection)
- Appropriate use of validation. I think DV is entirely under-used. If it's an input - then the input rules must be considered and the appropriate validation applied.
- No or few external links as possible! Where possible resort to queries.

Presentable:
- Print ready. Has the user set-up a default workbook that incorporates print settings that are less likely to need to be changed?
- Styled; according to brand. Styles ensure consistency. Not only within one workbook but across a suite of models.

Accurate:
- Balancing checks and flags. Is a consistent convention used? Or do I have to search the entire statement at various positions to see if figures stack? I like to use consistent ranges for balancing checks and flags such that I can aggregate and have a central place to view and help me identify errors, and specifically where they occur?
- Appropriate use of Pivot Tables. If the summary could have been presented in a Pivot Table instead then it should. Users are far more likely to make errors in formula based summaries. I have a lot more trust in Pivot Tables.
- Appropriate use of custom number formats instead of conditional formatting (where possible).

Efficient:
- Structured for best formula selection.
- Binary lookups on all possible occasions, particularly where referencing large data sets.
- Avoid excessive use of volatile functions, and avoid too many dependents on volatile references.

If I were to build a test - these are things I would check. Of course this means that the test will be extensive and take time, as will marking it! But if you can tick these boxes then I think you've got it!

 

[BiocideJ]

Interestingly, most of the items in your list are probably not things that lend themselves well to learning from a forum (or a book) but are something that would probably either come through an instructor-led course or through experience.

 

[Jon von der Heyden]

Interestingly, most of the items in your list are probably not things that lend themselves well to learning from a forum (or a book) but are something that would probably either come through an instructor-led course or through experience.

I think they are taught through many years of hints and tips. It's just that there's nothing structured. Most corporates are reluctant to invest in a training course beyond 2 days - (1) because obviously the cost is greater and (2) because they can ill-afford to lose an accountant for any longer. I cover this material in 2 days; but I certainly cannot cover it thoroughly enough.

 

[mfexcel]

Can't agree more.

You can't teach them everything in 2 days, but you can open their eyes.

Denis

 

[SydneyGeek]

In (parts of) the place where I work, models are built using templates created via a large add-in. Won't mention the name here, but it builds navigation, includes error checking, and forces a structure on the workbook, among other things.
It's reasonably good for financial modelling but not hte knid of stuff that I do. However, the structure is unnecessarily rigid and leads to bloated workbooks. I've seen models with 60 sheets that could have been achieved the result in a much smaller package, and probably been easier to use.

The point for me is that you can't buy good design, you have to learn and then apply it. At least you get 2 days with these guys. Do you get feedback on how they are going after they attend your courses? Do many of them use your course as a springboard?

Denis

 

[mfexcel]

As a matter of fact, there are so many people who thinks they are good in Excel when they know how to construct a basic vlookup. I was one of them.

Either people were lying about their skills or they just had no idea what actually constitutes an advanced Excel user.

Now when I deliver Excel training, I always emphasize the applications to daily work. Trainees show interests when they realize how Excel could help them finish their works much quicker. (e.g. as simple as using wildcard in Find and Replace). People are practical after all. :P