Future of Productivity Giveaway Winner

Did you enter the Excel 2010 Future of Productivity Giveaway, that ended at noon yesterday, Tuesday, June 8th? The giveaway prize was a copy of Microsoft Office 2010, a Flip video camera, and a Seagate 1TB hard drive.
Of course, I used Excel to select the winner, from all the valid comment entries. I typed the comment numbers onto an Excel worksheet, with RAND functions in the adjacent column. Then, I calculated the workbook 3 times, to mix up the RAND results.
Finally, I sorted the RAND column in ascending order, and the comment number at the top was the winner. To satisfy the auditors, here's a video of the random comment number selection.

And the Winner Is...

As you saw in the video, the random number selected was 22 -- the comment by Naomi B. Robbins. Congratulations, Naomi! You win the prize package:

  • one (1) copy of Microsoft Office 2010: Home and Business
  • a Flip Ultra HD Camcorder
  • a Seagate FreeAgent Desk Hard Drive (1 TB)

The prize value is approximately $589 USD, and someone from Ignite Social Media will be in touch with Naomi to arrange delivery.

Thanks for Entering

Thanks for entering the giveaway, and thanks to Ignite Social Media for sponsoring it. I hope you saw a few new features in Excel 2010 that you're looking forward to using!

Excel 2010 Future of Productivity Giveaway

Office2010HB_small Have you installed Office 2010 yet? Would you like to win a copy, along with a couple of other great prizes? [for USA residents only]

In this short video, Microsoft employees and customers talk about the benefits of Excel 2010, both for the users and the IT department. Watch carefully -- there will be a test later!

The Future of Productivity Giveaway

The nice people at Ignite Social Media invited me to participate in a giveaway [for USA residents only], as part of the Office 2010 launch. No free stuff for me, but one of you can win a cool prize package. Yes, there's probably a better adjective than "cool" -- at least I didn't say "groovy". 😉

The Prize Package
  • one (1) copy of Microsoft Office 2010: Home and Business
  • a Flip Ultra HD Camcorder
  • a Seagate FreeAgent Desk Hard Drive (1 TB)

Here's a picture of the prize package, obviously not shown to scale! The pictures are from the Microsoft Store site, so the actual prizes might look different. Based on the prices shown in the Microsoft Store, the prize value is approximately $589 USD.


How to Enter

The giveaway is a scavenger hunt, so you'll have to watch the video (at the top of this post) to find an answer to this question:

  • How does Excel 2010 make data analysis and reporting better/easier?

Add a comment below, with a unique, relevant answer to the question shown above. (Don't just copy someone else's comment!)

The Giveaway Rules
  • You must be a resident of the Unites States of America.
  • The entry deadline is 12:00 noon (Eastern time zone) on Tuesday, June 8th, 2010.
  • One entry per person – any additional entries will be deleted from the draw
  • A random draw will select the winner from all valid entries. 
  • Winner will be notified by email, so please provide a valid email address. This will not be publicly visible, but will be shared with the contest organizers at Ignite Social Media, so they can contact the winner to arrange delivery. 

The winner will be announced in this blog on Wednesday, June 9th.

More Future of Productivity Events

This widget has more videos, and you can click to learn more about the Future of Productivity launch.


Adrift in a Sea of Numbers

This summer I helped a client automate several Excel sales reports, comparing sales forecasts to actual sales, and last year's results to this year's. It's a complicated process, pulling numbers from different systems, updating lookup tables, compiling the numbers, and creating reports by product, by customer and by sales rep. Before we automated the process, it took one person almost a week to create the reports, and despite all that effort, nobody was happy with the results.

  • The sales reps thought there was too much data to wade through – they wanted to focus on their customers and sales. Were sales on target? Which customers need more attention?
  • The managers wanted to see the big picture, and quickly assess how things were going overall. Were all the brands performing as expected? Which products or customers were growing or declining?

Tableau Dashboard

While working on the reports, I noticed peopletalking about Tableau dashboards in Twitter. I followed their links, and was really impressed by what I found. With Tableau, we could quickly connect to the data in Excel, and also link to the lookup tables, so all the prices and descriptions would be accurate and instantly updated. So, I downloaded a trial version of Tableau, and created some reports from the client's data. It was really easy to get up and running, creating tables and charts, with quick filters that let me focus on a specific brand, customer, or sales rep. Next month, when there's a new set of data, the old file can simply be replaced by the new file (with the same name), and the charts and tables will automatically refresh when the Tableau workbook is opened.

