Course Expectations

Today I taught my “Data Cleanup Analysis & Display” course again, and it went very well. I always enjoy teaching Excel courses as the attendees are usually quite enthusiastic.

Last night, as I was working away on organizing my handout packages, I posted a Twitter status to that effect. This led to a conversation between Jon Peltier and I about how we distribute material to the attendees.

In each of my courses I include a folder that contains the following:

  • A business card
  • A “Profile” sheet (about me and my experience)
  • A “Sales” sheet advising the attendees that I will teach custom courses
  • A “RibbonX” insert
  • The notes pages for the Powerpoint presentation (3 slides per page)
  • A CD with
    • An e-Book of the entire course, step by step
    • All of the example files

By contrast, Jon distributes his Powerpoint and workbooks electronically, as paper is a hassle to work with.

I don’t disagree with Jon in that it is a hassle. It also costs me money to do, (I’d hazard a guess to say that it’s around $2.00 per attendee by the time is all said & done,) and time to assemble, (last night Dee & I spent about an hour between printing all the docs, burning all the CD’s and stuffing them all in folders.) And let’s not forget that I then have to haul them all to the course location as well. When you add it to the laptop, projector and extra peripherals, it does add up to a bunch of stuff to haul around. So yes, packages are a burden for the presenter… but to me they are an integral part of the presentation.

Now, neither of us is saying don’t distribute, far from it. I recognize that I could distribute everything I do electronically to people if I wanted to. I choose not to.

The value of presentation notes pages is not even a question. Whatever medium I’d chosen to use to distribute them, the fact that my students had the powerpoint slides to write notes on was huge. I saw a TON of notes being taken today, despite the fact that the class was made aware that everything I covered was also in the e-Book in a step by step manner.

The e-Book is a new thing I started recently after a huge amount of demand. I receive a TON of positive feedback on my presentations, but the biggest “wish” feedback is for a good take-away package. The challenge is that, with a technical course, you forget how things were done. Despite the huge commitment to write up the course in this fashion, I’ve embraced it as I see the value. And with it goes the example files so that people can follow along. People have responded REALLY well to this.

Again, the fact that the handouts and materials are appreciated is not in doubt. We come back again to the why do it physically vs electronically.

The reasons are all about “touch and feel” and perceived value, to be honest.

In addition to teaching, I’ve also attended quite a few presentations. To me, especially for a technical course, there is something about being handed something tangible when you get there. It makes me feel that the person has invested some time and money in their product, and that shows a level of caring. I get something that I can actually touch, pick up, and take away. My friend Dennis Wallentin (XL-Dennis) loves real books over e-Books because you get that satisfying feel of holding it… the same with these packages to me. You can’t just throw down the slide pages either, in my feeling. Just being handed out a stapled collection of slides holds less weight than adding that special touch of putting them in a folder. The folder finishes things and just feels a bit more professional, but maybe that’s me.

I also take pride in making sure that all participants have the notes pages in front of them so that they don’t have to spend all their time writing instead of watching. By providing them the note pages when they arrive, then I can ensure that no student forgets their notes pages and is left behind.

In addition to being able to include the notes pages, it also gives me the ability to slip in my own propaganda in an attractive format… something that is important if you’re trying to get future business. We all know that some superior technologies have died due to poor marketing. This is the marketing angle, as I always want to put my best foot forward.

I’m actually going to a conference next week where there will be no handouts in an effort to “go green”. (I’m both attending and presenting at this conference.) The handouts have all been distributed electronically, and attendees are expected to review them before the session and print them “if they wish”. I have three issues with this:

  1. As far as I’m concerned, this is a “green-washing” technique that is really about saving money. I plan to print the handouts for the sessions I’ll be attending and take them with me, as I want to take notes while I’m there. While it’s not much, it will be costing me additional money to print the notes.
  2. It assumes that I’m going to spend my personal time reviewing the entire presentation before I go to the conference. I’m taking time from work to go to this, so why am I expected to take either more time from work or personal time to review what they’re going to show me?
    Isn’t that the point of attending the presentation in the first place?
  3. In this case it isn’t the presenters that have requested this, but it does reflect on them.

