Learning Excel - Web Site, Book, and Add-in Recommendations

[adamsjm]

While my main expertise is in Process Improvement, I have become an intermediate MS Excel Application developer in order to implement many of the solutions. Having read this forum for the past couple of months and the questions about MS Excel posted on it, I thought I would pass on some MS Excel web sites to use and learn from, a list of books to read, and tools which can be added into Excel to speed up your application / project development.

MS Excel is an extremely versatile and powerful tool. You can even develop .xll (similar to .xls files) functions in C+ /C++ and integrate them directly into Excel. This process is used on Wall Street to value and assess Derivative Trading. While we will probably never obtain that level of expertise, we can greatly improve our effectiveness and efficiency along the way.

Websites

The most important web site to bookmark is a listing of Excel’s Most Valuable Programmers. These are independent Excel developers who do not work for MS, but make their living developing Excel applications. They have proven themselves in their knowledge and their willingness to share it.

► http://www.mvps.org/links.html#Excel

From that list the best “General Interest How-to Sites” (especially for new learners) are:
► http://www.cpearson.com/
► http://www.contextures.com/tiptech.html
► http://www.j-walk.com/ss/
► http://www.mvps.org/dmcritchie/excel/excel.htm
► http://www.exceluser.com/index.htm
► http://www.dailydoseofexcel.com/

The “Higher End How-to Sites” are:
► http://www.appspro.com/ - Rob Bovey
► http://www.oaltd.co.uk/ - Stephen Bullen
Rob and Steven are, in my opinion, the best Excel developers / experts in the world.
► http://www.datapigtechnologies.com
► http://www.jkp-ads.com/
► http://www.tushar-mehta.com/

The outstanding “Graphing and Charting How-to Sites” are:
► http://www.peltiertech.com/
Jon is a metallurgist by training, with a Doctor of Science degree from MIT. He has over 20 years experience in research and development, and more recently in manufacturing and production support, in the aerospace, automotive, and industrial parts industries. His background includes Statistics, Total Quality, and Six Sigma. He has developed many quality charts and has posted the how-tos on his web site.
► http://www.andypope.info/

Tip for searching the Web: How do you get a good response to your web search? When you are seeking a “how-do-I” start your search with "MVP", enter your key words that you are searching, and then finish with “*excel*”. By doing this your responses will have an answer from an Excel MVP which will be correct.


Books

The following books are targeted for Excel 2000-2003. The same authors have written companion books for Excel 2007. The books are listed in difficulty order – low to high.
[See the attached Word document or .jpg files for the book images.]

Add-In tools

These Add-in tools can greatly improve your productivity. And the best part is they are FREE.
Rob Bovey once told me, “If you find a tool that you need and they are charging for it, keep searching. There will be a better one free of charge.”

PopTools ► http://www.cse.csiro.au/poptools/
Greg Hood wrote the statistic tool add-in PopTools. MS Excel’s statistical functions are not noted for their robustness and accuracy. If you require the best in statistical tools, add PopTools to your Excel. It has been written as an Add-in so that you can see his methodology and as a compiled for fast execution (a ,xll).

[From the web site]
PopTools is a versatile add-in for PC versions of Microsoft Excel that facilitates analysis of matrix population models and simulation of stochastic processes. Included in PopTools are routines for iterating spreadsheets. These make it possible to run Monte Carlo simulations, conduct randomisation tests (including the Mantel test) and calculate bootstrap statistics. The routines include array formulas for matrix decompositions (Cholesky, QR, singular values, LU), eigenanalysis (eigenvalues and real eigenvectors of square matrices) and formulas for generation of random variables (eg, Normal, binomial, gamma, exponential, Poisson, logNormal). Many of these functions depend on Jean DeBord's TPMath library - a Pascal numerical library - which has been compiled into a DLL accessed via Excel.


Add-in tools from Rob Bovey ► http://www.appspro.com/

