Excel tips and tutorials
It’s been a long week. Do you still have the ability to count?
If your machine has Java installed, and it’s active in your browser, you can watch this brief video. Your task is to count the number of times that the basketballs change hands.
To see the answer, you can read the results of this experiment.
Maybe that’s why so few people read the messages that we put in our Excel applications. They don’t even see them!
If you’re a software developer, you might be interested in this. Neil Davidson has created the Business of Software social network, “For anybody interested in building long term, sustainable, profitable software businesses.”
Meanwhile, if you’re stuck on a project, whether it’s software or something else, the SCAMPER Random Question Tool might help dislodge your mental blocks.
To understand the theory behind the tool, you can read the SCAMPER guide.
Last month, Dick Kusleika, at Daily Dose of Excel, said, “Get the same laptop at work that you have at home. I liked my D810 so much that when I started a new job I got a virtually identical machine. Now that my personal D810 is at Dell getting fixed, I can use my work laptop by just swapping hard drives.”
They’re using a similar idea at Citrix, where employees can buy their own laptop and maintenance plan, then use it at work, as well as home. The company provides $2100 for the purchase, but if employees leave within three years they’ll have to repay some of the money. Maintenance will be the employee’s responsibility.
Why would you want to buy your own computer and use it at work?
I guess it’s a nice perk if you can’t afford your own computer at home, since you’d own this one after three years. Maybe the policies on what you can install are less strict than usual IT policies, so you can have games or other personal favourites on the machine.
What happens when it’s broken though? Do you use a sick day to take it to the repair shop?
Given the option, I’d leave the laptop in the company’s hands, assuming I could take it home when necessary. Life’s complicated enough, without having to be my own IT department.
Last Thursday I asked if you use checklists, and if someone could cover for you if you were away.
Who knows enough about Excel do do those tasks?
Based on your comments, it looks like we could use some improvement in that area!
In the same vein, what about backups? What happens if you show up for work, but your key files don’t?
When I’m writing, or working on a client project, I upload all my Excel files to my online storage account at the end of the day.
That way, the current project files are available off-site, in case of fire, flood, tornado, can’t remember where I live, whatever.
At one of my clients, they have extra server disks, and the latest one is taken off site at the end of the day.
For on-site storage, the Excel files from my desktop computer are backed up overnight to an external hard drive, and the laptop files are backed up to its external hard drive.
The backup program doesn’t copy the files from the Program Files folder though, so I have to remember to do that myself, once a week(ish).
It feels like there are holes in my backup bucket, so maybe I need to add some steps.
Last Friday I arrived in my office about 8:30 AM, ready to tackle several small projects, and get them sent off to clients. Full of motivation and caffeine, nothing could stop me. Nothing, that is, except a dead Internet connection. The first clue was MailWasher, lying on its back with its paws in the air. No email was coming in or going out.
Excellent! I couldn’t get to YouSendIt and upload or download client files. A client asked me to check some files on their network, but Citrix doesn’t work either, if you can’t connect to it.
So, the first hour of the workday was spent muttering, assuming the connection would be back up shortly. That didn’t happen, so I spent 1/2 hour on the phone with the ISP’s tech support, diagnosing and fixing the problem.
Apparently my static IP had become a little too static, and that was fixed by unplugging the modem for five minutes. I’ve made a note in my ISP manual, beside the tech support phone number, so I’ll remember to try that next time, before wasting my time on the phone.
Finally back in action, I managed to complete most of my to-do list. I’ll be travelling with my laptop this week, and hope it all goes smoothly. (What could possibly go wrong?)
And speaking of the Internet, how have I been using the it for all these years, without knowing this trick?
Here are a couple of quick Microsoft Excel tips for you . Try these tips a few times today, and maybe you’ll still remember them by Monday.
I often have to insert a few cells in a list, so here’s how I would do that, using the Excel Toolbar commands:
That method works very nicely, but it’s four clicks. I don’t have time for four clicks!
Recently I learned that you can quickly insert cells with a mouse shortcut!
Here are the steps:
Blank cells are inserted, and the other cells shift, to make room for the new cells
You can use a similar technique to delete cells too.
Here are the steps:
The cells are deleted, and the cells below shift up
Yesterday I compiled the Excel newsgroup statistics for September, a monthly task that entertains me, even if no one else reads them. Actually, the page gets a few hundred hits every month, so I guess a few other people find the numbers interesting too. Or maybe it’s one other person, with OCD.
Pulling the report together isn’t too complicated, but there’s a long series of steps involved — export messages from Agent, import to Access using QDN Stats, export totals from Access as HTML files, publish in Dreamweaver.
I have a checklist in Excel, and I print that, and follow it every month. Without that list, the process would take at least twice as long, because I’d have to think about every step, and probably miss a few.
Maybe the list is too detailed, but I’d rather include the seemingly obvious items, just in case someone else had to cover for me one month. (If you’d like to volunteer, please leave your name at the front desk.)
I have a few other checklists for things that I do occasionally, and they’re real time savers. Most of the lists are stored in Excel, so they’re easy to sort and edit, and make nice little check boxes beside the items.
Do you use checklists, or is your memory better than mine, and you just wing it?
Could someone cover for you, and get all your key tasks done, or is your business on hold if you’re away?
A few times a day I get email that asks for a response receipt, and it’s usually from someone I’ve never heard of, asking for help with an Excel problem. Don’t these people trust the Internet? Are they the same people who used to call to see if you got the fax they had just sent?
I always click No to the requests, because it’s nobody’s business what I’m receiving or reading. Today, I finally got annoyed enough to find out how to turn these requests off, once and for all. Annoyance>Inertia=Action.
After poking around in Outlook 2003 for a couple of minutes, and clicking quite a few buttons, here’s how I did it.
So, if you send me an email, please don’t be concerned if you don’t receive a response receipt. Just assume that I got your email, and I’m busy writing a reply. 😉
Last week a client mentioned that he uses lots of message boxes in his Excel code, and finds that users just click OK when a message appears.
Most of the messages are informational, and users have read them before, so a quick click is fine.
However, some of those messages are critical, and only appear when an important decision has to be made.
Because the users are so accustomed to seeing messages, they just click OK, without reading. That could cause problems!
For example, the Excel message shown below says:
Busy office worker, clicking the OK button: “Sure, why not!”
I suggested that he add an icon to the critical messages, so they stand out from the rest.
Here’s the current code for this message.
To add a warning icon to the message, follow these steps:
Now when the macro runs, the critical icon (red X) appears in the message.
That won’t force anyone to read the message, but the red X might catch their attention, and make them more likely to read it.
Do you have the inner strength to throw out old books?
I love computer books, and bring home way too many of them. Things were bad enough in the old days, when I had to leave my office and drive to a bookstore, in order to buy books.
Then the miracle of online shopping occurred, and now I can browse through the Amazon catalogue any time of night or day. A few mouse clicks, and there’s a pile of books headed to my front door.
The shelves in my office are crammed to capacity, and it’s time to purge some of the books, or buy new shelves. Since I’ve run out of wall space, additional shelving isn’t really an option, so purge it is.
Here are some of the books I’m releasing into the wild today. The internet has changed a bit since I started using it, and I don’t use Office 97, Lotus Notes, FrontPage or PageMill these days. Judging by the publication dates, you can see that I don’t clear the shelves too often. It’s painful!
There were several others, as you can see in the photo below. It cleared 18″ of shelf space, and reduced the load by about 30 pounds (my non-scientific estimate). Still lots that could be tossed, but this is a start.
I’m keeping this one though, because it’s an essential office reference guide:
So, I’ve come clean. What’s on your bookshelf that’s ready for the toss pile?