Ponder This

When you’re stuck working on a tough Excel problem, it may help if you set that aside and work on something else for a while.

The people at IBM research apparently do this, and have a monthly problem, Ponder This, that you can tackle when your brain needs a change of scenery. The problem is posted at the beginning of the month, and the solution is posted near the end of the month.

The first challenge was posted in May 1998, with this introduction:
“Here’s what happened… Business Week recently ran an item about a “little book of big ideas” we published for the people here at IBM Research. “Sort of like the Tao for people who think about computers all day,” they wrote. Seemed harmless enough.

The story highlighted some of the simple things our researchers do to get a new perspective on things, ending with this one:

Ponder something else. For example: If a belt were placed around the Earth’s equator, and then had six meters of length added to it, and you grabbed it at a point and lifted it until all the slack was gone, how high above Earth’s surface would you be?

Now Do This

Those of you with a short attention span, and a list of things you need to do, might like the Now Do This web site.

It certainly isn’t filled with distracting shiny things.


Click the ‘edit list’ link, enter the items you need to do, then click Save
As you complete each item, click the ‘done’ button, and the next item appears on the screen.

When all the items are completed, the web site is almost as happy as you’ll be.

Focus on the Business Basics

At The Business Brickyard site, you can download a free copy of president Howard Mann’s book, Your Business Brickyard (download link is at the bottom of the page).

[Update: The free ebook is now available if you subscribe to their newsletter.]

The book is short, and to the point, with no fluffy filler. It covers twelve points that will help you focus your business on the basics, rather than following all the latest trends, and wandering in different directions.

I read it in under an hour, and found several ideas that I can implement in my business.

One of the points that Excel users might relate to is, “Fit your business on a single page.” As the book describes it, “At the end of every day, you should be able to scan a simple 8½ × 11–inch piece of paper and know exactly how your business is doing.”

There is a practice drill for each point, and a summary at the end of the book, for quick reference and review.

After reading the free downloaded copy, you might find it valuable enough to buy a printed version.

Analyze Networks with Excel Add-In NetMap

It’s interesting that Microsoft is using an Excel Add-In, NetMap, to analyze newsgroup structures.

The researchers found at least three types of participants: Answer People, Reply Magnets and Discussion People.

Hey, I know a few of those “Answer People.” No mention of trolls though — maybe they’re in the Discussion People group.

Analyzing newsgroup networks in Excel
Analyzing newsgroup networks in Excel

Website Connections

Somewhat related, but more fun, I won’t admit how much time I’ve spent on the Walk2Web site this week, exploring the connections between web sites. I’m marking it in my time log as “Research” so it’s time invested, rather than time wasted. 😉

It’s a great way to discover new sites in your area of interest, and who knows what other gems (or garbage) you’ll find? It’s fascinating to see a picture of how sites are linked, and it does look like a web after you’ve walked to a few sites. A very messy web, granted.

In the screen shot below I’ve followed a couple of the links from my Excel Tips Index page.

  • In the overview you can click on a linked site, to see its connections, or point to a link and see a snapshot of the page.
  • Point to the top, bottom, left or right of the page, to scroll in that direction.
  • Green links are incoming and blue links are outgoing. A maximum of three of each are shown, and you can click the orange More links to expand the view.
  • You can mark sites as favorites, Digg them or StumbleUpon them.

So, if you’re looking for inspiration, or new ideas, or a way to kill an hour, visit Walk2Web. Don’t say that I didn’t warn you.

Simplify Data Entry With AutoCorrect

Instead of typing long phrases in Word or Excel, you can create AutoCorrect entries for those items.

What Do You Type Frequently?

Your AutoCorrect entries are most helpful if they are for long words or phrases that you type frequently, while you’re working.  For example:

  • your full name
  • your job title
  • your company’s full name
  • email sentences, such as, “I’ll send you the file tomorrow.”

Short Replacement Code

The goal is to type a very short AutoCorrect code, and have Excel automatically replace that with the full word or phrase.

  • Note: Don’t use a short code that is an actual word that you use!

Here are example codes, for the AutoCorrect entries that I suggested in the previous section:

  • DD: Debra Dalgleish
  • dmk: Director of Marketing, Canada
  • ctxi: Contextures Inc.
  • syf: I’ll send you the file tomorrow.”

Continue reading “Simplify Data Entry With AutoCorrect”

Daily Discounts on Computer Software

The Bits du Jour web site offers one discounted item per day (2 on Tuesdays and Thursdays) — either Windows software or a web service.

You can see the upcoming deals, and download a trial version, to see if it’s worth buying when the discount day arrives. You can also read comments from other users, which might alert you to any problems or questions about a specific product.

The products are ordered directly from the vendor, so you’re buying the full, current version of the product, not a limited version. The products appear to be downloads, so those of us who are outside of the USA shouldn’t have to worry about shipping problems.

A quick visit to the site, once a week, will keep you up to date, and then you can return if you’ve decide to buy something, on the day it’s offered.

Customize Windows Open and Save

Many programs use the Windows Common dialogs which you’ll see when you Open or Save files in those programs.

For example, in Notepad, if I select File►Open this dialog appears. The bar at the left is the Places Bar, and it contains 5 standard icons.


Not once, in my entire computing life, have I used that History folder, so it’s just a big waste of space.

