How did you guys learn everything?

[Initial E]

I want to become very very knowledgeable in excel formulas and macros. I think I am very capable of calculating and performing most of the day to day type functions. However, I am weak when it comes to very complex and specific calculations. How did you guys become experts in everything that excel can do? What about macros?

Some advice would be appreciated to get me started.

 

[Worf]

Hello
I can tell you what worked for me, although I don´t know everything about Excel, and suspect even Microsoft MVP´s don´t either…

A few years ago I was a mediocre Excel user, and decided to change that. So I bought John Walkenbach´s Excel Bible and read it from cover to cover. This took me several months to do, because I have a full time job.

Finishing that book I realized no one can be an Excel guru without VBA, so I bought JW´s Excel Power Programming with VBA. I am convinced that good books are the way to go, you should try to pick one adequate to your current level (beginner/intermediate/advanced).

Considering we are here at Mr. Excel, you could also check Bill Jelen´s material…

 

[hiker95]

Initial E,

You learn by doing, and also see how others solve the same issue.


Training / Books / Sites as of 08/23/2012

MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
MrExcel's Products: Books, CDs, Podcasts

There are over 1800 Excel videos/tutorials here:
excelisfun -- 1800 Excel How To Videos - YouTube

Getting Started with VBA.
DataPig Technologies

If you are serious about learning VBA try
Macros Made Easy for Microsoft Excel

Excel Tutorials and Tips - VBA - macros - training
Excel Tutorial | Excel Tips | Excel Articles

Excel 2007 VBA materials to learn here:
VBA for Excel 2007 tutorial-Understand VBA| Example code

Here's a good primer on the scope of variables.
Scope Of Variables And Procedures

See David McRitchie's site if you just started with VBA
Getting Started with Macros and User Defined Functions

What is a Visual Basic Module?
What Is A VBA Module?

Ron de Bruin's intro to macros:
Where do I paste the code that I want to use in my workbook

Creating An XLA Add-In For Excel, Writing User Defined Functions In VBA
Creating An XLA Add In

How do I create a PERSONAL.XLS(B) or Add-in
How do I create a PERSONAL.XLS(B) or Add-in

Creating custom functions
Creating custom functions - Excel - Office.com

Writing Your First VBA Function in Excel
Writing Your First VBA Function in Excel

VBA for Excel (Macros)
Excel Macros (VBA) Tutorial

Excel Macros & Programming
Learning about EXCEL macros

VBA Lesson 11: VBA Code General Tips and General Vocabulary
VBA for Excel macros language

Excel VBA -- Adding Code to a Workbook
Excel VBA -- Adding Code to Excel Workbook

Learn to debug:
Debugging VBA

How To: Assign a Macro to a Button or Shape
How To: Assign a Macro to a Button or Shape | Peltier Tech Blog | Excel Charts

User Form Creation
Create an Excel UserForm

When To Use a UserForm & What to Use a UserForm For
When to use Userform & What To Use Them For. Excel Training VBA 2 lesson 2

Excel Tutorials / Video Tutorials - Functions
Excel VLookup Function Examples

INDEX MATCH - Excel Index Function and Excel Match Function
Excel Index Function and Match Function

Excel Data Validation
Excel Data Validation Tips and Quirks
Excel Data Validation - Add New Items

Your Quick Reference to Microsoft Excel Solutions
XL-CENTRAL.COM : Your Quick Reference to Microsoft Excel Solutions

New! Excel Recorded Webinars
DataPig Technologies

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
Programming In The VBA Editor

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…

by Stephen/ Bovey, Rob/ Green, John Bullen (Paperback - Feb 11, 2005)
Professional Excel Development

by Rob Bovey, Stephen Bullen, John Green, and Robert Rosenberg (Paperback - Sep 26, 2001)
Excel 2002 VBA: Programmers Reference

DonkeyOte: My Recommended Reading, Volatility
Volatile Excel Functions -Decision Models

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Arrays
Excel: Introduction to Array Formulas - Xtreme Visual Basic Talk
Array in Excel VBA

Pivot Intro
Using Pivot Tables and Pivot Charts in Microsoft Excel

Email from XL - VBA
Example Code for sending mail from Excel

Outlook VBA
Writing VBA code for Microsoft Outlook

Function Dictionary
http://www.xlfdic.com/

Function Translations
Excel 2007 function name translations - Dictionary Chart Front Page

Dynamic Named Ranges
Excel Names -- Excel Named Ranges

How to create Excel Dashboards
http://www.mrexcel.com/Excel-dashboards-Xcelsius.html
Excel Dashboard Templates
Excel Dashboards - Templates, Tutorials, Downloads and Examples | Chandoo.org - Learn Microsoft Excel Online
http://chandoo.org/wp/management-dashboards-excel/
Free Microsoft Excel Dashboard Widgets to Download
AJP Excel Information - Gauge