Overall, I guess, I believe that presenting a course is providing a learning experience to someone. The handout package, to me, is an essential part of that, and I just prefer the good, old-fashioned physical handout. The presentation of my work is as important to me as the presentation of my material.

Ironically, I just got feedback yesterday from the previous time I taught the course, and here’s an excerpt:

“A printed workbook should be a requirement for all future courses- people learn differently and something to follow along is really important to some”

That I wouldn’t print. A handout is a small cost per person. Most presentations I’ve attended wouldn’t even provide such a thing in any format, and my 70 page e-Book would be way too much of a cost to bear for me, so there is a line.

So I’m curious… what are your thoughts? Presenters, what do you provide, and why? Attendees, what do you prefer and/or expect from your presenter? Obviously cost impacts expectations, but assume you were paying $100 to attend an all day Excel course… what then?

Trigger Conditional Formats Before Printing

A little background

My staff and spreadsheet users will tell you that any time I build a spreadsheet, there are always shaded cells on the grid. I preach that “Green means go”, and make sure that any cell they enter data in has a green background. I also use blue backgrounds for “update these sometimes” cells, like tax rates. If cells are left with no colouring, though — everyone here knows that they should be left alone.

The case I was working on today was a development report, of which I have a few data entry cells scattered throughout the overall file. You could argue that this violates a good design principle in that you should keep the data separate from the report, but in this case I’m comfortable with the design. I have shaded my input cells in green, and know that I’m not going to lose track of them.

The problem that I decided to address today was that the green backgrounds print. While they’re great for telling the user where to input data manually, our Asset Manager isn’t really interested in seeing that kind of info. In fact, it’s distracting, so better left off. But how do you do that?

I could create a full new report that essentially duplicates what I’ve got, but then it’s both a maintenance headache and performance hit. Not really where I want to go. Instead, I opted to use a conditional format to hide the colour on the cells when I go to print the sheet. This would have been really easy, if it weren’t for the fact that I wanted to do it automatically. That involved dipping into some VBA. If you’re not comfortable working with VBA though, read on anyway. You can still do the setup for this and control it manually.

I’ll get into the steps I used to solve the issue in just a sec, but I wanted to say that, to me, this article illustrates three things:

  • If you aren’t aware of conditional formatting, you should be
  • If you don’t know VBA, you should learn it
  • There should be a Workbook_AfterPrint event in VBA

Step 1: Setting up the Control Point

The first thing I decided to do was to create a cell to control what mode I’m in. i.e. Printing Mode or Working mode. There’s lots of ways I could have done this, but since I have a worksheet dedicated to being a “Control Panel”, I set it a cell on that worksheet to hold a “Yes” or “No” value:

As you can see, I use this approach to drive a few other things as well, such as dates for column headers, the type of data pulled from a PivotTable and more.)

Before someone asks why not True/False values, I opted to use Yes/No as it’s more readable to others if they have to work with the spreadsheet.

Step 2: Name the Range

The next thing I did is assign a name to the PrintMode value. In this case, I assigned the name rngPrintMode to the cell B11 on my control panel worksheet. While this isn’t truly required to make this work, I just find it much easier to work with the named range later, rather than try to remember the cell reference. It does become key when you move to the VBA side though. VBA doesn’t update its reference to the cell when a line in inserted above, while the name range does. For this reason, I always used named ranges when referring to worksheet ranges in VBA.

Step 3: Set up the Conditional Format

The steps shown below are for Excel 2007, but with a little ingenuity, I believe you could make this work in 2003. I’m assuming that you already have a green (or other) background on your data entry cell at this point.

  1. Select the data entry cell(s) that you want to hide the colour on
  2. On the Home TabàConditional FormattingàNew Rule then create a formula as shown below:

  3. Click Format
  4. From the “Fill” tab, click “No Color”
  5. Click OK twice

At this point, the conditional format has been set up. As an optional step, I actually also set the “Stop If True” option in 2007, as this allows me to use multiple conditional formats on the same section, yet ignore them all for printing. You won’t find this in Excel 2003 though. (Also, if you want to stop all formats, make sure the rule is at the top of the list!)

