Spreadsheet Page Blog
Show Us Your Spreadsheets Contest Results
Congrats to the winners in the big Show Us Your Spreadsheets contest.
First prize went to Peter in Windsor, Ontario. He gets a $250 VISA gift card and a collection of Mr. Spreadsheet's Bookshelf guides:
Second prize: Jared in Northbrook, Illinois
Thanks to everyone who participated, and thanks to the thousands of people who were motivated to buy a book.
Contest: Show Us Your Spreadsheets Challenge
Wiley is having a contest, and you could be a winner. For complete details, read the official contest announcement.
Submit a photo of yourself with your favorite John Walkenbach (Mr. Spreadsheet) or Michael Alexander (DataPig) book OR a photo of you creating stellar spreadsheets, dynamic dashboards, or something equally excellent in Excel to enter to win cash and other cool prizes.
Entries will be posted to Wiley's "Show Us Your Spreadsheets" photo page, and authors John Walkenbach and Michael Alexander will select winners at the conclusion of the challenge.
Notice that you don't have to actually create a spreadsheet. All that's required is a photo.
The contest has real prizes:
- FIRST PRIZE: $250 VISA gift card, plus a collection of Mr. Spreadsheet's Bookshelf guides
- SECOND PRIZE: $100 VISA gift card, plus a collection of Mr. Spreadsheet's Bookshelf guides
- Three RUNNERS-UP: $50 VISA gift card, plus a collection of Mr. Spreadsheet's Bookshelf guides
Photos must be submitted by December 3. And, don't post the photos here. The official rules explain how to do it.
Happy Spreadsheet Day
Today is the first annual Spreadsheet Day. It might even be International Spreadsheet Day.
Debra Dalgleish has the details, including lots of ideas to enhance your celebrations.
Climber Brings Excel Sign
At the Microsoft Excel 2010 Blog: Excel reaches the top of the World.
We just wanted to take this short post to congratulate one of our own on achieving a monumental task. Gabhan Berry a Lead Program Manager on the Excel team summited Mount Rainier on September 11th at 7:31 am and brought a little piece of Excel with him.
At first, I thought it was an ad for a pirated copy of Excel.
Arranging Your Data
Last week, someone sent me a workbook because he was having some problems with it. Here's a small section from the file:
It was arranged in categories, and each category had tasks below it, arrange in a row. Under each task name was date to indicate when the task was performed. Many of the date cells contained a cell comment to clarify (3,810 comments in all). Summary formulas were entered to count the dates. And each formula was hand-crafted because the layout was so haphazard.
I was very surprised to see such a poorly organized worksheet. But, after giving it some thought, this sort of thing is probably not at all unusual. The typical user, I think, probably starts entering data without giving the organization much thought. At first, it's easy makes sense. But after a few years of data entry, you end up with a complete mess.
I suggested that this person spend a day or two and copy/paste the data into a normalized table with four column headers: Category, Task, Date, and Comments. Data entry would be much easier, and the information in this table can be easily sorted, filtered, or summarized with a pivot table.
To a typical user, it probably seems very inefficient to repeat the category name and task name for every entry in the table. But it's actually the most efficient way to store data.
Annoying Window Behavior
You know what's annoying about Excel?
Assume that you have the VB Editor window open. You click Excel' minimize button to minimize the app to get it out of the way. Then you see the VB Editor window, and realize that there's no need for it to be open. So you close it -- and Excel's window pops back up!
Yeah, as far as annoyances go, it's a minor thing. But it's been going on since Excel 5. With every new version, I keep hoping it will be fixed, but it never is. I can't think of a single reason why closing the VB Editor window should restore Excel's window.
What little Excel quirks do you find annoying?
Excel 2010 will feature a new Equation Editor. That means we can can reproduce classic calculus jokes like this:
I ran across a site that features an add-in that provides an alternate MsgBox function: ktMsgBox. Here's the UserForm (click to see it in actual size):
I'd say that qualifies as a poorly designed UserForm. Besides being ugly, it's just way too confusing.
When designing a UserForm, my advice is to emulate the look of the dialog boxes in Excel as much as possible. Just because you can change the colors and cram 50 controls into one dialog box doesn't mean you should do so.
I downloaded the add-in just to see how it worked. I deleted it as soon as I saw the pop-up greeting that stayed on screen for 60-seconds and couldn't be closed.
VBA Speed Results
A few days ago I posted a VBA procedure that looped 100 million times. I asked readers to run the code and report on how long it took. See How Fast Is Your System?
About 60 people responded, and here's how the times were distributed:
It's certainly not a normal distribution. The times are skewed towards the faster end of the scale.
- Mean: 13.25 seconds
- Median: 11.29 seconds
- Fastest: 5.08 seconds
- Slowest: 28.52 seconds
So, the fastest machine ran the code about 5.6 times faster than the slowest machine.
But the real question is, how long would it take to do this task manually?
I wrote 0 on a piece of paper and I flipped a coin. If it came up heads, I added one to my tally. If it came up tails, I subtracted 1. I did this ten times and it took me 42 seconds. So, one time through my "loop" took 4.2 seconds. Using this information, I calculated that it would take me 799 years to perform this task 100 million times -- but only if I work non-stop. The conclusion: My computer is about 52.3 million times faster than I am.
How Fast Is Your System?
Just for fun, take a few minutes and post your result. Copy and paste this procedure into a VBA module. Then run it, and post the time it takes.
Sub TimeTest() '100 million random numbers, tests, and math operations Dim x As Long Dim StartTime As Single Dim i As Long x = 0 StartTime = Timer For i = 1 To 100000000 If Rnd <= 0.5 Then x = x + 1 Else x = x - 1 Next i MsgBox Timer - StartTime & " seconds" End Sub
Here's the time on my system (no significant difference between Excel 2003, 2007, and 2010 Tech Preview):
My system is about three years old. I'm just curious about the range of processor speed of systems in use. Is it possible to run this in less than a second?
Excel 4.0 First Look
Google Books has an old issue of InfoWorld, where you can read my "first look" at Excel 4.0: Excel 4.0's ease of use is sure to win converts. It's from March, 1992.
The active cell and selected ranges now include a small "auto-fill" handle in the lower right corner. To copy a cell or range down or across, you simply click and drag this handle. This technique is even intelligent. Excel examines the selection for words it recognizes -- such as January, Qtr-1, Monday, etc. -- and will extend the series for you.
I also raved about the right-click shortcut menus.
I've been working on the Excel 2010 Bible, and I read this passage in a section about data entry:
If a number contains a colon (:) or is followed by a space and the letter A or P, it may be converted to a time format.
Obviously, I knew this at one time, but I forgot it. If someone would have asked me yesterday, "Hey John, when I enter a time, can I just type a P, or do I need to type PM?" I would have replied that it's necessary to type PM.
An aging brain has room for only a limited about of Excel knowledge, so some of it goes away to make room for more. I probably used to know all of this, too:
- Type an integer from 0 through 9999, followed immediately by a colon, and it's interpreted as a time value. 0: is interpreted as 12:00:00 AM, and 9999: is interpreted as 2/19/1901 3:00:00 PM.
- Type an integer from 0 though 12, followed by a space and the letter P, and it's interpreted as a p.m. time. So 9 p is converted to 9:00 PM.
- Type an integer from 0 through 12, followed by a space and the letter A, and it's interpreted as an a.m. time. So 9 a is converted to 9:00 AM.
- Type an integer, followed by a colon and a value from 0 through 59, and it's interpreted as a time. A leading zero for the minute part is not required. For example, 9:2 is converted to 9:02 AM.
- Type an integer, followed by a colon and a value greater than 59, and it's interpreted as a time -- but Excel doesn't apply time formatting. For example, 6:200 is converted to 0.388888888888889. If you apply time formatting, it appears as 9:20 AM. (That's 6 hours plus 200 minutes.)
Feel free to list more Excel time-entry quirks -- even though I won't remember them.
Calculating In The Pre-Excel Days
This is an interesting site: Retro Calculators.
Here's a device called the Add-A-Mite Pocket Calculator ("Handsome as jewelry, accurate as a watch").
This simple mechanical pocket adder is made of aluminum with bright blue paint and dates perhaps from the 1950s. The cursor on the right is depressed and dragged to the number you wish to add, 0 to 99. When released, the cursor engages a gear tooth on the back of the disc and when you then drag the cursor clockwise back to the 0 position, your number is added to the result. Crossing from 99 to 00 causes an automatic carry into the hundreds window, allowing sums up to 2,499. Subtraction works similarly by starting from zero and dragging counter clockwise to the desired number.
Back then, people rarely added numbers that exceeded 2,499.
Sadly, version 2 failed miserably in the marketplace. The world just wasn't ready for Add-A-Mite-With-Pivot-Tables.
Everyone else is celebrating this momentous occasion, so I will too.
In Excel, today's date (July 6, 2009) has a serial number of 40,000. According to Excel, the world began on January 1, 1900, and that date is designated Day 1.
Personally, I'm really looking forward to September 5, 2021 (also known as Day 44,444).
I'm not sure if this spreadsheet grill is real. If not, it should be.
It solves a serious problem: Remind campers of office work.
Notice that the widths of columns A and G have been reduced to accommodate the handles.