Share the Results

After you create reports in Tableau, you can send out a pdf file, or a Tableau workbook, to share the results. There's a free Tableau reader, so recipients can open the workbook file, and adjust all the filters, sliders and other controls that you've added to the dashboard. Each person can analyze the data in a way that's meaningful to them, and drill down to the details, or step back to absorb the bigger picture. To give you an idea of what's possible in Tableau, I've created a sample workbook, shown below. Dan G. Murray, COO of InterWorks Inc., generously shared the sample sales data that he used for a presentation at the Tableau Customer Conference this summer. With Dan's data, I made a few dashboards, and Elissa Fink, VP Marketing at Tableau, published them on the Tableau Public server.

Try It Yourself!

Here's the first dashboard in the workbook, focused on sales rep results and country totals. Each sales rep can see their overall total, and total and average sales by country. The map gives a snapshot of sales location, and clearly shows that large sections of the USA aren't buying the company's products. You can experiment with the dashboard controls, and change the way the data is presented. You might have to widen your browser window, to see the full dashboard.

  • There's a date range selector at the top left of the dashboard. Only the past four weeks of sales results are in the dashboard, but you can select a specific number of previous weeks, or other range, such as previous quarter.
  • At the bottom left of the dashboard, in the Salesman Name list, click the highlighter at the top right, to turn on the Highlight Selected Items feature. Then, click on a Sales Rep name in one section of the dashboard, and that name is highlighted in all the sections.

Dashboard on Tableau: Sales Rep Dashboard

Summer Giveaway for Excel Nerds Winners

Thanks for participating! You're an awesome bunch of Excel nerds. The entries in the "You might be an Excel nerd..." giveaway were very entertaining, and read more like my resume than a list of contest entries. Yep, I do some of those things, and I'll bet you do too. 😉
Thanks again to the generous prize donors:

The Draw

As promised, I did a random draw of prizes and entries. But, in true Excel nerd fashion, I didn't just drop the names into a hat. No, that might have only taken 5 minutes or so. Instead, I wrote a macro to do the draws for me.
Before the draw, I cleaned up the list of entries, removing the people who said they weren't participating, and deleting any extra entries per person. I kept the comment numbers with the names, and created a numbered list of prizes, based on the order they appeared in the original giveaway post.
I set up a list to record the draw number, prize number and entry number, then ran the macro. There was even a drum roll at the start of the draw, but it was disappointingly quiet. Oh well, at least everything else went smoothly.

You'd probably do it differently, and maybe without the drum roll, but if you'd like to see the macro that I used, you can download the Draw Macro Sample file. Now you'll be able to organize your own giveaways!

And the Winners Are...

After the numbers had been selected, I used a couple VLOOKUP formulas to pull the prize names and winner names from the original lists of numbered prizes and entries. Congratulations to all the winners! Here's the list:

Collect Your Prize

I'll send an email to all the winners on July 29, 2009, with instructions for claiming your prize. Please reply by 5 PM EDT on Monday August 10, 2009, or your prize will be put back into the vault for the next giveaway.
Note: Some of the prize donors will be away from their offices in August, so there may be a delay in mailing.

Update: I've sent out the emails, using the email address that you used in your comment. If you haven't received your email, please let me know at ddalgleish @ contextures.com


AutoFilter By Selection In Excel

In Excel 2003, you can add a couple of buttons to the toolbar to make it easy to filter a table. For example, in the table below, the East region is selected. With one click of a button, and no programming, you can add an AutoFilter and filter the table to show only the East region orders. Thanks to Roger Govier for sharing this tip.
[Update: For Excel 2007 instructions, see Easy Filtering in Excel 2007]

Add Buttons to the Toolbar

You can add two buttons to the toolbar, to make filtering easy. One button will filter the table for the selected item, and the other button will show all the records.
To add the buttons:

  1. On the menu bar, click Tools, then click Customize.
  2. In the Customize dialog box, click the Commands tab.
  3. Click the Data category, then drag the AutoFilter command to an existing toolbar.
  4. Drag the Show All command to an existing toolbar.
  5. Close the Customize dialog box.


