There is a lot of good research on how to teach introductory programming. But maybe(?) more important to most students is the ability to use a spreadsheet. What is the best way to teach introductory spreadsheets?
While I've never taught spreadsheets to anyone other than myself, I don't think there is any unique magic here. In fact, since a spreadsheet is its own metaphor, the pedagogy should be fairly straight forward. It is like learning any other skill, even swimming.
Lots of practice. Lots of feedback.
What I'd do is create a list of short instructions (lectures or other) followed by a lot of exercises using what was in the recent instructions. The instructions can become increasingly complex as can the exercises. Feedback to students is required for the exercises, perhaps by grading, and perhaps by distributing correct (and maybe incorrect) solutions. The incorrect "solutions" can illustrate common mistakes so as to make them explicit in the minds of the students, thus making them recognizable when they occur in practice.
One sort of exercise you might consider is a "fixer-upper" which is an example of a design pattern of the same name. A Fixer Upper is a partial solution to be completed by the students, or it is a complete solution that has had specific errors introduced that should be found and corrected. It is possible to combine these in a more sophisticated version, though simpler examples should be given at the start.
I would also make sure that the students have access to the documentation of the spreadsheet program and are tested on their understanding of it. This can come by giving exercises that have not had prior instruction other than to point them to the details in the documentation.
For the first exercises it is probably best to test single features, such as summing a column. For later ones, combinations should be introduced.
And if you want to go to extremes, note that some spreadsheet systems are Turing Complete, so it is possible to do arbitrary programming within them. In particular, recursive programs are possible.
I have never taught spreadsheets, per se, but have given projects to create a working spreadsheet. To test theses implementations, one needs a sample spreadsheet that exercises the various functions implemented by the system. The one that students seemed to enjoy the most was a list of students, and their grades (all factitious) giving multiple exam grades and final exam grade, quiz grades, program grades, etc. The grades are combined in various ways (different weightings, throw out lowest, ...) to produce a numerical grade for each student. It is a process they are familiar with and they had fun putting in their own grades and running "what grade might I get" scenarios.
I had them sort the final calculated grades and determine where the "breaks" between letter grades might fall. That was a manual exercise based on the data (since their implementation did not have conditionals), but gave the students a look at what teachers can go thru in the grading process.
It seems like it would be good to introduce sample problems that the student can relate to. Also point out that spreadsheets were the original "killer" application that helped drive the use of personal computers in a business setting, and how the language opened up "programming" to many users who never thought they could program.