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]

Tony,

What version of Excel?

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

 

[N1GHTEYES]

Sorry Skip - 2003.

I've been warned we will be going to 2010 later this year, but for now I'd better keep this presentation to items relevant to 2003.

Tony

 

[SkipVought]

Formatting changes nothing!

Formatting does NOTHING the the underlying value. Formatting cannot change NUMBERS to TEXT (a string of digits) or TEXT (a string of digits) to NUMBERS.

Formatting changes nothing!

Excel changes data that is entered under certain circumstances such as

1) when Excel thinks that you are entering a DATE, such as 1/2

2) when Excel thinks that you are entering a TIME, such as 1:

3) when Excel thinks that you are entering a NUMBER in SCIENTIFIC NOTATION, such as 178E2.

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

 

[Gavona]

Best tip of all is to frequent Tek-Tips.

Not sure I would include array formulae but maybe that is my lack of expertise in that area.

Pivot tables are great to analyse data.

A few threads to get you thinking:

thread68-1573599, especially the FAQ re List and Table principles.
(Demonstrate the issues with Autofilter if you don't follow the principles - eg have a totally blank row in the table or don't have one above the title row)

thread68-1643747 I particularly like the link in Strongm's post but the thread also has a link to the "when is a number not a number" FAQ and an example of Sumproduct.

thread68-1253790 vlookup and wildcards

faq68-5827 Dates and Times





Gavin

 

[N1GHTEYES]

Thanks Skip. That's a point I should certainly consider including. Do any others spring to mind?

Also, could anyone suggest any good example workbooks out there? I could whip up some worked examples to demonstrate interesting / useful features (and I have done so in the past for specific colleagues) but a fresh perspective would be nice. Besides which it all takes time and, as usual, I've been given the job, but no additional time to do it - so I'm going to have to squeeze it into the gaps in my current schedule (of which there are none).

So any and all suggestions will be very gratefully received.

Tony

 

[MCuthill]

Tony,

Personally... I find Ctrl+1 (Format Cell) to be something that saves me a ton of time when working in Excel.

VLOOKUP being an underappreciated function - depending on the type of Excel work your organization does, it can make life much easier. I have been able to streamline alot of our processes here using VLOOKUP's in place of basic cell referencing (especially for links where the Row numbers can move from month to month, ex: files exported by other programs such as MS Access).

Other than Tek-Tips, I have found the following site to be an excellent one-stop-shop for the "higher-level" Excel issues that have arisen for me. Link: CPearson.com

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[SkipVought]

Here's the rub: There are hundreds, perhaps thousands of features, formulae, tips, tricks. Some features, formulae, tips, tricks are used primarily in engineering. Some features, formulae, tips, tricks are used primarily in finance. Name your industry: you probably use a mere FRACTION of the TOTAL capabilities of this tool.

Your presentation needs to be taylored to the needs and deficiencies of your users in the performance of their jobs.

Personally, I have found precious few everyday users, who will take the time to learn something new that MIGHT aid their efforts. In many cases, the obtuse tip, slick and neat as it might be, will be soon forgotten, if it has no direct relationship to a user's DAILY work, and you are there to reenforce and encourage.

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

 

[Gavona]

I would suggest that the best example would be one from your own workplace. For example I might:
Set out the data layout / table design principles
Open up a download from our SAP system
Demonstrate how autofilter does not correctly identify the title row because of data in the row above the titles and a blank row after.

Sort those issues. Demonstrate how Excel now successfully recognises the table. Add some =Subtotal(9,......) formulae a few rows above the title row. Show how this subtotals the currently visible cells only. Share a function that displays the filter criteria.

Use Ctrl-A or Edit,goto,special,current region to select the whole table and give that range a name

Pivot the data, modifying to use the named range as the source. Group by a date field to show that feature (in my case have to first convert SAP dates to real dates by search for . and replace with /)

Add some data from another table to the data using vlookup. Demonstrate that index in one column and match in the next is quicker if you have large tables and are getting data from several columns.
Extend the named range to include any new data rows/columns.
Refresh the pivot and bring in the new data.

Customise the toolbar to include the Getpivotdata function. One of the most annoying features is the default to generating those formulae when I want to use simple Row/column addressing. The button enables you to change that behaviour.

Sorry, you are well over 20 minutes!

Gavin

 

[N1GHTEYES]

Skip, Gavin, Mike, excellent response - thanks. I knew I could count on the fine folks at TT for some good ideas. Your contributions have all been acknowledged appropriately.

Gavin is of course correct in suggesting that 20mins or so, is a laughably small time to cover the subject, and Skip is right to point out that with most people it will go in one ear and out the other. So I think I'll have to restrict myself to the real gems and make them memorable. That will certainly include Gavin's suggestion of pointing them at TT. I get asked for a lot of Excel advice at work and I always recommend it, but it can't hurt to drill it home!

You've already given me plenty of food for thought to get me started on collecting possible ideas to include, but please feel free to continue adding any other nuggets if you think of some. Even if I don't have time to include them all, I'm sure I'll learn something useful, and probably a lot of other readers will too.

Skip pointed out that knowing what we do would be helpful, so here goes. The industry is Aerospace, R&D, Optics / Electro-Optics systems. I guess most users here will be doing a little bit of data analysis, but mostly it is fairly simple modelling.