XY Chart Labeler
A very commonly requested Excel feature is the ability to add labels to XY chart data points. The XY Chart Labeler adds this feature to Excel. [Used extensively by Jon Peltier]

Excel Utilities
There are several great development utilities available for the VBE, but the Excel Utilities is the only comprehensive add-in aimed at Excel user interface development. The Excel Utilities provides a set of 30 features that you'll use on a daily basis.

VBA Code Cleaner
During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage.
Note: Rob does not write his code to function in Worksheet Modules or in the ThisWorkbook Module. If the code breaks, it becomes unrecoverable. Code Cleaner can “fix” Standard, Form and Class Modules.

Code Documentor
The Excel Code Documentor automatically documents the vital statistics of your VBA project.


Add-in tools from Jan Karel Pieterse ► http://www.jkp-ads.com/

The Name Manager
An excellent utility to manage defined names in your workbooks.
(Incorporated into MS Excel 2007)

Flexfind
Eases searching and replacing throughout an entire workbook. Also enables you to search and replace strings in objects such as headers and footers, chart titles, buttons and many, many more.


Add-in tools from Decision Models Ltd, Charles Williams & Jan Karel Pieterse
► http://www.decisionmodels.com/fastexcel.htm

FastExcel (Cost $79)
"Most slow-running spreadsheets contain a small number of problem areas, or Bottlenecks. Because Excel is such a flexible spreadsheet system there are usually many different formulas that can produce the answer you want. Some of these formulas are much faster than others.
In large spreadsheets it can be difficult to locate and prioritise the Bottlenecks.You can use the wide variety of timing and profiling tools in FastExcel to rapidly drill down, locate and prioritise these bottlenecks, and then use FastExcel's built-in Help for advice on eliminating or optimising them."


Add-in tools from Andy Pope ► http://www.andypope.info/

Button Editor
An add-in for creating button images.

Graphics Exporter
Add-in for exporting items in your workbook to graphics files



Add-in tools from Bill Manville ► http://www.oaltd.co.uk/MVP/Default.htm
FindLink
To look for links from a workbook


Add-in tools from MZ-Tools ► http://www.mztools.com/v3/mztools3.htm
MZToolsVBA
MZ-Tools 3.0 is a freeware 'all-in-one' add-in for Visual Basic 6.0, Visual Basic 5.0 and the Visual Basic For Applications editor (provided by a VBA-enabled application such as those in Office 2000 and higher) which adds several productivity features to


Program from TechSmith ► http://www.techsmith.com/
SnagIt Screen Capture (Cost $39.95)
Using SnagIt, you can select and capture anything on your screen and share it with others to visually clarify difficult concepts, create clearer documentation, and give your presentations a professional, dynamic look.

This is by no means an all-inclusive list of sources or tools, but I hope it will be a start.

Joe Adams
Process Excellence Architect
** Company name removed **
(near) Indianapolis, Indiana

 

[mguilbert]

Thanks for the list. I have used most of these in past and they have been quite helpful. I would add www.mrexcel.com as another useful excel site that also deals with access and other programs. Also www.utteraccess.com is useful for access training.

Thanks again for compiling the list.
Matt

 

[RRder]

Not being a guru of Excel, but have touched the surface and like it. My company operates with Mac's, so my computer is a Mac OS X, version 10.5.7 and using MS Excel 2008 for Mac, version 12.2.0. Will many of these aids work with my Mac - Excel system or are they designed strictly for the PC - IBM version?

 

[adamsjm]

Many of the websites post versions for the Mac but not all.

Another good site is Mike Alexander's Data Pig, http://www.datapigtechnologies.com/ for Excel and Access

Joe Adams

 

[Jim Wynne]

The most important web site to bookmark is a listing of Excel’s Most Valuable Programmers. These are independent Excel developers who do not work for MS, but make their living developing Excel applications. They have proven themselves in their knowledge and their willingness to share it.

