Top three XL Efficiencies

[Dennis]

I believe it would be interesting to many using this newsgroup
if all could share their top-3 (or 10) methods/techniques that
they believe saves time in 1) entering data/formulas,
2) evaluating/comparing data, 3) VBA, & 4) Importing & Exporting
Data from/to other applications, 5) Importing data from Main Frames,
and 6) Other.

For example, Selecting a data range and entering values
sequentially w/o having to look at the screen knowing that
one will not have to be concerned about entering data past
the anticipated data range

Secondly, Double-clicking the fill handle in a column (selection
includes the formula to be copied). XL then automatically copies
all cells below with information in cells immediately to the left.
Thus one does not have to find the last cell in a i.e. 50,000
record file.

Third, the simple but extremely useful GetFormuta Macro. Thanks
to a very helpful MVP whose name I cannot find quickly.

Fourth, "Access" is very touchy about imported data - even from XL.
I.e. If data has mixed information like 123456 12/31/2003 2000MCF
in one cell, Access will send an error message like "cannot find
file or data not in the correct format." The reason being, that Access
does not know how to format that column with mixed data. (As a
number, date, or text) whereas XL is much more forgiving.
(The data mentioned above is from a Memo Field from an
Accounting S/W download. I showed it just as an example of a
real-life situation that needs to be parsed into three separate columns)


Now your turn! Come on, take some time to raise all of our skill
levels.


Thanks to all from all,

Dennis

 

[Dave Peterson]

In no particular order:

For filling empty cells (sometimes mainframe reports are formatted too pretty),
I use the technique that Debra Dalgleish describes at:

http://www.contextures.com/xlDataEntry02.html

For data comparison, =vlookup() or =index(match())
(toss in a "data|filter|advanced filter|unique records only" every so often.)

For doing repetitive importing/exporting, tweaking a recorded macro and
rerunning it on demand.

And Data|Filter|autofilter and pivottables make life easier.

