Volunteering to Teach High School Students Advanced Excel and VBA -- Seeking Input!

[pawest]

Hello Advanced Excel and VBA community,
Separate from the typical questions seeking help on this form, I am seeking input. I'm starting up a volunteer program (3 trial sessions) to teach Advanced Excel and VBA. These session will be around 90 minutes and with private high school students apt to the math and sciences. These kids can handle fast pace instruction. I would like to get your opinions on what you think is absolutely necessary to teach.

Here's my very general plan right:

Session 1:

• Introduction to capabilities of Excel
• Using the keyboard/Alt and learning hot keys
• Walking though each section of the ribbon
• Basic finance and math models using Excel functions

Session 2:

• Advanced excel formulas
• Pivot Tables, Handling Flat Files, Possibility of External Data Add-Ins and Solver
• Proper Formatting
• Advanced excel models and handling of reports (relating to my real world work)

Session 3:

• Basic VBA

Any input would be valued. Thanks!

 

[JLGWhiz]

Hello Advanced Excel and VBA community,
Separate from the typical questions seeking help on this form, I am seeking input. I'm starting up a volunteer program (3 trial sessions) to teach Advanced Excel and VBA. These session will be around 90 minutes and with private high school students apt to the math and sciences. These kids can handle fast pace instruction. I would like to get your opinions on what you think is absolutely necessary to teach.

Here's my very general plan right:

Session 1:

• Introduction to capabilities of Excel
• Using the keyboard/Alt and learning hot keys
• Walking though each section of the ribbon
• Basic finance and math models using Excel functions

Session 2:

• Advanced excel formulas
• Pivot Tables, Handling Flat Files, Possibility of External Data Add-Ins and Solver
• Proper Formatting
• Advanced excel models and handling of reports (relating to my real world work)

Session 3:

• Basic VBA

Any input would be valued. Thanks!

IMHO, the most important of all is teaching how to use the help files of both Excel and VBA. 90 percent of the questions asked in this forum can be answered in those files, if the user knew how to use them.

 

[shg]

Amen.

 

[pawest]

Noted. Great input.

One of my proposals was to teach the students how to learn on their own by looking into great books
(such as John Walkenbach's texts), using great youtube videos, using this site, etc.
I should include the help files (which is something I admittedly don't take advantage of as much as I should).

Many people would argue there are two people: people who don't know VLOOKUP, and people who know VLOOKUP. Do either of you have any opinions on this and other things that should be taught?

Thanks.

 

[T. Valko]

Hello Advanced Excel and VBA community,
Separate from the typical questions seeking help on this form, I am seeking input. I'm starting up a volunteer program (3 trial sessions) to teach Advanced Excel and VBA. These session will be around 90 minutes and with private high school students apt to the math and sciences. These kids can handle fast pace instruction. I would like to get your opinions on what you think is absolutely necessary to teach.

Here's my very general plan right:

Session 1:

• Introduction to capabilities of Excel
• Using the keyboard/Alt and learning hot keys
• Walking though each section of the ribbon
• Basic finance and math models using Excel functions

Session 2:

• Advanced excel formulas
• Pivot Tables, Handling Flat Files, Possibility of External Data Add-Ins and Solver
• Proper Formatting
• Advanced excel models and handling of reports (relating to my real world work)

Session 3:

• Basic VBA

Any input would be valued. Thanks!

I hope "proper formatting" includes "use minimal formatting"!

Teach them that Excel is not an electronic coloring book for adults!

 

[shg]

Excel is not an electronic coloring book for adults!

Say it, Brother!

 

[pawest]

Exactly what I meant!
A spreadsheet with little to no colors is the best kind of spreadsheet in my opinion.
I would include how to format continuous data sets/tables (at least how I think they should be formatted-- autofit with bold headings and non-bold raw data and that's it) and financial statements (with bold headings, currency formats, proper spacing, red and black text, and cell borders).

"Less is more"

 

[NewOrderFac33]

Something that I would definitely teach is the optimum way to set up a database that consists of data headers followed by data with the whole thing surrounded by empty space (blank row above and below, empty column to the left and right).
I have improved on this over many years and now have what i consider to be an optimum database format where the first field header is always called "DatabaseStart" and VBA is called to define a range called "Database" that includes the field headers and a range called "Data" that doesn't. The best way in which these ranges can be defined is using Excel's CurrentRegion property, which in turn allows you to create range names for each column of data, which can subsequently be used by SUMPRODUCT formulae.
Thus, it's vital to build the database correctly first so that all the other stuff can follow on from it.
In fact, I'm so pleased with where I've got to with this that I'd be happy to share a sample of the model if you'd be interested.

 

[pawest]

see following post...

 

[pawest]

Fellow Pete (my name is Pete too!)

I do not disagree with your formatting. However, I am not an advocate of using Excel like a database. I believe that's what Access, SQL Server, MySQL, etc. have been made for. I believe Excel should only be used for performing analysis on data. This may entail a lot of data. For example, I use spreadsheets with millions of cells filled with data on a daily basis. Yet, I don't think I would advocate to young students that Excel should be misconstrued as a database.

What do you think?


That being said, when I use FTP files for example that contain huge sets of data, I virtually calculate the data in ways you would with VBA, as well. I would be pleased to see your an example model. I will message you my email address.