Thanks for all the good links! A few points of clarification and a little more information: The "P" in "MVP" stands for "Professional," not "Programmers," and not all of the Excel MVPs make their living developing Excel applications. The best way to take advantage of their expertise is through the Microsoft Usenet newsgroups. There's more than one for Excel, such as microsoft.public.excel and microsoft.public.excel.programming. If you don't have or don't want Usenet access, MS also has a web interface that can be accessed here.

 

[QE]

Any Excel Guide Specifically concentrating on Excel Macro Application ? please share !

While my main expertise is in Process Improvement, I have become an intermediate MS Excel Application developer in order to implement many of the solutions. Having read this forum for the past couple of months and the questions about MS Excel posted on it, I thought I would pass on some MS Excel web sites to use and learn from, a list of books to read, and tools which can be added into Excel to speed up your application / project development.

MS Excel is an extremely versatile and powerful tool. You can even develop .xll (similar to .xls files) functions in C+ /C++ and integrate them directly into Excel. This process is used on Wall Street to value and assess Derivative Trading. While we will probably never obtain that level of expertise, we can greatly improve our effectiveness and efficiency along the way.

Websites

The most important web site to bookmark is a listing of Excel’s Most Valuable Programmers. These are independent Excel developers who do not work for MS, but make their living developing Excel applications. They have proven themselves in their knowledge and their willingness to share it.

► http://www.mvps.org/links.html#Excel

From that list the best “General Interest How-to Sites” (especially for new learners) are:
► http://www.cpearson.com/
► http://www.contextures.com/tiptech.html
► http://www.j-walk.com/ss/
► http://www.mvps.org/dmcritchie/excel/excel.htm
► http://www.exceluser.com/index.htm
► http://www.dailydoseofexcel.com/

The “Higher End How-to Sites” are:
► http://www.appspro.com/ - Rob Bovey
► http://www.oaltd.co.uk/ - Stephen Bullen
Rob and Steven are, in my opinion, the best Excel developers / experts in the world.
► http://www.datapigtechnologies.com
► http://www.jkp-ads.com/
► http://www.tushar-mehta.com/

The outstanding “Graphing and Charting How-to Sites” are:
► http://www.peltiertech.com/
Jon is a metallurgist by training, with a Doctor of Science degree from MIT. He has over 20 years experience in research and development, and more recently in manufacturing and production support, in the aerospace, automotive, and industrial parts industries. His background includes Statistics, Total Quality, and Six Sigma. He has developed many quality charts and has posted the how-tos on his web site.
► http://www.andypope.info/

Tip for searching the Web: How do you get a good response to your web search? When you are seeking a “how-do-I” start your search with "MVP", enter your key words that you are searching, and then finish with “*excel*”. By doing this your responses will have an answer from an Excel MVP which will be correct.


Books

The following books are targeted for Excel 2000-2003. The same authors have written companion books for Excel 2007. The books are listed in difficulty order – low to high.
[See the attached Word document or .jpg files for the book images.]

Add-In tools

These Add-in tools can greatly improve your productivity. And the best part is they are FREE.
Rob Bovey once told me, “If you find a tool that you need and they are charging for it, keep searching. There will be a better one free of charge.”

PopTools ► http://www.cse.csiro.au/poptools/
Greg Hood wrote the statistic tool add-in PopTools. MS Excel’s statistical functions are not noted for their robustness and accuracy. If you require the best in statistical tools, add PopTools to your Excel. It has been written as an Add-in so that you can see his methodology and as a compiled for fast execution (a ,xll).

