Excel expert opinions please 3

[N1GHTEYES]

I need some suggestions for ideas on "tips & best practice" for Excel.

At the end of a recent presentation to our department, my boss gave an additional ~20 min lecture on tips and best practice when using Powerpoint to give good presentations. He then dropped me right in it by suggesting that sometime soon I should do something similar with respect to Excel. The fact that I'm seen as the local Excel guru tells you a lot about the state of everybody else's knowledge in our dept!

Most people here tend to use it for fairly technical data analysis purposes, though not necessarily with a great deal of technical skill. So I think it would be best to avoid dealing with issues like making a spreadsheet look "pretty". Instead I reckon more useful issues would be:

keyboard shortcuts - e.g. F4 to toggle between relative & absolute refs

use of array formulae

named ranges - including dynamic ones using offset

data layout - e.g. Skip's constantly repeated exhortation not to mix datatypes in a single column

At this stage I have not decided what to include or how "deep" to go, but I'd really appreciate any suggestions for topics to include and any "hot tips" that would be worth including.

Thanks in advance,

Tony

 

[SkipVought]

Just TRANSPOSE your search items.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BNPMike]

Tony

What you say is fair.

Well, apart from a slight issue of terminology.

I was in at 06:00.

09:30 isn't morning.

It's Brunch.

 

[N1GHTEYES]

BNPMike:
That's what I like about TekTips - you learn so much. For instance, I never realised there were two "six o'clock"s in the day.

Skip:
You would have heard the sound of the penny dropping, but, into spaghetti, it just makes a quiet kind of squelch. Of course, it would help if we had the same adverts on this side of the pond.

Tony

 

[GlennUK]

Skip:
You would have heard the sound of the penny dropping, but, into spaghetti, it just makes a quiet kind of squelch. Of course, it would help if we had the same adverts on this side of the pond.

Tony

ah ha, I'd have never have realised that ... thanks N1GHTEYES ( my google searching was coming back with lyrics for a Prince song )

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[BNPMike]

I'll just float another idea.

In the world of Web applications, if you're not MVC, your friends won't talk to you.

If we translate the same principle to spreadsheets, shouldn't we separate presentation, data, business rules, and business logic - either by saying any given sheet should only have one of those elements, or even that we should try and break things up into separate workbooks?

 

[SkipVought]

Wednesday is Prince Spaghettie Day
BTW, Boston, home of Anthony, (actually it was in Cambridge, right next door) is where I gained a working knowledge of FORTRAN, by acquiring a copy of "A FORTRAN Coloring Book"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mscallisto]

Actually Skip some now refer to Cambridge as "The People's Republic of Cambridge" where the moonbats and beautiful people live.

 

[SkipVought]

It mirrors Berkeley. ;-)

NEVER rubbed off on me!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gammachaser]

It mirrors Berkeley.

Is that near Berserkely? In California?

 

[Lilliabeth]

This thread reminds me of something... several years ago, someone walked in the door, walked past me to approach the male programmer and told him that he needed to be able to convert minutes-and-seconds to seconds. My co-worker sat down and wrote what seemed to me like a mountain of code. When they were done, I remarked "Or you could multiply it by 1440."

--Lilliabeth

 

[Andrzejek]

Lilliabeth, often the best man for the job is a woman.

Have fun.

---- Andy

 

[kjv1611]

N1GHTEYES,

A lot of different things in this thread. How are you coming on your list for your presentation?

Perhaps when you gather it all together, you could share what you've come up with as a whole. Might be helpful to the passer by who doesn't want to read pages of "Use VBA - NO don't use it YES use it - NO, you're just a neard." and so on.

Might not be a bad idea to work this sort of list (maybe that was SKIP's general idea with the other post he started) into a community driven FAQ. Of course, can't really do it as a wiki, as I don't think that is even possible with the current tek-tips setup/layout.

 

[N1GHTEYES]

kjv1611:

You are right, there are a lot of ideas coming out of this thread. Clearly more than I can possibly cover in a brief-ish presentation. Actually, it turns out there was a little bit of confusion. The story goes like this:

Ages ago, I wrote an add-in to collate a lot of useful Macros and UDFs I'd written over the years - some departmental-specific and some which just provide functionality missing in "vanilla" Excel. About 2-3 months ago I did a lunchtime presentation on what the add-in has to offer.

Then, the other day, after the monthly departmental coms session, my dept head gave a presentation on how to use Powerpoint to best effect for presentations. At the end, another boss (who thinks that "Excel" used to be the 12th letter of the alphabet, or possibly his clothes size) suggested I should do an Excel presentation. After further discussion, it turns out that he'd got his wires crossed and he was actually suggesting that I should re-present the one I'd already given. He does not understand what an add-in is, or how that presentation differs from a general one on Excel.

Having cleared that up, it seemed like I no longer was expected to do the general Excel presentation, but the thread seemed so productive, I just let it go on - after all, I was not the only one benfiting from it. But then, the dept head waded in and suggested I should give a general Excel talk after all!!

However, he realises that to do it justice will require some prep and it is not urgent - unlike the rest of my current workload - so I've been given a couple of months in which to come up with something. Therefore I will be doing one, just not yet.

When I do, I will try to also compile a summary and put it back onto TT.

Tony

 

[GlennUK]

BTW, Boston, home of Anthony, (actually it was in Cambridge, right next door) is where I gained a working knowledge of FORTRAN, by acquiring a copy of "A FORTRAN Coloring Book"

That looks like a very interesting book ... I was given a McCracken's Fortran book by my boss, and told "learn that" ... and thus was my new career launched. I think I'd have enjoyed the Coloring Book a whole lot more.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Haysere]

Always follow the same steps when setting up an excel file, use the same style, for instance always have a summary sheet as tab 1, data sheet tab 2, graphs tab 3 etc. If you can create a common consensus it makes it really easy to use other peoples work. This is especially useful when the users are of differing skill levels. I always try to make a summary sheet do most of the work for you, so that the less able user can access the file and instantly get what they need. But thats me...

 

[lionelhill]

OK, while we're on really silly ones to help users:

Always assume your user is unfamiliar with Excel, and warn them when you send a workbook containing multiple worksheets. The number of times I've been mailed "Thanks for your file, but unfortunately you only sent a summary and not the full data I needed", and I've had to mail back "If you want the full data, please look at the bottom of the screen and find the tab marked 'FullData' and click on it...."

 

[Wicca]

Well, I have just used a VBA program in Excel (of course) and reduced a monthly data analysis task from 3 hours to less than 10 minutes . . . the majority of that time is actually inputting the data into the spreadsheet . . . a single click of the macro and its done and dusted, sorted, formated, emailed in three different formats, saved, copied to external backup drive and closed. I think there is a place for VBA . . . and yeah . . . TechTips gave me the necessary information and help to make it happen

With respect
Wicca

Believe in yourself, you are worth the investment.

 

[kjv1611]

and warn them when you send a workbook containing multiple worksheets.

I can definitely vouch for that one, though I don't usually remember it myself. Then again, most of the time for me over the past couple/few years, I've had the luxury of sending to the same people. And most of those people know or have learned enough to at least know to look for different worksheets within a workbook (even if they just call them tabs, and call the workbook a worksheet or spreadsheet).

Of course, the worksheets are switched/activated via tabs. Sometimes, I've even caught myself referring to them as "The Summary Tab".

I suppose mainly I just know that with some audiences, it's best to just adapt to their terminology rather than get blank stares or questions.