Apply the AutoFilter

Now you can use the new buttons to filter the table, or to show all the records. The table doesn't need to have an AutoFilter currently applied.

  1. In a table in Excel, select a cell that contains the criterion you'd like to use. For example, to filter for the East region records, select an East cell in the Region column.
  2. On the toolbar, click the AutoFilter button

The table is filtered, and shows only the East region records.

Remove the Filter

To remove the filter, and show all the records again:

  1. Select any cell on the worksheet
  2. On the toolbar, click the Show All button.

The filter is removed, but the AutoFilter feature is still turned on, and all the records are visible.
For more information on Excel AutoFilters, visit the pages on Excel AutoFilter Basics and Excel AutoFilter Tips on my website.

Watch the Video

To see the steps, you can watch the short video below.


What’s in Your Desk Drawer?

Recently, I've made some progress in clearing out my office, and organizing my files and bookshelves. This week, after I was stabbed by a push pin, while trying to dig out a paper clip, I realized that my desk drawer could use some improvement.
At some point, I had good intentions, because you can see the boxes and clear container with storage compartments, that were meant to organize things. Somewhere, though, things got off track.

  • Buried under the top layer are the paper clips, in assorted sizes, that I need occasionally.
  • The glue sticks and Scotch tape are easily accessible, but I don't use them too often.
  • Stamps, that I use about once a month, are easy to find.
  • A nice eraser, for those paper-based mistakes
  • Hand cream and nail clippers, because good grooming is important
  • The rest of it, I should toss out, or at least move to a different part of the office, so I can find those paper clips when I need them.

That desk drawer is prime office real estate, and I'm cleaning it up.
How about you?  Is your desk drawer better organized than mine? Or are you risking injury every time you reach for a paper clip?

Calculate a Ratio in Excel

In Excel, if you divide 2 by 8, the result is 0.25. if you format the cell as a fraction, the cell might show 1/4 as the result.

What if you want to show the result as a ratio? How can you get the cell to show 1:4 instead of 1/4? There may be other ways, but here's the formula that I used.

=B2/GCD(B2,C2) & ":" & C2/GCD(B2,C2) 

This formula requires that the Analysis ToolPak be installed, in Excel 2003 and earlier versions. It divides each cell by the greatest common divisor (GCD), and puts a colon between the two numbers. Would you use a different formula? __________________

Be a Roman

To survive business ups and downs, become a Roman -- disciplined and willing to keep fighting. That's advice from The First-Time CEO's Recession Survival Guide, on TechCrunch, written by Glenn Kelman, the CEO of Redfin, an online real estate broker.
Some of the tips in this article aren't relevant to my small business, but many of the ideas can be used or adapted if your running a business of any size. For example, I don't have a staff to call in for a weekly revenue meeting, but I could set aside time each week to do some thinking on my own.
Create simplicity is another suggestion that's worth considering. Apply that to your Excel files too, wherever possible.

Use the Roman Function

Speaking of Excel, it has a ROMAN function that converts a number into Roman numerals (now that's a segue!)
The first argument is the number, between 0 and 3999, that you want to convert. The second argument (optional) is the type of conversion that you want. If you omit the second argument, the result is a classic Roman numeral. You can also use numbers 1 to 4 to create more concise versions of the Roman numeral.
Not all numbers will be affected by the levels of conciseness, but some will change, as you can see in the example below.

Clearing Out the Deadwood

Last weekend a friend, aka Chauncey, who has some gardening knowledge, came over to help trim the trees and shrubs in the back yard.  About an hour later, much of the lawn was covered with branches, twigs and leaves. It took another couple of hours to stuff everything into yard waste bags, and bundle the big pieces. This week it'll be taken to the city recycling centre, where it will be turned into compost or mulch.


It was way more work than I expected, but the catalpa tree, which was overgrown, now looks much better, and Chauncey claims it will flourish in the spring, without all the extra branches.


When I got back to my office, I realized that I should do the same thing there. So, next weekend I'll clear out more of the deadwood in the office -- old paper files, computer programs that I'm no longer using, RSS feeds that I never read, links to time-wasting web sites, and old email with large attachments that I don't need.

All that stuff is slowing me and my computer down, and a few hours of work should make other things go faster when I'm finished the cleanup. I hope!