Programming and Excel
Not everyone needs to be a programmer! You probably hear that a lot if you talk about trying to get more students to program. I know that I do. And of course there is some truth to that. But I believe programming is valuable for several reasons (learning critical thinking is one, algorithmic problem solving another, and more) and one of the commonly neglected reason is better usage of spreadsheets. Yes, spreadsheets.
Everyone knows how to use spreadsheets right? Actually no. And even many who know a little about using them take very little advantage of features that could be very useful for them. A little programming knowledge can help here.
I used to give placement tests to incoming high school students who thought they didn’t need to take a computer applications course. One part of the test was to enter data in a spreadsheet and have it show the results of some basic formulas. I could often tell who was going to do poorly – they took out calculators and used them. No, really! Clearly they didn’t “get” what a spreadsheet was all about. If they could not use the spreadsheet features to total a row or column of numbers you can imagine what else they were missing! (You can can’t you? Please?)
The two must useful and over looked features of spreadsheets are (in my opinion) conditionals and built in formula functions. take a look at this screen image (of Excel 2010 BTW) and just look at the categories. There are many functions under each menu item.
In programming we quickly learn to use and to create functions. It becomes part of the way we think about solving problems. In spreadsheets it can be the same. Now a lot of those functions are conditionals – things like “If” that we use in programming all the time. There is also conditional formatting.Did you know that you could set a lot of things in a cell based on their contents?
This image shows only a few of the options included in Excel 2010 for formatting. You can add icons, background colors, data bars and more. But of course you have to understand a little about Boolean constructs and conditional coding. Not a lot really but some. While most people are not going to need programming in the sense of writing code in C# or Visual Basic or even F# a lot of people are going to be using spreadsheets to evaluate data and make business decisions. We do them a disservice if we don’t make sure they know how to use more of the advanced features that programming can help them use and understand.
Late edit: This post is listed on DZone (at https://www.dzone.com/links/programming_and_excel.html - thanks for the positive votes BTW) and there are some comments there. One in particular is interesting to me because it makes an interesting point.
Ric Rude replied Excel is a declarative programming language but one where, unlike other languages, the program output is prominent while the code is hidden.
Of course we can use tools like this and teach these concepts outside of programming courses. I found this set of Innovids in the category How to use Excel across the curriculum. Some of them include: