Category Archives: Excel

Excel Final Project: the Only True “Student Data File”

Always seem to be working on the BTS 165 final project. Tweaking it to make it better…

One realization I had in Monday night class last night is this: this project is, essentially, like creating your own “Student Data File.” That is, for every assignment in this Go! textbook, the authors provide the data for you. Most of the time, that is in the form of a starting file, which the publishers always seem to call “student data files.” (Why they don’t call them “starting data files,” i don’t know.) Sometimes, they’ll have you start with a blank Excel file, then tell you what to type where. But either way, you do not create your own data.

The final project is the only assignment in this class in which the student creates their own starting data file. THAT really is a “Student Data File!” (ha!)

Excel, Used

When I teach Excel, I always highlight what I consider the two most important features that make Excel useful: formulas and charts. Formulas are important because they allow us to analyze, manipulate, twist and turn numbers so that we can learn new things about them. And charts allow us to visualize numbers so that we can understand them in an instant. (If a picture is worth a thousand words, then a chart is worth a thousand numbers.)

But, when we start creating our own charts, and formatting them, I always point out how charts can be used to manipulate the data. The easiest way to point this out is to change the vertical axis so that it doesn’t start at zero. This amplifies any changes in the vertical axis, making the chart more dramatic. Here is an excellent article by Eric Portelance that further discusses this manipulation, as well as several other manipulations — either on purpose in order to mislead an audience, or by mistake by someone who doesn’t know how to present data accurately. First Chart: inaccurate

The chart Portelance discusses is from a (late 2013) article by Mark Gimein, “Companies and Markets editor at, and lead writer for the Market Now blog and newsletter.” Bloomberg is, of course, the huge financial services conglomerate. These guys DO data. They should know better. In fact, I find it extremely hard to believe that they would create a chart that mistakenly mis-represented the data. The only other conclusion, then? That Bloomberg purposefully mis-represented the data.

The question then becomes, why? What do they hope to gain by publishing an article with the title, “For U.S. Men, 40 Years of Falling Income,” and then backing it up with purposefully mis-represented data?

Gimein’s concluding paragraph states, “To TMN it seems that the focus of the economic debate belongs less on rising incomes at the top than on falling incomes in the middle. The concern of Americans on the middle of the economic ladder is not really that their neighbors behind some high hedge are doing too well. It’s that they themselves are not earning anything like the incomes they expected. Judging by the data, that concern is well-founded.”

“Well-founded?” Look at the data in Portelance’s corrected chart:Second chart: corrected

The real question with this chart is, “what happened in 1972 that caused the steady progression of income growth to immediately level off?” Portelance, admitting he is not an economist, takes a shot in the dark anyway: “Bretton Woods and the end of the Gold Standard?” Maybe so. I’m not an economist either, and a cursory glance at Wikipedia’s “Nixon Shock” article only provided me more anxiety.

However, I do have my own guesses as to Bloomberg’s motives for publishing this purposefully-inaccurate article: Is Bloomberg trying to deflect attention away from income inequality (aka “Class Warfare”)? Are the richest 1% trying to “divide and conquer” the 99%?

Let’s look at a chart that looks at income by class: Third chart: income by class

The income of the bottom four-fifths — the middle  and lower classes — has remained steady since the mid 1960s. However, the top 20% and especially the top 5% have grown steadily since the mid 60s. This means that all the growth in income is going to the people who are already rich.

Is Bloomberg afraid that the proletariat will realized the rich have rigged the system? That the rich run things? That the rich make the rules, which of course favor the rich?

Assessing Computer Users: “Easy” May Not Always Equal “Best”

5-year-old kid sitting at his computer desk.
Ayan Qureshi: the 5-year-old kid who passed the Microsoft Certified Professional exam.

This 5-year-old passed the Microsoft Certified Professional exam. Granted that the kid may be special, but, seriously, what does that say about the Microsoft Certified Professional exam?

I’ve taken the Microsoft Office Specialist exams for Word, Excel, and PowerPoint, and they were pretty dang easy. I wonder how much of a true measure of a computer user’s ability these tests are. I mean, the instructions are all there. The test instructions say stuff like, “insert a photo,” “make the text bold and red,” “move slide 1 to become slide 5,” and so on. So, the tests may accurately asses IF a user can do a specific given task using the software, but my main concern is that, in a real world situation, there is nobody around to tell a user WHAT to do, or WHICH tool to use WHEN. In the real world, a computer worker is told to “create a flyer.” Maybe the boss will bring in a flyer that they saw in the mall and say, “make it look like this.” But the boss is not sitting there next to you saying, “merge and center cells A1:D1, and format using the Title style.”