[From the web site]
PopTools is a versatile add-in for PC versions of Microsoft Excel that facilitates analysis of matrix population models and simulation of stochastic processes. Included in PopTools are routines for iterating spreadsheets. These make it possible to run Monte Carlo simulations, conduct randomisation tests (including the Mantel test) and calculate bootstrap statistics. The routines include array formulas for matrix decompositions (Cholesky, QR, singular values, LU), eigenanalysis (eigenvalues and real eigenvectors of square matrices) and formulas for generation of random variables (eg, Normal, binomial, gamma, exponential, Poisson, logNormal). Many of these functions depend on Jean DeBord's TPMath library - a Pascal numerical library - which has been compiled into a DLL accessed via Excel.


Add-in tools from Rob Bovey ► http://www.appspro.com/

XY Chart Labeler
A very commonly requested Excel feature is the ability to add labels to XY chart data points. The XY Chart Labeler adds this feature to Excel. [Used extensively by Jon Peltier]

Excel Utilities
There are several great development utilities available for the VBE, but the Excel Utilities is the only comprehensive add-in aimed at Excel user interface development. The Excel Utilities provides a set of 30 features that you'll use on a daily basis.

VBA Code Cleaner
During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage.
Note: Rob does not write his code to function in Worksheet Modules or in the ThisWorkbook Module. If the code breaks, it becomes unrecoverable. Code Cleaner can “fix” Standard, Form and Class Modules.

Code Documentor
The Excel Code Documentor automatically documents the vital statistics of your VBA project.


Add-in tools from Jan Karel Pieterse ► http://www.jkp-ads.com/

The Name Manager
An excellent utility to manage defined names in your workbooks.
(Incorporated into MS Excel 2007)

Flexfind
Eases searching and replacing throughout an entire workbook. Also enables you to search and replace strings in objects such as headers and footers, chart titles, buttons and many, many more.


Add-in tools from Decision Models Ltd, Charles Williams & Jan Karel Pieterse
► http://www.decisionmodels.com/fastexcel.htm

FastExcel (Cost $79)
"Most slow-running spreadsheets contain a small number of problem areas, or Bottlenecks. Because Excel is such a flexible spreadsheet system there are usually many different formulas that can produce the answer you want. Some of these formulas are much faster than others.
In large spreadsheets it can be difficult to locate and prioritise the Bottlenecks.You can use the wide variety of timing and profiling tools in FastExcel to rapidly drill down, locate and prioritise these bottlenecks, and then use FastExcel's built-in Help for advice on eliminating or optimising them."


Add-in tools from Andy Pope ► http://www.andypope.info/

Button Editor
An add-in for creating button images.

Graphics Exporter
Add-in for exporting items in your workbook to graphics files



Add-in tools from Bill Manville ► http://www.oaltd.co.uk/MVP/Default.htm
FindLink
To look for links from a workbook


Add-in tools from MZ-Tools ► http://www.mztools.com/v3/mztools3.htm
MZToolsVBA
MZ-Tools 3.0 is a freeware 'all-in-one' add-in for Visual Basic 6.0, Visual Basic 5.0 and the Visual Basic For Applications editor (provided by a VBA-enabled application such as those in Office 2000 and higher) which adds several productivity features to


Program from TechSmith ► http://www.techsmith.com/
SnagIt Screen Capture (Cost $39.95)
Using SnagIt, you can select and capture anything on your screen and share it with others to visually clarify difficult concepts, create clearer documentation, and give your presentations a professional, dynamic look.

This is by no means an all-inclusive list of sources or tools, but I hope it will be a start.

Joe Adams
Process Excellence Architect
Strategic Quality
(near) Indianapolis, Indiana

 

[adamsjm]

nirlep quote:

Any Excel Guide Specifically concentrating on Excel Macro Application ? please share !

For application how-to's, I would start with the book route.
The first book to read would be any VBA book by John Walkenback. Start with one for your version of Excel.
The second book would be Excel 2002 (or 2007) by Bullen and Bovey.
The third would be "Professional Excel Development" by Bullen and Bovey. It is the authority on application development, but it is also not for beginners or the uncommitted.

The websites I listed would have individule modules (or the bits and pieces) to speed up your application coding.

Joe