It would be mainly in support of top-level feasibility studies rather than detailed system performance analysis - we'd normally leave that to the modelling dept. Some people around here do sometimes get down and dirty with more serious modelling, but then most of them would be more likely to use Matlab / Simulink. Excel is more likely to be used in this dept for quick 'n' dirty checks where it helps to be able to see all the intermediate variables at once.

I hope that answers the question sufficiently.

Thanks again,

Tony

 

[Gavona]

Thanks for the star. I wonder if Tools, Goal Seek should be added to the list.

Could you run a monthly half hour session where you take turns to demonstrate to one another an aspect of Excel?
Create a shared folder for examples - that you all contribute to over time? For example I would include documentation of all Excel functions (found on the web and downloaded), translations between xl2003 and the ribbon(their home software may well be more upto date than the business's version). Workbooks exemplifying vlookups and conditional formatting created for past knowledge sharing sessions or responses to queries.

When I did a similar session I also took a selection of VBA snippets I had developed over the years and saved in personal.xls and put them into a workbook in the shared folder so others could use them. To smarten them up I used BYG Softwares menumaker free download to make them run from the menubar and set Option Private Module so they didn't appear on the macros menu.

http://www.bygsoftware.com/Excel/InterFace/menu_maker.htm

Gavin

 

[N1GHTEYES]

Gavin,

Thanks for the suggestions. I have to confess that, although I often use Solver, I've never actually used Goal Seek. Indeed, although my knowledge is fairly extensive in some areas and goes well beyond stuff I've seen in most books on Excel (partly thanks to TT) in other, perhaps surprising, areas, I'm a bit of an ignoramus. For example, pivot tables and database access are almost a complete mystery to me.

I'd love to do the reciprocal half hour idea, but I doubt I'd be able to raise enough interest. The problem is that people tend to fall into 3 categories: management, do hardly any "real" (i.e. technical) work, so at best they just want to use Excel to provide something pretty for a presentation; those who work almost purely theoretically and are happy to just push equations around with pen & paper (or MathCad), and the ones like me who deal with the real world and need to do actual data analysis and modelling. Unfortunately most of the latter use Matlab.

My feeling is that Excel and Matlab both do different things well and it is best to use both appropriately, but it can be hard to persuade people to expand their comfort zones.

I have done something similar to your shared snippets. A few years ago I created an Add-in which brought together all the macros and user-functions I've developed over the years partly for general Excel tasks, and partly to perform technical functions related to our specific needs - such as "live" least-squares fits, fourier transforms, radiance integrals, point-spread-functions etc... I've already done a presentation on the use of that addin and most members of the dept at least have a copy, but only a handfull use it regularly.

WRT the above, thanks for the tip re BYG. That looks like I should investigate it more fully soon. I have created a user-manual and a worked-example workbook for the addin, and I have included function help tips, but if that S/W can improve the interface I look forward to using it.

Thanks again - and at the risk of repeating myself - please feel free to continue using this thread to suggest any other gems.

Tony

 

[mintjulep]

Never MERGE cells.

 

[N1GHTEYES]

Erm, I can see some reasons why merging can be a bit of a pain (e.g. inability to select whole individual columns, restrictions in moving the layout around etc) but surely sometimes it is handy ins't it? What is the alternative if you require that appearance?

Tony

 

[mintjulep]

Merge also mucks up sort and filter.

If you need the appearance use Format | Center Across Selection.

 

[N1GHTEYES]

Thanks Mint. I knew I heard of a way of doing that without merge, but I could not remember what it was. It works well. Thanks for the idea.

That is another one to shoehorn into the 20 mins!

Tony

 

[Gavona]

Never merge cells

Seconded.
I know this is not the VBA solution but In my shared workbook referred to above I also include a toolbar. One button is to "Centre Across" and drives the following code:

Sub CentreAcross()
With Selection
    .HorizontalAlignment = xlCenterAcrossSelection
    .VerticalAlignment = xlTop
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
End Sub

Another adds a standardised footer (path,sheet, date, time)to each sheet and rather than discards any existing footer offers to put it into the Centre Footer.

Gavin

 

[combo]

All office applications - customise toolbars. Some unused buttons can be removed, frequently used actions added. Additional benefit - one can see what excel is.

My favourite excel buttons:
- format cells (in case of other selections automatically adapts to object),
- paste values, paste formats,
- clear values, clear formatting,
- freeze panes,
- paste special,
- vba (VBE, run/record macro).



combo

 

[Andrzejek]

Use the right terminology in your presentation – Excel has sheets, workbooks, etc Access - and other data bases - has tables. I see many people refer to ‘tables’ in Excel. The same goes for ‘VB in Excel’ – it is VBA (Visual Basic for Application). It helps to get a good help on the net when you name ‘stuff’ right.

And feel free to ‘snick in’ some other tricks not necessarily limited to Excel, like Windows Key use (Win-E starts Windows Explorer, Win-D minimizes all windows and shows you desktop, handy if you have a lot of icons there). And many other helpful shortcuts with Win Key. Almost any time I am on somebody else’s computer and I hit Win-E they go: “How did you do it?”


Have fun.

---- Andy

 

[SkipVought]

Proper structure of tables & lists

faq68-5184

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