Such assessment — of a user’s ability of HOW to use a software tool — may be easy to implement, but I am skeptical of its ability to  accurately assess a different and more important dimension of computer users’ abilities: WHEN to use WHICH tool to solve various real-world problems.

In my BTS 165 Excel classes, the final project assignment is to use Excel to solve a real-world problem. Students must find a problem  to solve, then use Excel to solve it. It doesn’t have to be a huge problem, but the application of the tool to the problem must be theirs. While assessing the student’s success is a considerable challenge — especially in the amount of time it takes to work with students to understand their decisions and reasoning at several key points in the weeks-long process — and even then, perhaps open to some amount of subjective interpretation on the teacher’s part, I feel that this project is a much more accurate assessment of a student’s understanding not only of how to use Excel’s various tools and features, but WHICH tools and features to use WHEN.

Sometimes “easy” does not equal “best.”

Excel and BTS 165 Ruminations

Shiny Excel buttonI love Excel. I’ve been teaching Excel now for about five years, I guess (since the 2007 version, and we’re well into the 2013 version now), and have seen some cool new features.

Excel itself has been around for a long time. It’s older than about half my students, probably. The first version of Excel came out in September 1985 (Wikipedia), and was Mac-only (as was the first version of Word)– which I love to point out because I’m a Mac dude. So Excel is a very mature program. Microsoft has had a lot of time to add new functionality and power.

And I love teaching. So, put Excel and teaching together, and I love to teach Excel. Here in the BTS department here at BC (our BTS 165 class), we’ve used three different textbooks/curriculum systems– a different one for 2007, 2010, and 2013. For Excel 2007, we used Pearson’s Go! series; for 2010, we used Cengage’s New Perspective series; and for 2013, we’re using Pearson’s Go! series again, but this time, it’s coupled with MyITLab.

Now, every textbook has its strengths and weaknesses. At the time we used 2007, I considered the Go! series too simplistic. I appreciated the New Perspectives way more– it seemed to be more comprehensive and challenging to the students. But it was also well-paced– it seemed to “scaffold” learning well– that is, it did a better job of preparing students for more challenging work. For instance, it had Capstone Projects every three chapters or so, which did a good job of wrapping up a good deal of functionality into a coherent project that was at the same time not out of reach for students. I used one of those capstone projects as a final project for many years in my Excel classes, with good results.

And I still use that same project, even though we use Go! again. Well, not in the summer– seven weeks, I have decided, is too short a time to smash in such a major project. But the next time I teach BTS 165, and in all the longer, 11-week quarters, I will use that final project.

But here is one thing that I really want to mention in this post: Chapter 2 of Excel in the Go! series of textbooks. Simply put, there is TOO MUCH STUFF in this chapter! It should be divided up into at least two chapters, and maybe three. Here’s a list of all the stuff covered:

  1. Flash Fill
  2. SUM
  5. MIN
  6. MAX
  7. Moving data
  8. Resolving error messages
  9. Rotating text
  11. IF
  12. Conditional Formatting
  13. Date functions
  14. Time functions
  15. Freezing panes
  16. Tables
  17. Sorting
  18. Filtering
  19. Viewing, formatting, and printing large worksheets
  20. Renaming worksheets
  21. Entering dates
  22. Clearing contents
  23. Clearing formats
  24. Copying and pasting with Paste Options Gallery
  25. Editing and formatting multiple sheets at a time
  26. Creating a summary sheet
  27. Column Sparklines
  28. Formatting and printing multiple worksheets at a time

And that’s it. That’s all. Only 28 features of Excel.

In class today, I went over these chapter objectives. It took an hour. (Thank goodness it’s a three-hour class!) The students were wiped out! Shoot, *I* was wiped out!

We took our break, then came back and started tackling the chapter. It went well. They’re good students, committed to doing the work, to understanding the material, to understanding not just “how” to do something in Excel, but also “why.” I love that. I love that, because, when they get out of this class (NEXT WEEK!), they’re not going to remember HOW to do most of this stuff… I therefore hope to instill a sense of familiarity with Excel’s interface, a sense that, even if they don’t know how to do any particular thing in Excel, they at least have a method of figuring it out.

