You know those cool keyboard shortcuts – the control key combined with another key, like CTL+C for copy? You can make Excel do your own keyboard shortcuts for more than just simple commands. That’s a macro.
Let’s say you’re on the receiving end of raw data dumps from an external source, and the first part of your job is importing the files into Excel, or just opening them in Excel. They’re full of extra blank columns, columns you don’t want to use, not formatted the way you need them to be, etc. So on every single file, you go through and clean them up, configuring them the way you need them – the exact same series of steps on every file. It’s gotten to the point the letters have worn off those keys.
You can turn all those steps into one keyboard shortcut. That’s a macro.
Macros are great time savers and productivity enhancers, and far easier to use than many think – for some reason, they seem to intimidate most of my students when we get to that chapter. What follows is specific to Excel 2013; it will more or less work in other versions – YMMV; you may have to do some research.
Trust me on this, it’s easy. This will not be a complete macro tutorial, just a primer to get you started. Once you know how to record one and assign a shortcut, you have much of the value of a macro. There is more, but this is the simple stuff. This simplified set of instructions will only work with a default Excel installation. If you’ve already been using macros, and customized how Excel works with them, then these instructions may not work, and you probably don’t need them.
- First Fun Fact About Macros: Any time you find yourself doing the same thing repeatedly, make a macro.
- Second Fun Fact About Macros: You can share macros! You can copy, paste, email, import, export and otherwise share macros on other computers, files, and users.
- Third Fun Fact About Macros: WARNING: Macros are executable code – like a virus or other malware. Do Not Use, Download, Open, Run, or otherwise have anything to do with a macro unless you built it or you know who did. In fact, Excel won’t even let you keep it until you change the type of workbook your macro is in, and, Windows will act as though you had brought in bubonic plague when it sees a macro until you promise it’s OK.
- Fourth Fun Fact About Macros: yes, macros are written in Visual Basic – that’s the “executable code” – but don’t worry, you don’t do the writing. You just tell Excel “watch this” then do what you do. Excel will record your actions (aka “macro”), writing the VB code for you, until you tell it to stop.
Here are the steps for making (recording) a macro:
- Remember, the reason you decided to make a macro was because you were repeating yourself. So you should know what it is you are going to do. Feel free to make notes.
- First, you must enable macros by enabling the Developer Tab on the Ribbon bar.
- Open the Excel Options dialog box:
OPTION 1: Click on File, Options, Customize Ribbon. Or,
OPTION 2: One after the other, press and release the Alt key, then press and release the F key, then press and release the T key, then press and release the O key.
Either option will open the Excel Options dialog box.
If not, please check to see whether you are now holding down the Alt, F, T, and O keys. If you are, let them go, then go back to step 2, OPTION 2, and read it very, very, carefully. We’ll wait.
- If you’re not already there, In the Excel Options dialog box, on the left side, click on Customize Ribbon.
- In the Customize the Ribbon section on the right, make sure Main Tabs is selected in the drop down (should be, it’s the default), then look down the list to find the Developer. If its check box is clear, click on it to place a check mark in it. It should look exactly like the figure above.
- Click OK at the bottom.
Now, macros are enabled and you are ready to record a macro. It’s called “record” because that’s exactly what happens. Excel records your actions as you carry out the steps you have been repeating.
- Before you start recording the macro, decide what the name will be, and decide what the shortcut key will be. Best practice is to use an upper case shortcut key, and try it on a spreadsheet before you start recording. You want to make sure it is not already in use by Excel or Windows. If it is, you can still use it, but you will lose whatever it was doing before. If you want to keep its original function, select another key and test it. The shortcut key will always be the Control Key followed by whatever key you have decided on. If it is a capital letter, and it should be, then the shortcut key combo will be, for example, CTL+Shf+T. That’s the Control key, and the Shift key, and whatever other key you picked, pressed all at the same time. That’s what the plus sign means – “press these together”. If it was one after the other, they would be separated by a comma instead of a plus sign.
- On the Developer tab, in the Code group, click on Record Macro.
- In the Record Macro dialog box, fill in the prompts. For the moment, make sure to Store Macro in This Workbook. The Description should tell anyone managing/editing this workbook after you what the macro is for.
- Click on OK and carry out your procedure. Don’t worry about errors, correct yourself and keep going. Worst case, you’ll have to delete the macro and start over.
- When you are finished, there are two ways to stop the macro:
OPTION 1: Where you clicked on Record Macro, it now says Stop Recording. Click on that. Or,
OPTION 2: At the bottom left of the Excel window, on the status bar, just right of the READY prompt, is a small square. If you hover over it, a popup will say “A macro is currently recording. Click to stop recording”. Click on it.
Voila! You have now made a macro. To test it, open a new spreadsheet tab (the plus sign at the bottom) and use the shortcut key. You should switch to a new spreadsheet tab because the one you were on already has the macro action done to it and it may be difficult to tell what changed when you press the shortcut key.
Now, this macro works on this spreadsheet that you had open when you created it. To make it available in Excel whenever you open Excel, with any spreadsheet, simply select Personal Macro Workbook in the Record Macro dialog box in the Store macro in drop down list when you record it.
Ready for a little advanced macro stuff? Get your geek on!
Now that you have a macro, click on Macros in the Code group on the Developer tab on the Ribbon. The Macro dialog box will open. Select the macro you just created in the Macro Name: section, then click on the Edit button on the right side. This opens the Visual Basic Editor to the code for your macro. Some things to look for, beginning on the left side in the Project window:
- In this case, the spreadsheet’s file name I created a macro in is found in the line VBAProject (MACROmod4 SAMPLE FINAL PRODUCT DATA REPO…) In your case, your file name will be seen as VBAProject (your file name)
- If it is not already expanded, click on the plus sign to the left of this VBAProject line, then again as needed to expand the structure.
- Look for the section named Modules
- Immediately underneath it is Module1 – this is your macro. If the code for it isn’t already open on the right, double-click Module1 and it will be. Try to read the code, it might be surprising.
- Now, click on the File command at the top left. Note the Import File and Export File commands. Yep, they work exactly as you would think they would – you can export your macro and save it, thus making it available for email and such. And, likewise, you can import a macro someone has sent you.
- The macro – Module1 – in this location is in the workbook you were working in when you opened the VB editor. You can move it to the Personal Macro Workbook, making it available to any spreadsheet in Excel. Simply click and hold on it, then drag it to VBAProject (Personal.XLSB) and drop it there, not in the Modules section below. Excel will take care of putting it where it belongs. Save.
- When you’re done, click on File again, then Close and Return to Excel, then save and close Excel. Be sure to save when prompted. You’ll have to change the file type to a macro enabled workbook – don’t worry, Excel will prompt you. Reopen Excel and try the shortcut again. If you get a warning, just accept it – it’s the macro setting off Windows’ plague alarm. Since you created this one, you know it’s not going to mail your secret folder to everybody in your contacts folder. Probably.
That’s it. Now you know how to record a macro and how to share them. As always, feel free to email if you have questions.
What with Veteran’s Day and all, I had occasion to reflect on bravery this morning – among other things, it was actually an assigned lesson in Bible Study. I would suppose most of us have seen brave men and women at some time or another. The first one I can remember is the guy that defined bravery for me.
Greg Welch was trying to teach me to be a rodeo bullfighter, or clown, as it was called then. It never took, since I would never get close enough to the bull, except for one occasion, which did not turn out well, though the bull seemed unperturbed.
A young boy, perhaps 12 or 13, had come off the bull but his hand was stuck under the rope and he was being thrown around like bait on a line – why his arm didn’t come off at the shoulder I’ll never know. None of the bullfighters could get in to release his hand because the bull was trying to gore the boy; throwing his horns back at the boy. Sooner or later, the wildly swinging boy and the horns were going to intersect.
I was about a half step behind Greg; I could clearly see the look in his eye as he focused on the bull’s swinging head. I realized he was timing the swing. He stepped in as the horns swung away and threw himself on the bull’s neck and hung on like a vise clamp. That let the rest of us get in and get the boy freed. I saw the horns hit Greg in the back three times, but without the leverage they would have had on the boy. Still, I don’t imagine Greg enjoyed it. In fact, I know he didn’t.
Getting off was almost as tricky; as he had to wait for one of us to get the bull’s attention, though one of the bullfighters managed it well enough. He yelled “Greg!” to get Greg’s attention, when Greg looked up, he ran in front of the bull and slapped the crap out of it, then took off running as Greg let go and dropped. The bull went after the bullfighter.
Greg had awful bruises on his back, and the boy had a dislocated shoulder, but that was it. Greg, my beer drinking, always in trouble, rodeo clown, girl-chasing teacher. I would never look at him the same way again. The image of him stepping in carefully, thinking about it as he watched the swinging horns before he jumped in, is still with me more than three decades later.
I was in one of the first, if not the first, jump school classes with girls in it. At jump school, you jumped out of the plane 1,250 above the ground. A typical jump from that altitude normally takes less than 2 minutes, and the jumper hits the ground at about 10-13 miles per hour, depending on many things, such as weight, etc. On our third jump, a female lieutenant had two malfunctions in the air, back to back – one equipment, then one process. First she had a partial inversion, aka “Mae West”. Then, as she followed exactly correct procedure to deploy her reserve, that procedure failed her.
The Army Parachute School has a PA system only an Army could have; we called it “The Voice of God”. 1,250 feet in the air, you’d hear “Jumper! Check Your Canopy”. The Black Hats (instructors) on the drop zone monitoring the jump would have spotted someone with a malfunction who was not reacting to it. The story is that there has been more than one occasion when this announcement was met by the sky turning white as a large number of jumpers with absolutely no problem promptly pulled their reserves (the main parachutes are camouflaged). This could lead to problems, of which more of in a moment. At any rate, the Black Hats saw she was in trouble and announced, though apparently she needed no warning. She was already hard at it. What the announcement did do was get the attention of everyone who’d already completed their jump and were at the assembly area waiting for the buses. What happened next could not have had a more spell-bound audience.
When you have any canopy above you at all, simply letting the reserve out can let it foul with the main. So you don’t. One hand holds the case while the other hand pulls the handle, then you reach in and pull it out to throw it away from you, so it will clear the main when it inflates.
She was a small girl. What worked in ground school, when she was standing on the ground, both feet firmly planted, failed in the air when her one arm simply couldn’t generate enough power to pull the reserve handle without the leverage assist lost to feet and legs simply dangling below her harness, unable to brace.
Finally, she just used both hands to pull the handle. The reserve deployed, went into the main, and fouled there.
She pulled it down, still following procedure, and threw it out. It fouled again. Once more, she pulled it down and threw it out. It fouled again. Again, she pulled it down and repeated the same procedure that was her last chance at life as she fell, now considerably faster than normal.
This time, it cleared the main and inflated properly, just in time to check her speed enough. Then she hit. I saw her bounce as high as the now deflating canopy. I believed she was going to be critically injured, but that last minute inflation had done its job even though it had to have been far over the design limits by the time she got it inflated.
Black hats closed in on her from all over the drop zone. After a few minutes of fuss, they loaded her into a jeep and drove her to the assembly area where the rest of us were. She was apparently unharmed, and not all that shook up, though she was pale and quiet. She was normally a cheerful and outgoing young lady, sort of the California surfer chick stereotype, if on the small side.
That was the morning. The afternoon jump went without incident; I’m sure I wasn’t the only one keeping an eye on her.
At the assembly area, we were to put our kit bags, into which we had stuffed the now deployed parachute, and then clipped our unused reserve to the outside of, in a line where we eventually got on a bus to return to main base from the drop zone. This being an Army school, when we were called into line, it was very rigid, by the numbers. On command, we would squat, grab the bag by its handles, straighten up, and again on command, march onto the bus, and take our seats.
As she grabbed her bag, she accidentally grabbed the handle for the reserve. When she stood up, she pulled the handle and the reserve parachute deployed into the parking lot. We all stood there staring at it, expecting the Black Hats to go into the ritualistic screaming of an outraged NCO with a miscreant to torture.
They were smarter than that. As a couple of the sergeants walked over to her, I realized tears were streaming down her face as she sobbed quietly, still holding the reserve handle. They packed her bag for her and got her on the bus. She cried through the last formation, cried through dinner at the mess hall and cried at the club, where some of us thought we’d take her for a few beers. She was still crying when I left.
We’d known she’d been upset that morning, any fool could see that. We were impressed that she was there for the second jump, but it wasn’t until that moment that we realized how scared she’d been when she came back for the second jump that day.
She was there for the last jump the next morning, and made it just fine, graduating and getting her wings with the rest of us.
AKA the opening battle in the last great war of the 20th century, the Desktop Marketing War, ultimately won by Windows via Microsoft’s marketing strategy. In which I discuss the animosity between the loyal, stalwart, and superior in every way Apple adherents and the ignorant, lowlife simpletons clinging bitterly to their Windows trailer-park “computers”. This will be a more-than-usual mean-spirited, bitter rant, awash in a sea of sarcasm. It’s a harsh world. You have been warned.
TRIGGER WARNING – RANT ENSUES: Herein I am less than kind to a class of IT denizens stereotyped as socially unskilled geniuses living in relative’s basements. These unfortunates share a common trait relevant to this post; a religious belief in the purity of some technologies over others. For example, a hunting sling (David’s sling) is somehow more moral than an atomic bomb. Or Windows, and especially Microsoft, are evil, but Macintosh was delivered by a benevolent Earth mother goddess because Apple is pure of heart and the salvation of nature.
Having had to work with such fools, or work at undoing the damage some of them (aka “hackers”) cause, I am unsympathetic to their feelings, their claims, or their notion of logic. I barely tolerate them near keyboards, and never unsupervised. I am not objective, I am biased, prejudiced, discriminatory, intolerant, and completely out of patience with these luddites. They lost, it’s time for them to move on.
Also, I will generalize and assert unsupported. Offensive, I know, but it is a harsh world. THUS ENDETH THE RANT.
It is common knowledge; i.e., “everyone knows”, that Bill Gates stole the idea for Windows from Steve Jobs through legal chicanery and influential friends in high places on Wall Street, that den of iniquity that is the downfall and ruin of America and all that is good in the world. Also with help from international conspiracies. And Satan.
“Everyone” is ignorant, spending too much time listening to ganja addled adolescents editing Halo maps in their grandmother’s basement thinking this makes them programmers. These poor misfits like Mac because the interface was then easier to use and they believed with a passion born of desperation that their mastery of Apple PCs would make girls think they were cool. Unfortunately, as in so much else, they were wrong, but they clung to their belief in the Mac OS desperately.
These ideological worshipers of a technology they would never understand took offense when the Harvard dropout’s marketing strategy put his arguably inferior product first in the wallets of those ignorant consumers who should be listening to them. They still hold to this even though Apple’s implementation of a cell phone has given Apple a clear victory in the money game- that Windows holds an equally clear lead in the desktop market still leaves a burning in their hearts; they will still claim that is only because Bill cheated and is immoral; Microsoft is not worthy:
“The only problem with Microsoft is they just have no taste … They have absolutely no taste. And I don’t mean that in a small way, I mean that in a big way, in the sense that they don’t think of original ideas, and they don’t bring much culture into their products.” – Jobs, 1996 public television documentary “Triumph of the Nerds.”
Here are some essential facts pertinent to the claims that Bill stole Windows from Steve:
- Apple did not invent the idea of “windows” for an operating system. They copied that idea from somebody else.
- Copyright protection does not protect ideas, but rather, with some limitations, the expression of an idea; how an idea is implemented. You can patent a ladder, you can’t patent the idea of climbing.
- Xerox* invented the graphical user interface technology (GUI), the technology behind both Windows and the Mac OS.
- Xerox was visited by the Mac design team, where they saw Xerox’s GUI.
- Apple licensed elements of Xerox’s GUI for its OS.
- Microsoft licensed elements of Apple’s GUI for its OS. Of 189 claims of copyright infringement, the court ruled that 179 were covered by this license agreement. (FWIW, Steve Jobs had resigned from Apple by then).
- Copyright protection extends only to original expression. Of nearly two hundred separate claims of copyright infringement in the lawsuit, many were based on their license from Xerox and so failed because they were not original to Apple, or because there was only one possible way of expressing the idea.
“Much of the court’s ruling was based on the original licensing agreement between Apple and Microsoft for Windows 1.0, and this fact made the case more of a contractual matter than of copyright law, to the chagrin of Apple.” – wiki
So, given that Apple is not run by idiots, and they knew the facts as well as anyone, why did they sue?
Simple. Microsoft was taking Apple’s lead in the PC market away from them through superior marketing. Considering the lawsuit, it appears that Apple believed that Microsoft had taken advantage of their licensing agreement to build a product that was more competitive, but still based on Apple’s idea. If so, they were wrong.
As always, PC history seems to depend on who is telling; many of the players are still around and have yet to come clean. This is the way I see it:
Bill beat Steve through marketing, not technology. It is especially telling that when Apple supporters do realize that Microsoft’s business processes, common throughout the history of business, are responsible for beating out Apple’s arguably superior technology in the minds and wallets of consumers, these supporters, who know absolutely nothing of business, then claim that these common and traditional business processes are somehow cheating and immoral, and should be prevented by law. Preferably as a capital offense, with public hangings and beheadings.
Specifically, that Microsoft will buy or license applications from others and incorporate them in Windows, such as various browsers; doing that is somehow cheating and immoral.
Understand what happens here: Microsoft recognizes an idea they would like to use in Windows when they see someone else express that idea in an application that is doing well in the market. Microsoft could do their own development and then bring to market their expression of this idea and risk competing with the original application in the market. If they lose, all the money Microsoft put into it is lost. Or, they could reduce a lot of risk by simply asking the original application owners whether they’d be willing to sell it or license it to Microsoft. For money. Like, you know, filthy lucre, ‘cuz Apple supporters are far too moral to care about profits and such. Apparently their bills are paid by the magic bill fairy.
This further outraged some Apple supporters, who thought it should be illegal. OBTW, this is exactly what Apple did when it licensed a GUI from Xerox. Apparently it was moral enough when Apple did it.
Let me make this one thing clear: At least at the beginning of this competition, the Mac OS was a superior operating system, and did many things better than Windows did for a very long time. Among other things, it was probably much easier for a newcomer to learn. Then, and now, it enjoyed a performance edge in graphics applications so much so that it is still a force to be reckoned with in that market. If you want an Apple, go for it, you will be happy with it and be productive as anyone else. It is a very good computer; their laptops are things of beauty and a joy to behold. I use an iPad mini myself, and I’ve borrowed a Mac laptop for business use. I’m writing about the constant refrain of “bad, bad, bad” that comes from a segment of the Apple community that I wish to correct, not the value of Apple products. You decide that. Personally, I like them enough to use them along with the Wintel boxes I’m using. Between my wife and I, we have 5 Apple systems, 2 WINTEL platforms, and I’m about to develop at least one, if not as many as 3, Linux laptops. Of course, the workhorse of the place is my Frankenstein PC, of which I’ve written about elsewhere, but the others get their share of use. My PC won’t fit in my briefcase, anyway.
Window’s performance in the market is not due to any advantages in technology, or performance. Windows’ advantage came from a price advantage, Apple systems tended to be pricey compared to the Wintel platform.
“By 2000 a Mac product was costing ten times more than Windows to develop, and its market was about ten times less than Windows” – Tim Berry
Where did that price advantage come from?
What is marketing? Contrary to popular belief, it is not advertising. Marketing is the study and management of exchange relationships.
Oh, well, that’s clear then. What the heck is an “exchange relationship”? An exchange relationship is a voluntary exchange of values (aka a “trade”) between two participants – a sell, and a buy, between two willing participants who come to a mutually beneficial agreement on the value of the transaction. Marketing is about all of these events and all of the people that participate, aka a “market”, such as the PC market, or the car market, or the housing market. Who’s buying, who is selling, what are specific products going for, are prices rising or falling, and why? What product is most popular (aka market share)? How can I get my product into a market, such as when starting a new car company? Where can I sell my idea, i.e., find a market – I have a new widget, who is buying things like this? Then there’s actually creating a market where there was none – somewhere, somebody made the first purchase of the first cell phone, and the cell phone market was born! Or, is this just an extension of the market for telephones? How does the market for a phone with a wire differ from the market for wireless phones?
Let us now consider the marketing for software. Such as Operating Systems like Windows, Mac OSs, and their predecessors, such as CP/M, Commodore DOS, Atari OSs, GEOS, MS-DOS, and others. A vaguely generalized timeline of the PC market might look something like this: Research/academia, hobbyist/special interests, business world, home computing, more or less in that order with lots of overlap.
Each step, or growth in the size of the market, gave rise to the next. For example, in the research/academia step, almost all PCs are strongly related to minicomputers, hand-made one-offs made by their ultimate users, or for single use purposes. This led to the hobbyist/special interest market, which grew to a size that some thought there might be a profit to be made from meeting this demand. IBM’s decision to get into the PC market is attributed to the growth in this step of the market.
At that time, the “industrialization” of the hobbyist market, anyone could order a wide variety of PCs from a variety of manufacturers, with a wide range of compatibility, or incompatibility. This hardware did not then automatically include software, which could be ordered as one of many options, or bought elsewhere – and often was. There was a wide compatibility gap – most software simply would not run on most boxes; you had to ensure the software you wanted ran on the box you wanted. A subsidiary market grew for the more popular PCs; those manufacturers wanted you to buy from them when you bought software, so they licensed software from the developers of that software and listed it as an option, promising to only offer compatible software, reducing the buyer’s risk.
Typically, for example, a PC you wanted to buy had more than one option for an operating system, for a range of prices. Which one you ultimately selected depended on many factors, including what you were going to do with it – research, publication, gaming, or whatever.
A common arrangement among the distributors of the PC and the software developers, such as Operating System (OS) developers, was that it would license the OS for a fee. Every time that OS was selected by a buyer for a PC, the developer would get a share of the proceeds from that sale. To be clear: Bob, a programmer, develops Operating Systems. So does Les Lee. Steve, a programmer, writes applications, such as NotExcel, or Games, such as WhackaMoleOnline. Elise, a programmer, writes software for video cards, or game controllers. Darla, Justin, and Lacey each run their own little business making their own computers for people to buy. Vanessa, a budding entrepreneur, started a company, NewBraunfelsBrainBox, Inc., reselling computers she got from Darla, Justin or Lacey, with software she gets from the developers. Vanessa puts out a catalog that includes all of the computers, and lists software as options for the computers. It all works a little like this:
- Justin, with a known reputation as a serious gamer and hardware guy, builds a computer a lot of people want to buy because they figure if Justin designed it, it will be awesome.
- Justin pays Elise a license fee to include the video cards and controllers she writes software for.
- Justin and Bob get together to make sure Bob’s OS runs on Justin’s computer. They have a financial arrangement that depends on how many sales each has.
- Elise has to pay Bob a license fee to make sure her software runs on Bob’s OS.
- Les Lee also pays Justin a fee, and Elise has to pay Les Lee a fee too, if she wants to get the info she needs to make sure her software runs on Les Lee’s OS.
- Repeat as needed for computers built by Darla or Lacey
- NewBraunfelsBrainBox lists all these in their catalog.
- Apple sells their own computers through their own outlets.
See where this is going? everybody’s got some skin in the game, but they all depend on the buyer deciding to buy the computer and the OS that they have software available for. If the buyer picked something else, oh, well.
Then Bill made a deal with IBM for an OS he had bought from somebody else and modified for the IBM PC under a set of arrangements that left Microsoft free to license this new Microsoft OS to anybody else he wanted to – such as competing, existing PCs you could buy out of Vanessa’s catalog. Bill could enter into a license agreement with these PC distributors and compete with all the other software developers. And it was very competitive. Each software licensee wanted as much as he could get, but the harsh reality was that if your OS was a 350 dollar option with a PC, and somebody else’s was a 240 dollar option, you might not get as much market share as you’d like. Your product (the OS) would have to provide at least 110 dollars more value for people to buy it, or at least they’d have to believe it did.
Then Bill came along to make a deal with the PC distributors, NewBraunfelsBrainBox, Inc. Negotiations opened up with the expectation that Microsoft would ask for a lot; after all, their operating system, MS-DOS, was running IBM PCs! It must be better!
Bill had a better idea. His licensing fee was very low, but with a switch. He was not asking to be paid a fee for every computer Vanessa sold with MS-DOS, he was asking her to pay him a very low fee for every computer Vanessa’s company sold, regardless of what OS was on it. In return, NewBraunfelsBrainBox, Inc. could sell, or not, as much MS-DOS as their heart desired.
Got that? Every computer they sold would already have MS-DOS factored into its price, regardless of what OS the buyer chose. Oh, NewBraunfelsBrainBox offered it as an option anyway – Vanessa not being born yesterday. For a very low fee. Now the user is looking at a set of optional operating systems for his computer that might look like this:
- $350.00 BeOS
- $240.00 U/PM64
- $40.00 MS-DOS
How many copies of DOS do you think were being loaded on PCs? Obviously, Microsoft’s market share grew. Wait. It gets better.
Wanna take a wild guess what Bill’s license fee was to the developers and programmers, so they could write their software to run on MS-DOS? In mid-1990, Microsoft gave away the Microsoft Solution Developer Kit, everything a developer needed to write applications compatible with MS-DOS. In other words, the license fee was zero. Guess what OS had more applications and accessories available to it than the others?
At the same time, Apple computers, which required proprietary software and was not sold through other distributors, or even built by others, required a stiff license fee for a developer to acquire the rights to write compatible code. So, the availability of application software and accessories for Apple computers was relatively limited compared to Microsoft compatible computers. Which were cheaper, too. And ran more software. Like, way more games. And had IBM selling to the business market. Plus, there was Justin’s Serious Gamer Box. Hard to beat.
So, if you specified DOS as the operating system for your new PC, it was cheaper than if you ordered any other OS. And, it had more applications, games, and accessories available than a PC with another OS, including Apple PCs. Pretty soon, more people were buying PCs with DOS than anything else. When Microsoft then started offering Windows as their new and improved Operating system, they had a ready –made market for it, along with a bunch of developers familiar with and ready to do business, writing games, applications, and software for accessories.
Microsoft’s market share grew and grew. People bought computers running on Windows more than any other kind. Microsoft became huge, and, for a while, Bill was the richest man in the world. He bought Apple and changed the name to Peaches.
Ok, I made that last part up. But even though Apple not only overtook MS, but passed it in sales, that was because of the iPhone, not PCs. Microsoft still dominates the desktop operating system market. And many Apple worshippers hate the thought. Life sucks when you’re wrong.
Victory was ours! Bwaahhahahahha… the forces of darkness, and Microsoft, Reign Supreme!
So, there I was cruising down the campus main drag, very like some open road racer of the 20’s, with oil flying in my face because I had taken off without a valve cover, at all of about 10 miles per hour. Grinning like a Cheshire cat at this tractor, moving under its own power for the first time in something like 5 years.
At least this time it had an ignition switch and a radiator. The first time we got the motor running, I had to kill it by knocking three spark plug cables off with a broomstick before the engine block melted.
Don’t look at me. Of course there’s a redneck behind this. What were you expecting, an IBM engineer?
This wasn’t the first time a redneck had gotten me into something fun. A few years before, some of the rednecks in the dorm invited me to a buckout. Honestly, they meant well. Few people have been as kind to me, or as generous, as rednecks have, but they do live a different, and far more exciting life, than most. I was a coddled army brat whose most adventurous activity up till then had been failing to ride motorcycles and swimming very fast. By going to an East Texas school, that would change forever. Almost 4 decades later, my memory might be a little fuzzy about some of the details, but what follows is what I remember. Mostly.
After a semester of learning to be a rodeo clown, I was astonished to learn they thought I was crazy for going to jump school. These are guys who routinely paced the bull, running directly ahead of it trailing a hand behind them so they could feel its nose behind them, to keep track of it. And I‘m the crazy one?
A buckout is an informal rodeo in which not-ready-for-prime-time livestock is used for practice by rodeo cowboys. The whole enterprise is relatively unsupervised, and near-misses are common. So are injuries. I saw a young boy go to the hospital with broken ribs when a bull learned a trick of slamming a rider sideways into the ground. The boy went to the hospital and the bull went to the glue factory – now that he’d learned that trick, he was unrideable.
So I learned to be a rodeo clown. They are no longer called clowns; probably swept up in the PC craze, but as they are now called bullfighters, it is far more accurate. I saw my instructor throw himself on a rampaging bull’s neck to keep it from goring a young rider whose hand had become jammed in the rope, giving the other bullfighters time to free him. So the bull got my instructor in the ribs a few times instead. He thought it a fair trade, since the boy got to go home. They still dress funny, and the kids still love them.
I don’t remember whose idea it was, but there was a stunningly beautiful blonde East Texas girl involved. She was very nice to me, too. I was never a good rodeo clown – as my instructor told me “you need to get a whole lot closer to the bull”. When I finally did, the bull ran me over.
One thing I had learned is that unless the bull is actually after you, they prefer not to run into things – I’d seen the bullfighters divert a bull time and time again by stepping directly in front of it and waving their arms and hats at it. The bulls would almost trip over their own feet stopping or turning away.
Of course there was a trick to it, known by all rednecks, and in their innocence, they would be astonished to realize I did not know it.
On that fatal day, a rider had gotten fouled coming out of the chute and badly injured his ankle. He was unable to stay on the bull and was almost immediately thrown, falling almost directly in front of the bleachers. The bullfighters did their job and had the bull chasing them off into the far reaches of the arena. Buckouts are family affairs, and in short order, the rider’s mother, sisters, girlfriends, and assorted family and friends were over the fence tending to him.
At which point the bull got away from the bullfighters and came back to kill the rider.
Make no mistake – this is a sport to us, but the bulls deeply resent being ridden and have every intention of killing the rider. Every now and then, they succeed. Bullriders didn’t used to wear helmets and flak jackets – that’s a response to the death toll. Keep that in mind when you consider what the bullfighters are doing.
I was kneeling next to the rider on the ground when I heard the audience screaming and looked up in time to see the bull coming. It had almost gotten to us when I stepped over the prone rider, took off my hat, and strode confidently towards the bull waving my hat and arms at him and shouting loudly as I had seen the bullfighters doing.
The bull never wavered. I went down like a blade of grass in a hurricane. I don’t know what happened after that – apparently others pulled the rider away and the bullfighters got the bull back under control.
As I recovered my senses, I had some awareness that whatever had gone wrong, I’d just bravely sacrificed myself in front of the stunningly beautiful East Texas girl, along with everybody else I knew from school. I focused on my instructor looking down at me, his brow furrowed, asking me “What the heck is wrong with you? Why did you do that?” Then he turned and stomped off, waving his arms in the air and shouting something about casting pearls before swine.
Eventually I learned the redneck trick. Bulls are really stupid and not all that fast to think and adapt to new situations. If you’re going to step in front of a bull to stop it, you have to give it time to see you. Who knew?
Apparently every redneck at the buckout that day knew, as henceforth I would be known as “that idiot that got in front of a bull”. I gathered my instructor’s reputation took a severe hit.
The stunningly beautiful East Texas girl was still nice to me but now there was a flavor of pity about it, and she was always checking to make sure I’d gotten home safely. The magic was gone.
So I had some really good friends who taught me new and interesting things. As the end of our time at college approached, we were constantly checking degree plans to make sure we had everything we needed, and I and a friend of mine needed one course of absolutely anything. Of course, the goal was always something easy. An easy A, preferably. So, my friend, who shall remain nameless to protect his family, had this great idea for a really fun and easy course. The ag department had a course called Farm Power in which the students would restore a tractor and then sell it at the end of the term, pocketing the profits and getting an easy A, having learned quite a bit about tractors – arguably a useful subject for agriculture students. My friend who shall not be named had a tractor, and would fund the entire project; he just needed a partner as the course required two man teams for safety. Remember that. I teamed up with a redneck for more safety. To be safe. Oh, the irony! Is the phrase “here, hold my beer” at all familiar?
I did know something about auto mechanics, so this seemed easy enough – I imagined some old tractor in need of a tuneup and some parts and labor – simple enough for a once a week afternoon lab over the course of a semester.
As I walked into the shop on the first day, my friend who shall not be named was having an animated discussion with the instructor, who was quite firm that that “piece of s**t is not coming into my shop until it’s cleaned up, and you will start by cleaning up the loading dock before the end of the day.”
A flatbed truck had just delivered our tractor. In early September of 1980, my friend who shall not be named presented me with a 1934 Allis Chalmers tractor that had been underwater for 5 years. 1934 was the second year Allis Chalmers had made tractors. All I could think of as I looked at our “easy A” was the movie “The Creature From the Black Lagoon”. Henceforth, my friend who shall not be named shall be referred to in this missive as “Bella Lugosi”
So we spent the first day, and part of the next day, cleaning 5 years of sludge, slime, weeds, mold, fungus, algae, corrosion, crustaceans and invertebrates from the tractor and the loading dock. If I’d thought of it, I would have applied for credit for a Biology or Zoology elective. Or a Paleontology elective.
Once we had removed everything that was not tractor, we were dismayed to see how little remained- everything that could corrode away, had. Other than the tires, there was not a piece of rubber, leather, plastic, electrical insulation, or other soft matter remaining. Even the thinner metal pieces, such as body panels and engine covers, were gone. The tires themselves were a testament to their design – somehow, they still held the weight of the tractor.
Eventually, we found solutions to everything. Bella Lugosi turned out to be quite the craftsman and managed to make by hand nearly everything that we could not buy, such as radiator hoses for a 46 year old tractor. He would go on to build ships and oilfields. The generator had to be completely rebuilt – or we may have adapted one, I don’t remember now. Somehow, we were able to restore the carburetor and the distributor, but what had us defeated for the longest time was the ignition.
One of the first things we had to do was determine whether we even had a salvageable engine – if the pistons had frozen in the cylinders, we might not be able to remove them without damaging the cylinders beyond repair. There would probably be some difficulty in finding a replacement engine block for a 46 year old tractor. Bella, ever the optimist, spent much time in the library researching ancient metal casting techniques just in case.
Once we got it upright and cleaned up, our first thought was to learn whether the motor would turn. Of course, there was no ignition or starter, but this tractor was built to hand crank. A suitable crank was found (Bella later would make a very nice one) and we gave it a shot. At one point, there were three of us putting all our weight on a cheater pipe trying to turn the motor. One big galoot managed to stand on it while pressing against an overhead beam, to no avail. The instructor made us stop when he walked in on us and noticed the tractor was leaning over on one of the back tires. Somehow we’d missed that. The motor was not going to turn.
We were very lucky. We were able to remove the head fairly easily, all things considered, and then poured a magic mixture of solvents concocted by the wizard… I mean the instructor (a PhD Mechanical Engineer, by the way) into each cylinder and let it sit for a week. Also, Bella had had an idea.
Now, you may not be aware of this, but there are few things as dangerous as a redneck who has had an idea. This event is right up there with Infantry Lieutenants who begin discussions with their platoon sergeants with “So, I was thinking…” Wise men leave the area rapidly.
In this case, there were no wise men involved. There was just me and I thought well of Bella.
Anyway, it worked. Our instructor nearly had a heart attack when he learned what we had done, but it worked. So there.
The shop was well equipped, including hydraulic rams in various capacities. Bella checked one out of sufficient capacity to hold the entire weight of the tractor. He inserted the hand crank at about the 3/4 point, so the crank was now moving up, and put the ram under it and proceeded to crank away on the hydraulic ram. After a few minutes of cranking, the front of the tractor was about 6 inches off the ground, entirely suspended by the hand crank on the hydraulic ram. A 1934 Allis Chalmers tractor weighs at least 2700 pounds; some models weigh over two tons.
We had somehow managed to overlook that the crank was inserted into the crankshaft pulley as a slip fit, with a small ramp that would push it off once the motor turned on its own. Try to keep this firmly in mind in what follows.
By now, there was a considerable amount of force on the hand crank where it inserted into the crankshaft pulley. It is a mystery why it was still in place.
Of course, the proper thing to do when a large amount of force has no effect is to apply more force. This is widely known as the “BFH” theory in these parts of the country. So Bella, being wise, suggested I get on top of the tractor where the seat used to be. I did. No result. Bella then climbed aboard, directly on top of the headless engine. No result. We invited a classmate to climb on with us. He pointed out his opinion of our plan with one finger, and left the shop, muttering something about “…not going down with the Titanic…”
Feeling I had contributed little so far, I suggested we bounce up and down on the tractor. Bella agreed, and suggested we do this one at a time. I got one bounce in before something blew up like a shot from a cannon, the tractor was back on the ground, and both of us felt … well, like a tractor had been rammed up our fundaments. The hand crank was nowhere to be seen.
It would eventually be found some distance away in a field behind the shop. There was a hand crank sized hole in the shop wall.
When the instructor came in, attracted by what had sounded like the shop blowing up, he found Bella and me laying on the ground, holding our fundaments and groaning, and most of our classmates either staring in slack-jawed amazement, or laughing uproariously. Eventually what we had done came to light. We were packed off to the school clinic for a cursory examination that resulted in a diagnosis of multiple cases of “dumb**s” and then sent home. Our instructor broke a 3 decade abstinence and closed down a couple of local bars. In the morning he had long discussions with the Dean and the school’s insurance agents and lawyers.
At one point, everybody was present at the scene of the crime, and one of the insurance agents, exclaiming “what in the world made you think this PoS would ever turn again after…” grabbed the crank pulley and jerked on it. The engine slowly spun through a couple of revolutions and slid to a stop.
For a moment, nobody moved; I think there was some confusion about just what had happened. Bella stepped over to the tractor, grabbed the crankshaft pulley and started spinning it like a little boy spinning a bicycle wheel. Of course, the cylinders were full of solvent, so as the engine built up speed, this got pumped out of the cylinders like a blender losing its cover, showering most of us in 46 year old metal corrosion and solvent. Several suits were ruined, but the point had been made. With the exception of me, everybody in the room was a good ‘ole boy from East Texas, and I could feel the glow of pride in the wonderful quality of Allis Chalmers tractors and the ingenuity of good ‘ole boys. All was forgiven; we were warned to take a little more care in the future, and the suits left, to the dry cleaners or the haberdashers as the case may be.
The instructor looked at us balefully, told us to clean up our mess, and left. I think he’d been hoping for the death penalty.
So. We had a useable motor. We reassembled the top end and prepared to deal with the problem of the ignition. We’d renewed the valve train while all the rest was going on; much to our surprise the valve springs still met specs. Something of a miracle right there, in my opinion.
The tractor also passed a compression test – another miracle. Finally, we realized we were just stalling and we were going to have to figure out the ignition.
Our problem was that we had no clue as to the ignition specifications, such as the advance, the dwell, or even the firing order. The first two you could ballpark enough to get a motor running, and then adjust as needed for optimum vacuum, for example, but the firing order was going to have to be right, and we’d rather not try the plug and pray method since this ran the risk of damaging the engine. In this case, “Plug and Pray” means plug the cylinders into the distributor in whatever order you like and try to start it. If that doesn’t work, rearrange the order and try again. The problem with this is that it almost never works, since it relies on everything else being correct and it will almost certainly cause kickback which can damage an engine badly.
In my experience, most manufacturers helpfully cast the firing order directly into the engine block, where you could just read it, so you would know what order the engine cylinders would fire. Guess what was not common in 1934? Yup. No casting of firing order into the block. Despite our best efforts (this was almost two decades before the internet), and the help of quite a few academics who were very proficient at this sort of thing, we were unable to turn up any of the original manuals for this tractor.
I was eventually lucky enough to find a book on internal combustion engine theory which included a section on coil-operated ignitions as on our tractor. With it, I was able to understand the relationship between the 4 stroke combustion cycle, the valve train, and the ignition timing. I now knew how to determine the firing order of our tractor.
At our next shop day, I announced to one and all “stand back! I will now do science!” With amused looks on their faces, my classmates watched as I removed the spark plugs, inserted broom straws into each cylinder, and then proceeded to turn the crank and observe the straws go up and down and the valves open and close. I was able to determine where each cylinder was in the combustion cycle in relation to each other, and thus the firing order, in less than a few minutes, and then demonstrate it repeatedly to everyone’s satisfaction. My peers were amazed.
In short order, Bella threw together some spark plug cables, I wired up an ignition system and set the distributor to a start point, and I did the honors – I gave the crank a hefty turn and was promptly thrown into a bench.
Something else that will cause kickback is incorrect ignition settings. Kickback is when a cylinder fires before it should and thus tries to run backwards. Even in a low power engine, one cylinder will generate several horsepower, easily overcoming a starter, or a hand crank. And the guy turning it. I was lucky not to have broken my arm.
So, with much caution, we adjusted the ignition settings and tried again, Bella replacing me at the danger point. Another kickback, but now, ready for it, nobody imitated any of The Flying Wallendas.
This went on for a few minutes. Try new settings, crank the motor. Sputter, sputter, shake, shake. Smoke up the joint.
And then it happened. I don’t remember who was on the crank, but with barely a turn, the motor caught and was running as smoothly as any new car. We all gathered around and watched as the 46 year old engine roared along (no muffler yet), happily squirting oil out of the valve train since we still hadn’t put the valve cover back on. We were all happy just to be there.
The instructor came in and stood with us, his arms crossed and an odd smile on his face. After a moment, he turned to me, clapped me on the back, and suggested I shut it off before it overheated. I turned to him with my best stupid look. He pointed at the radiator, sitting on a shelf, where Bella had been restoring it. I looked at the motor and realized I had built the ignition in rather a hurry, with some shortcuts, such as no switch. Since it had no switch, I had wired it as “always on”. I looked at the instructor with my best stupid look. He stepped over to a corner, grabbed the shop broom, unscrewed the handle, handed it to me, then waved everyone back. I stood there looking at him with my best stupid look, holding the broom handle.
He very kindly told me I would have to knock the wires off the plugs. “Don’t worry”, he said, “the wood will insulate you”. The plug wires in most ignitions carry 50,000 volts. Our tractor would not be far off from that. I looked at Bella with my best stupid look. I thought maybe, since he’d spelled me on the crank after I’d been thrown for a loop, that he would…
Bella looked back at me very encouragingly. So I started knocking plug wires off. The first one came off easily enough, and the wood did protect me. The engine ran a little roughly, but it wasn’t giving up that easily. Oh, well, it was only misfiring on one cylinder. Of four. I suppose it was too much to expect that a 46 year old tractor would give up that easy. So I knocked off another one.
That stubborn, 46 year old piece of marvelous engineering was now running on half of its cylinders. Running horribly, but running nonetheless. On two cylinders, it choked and sputtered, and shook and fired every other turn, and would not die. Now, my classmates were impressed. The team had pulled it off. Bella’s Folly was a success.
Regretfully, it had to be stopped, before it ran off and ate Nacogdoches, or whatever it took into its mechanical heart next. I knocked off another wire and it shook to a rattling stop. It tried, but one cylinder just could not keep pushing the other three along. Though the issue seemed to be in doubt for a moment.
In the sudden silence of the shop, the class just stood there clapping their hands. About an hour later, with everyone helping, we had a cooling system and a complete ignition system, switch included, and I was cruising down Wilson Drive in front of the girl’s dorm on our 1934 Allis Chalmers, covered in oil.
I love East Texas. Going to school there was the best thing I ever did. Also, they have the best BBQ on earth.
ProfessorJoe teaches MS PowerPoint as part of our MS Office offering, and we also teach presentation skills, including a presentation practicum. What follows is a combination of highlights from both.
What is the difference between a “speech” or “public speaking”, and a “presentation”? Turns out there are as many definitions as there are interested parties. How surprising. Here I’ll offer something I think this audience can work with; what I use when I teach presentation skills at the college level.
Since there are so many different ways of thinking about this subject, I’ll make clear what Assumptions and Definitions I am using here:
PowerPoint is a presentation tool; it is an aid to communication and a spur to thinking. It employs the concept of the bulleted slide. Generally, a slide is used to set an expectation in the mind of the audience what the speaker will be talking about – preparing the ground, so to speak. This facilitates understanding.
Public Speaking, aka a “speech”, is usually to the public, whereas a presentation is usually meant for a specific, more defined, audience. This post will not discuss public speaking. This post is limited to a discussion about using PowerPoint to support effective presentations.
There are generally two types of presentations:
- Information transfer, as in teaching a class, or providing a status report.
- A decision request – at the completion of the presentation, a decision is asked for.
Fun Facts about presentations:
Fun Fact #1: The speaker is the presentation. The deck of PowerPoint slides that may be used to support a presentation is not the presentation. A slide show is not a presentation.
Fun Fact #2: An effective presenter can give his presentation without the slides. The slides are for the audience, not the speaker.
Fun Fact #3: Fancy slides, or many slides, or both, usually ruin presentations. Every slide; every bullet point on a slide, and every element on a slide, should add value to the presentation; should enhance the speaker’s communication with his audience, or it detracts from the presentation. There is no middle ground. It helps, or it hurts.
An example of a bad slide, and possible contender for world’s worst (your tax dollars at work):
And first runner up:
If you are a glutton for punishment, feel free to expand them to a legible size. Legend has it that when General Stanley McChrystal saw the first one, he is alleged to have remarked that when we understand that slide, we’ll have won the war.
And finally, an example of a good slide, if a little on the sparse side:
Now that we share a common understanding of PowerPoint and Presentations, I’ll discuss a process that I believe uses PowerPoint to assist an effective presentation.
Presentations: There are many ways to “flow” a presentation; what works best depends on the presenter, the topic, and the audience. Here, I’ll suggest a general outline that can be varied to match a broad range of requirements. The components of an effective presentation include, and follow the general pattern of “Tell ‘em what you’re going to tell them, Tell ‘em, Tell ‘em what you told them”:
- Title: This is often already up as people come in to the room; so they know they’re in the right place. It should at least tell the audience something about the topic (Presentations), perhaps the name of the speaker and/or a corporate sponsor.
- Introduction: Once the presentation starts, this first slide can expand on the speaker’s remarks welcoming everyone and providing more info about the topic, such as why, or an expected result, for example.
- Table of Contents / An Agenda: either or both, in whatever order seems best; the point is to give the audience some idea of what they’re in for and some idea of what order major topics will be presented.
- Body of presentation: Slide Titles for major topics – NOTE: this is not the title of every slide. A major topic could consist of several slides, here only the major topics should be listed – this expands on the Table of Contents or Agenda.
- Discussion/Questions: This and the next bullet tend to vary in order and practice. The point is to gather audience feedback and answer questions.
An effective presentation – or even a bad one, for that matter – consists of three elements: the speaker, the topic, or subject matter, and the audience. Each exerts an influence on the presentation; with the audience being the most significant. Every part of a presentation should be shaped around the audience; the success or failure of a presentation is defined by the audience. What follows address each of those in no particular order.
- The key to an effective presentation is controlling the audience’s attention. The sole purpose of PowerPoint is to assist the speaker in controlling the audience’s attention. The relationship between the speaker and the slides is exactly that of the magician and the diversions he uses to keep his audience spellbound while he works his magic.
- Know your audience. What do they know about your topic? Are they receptive or hostile? Who are they? Why are they attending? What is each one’s job? What do they do for fun? What do they read, what are their hobbies? Check their social media. What will they do with the information you provide? Learn everything you can about them; you won’t know what is relevant or not until the presentation.
- Know your subject matter. When you know it well enough to deliver an impromptu chalk talk at the water cooler in response to an idle question, you might be ready. When you can discuss the topic reasonably and justly, both for and against, then you might be ready. The first time you present on a topic, you are not ready. At best, you are smooth; you present well, you are calm, etc. You are ready when the questions stop being new.
- Use a murder board; a practice audience that will challenge you – if your target audience surprises you, your preparation was not as effective as it could have been.
- Always try to visit the venue a day ahead. If at all possible, rehearse at the venue, with all equipment and software. You don’t want your first use of or attempt at anything to be in front of a live audience. Be wary of substitutions.
- The information on the slides is secondary to how and when the slides are presented – the speaker does not match his words to the slides, the slides are matched to what the speaker is saying. A slide appears when the speaker is ready for the audience to look at something, and then builds, using animations and transitions, with what the speaker wants the audience to see when the speaker wants the audience to see it.
- NOTE: a slide can stay up until the speaker is ready for the next one – once the audience has gotten what they can from a slide, they will ignore it. Using a “filler” slide is a terrible idea since every slide change will divert the audience for at least a moment, and a slide that doesn’t fit with what the speaker is saying will confuse the audience, it will break their concentration, and the speaker will have lost control and have to regain it.
- The content of a slide supplements, or expands, or introduces, what the speaker is saying. It does not repeat it. The only reason for a speaker to speak words found on a slide is to emphasize a point; make it clear that is what is being done. Never, Never, Never let the audience think you are just reading the slides. You will lose them at that point.
- Do Not Read The Slides. The audience is rarely blind; they usually can read slides by themselves; they rarely are in need of assistance in doing so and will usually resent it. They did not come to read slides, they came to hear you. On occasion, reading part of a slide can emphasize a point.
Slides: PowerPoint has more features than you can shake a stick at, plus the stick, and they are impressive. It is tempting to use them, but in fact, without careful consideration, these features will usually constrain rather than enhance communication. Learn how to use at least the basics of PowerPoint, including Master slides, templates and themes, transitions, animations, and multimedia. Learn how to integrate MS Office applications, such as Excel, with PowerPoint.
The primary question to ask before anything – a bullet point, a comment or note, some impressive feature, anything at all, goes into a slide is “How will this enhance communication with the audience?”. If that question cannot be answered in my classroom, the student has failed to meet this requirement. In the classroom, if the student can answer that question, I will give them an A. I may not agree that what they have done on the slide enhances communication, but they get the A; they understand how to decide what goes in and what does not. That’s the goal.
Some basic principles of an effective PowerPoint supported presentation:
- Keep it Simple
- Presentations are:
- A knowledge transfer of a limited set of concepts.
- Or, decision support limited to relevant factors.
- Generally, the slide deck should be about 5 to 7 slides, with 15 as a maximum.
- Each slide should have 3-5 bullet points
- If less, consider combining slides
- If more, consider breaking up into multiple slides
- These approximate limitations – the numbers mentioned above – are based on studies of the capacity of the human mind to absorb concepts quickly; they are approximate guidelines.
- Slides should be consistent – every change from slide to slide causes a break in the audience’s attention from the speaker. The speaker must always control the audience’s attention, so any such breaks should be deliberate, not inadvertent. Therefore:
- The slides should share a common format, font, and pitch, and variation in same, as in bullet points for example.
- Use templates, themes, slide masters and such to ensure consistency.
- To Repeat: Any break in consistency should answer the question: How will this enhance communication?
- Be wary of backgrounds and such. Most common use is for branding; it identifies where the speaker is from, or the background is on topic – a watermark of a giant trout in a sales pitch about fishing equipment, for example. These make sense, and the audience gets it immediately. Avoid like the plague “theme” backgrounds. What exactly does “Geometric” mean? Or “Contemporary”? I don’t know what Microsoft had in mind, and I don’t want my audience trying to figure it out, either. I want them paying attention to what I’m saying.
- Hidden slides: some slides are focused on specific questions and are otherwise unnecessary. Hide such slides at the end of the presentation, with a hyperlink to them at the slide that would likely bring the question up. If nobody asks, the slide never shows. Hint: Be sure to put a hyperlink on the hidden slide to get back to the jump point.
- Always bring originals of multimedia files or other embedded, attached files, etc., with you on a flash drive or some such. You will be amazed to learn how often what worked on your computer at work and at home is just a broken link at the venue. Windows works in mysterious ways.
- Audience handouts: There are different schools of thought on this. On the one hand, if you give the audience handouts, it can complicate attention control. On the other hand, it can give them something to focus note taking on. I’ve always thought it depends on the specific presentation; usually more useful for a decision brief than a sales pitch, for example. I prefer the 3 slides to a page printout for this.
- Similarly, put a page number on every slide. This simple practice vastly improves the discussion session at the end.
- Speaker’s notes: Definitely use for rehearsals, and write extensive notes, etc., on them – use the same set for as long as possible. For me, at least, this helps to put the presentation firmly into my head. Doesn’t necessarily work for everyone. Find out what works for you and do that. I prefer the one slide to a page with a note section printout for this. Do not use during a presentation.
- If you have to have a memory aid, do anything but hold it in your hand. This pulls your head down and away from eye contact with the audience. Use a teleprompter, have people hold up prompt cards, use a laptop or a monitor, anything but note-cards in your hand you read from.
- Audience gimmicks can be effective, but watch your audience – if you gauged them wrong, some gimmicks will lose them, so be prepared to abandon whatever cute trick you thought would work well. Having said that, it’s amazing how much attention people will pay if they think they can win a prize or a candy. Be conservative with this, you never know who will be offended by what.
- Anybody who brought only one backup deserves everything they get. A backup is not a backup unless there are two of them. Two copies of a file on the same flash drive are not two backups. Depending on load limitations, this should include cables, laptops, projectors, etc. Test every backup. Assumptions will kill you.
Well, that’s it. That’s what I teach, it’s how I do it, and it’s what’s worked for me for a while now. I’d love to hear what works for others – one thing I’ve learned is that there’s always another way. What’s your way?
This post is actually the script for an upcoming recorded video on the ProfessorJoe YouTube channel. It will work best if the reader actually opens Excel and recreates the tables, then follows along. Remember to update the cell references appropriately.
Today, we’ll talk about the lookup functions, VLOOKUP and HLOOKUP, as they are used in MS EXCEL 2010. These functions search for a value from the current worksheet in another worksheet and, based on that value, return a specified value from the other worksheet.
Let’s look at an example. This is a table from HR. It’s missing some data:
Here’s a table from a file a Project group is using, that contains the data we want:
So the question is: how do we get the phone numbers from the project table into the correct row in the HR table? Obviously, for tables this small, the answer is simple: type them in. However, what if these tables are thousands of rows and columns? The latest version of Excel will hold over a million rows and over 16 thousand columns. Probably don’t want to be searching through that.
So how would we do this for any table, regardless of size? Read on!
The first name in the HR table is Bob; we want to get his phone number from the project table. VLOOKUP would do that. The syntax for the VLOOKUP function is:
(lookup_value, table_array, col_index_num, [range_lookup]). The brackets around range_lookup mean it is optional; more on that later. Let’s walk through what the syntax means, and what VLOOKUP is doing. We’ll refer to the project table as the source, and the HR table as the target.
In the figure below, we’re looking at a range (aka “table”) from the source worksheet (the project table), called table_array in the VLOOKUP syntax. It contains the data (phone number) we want to come back with for our current worksheet (the HR table). VLOOKUP will search vertically, down the 1st column in table_array (aka the project table) for the lookup_value (Bob). Once lookup_value is found, VLOOKUP moves across that row to the column specified by the index, and returns the value (phone number) located there.
Similarly, this is a table_array for HLOOKUP:
In this case, HLOOKUP will search horizontally, across the 1st row in table_array for the lookup_value. Once lookup_value is found, HLOOKUP moves down that column to the row specified by the index, and returns the value located there. The syntax is =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). Again, range_lookup is an optional value. The only difference is the H or the V in HLOOKUP or VLOOKUP, and row or col (column) in col_index_num or row_index_num. The rest of the syntax is unchanged.
Let’s take a close look at that syntax: lookup_value, table_array, col_index_num, range_lookup:
- lookup_value: What to look for – in this case, we’re looking for “Bob” in the project table.
- This can be anything – a cell reference, a literal (Bob), another function, etc. This is what the lookup function is going to look for in the 1st column or row of table_array (from the project table). So when you designate table_array, you must specify it so that the 1st column or row of this array contains the value VLOOKUP is looking for. As well, considering range_lookup below, this table_array should be sorted on this 1st column or row.
- table_array: Where to look – in this case, somewhere in the project table.
- The table_array can actually be anywhere, but the function will work better if it is copied into the same workbook as the target (the HR table). Remember, table_array must begin with the column or row to be searched for lookup_value and it must contain the column or row specified by index, below. If the array only contains 3 columns, but index specifies a return from a 4th column, the function will return the #N/A error.
- To be clear: table_array is not the entire table or spreadsheet containing the data you are working with. It is a range that is within that spreadsheet. If you look at the project table above, you will note that the lookup_value (Bob) is not the first column of the project table, and that the phone numbers are not in the last column of the project table. Therefore, table_array is located within the source table but it is not the same size, except by coincidence.
- col_index_num or row_index_num: The index number is the column or row in table_array containing the value (phone number) to return to the current spreadsheet (the HR table).
◦ NOTE: The 1st column or row of table_array is 1 (where lookup_value is to be found), so the next is 2, and so on. Again, this is not the 1st column of the project table, but the column containing the lookup_value (Bob).
> Consider: the first column of the project table is column J. Bob is in column K. So the 1st column of table_array is column K. The last column of the project table is column P. The phone numbers are in column O. So column O becomes the index (5th) column of table_array.
- range_lookup: Does lookup_value have to match exactly?
- range_lookup is an optional logical value; its allowable values are True/1, and False/0. The first means the function will accept an approximate match for lookup_value, the latter means it must find an exact match. If range_lookup is omitted, VLOOKUP assumes an approximate match.
> For an approximate match, the column or row must be sorted. The search will stop when it encounters a value that exceeds lookup_value and use the value immediately preceding, if there is one. If the column or row was not sorted, the search may stop too soon and use an approximate match when an exact match was available.
◦ If lookup_value is not found, or if the array does not contain the column or row specified by index, then a #N/A error is returned.
Let’s try a VLOOKUP: here’s that table from HR:
Here’s the project table:
Gee, wonder why they want the phone numbers? Who do you think they’ll call first?
We’ll take a short pause here while you open Excel and recreate the two tables. Just hit the “pause” control on the webinar/video. In this case, for the sake of convenience, you should put them on the same worksheet so you can see both at the same time. Remember to use the correct cell references for your spreadsheets. For simplicity, you might want to make Tables of both using the Insert Table command. Restart when you are ready.
We have a winner! Corporate wants Bob’s number first! What a surprise. Only 13 zeroes in that column. Bob may have overspent a little. Here’s how we’ll do this: remember the VLOOKUP syntax:
(lookup_value, table_array, col_index_num, [range_lookup])
Our VLOOKUP function, in C2 in the HR table, will use a table_array of K22:O28 from the project table. Therefore, Column K is the 1st column of table_array, making column O, where the phone numbers are, the 5th Column. Therefore, our index is 5. Here are the values for the syntax:
- Lookup_Value: Bob
- Table_Array: K22:O28
- Col_index_num: 5
- Range_lookup: omitted
So in C2 of the HR table, where we want the phone numbers, we type: =VLOOKUP(“Bob”,K22:O28,5).
NOTE: Remember, when you type that into your spreadsheet, you have to change the references to match where they were located on your worksheet.
When you’re ready, hit Enter.
When you hit “Enter”, you got #N/A. Why? Pause a moment and consider Column K of table_array, and the range_lookup value used by VLOOKUP in the VLOOKUP function in C2 above. Continue when you are ready.
range_lookup in the function =VLOOKUP(“Bob”,K22:O28,5) is omitted, so VLOOKUP assumes TRUE/1, which allows VLOOKUP to look for an approximate match for lookup_value. However, this requires column K of table_array to be sorted. Column K of table_array is not sorted. So what happened? Again, pause and consider. Continue when ready.
The 1st value VLOOKUP encounters in column K is “Sally”. Since it is looking for approximate matches, it is assuming a sort order, in this case the alphabet. Since S is already past B (Bob) in the alphabet, VLOOKUP stops searching. It would have matched on the last value it encountered before Sally, but since Sally was the 1st value, there was nothing to match.
What would have fixed this? Again, pause and consider. Continue when ready.
There are two solutions. Either sort column K, or (and what I would prefer), specify range_lookup as False/0, in the function: =VLOOKUP(“Bob”,K22:O28,5, 0) This would have forced an exact match for the search, and then the sort would be irrelevant.
Obviously, this function was specific to Bob – he’s going to have to explain that 2 trillion-dollar variance – but the rest of ‘em aren’t getting off the hook. To make the function work for everybody, change it so:
This allows the name cell (C2) reference to adjust as autofill is used to fill in the rest of the phone column, and the table_array reference is changed to an absolute reference so autofill does not change it.
That’s it. I hope this helps to clear up VLOOKUP. HLOOKUP would work similarly, with the difference as explained above.
Contact me through the website if you have questions!