(with these two, I don't use data|subtotal or sorting as often)

 

[Norman Harker]

Hi Dennis!

Good idea!

1. Using Google Search 6.0 from: Ron de Bruin
http://www.rondebruin.nl/Google.htm Certainly covers all common
questions and more.
2. Using double click on the bottom right hand corner of a cell to
copy down parallel to a range (I think it was Gord Dibben who
introduced me to this one)
3. Using John Walkenbach's Megaformula technique to build formulas.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Dennis]

Dave,

Thanks for taking the time. I'll add these to my list and share them
with you and the other MVP's so that you all can consider adding them to
your web sites.

For myself, I am currently taking structured courses in XL. Before that
I am self-taught with 5+ years of inefficient use of XL.

I can not tell how much more efficient I have become as a result of this
news group. I am frustrated that I still feel so "new" comparing my
skills. So I am looking for something to Quantum-Leap myself. It will
be even better if others could also benefit.

I am going to post a new question about everyday examples of the use
of Match(); Offset() and Index(). XL Help and reference books give very
technical references as to what the variables are but little to none on
typical uses.

Dennis

 

[Dennis]

Thanks for you knowledge & time!! Dennis

 

[David McRitchie]

Before you post your new questions on OFFSET, INDEX, and MATCH
take a look at a spreadsheet with examples by Peter Noneley that you
can download.

A reference to definitely download and maintain on your computer
Excel Function Dictionary -- http://homepage.ntlworld.com/noneley « by Peter Noneley,
Alternative download site http://homepage.ntlworld.com/peter.noneley
workbook with 157+ sheets, each with an explanation and example of an Excel function.

Some help with use of OFFSET in
http://www.mvps.org/dmcritchie/excel/insrtrowl.htm#offset

Some help with use of INDEX and MATCH, and of course, VLOOKUP
http://www.mvps.org/dmcritchie/excel/vlookup.htm
and also follow links to other sites for more material

Additional references for what Dave Peterson posted:

GETFORMULA User Defined Function was mine see page
http://www.mvps.org/dmcritchie/excel/formula.htm
also look at GETFORMULAD and GETFORMAT

Use of Fill Handle and filling down
http://www.mvps.org/dmcritchie/excel/fillhand.htm

But you were looking for the 3 neat things and those mentioned
would also include my list. So perhaps some things to help
you know what you have or work with what you have instead,
and I can't stop at three.

List of modules, plus some infomation for each
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
which also contains
SortAllSheets
List of Subroutines and Functions in all open workbooks
Deleting Hyperlinks
GoToSub -- includes UDF as well, for edit a macro

List of what you have in your own Toolbars
http://www.mvps.org/dmcritchie/excel/barhopper.htm

One of my other favorite pages on my site is
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm
which should be helpful for some techniques in coding VBA

 

[David McRitchie]

Link to possibly your other thread, I'm not going to try to
track through anonymous posters.
Re: Match() - Offset() - Index() Help
http://google.com/[email protected]

You can add TrimALL function to list of things that really help
especially if you have pasted from HTML. It trims spaces
after convertin non-breaking spaces (&nbsp to regular spaces,
and reenters the result. Put the formatting in place first if you
don't want the default of General.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Jon Peltier]

Hey Dennis -

Here are three. There are probably many more.

1. Pivot Tables, Text-to-Columns, AutoFilter

2. Some of the personal macros I've built over the years, including
these I've made available on my web site:
- http://peltiertech.com/Excel/Charts/ChgSrsFmla.html
- http://peltiertech.com/Excel/Zips/SwitchXYAxes.zip
- http://peltiertech.com/Excel/XL_PPT.html (particularly my technique
of selecting several charts on a worksheet and dumping them all into
PowerPoint)

3. Customizable command bars that put all this and more a mouse click away.

- Jon

 

[Dennis]

Excellent Dave!

No wonder the MVP's are MVP's

Dennis

Before you post your new questions on OFFSET, INDEX, and MATCH
take a look at a spreadsheet with examples by Peter Noneley that you
can download.

A reference to definitely download and maintain on your computer
Excel Function Dictionary -- http://homepage.ntlworld.com/noneley « by Peter Noneley,
Alternative download site http://homepage.ntlworld.com/peter.noneley
workbook with 157+ sheets, each with an explanation and example of an Excel function.

Some help with use of OFFSET in
http://www.mvps.org/dmcritchie/excel/insrtrowl.htm#offset

Some help with use of INDEX and MATCH, and of course, VLOOKUP
http://www.mvps.org/dmcritchie/excel/vlookup.htm
and also follow links to other sites for more material

Additional references for what Dave Peterson posted:

GETFORMULA User Defined Function was mine see page
http://www.mvps.org/dmcritchie/excel/formula.htm
also look at GETFORMULAD and GETFORMAT

Use of Fill Handle and filling down
http://www.mvps.org/dmcritchie/excel/fillhand.htm

But you were looking for the 3 neat things and those mentioned
would also include my list. So perhaps some things to help
you know what you have or work with what you have instead,
and I can't stop at three.

List of modules, plus some infomation for each
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
which also contains
SortAllSheets
List of Subroutines and Functions in all open workbooks
Deleting Hyperlinks
GoToSub -- includes UDF as well, for edit a macro

List of what you have in your own Toolbars
http://www.mvps.org/dmcritchie/excel/barhopper.htm

One of my other favorite pages on my site is
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm
which should be helpful for some techniques in coding VBA

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Dave,

Thanks for taking the time. I'll add these to my list and share them
with you and the other MVP's so that you all can consider adding them to
your web sites.

For myself, I am currently taking structured courses in XL. Before that
I am self-taught with 5+ years of inefficient use of XL.

I can not tell how much more efficient I have become as a result of this
news group. I am frustrated that I still feel so "new" comparing my
skills. So I am looking for something to Quantum-Leap myself. It will
be even better if others could also benefit.

I am going to post a new question about everyday examples of the use
of Match(); Offset() and Index(). XL Help and reference books give very
technical references as to what the variables are but little to none on
typical uses.

Dennis

 

[Dennis]

Jon,

I am in a PP course through January 20th.

Tomorrow, I will take XL PPT.html to the Moderator.

The other macros i will download and review soon.

Dennis