And not just Excel, but the computer in general. I mean, a computer can be used for almost anything. That’s one of the main reasons that computers are so powerful– through programming, they can become a jillion different tools. No one person knows all of that stuff. It’s just too much! I mean, today in class, we looked at Excel’s Functions. There are 458 of them in the version of Excel 2013 that I have on this computer in my office.

“Nobody knows them all,” I told my students. “Not even Mike Girvin, the Excel Guru! So don’t feel bad if you don’t.”

And this brings me to another issue that I’m having lately… skills versus understanding. So, I was talking about my Final Project assignment, which, I think, is a pretty good measure of a student’s “real” understanding of Excel. I guess one of the main things this project does that is especially challenging to BTS 165 students is that they must create this workbook from scratch. This is new to them. In every single assignment, in both the Go! and New Perspectives series of textbooks, the data is provided for them. So, the fact that this one Capstone Project in New Perspectives on Excel 2010 challenges students to solve a “real world” problem means that they have to figure out a way to get that problem into an “Excellable” format! This reminds me of Alan Turing’s notion of “computability,” which is the notion that some problems are “computable,”– that is, solvable by a computer– while others are not. In this same way, Excel students are, for the first time, presented with this notion of “Excelability,”– that is, the question of whether a given problem is solvable by Excel. Up to this point, they’ve been fed problems that ARE “Excelable,” and then they’re just shown how to use Excel’s tools and functions to solve them.

What a great challenge! I mean, Excel can’t help me tie my shoes, can it? On the other hand, it can help me immensely with analyzing and understanding my inventory of products on hand.

… So, not just how to use Excel, but why. And when.

But, as I’ve said, this is not addressed in the Go! series nor the New Perspectives series (other than this one particular Capstone Project).

And, now, we’re using MyITLab. Now, MyITLab has some great tools for learning how to use Excel: the electronic version of the textbook includes hyperlinked glossary terms. Each chapter Objective is preceded by an accompanying video by one of the authors that shows how to do the stuff in that Objective. There are “Skill-Based Trainings” and “Skill-Based Exams” that train and test students on their ability to use specific features and commands. There are “Grader Projects,” which give students step-by-step instructions on how to complete a real project in Excel, which students upload for immediate grading and feedback. MyITLab is pretty cool.

These textbooks are all dedicated to teaching the technical skills, the how-to, of Excel. They’re useful references.  But how effective are they in preparing students for real life? How effective are the in preparing students for a job? I’m not sure.

Maybe it’s different for each student. Some students take BTS 165 because 1.) it’s a requirement for some certificate or degree program. Some take it because 2.) they need a job, and they’ve noticed that Excel knowledge is a common requirement for jobs these days. Some take it because 3.) they’re using it in their current life/job, and want to know more. Some take it because 4.) their parents told them to. Out of these four kinds of Excel students, BTS 165 is best for the third one: for a student who is already USING Excel, and who just need to know more about it, to learn some new tricks, to learn “the Microsoft way” (which is what I call the perspective a computer user must come from in order to best understand how to use Microsoft programs). Such students can continue to actively practice the new stuff they’re learning, to incorporate it regularly into their current lives.

But the other three kinds of students– the ones who won’t be using it every day– they’re going to have a harder time with BTS 165. I mean, they’ll be fine during the course of the quarter itself. But, when the class is over, their knowledge will immediately begin to evaporate because of non-use– the old saying, “if you don’t use it, you lose it.”

It is these kinds of students that our textbooks, and the MOS certification exams, do NOT help. Our textbooks and materials, prepare students for the MOS exams, which test whether a student can find the right tool/command/method for completing a specific little task in Excel. But how deep does an Excel user’s understanding need to be in order to pass such a test? Not very deep, indeed.

Now, I’m not dissing the MOS certifications. They serve a purpose. They look good on a resume. But, do they measure how well a user can use Excel to solve a real-world problem? In my opinion, not very well.

But there is a real challenge here, which may give some insight as to why there is such a dichotomy between the way our teaching/learning materials are structured and the way that I think they SHOULD be structured: assessment. I recall when my program chair presented our new course and program outcomes about a year ago. Essentially, our new outcomes are: “the student should be able to pass the MOS certification exam.” And the way she presented it, it made total sense: it is measureable! And, when it comes to the college “certification” process, one of the main things that certification board is looking for are measureable course outcomes.

It is much easier to measure whether a student passed the MOS test than it is to determine whether they understand how to use Excel to solve a real-world problem.

So, there it is. In one blog post, some of the major issues with Excel and BTS 165 that I have been thinking about for the better part of a full year.

Comments welcome.