The Desktop and My Computer icons aren’t used too often either. Usually I click on My Documents, and hunt for the folder I need. I use the My Pictures folder several times a day, while blogging, so it would be nice to have it one click away.

There’s a free utility, PlacesBar Tweaker, that will let you rearrange the folders in the Places Bar, or replace them with folders you prefer.

I removed History and Desktop, and substituted My Pictures and my external hard drive.

The program was simple to download and install, and very easy to use.

  • Click on an icon, then use the arrows to move it up or down in the Places Bar.
  • Or, click on an icon and select a different folder to replace it.


It only took me a few minutes to download and install the utility, and rearrange my Places Bar. I’m sure that investment will pay off very quickly in time saved.

Note: Currently PlacesBar only works for Users who have Administrator privileges.

Hide Used Items in Excel Drop Down

One of the most popular downloads on my web site is an Excel file that uses Data Validation to assign players to a position each inning in a ball game.

Since the baseball file is so popular, maybe I should make a golf sample file next. How many innings does it have? 😉

Selected Player Names Removed

After a player’s name has been selected for an inning, that name disappears from the inning’s drop down lists.

For example, in the screen shot below, Fred’s name is missing from the data validation list, because he was selected as Pitcher.

Selected Player's name removed from drop-down list
Selected Player’s name removed from drop-down list

Blank Spaces in List

In the example shown above, the data validation drop down lists have blanks where the used names were.

This isn’t a perfect solution, but it was a quick and relatively simple solution to the problem of assigning players without duplicating.

However, I’ve created a newer version that you can download, and it removes the names, without leaving blank spaces.

Selected Player's name removed - no blanks
Selected Player’s name removed – no blanks

Select Employees for Tasks

There are other “Hide Used Item” examples too, in case you’re not coaching a baseball team!

For example, assign employees to a work schedule, and their names are removed from that column’s drop down list.

Selected employee names removed from list
Selected employee names removed from list

Get Hide Used Items Workbooks

To get the baseball player sample file, and other other “Hide Used Items” workbooks, go to the Hide Used Items in Drop Down list page, on my Contextures site.

The page also has step-by-step instructions for setting up the items lists, and the data validation drop downs, in the data entry sheet.
______________

Speed Up the Start Up

I don’t reboot my desktop computer too often, but when I do, it takes extra time because of all the programs that have elbowed their way into my Startup folder. The system tray fills with the icons for these programs, and I manually disable them, which takes even more time.

After every reboot, I promise myself that I’ll turn off these annoying startups, and limit the programs to those that I actually want or need. Well, my long procrastination is over, and I’m clearing out the riff-raff.

If you’ve been delaying this process too, you can follow these steps to trim the list in your computer’s Startup. I’m using Windows XP, so your system might do things differently.

Remove a Program from the Startup Shortcut List

Some programs appear in the Startup Shortcut list and are easy to delete.
Update: In the comments, Dave suggested moving the startup shortcuts to a different group, so you can restore them easily if you change your mind.

  • Click the Start button in the Windows Taskbar.
  • Click All Programs, then click on Startup
  • Right-click on a program that you want to remove from the Startup list

  • Click on Delete, to remove it from the list
  • Click OK, if prompted, to confirm the deletion.

Remove Other Startup Programs

For startup programs that don’t appear in the Shortcuts list, you can change your System Configuration settings. Do this at your own risk!

Only uncheck an item if you’re sure it’s a program that you don’t need running at startup. As a precaution, create a system restore point before changing the System Configuration.

Update: In the comments, Dave suggested that you make a list of the items you’re removing, so you can add them later, if necessary. Good idea, and I’d take a screen shot with SnagIt, since I’m too lazy to write a list. 😉

  • Click the Start button on the Windows Taskbar.
  • Click Run
  • In the Run dialog box, type MSCONFIG and click OK.
  • Go to the Startup tab.
  • Widen the Command column, to see more of the program location details.

  • If you’re sure that you don’t need the program to run at startup, click on the check box to remove the check mark.
  • Click OK, to close the dialog box, and choose to Exit Without Restart, when prompted.

Control Key Tricks in Excel

Last week I listed a few tricks with the Shift key in Excel, and in the comments, Jon Peltier mentioned a trick he’d discovered with the Ctrl key.

Here are a few more things you can do with the Ctrl key in Excel.
There are many other Ctrl tricks, so if you have favourites, please share them in the comments.

Select Nonadjacent Ranges

This is my favourite use of the Ctrl key — to select nonadjacent ranges. For example:

  1. Click on cell A1 to select it
  2. Press the Ctrl key, and click on cell A5, then click on cell B3
  3. Release the Ctrl key, and all three cells are selected, with B3 as the active cell.

Enter Data in Several Cells

After you’ve selected several cells, adjacent or nonadjacent, you can enter the same data or formula in all of them

  1. Select the cells, and type the data or a formula.
  2. Press the Ctrl key, then tap the Enter key, to enter the data or formula in all the selected cells

Show the Active Cell

Sometimes I scroll to the bottom or far side of a worksheet, to view something, then want to get back to the active cell quickly.

  • To show the active cell, press Ctrl + Backspace

Select Precedent Cells

If you’re analyzing a formula, you might want to go to the precedent cells — those cells that are used in the current cell’s formula.

  • Select a cell with a formula that refers to other cells
  • Press Ctrl + [

The first cell referenced in the formula will be the active cell, and other referenced cells on the same sheet will be selected.