As far as the setting up the conditional formatting, you’re actually done. Change the value on the rngPrintMode cell to “Yes”, and your conditional formats should kick in, removing any applied background colours. Now you can print without the data entry cells conspicuously marked! Change it back to “No”, and the colours re-appear.

Just a hint here: if you want to leave this as a manual control, I’d suggest setting up a data validation list to control the field. Debra Dalgleish has a great article on doing that which you can find right here.

Step 4: Adding Automation

Personally, I don’t do manual if I can help it. Maybe I’m pretty lazy, but I want this to automatically kick in when I hit print, and set itself back once it’s done. So to do this, I added a couple of procedures to my project.

In a STANDARD MODULE, I added the following:

  1. Public Sub PrintingActive(Optional bStatus = False)
  2. 'Date Created : 5/11/2009 10:48
  3. 'Author  : Ken Puls (www.excelguru.ca)
  4. 'Macro Purpose: Toggle the printing mode variable to control conditional format set
  5.  
  6. Dim rngInfo as Range
  7.  
  8. '!!! Set Your Worksheet Here !!!
  9. Set rngInfo = Worksheets("Control Panel").Range("rngPrintMode")
  10.  
  11. 'Check the status and set the cell accordingly
  12. If bStatus = True Then
  13. rngInfo.Value = "Yes"
  14. Else
  15. rngInfo.Value = "No"
  16. End If
  17.  
  18. End Sub

You’ll need to change your worksheet name for the one that holds the named range you defined earlier. Just change “Control Panel” to “Your Worksheet Name”.

Then, I went to the THISWORKBOOK module, and put the following in it:

  1. Private Sub Workbook_BeforePrint(Cancel As Boolean)
  2. 'Date Created : 5/11/2009 10:34
  3. 'Author  : Ken Puls (www.excelguru.ca)
  4. 'Macro Purpose: Toggle the conditional formats for printing
  5.  
  6. Call PrintingActive(True)
  7.  
  8. Application.OnTime Now + TimeValue("00:00:01"), "PrintingActive"
  9.  
  10. End Sub

At this point, providing you had things working manually in step 3, things should just work by hitting the print button. The BeforePrint routine will call the PrintingActive routine, which will flip the rngPrintMode variable to TRUE. This will in turn trigger the conditional format rule. The Application.OnTime then schedules a call of the PrintingActive routine to run in one second. The worksheet(s) are then printed.

The part that bugs me about this approach is that I have to rely on using OnTime, which kicks off a routine based on time, not necessarily when my existing routine completes. Theoretically, if I had a really huge print job, the call to PrintingActive could be triggered before my job is complete. In my tests that didn’t happen, but if it does, just add a few seconds to the TimeValue. I.e. “00:00:15″ will turn it into a 15 second delay.

The ideal solution would have been to leverage a Workbook_AfterPrint event, but sadly one does not exist.

Conclusion

For my needs, this works. I now just click the print button and all of my green cells change their backgrounds to white (transparent), the file prints, and then the colour(s) are reintstated. And it all happens without me having the change the cell value manually.

Turn data on its ear

I recently taught a course and, while this wasn’t actually covered in the course content, we did discuss it. I’ve found that every time I demonstrate this technique, it gets some “oohs” and “aahs”, so I figured that I’d share it here.

The question I was posed is “I have a spreadsheet where the data is set up across rows, and I’d like it down the columns. Is there an easy way to do that?”

It is a fairly common request, in my experience. One specific example I remember helping a friend with was a list of product and serial numbers that they pulled from a system. I’ll pretend that it looked a little like this:

In this case he wanted to change it to a column of item descriptions and serial numbers. It was a lot more columns of course, and was floored when I sent it back to him within a minute. (It took longer to open the file and respond to the email than to fix it.) Here’s how you do it:

  • Select the data in A1:E2
  • Right click and choose “Copy”
  • Click in another cell (I chose A5)
  • Right click and choose Paste Special
  • In the dialog box shown below, ensure you check “Transpose”

  • Click OK and you’re done

The completed data looks as shown below:

And yes, if you want to go from columns to rows, it works too. Same steps exactly.