Excel Dashboard / Scorecard Ebook
Excel Dashboards and Scorecards Ebook | How to Create Dashboards in Excel

Mike Alexander from Data Pig Technologies
Excel 2007 Dashboards & Reports For Dummies

Templates
CPearson.com Topic Index
Excel Template - Golf Scores
Free Microsoft Excel Template Links & Search Engine

Microsoft Excel Cascading Listboxes Tutorial
Microsoft Excel Cascading Listboxes Tutorial - YouTube

Date & Time stamping:
McGimpsey & Associates : Excel : Time and date stamps

Get Formula / Formats thru custom functions:
Show FORMULA or FORMAT of another cell

A nice informative MS article "Improving Performance in Excel 2007"
Improving Performance in Excel 2007

Progress Meters
AJP Excel Information - Progress meters
Excel

And, as your skills increase, try answering posts on sites like:
MrExcel.com | Excel Resources | Excel Seminars | Excel Products
Excel Help Forum
Excel Templates | Excel Add-ins and Excel Help with formulas and VBA Macros
VBA Express Portal
Home

 

[Martin_King]

I am no where near the level of the users of this forum but I am comfortable with VBA.
TBH, the best way I've learnt is by doing (it's how I always learn)
Books are great for referencing and I have a couple of advanced excel and vba books (mr excel included) but I also find google is extremely powerful - there are a lot of forums similar to this one that will have custom solutions and it can be bewildering the amount of various routes you can go down to get the same solution.

Working in a job that requires you to write VBA and general computer programming IMO will be most beneficial - any office job requires excel but the level is normally very basic such as vlookups and pivot tables.

I try to log onto these forums and have a go at solving problems for others (at a junior level) as it will cement what I know, hopefully teach me new things, and also help others as they have done for me so many times here.

 

[xenou]

I'm also a fan of reading a book (advantage is a structured and comprehensive guide rather than the rather random results you get from searching the web). That said, once you know the basics you learn a great deal solving practical problems with the web as a research tool.

 

[Jeffrey Smith]

I got a macro workbook loaded with examples. That started me on the basics. The MrExcel forums helped the most. The book "Professional Excel Development" by Rob Bovey, Dennis Wallentin, Stephen Bullen, & John Green helped a lot.

 

[EdNerd]

Same here:
-- Read books
-- Search forums and blogs
-- Copious use of F1 in the workbook and VBE
-- Find a good forum and ask lots of questions until you understand more
-- On that forum, pick some questions and attempt to answer them, or at least work through the problem. Don't be afraid to jump in and ask how or why a certain "thing" works "that way".

There is nothing so satisfying to someone who wants to help as finding someone who wants to learn.

Ed

 

[mathchick]

I would consider myself to be a fairly advanced excel user, and a somewhat proficient VBAer, and I've never read an excel book. I learned by doing. I've been tempted to get books to go through, so I certainly think they're valid, but here are the reasons I've gotten better at excel:

1. I poke around and try things. Curiosity and tenacity will get you far! Even if my first 20 attempts don't solve my current problem I've learned about new things in excel, and more importantly trying things is almost the only way to truly increase your intuition from what is most likely to be a useful path to take in solving any given excel problem.
2. I've learned from the best. There are many true excel pros out there who have published tutorials and who are willing to help others on forums. Google probably accounts for at least half of my excel knowledge. Find good forums and websites and go back to them often!
3. I don't turn down a challenge. I take any excel question that comes my way at work, even if I don't have a clue what the answer is -- I can always find it (even if the answer ends up being "sorry, that isn't possible, but here's an alternative"). People almost always understand the, "I'm not completely sure, let me look into it" answer and if you can come back with an answer within a reasonable period of time no one cares that you didn't know initially, and eventually you become the resident excel guru. I also look for questions to answer on-line. Often I also get to see solutions other folks come up with, which only deepens my knowledge of the particular problem I was trying to solve.
4. I ask for help. If I've googled like crazy, tried everything I can think of, took a break, and tried again I post on this forum.

I've also started a bookmark library because I often stumble on quality information that either isn't applicable for what I'm currently working on or that I know I'll forget 30 minutes after I use. I also have a workbook containing any possibly reusable bits of code or formulas. This way you can (reasonably) easily find the answers to questions you've already answered.

 

[Amnesiac]

Learning from others by reading blogs, websites, and forums and then doing by actually applying the knowledge by creating formulas and macros.

 

[obiron]

I would say that one key is to learn the Excel object structure in depth and understand what is possible at each level

Application->workbook collection->Workbook -> worksheets collection ->worksheet -> range
Application->workbook collection ->Workbook-> Pivot Cache collection
Application->workbook collection ->Workbook -> worksheets collection ->worksheet -> Pivot Tables collection
etc..
(I'm not an expert on charts and graphs, but the same applies there)

You don't need to know the answers, they can be found online; but you do need to know the questions.