Yes, I definitely need more space for all this office stuff. At least that was my first thought as I looked around my office on the weekend. Even though I cleared out a few bookshelves recently, there’s just not enough room in here for all the remaining books, files, computers, printers, gadgets, project binders, and penguins.

So, I thought about a trip to Home Depot, to buy a shelving unit that would make better use of the corner space, and maybe hide some of the tangle of wiring.

Fortunately, I came to my senses and realized that the problem isn’t storage space. The problem is stuff.

• Do I really need those notes from projects I worked on in 1995. (No, I’m not exaggerating.)
• Why am I keeping those disks for CorelDraw 7? I haven’t installed it on my past 4 machines.
• When was the last time I used a floppy disk? And why do I have all those boxes of them in the storage closet?

Fire Up the Shredder

This week I’ll be keeping the shredder busy, as I clear the file drawers and storage boxes of old documents. The old floppy disks and CDs with client information will have to stay for now, until I figure out a way to securely dispose of them.

Any ideas on how to get rid of them? My shredder is only designed for paper, so maybe I’ll have to buy one of those fancy new ones that eats anything.

In the meantime, if you need me, I’ll be under that pile of paper in the corner. And don’t worry, the penguins are safe.

Filed under: Office Supplies, Organization, Storage | 2 Comments »

Another week in Twitterville, with the usual whining about Excel crashes and lost data. Here’s a tip — save your work in Excel every 10 minutes. Don’t blame Excel when you lose a day’s work because you never bothered to hit the Save button. Geez.

Oh, and Excel comes with a Help file — don’t be afraid to use it.

Incredibly Annoying

• it’s incredibly annoying to not be able to take excel sheets outside of the excel program window
• I keep running these massive, macro-driven excel reports while having ~17 other apps going. Why is my computer whining?
• “Satan will take the form of Excel spreadsheet cell G-14 this week and refuse to assume the proper formatting. “
• Damnit - I’ve had my recommended lifetime dosage of Excel three times in a row now. Next thing, I’m thinking in $Absolute $References…
• Always, *always* been able to help co-workers with every conceivable Word and Excel issue, But MS is working just as hard to stop that.
• I don’t mind so much when Access and Excel try to think for me, but when they’re wrong, I expect them to listen to me.
• …cries salty tears over Excel. LOOKUP requires data to be sorted in ascending order? Why didn’t you JUST FRICKIN’ SAY SO! 2 hrs lost…

Pretty Fancy

• I need a cheap laptop- all it has to do is show me DVDs on Windows media and run excel.
• Just calculated that I’ve spent over 15hrs in Excel since Saturday afternoon. The business world runs on XLSX files, that is certain.
• I love the smell of Excel and Reports in the morning
• thanking an irrational, imaginary deity for Excel (no, not Bill Gates who is very real)
• I’m an excel chart badass! Not really, but I did feel pretty fancy for like, 5 seconds.

Mmmm…Excel Food

• Planning turkey dinner for 25, using Excel spreadsheet with Gantt charts and pivot tables. Does it get any geekier than this?
• playing with dry & fluid ounce conversions in an Excel spreadsheet. Yep, I’m a baking geek.

Hmmm…

• AC/DC’s video-in-Excel is great, but it raises an alarming question: how’d a generation of teenage stoners become accountants?

Filed under: Excel, Twitter | No Comments »

In Microsoft Word, recording a macro is just a double-click away.

Normally, to record a macro in Word, you’d click on the Tools menu, then click Macro, then click Record New Macro.

A much quicker way is to double-click on the REC box in the status bar, at the bottom of the Word window.

The Record Macro dialog box immediately opens, and you can begin recording.

Double-click the REC box again, to turn off the recorder.

Other Shortcuts

There are other double-click shortcuts in the status bar:

TRK: Toggle the Track Changes feature

EXT: Toggle the Extend Selection feature

OVR: Toggle the Overtype feature (does anyone use that?)

Page Number (or any area in the left end of the Status Bar): Open the Go To dialog box

Filed under: Mouse Shortcuts, Word | 3 Comments »

If you use Feedburner to manage your blog’s email subscriptions, you can customize the activation letter that goes out to new subscribers. Instead of using the default address, subject line and email body, you can use your own greeting. I just discovered this feature, so if you got a boring, generic greeting from me, I apologize!

How To Customize the Email

Log in to Feedburn and click the Publicize tab

In the list of Services, click on Email Subscriptions

In the list that appears below Email Subscriptions, click on Communication Preferences

In the Communication Preferences page you can modify your From address, the Subject line, and the Body text. There are instructions below each box to help you.

Even if you just add a few words of your own, like “please” and “thanks”, it will improve the default message. You could also add the URL of your main site (it won’t be clickable), in case the subscriber hasn’t found that yet.

When you’re done, click the Save button, and log out of Feedburner.

Filed under: Blogging, Communication | 4 Comments »

A couple of weeks ago I started reading Never Procrastinate Again, and promised to report on what I thought of the material, and how well it worked for me.

Reasons For Procrastination

This is an audio course with written transcript and worksheets, by Dave Navarro from the Rock Your Day blog. I listened to a few minutes of the audio files, but those just don’t suit my learning style, so I used the written transcript instead. There are also video files (about 100 MB) that you can download separately, but I didn’t bother with those.

The key to this course is identifying why you’re procrastinating, by reviewing ten possible reasons in a worksheet. The ten procrastination causes fall into three basic categories:

1. I don’t like to do it.
2. I don’t know how to do it.
3. I’m afraid to do it.

Beside each of the ten reasons there’s a suggested method for overcoming your resistance, and there’s space to write little pep talks to yourself. On another worksheet you can schedule time to practice these pep talks.

My Checklist

I haven’t bought into the self pep talk approach, but the list of procrastination reasons was thought provoking. So, I wrote a list of work-related things I have to do, and things I want to do, both long and short term. A few of those things made my stomach knot when I thought about them, so I ran a couple of those through the reasons checklist.

The first example was invoicing. Why do I avoid that task, even though I like getting paid for the work that I do? The reason seemed to be a mixture of dislike and fear. It’s a bit tedious, even though I have a database with all the information, and the reports and queries help make it easier. The fear is that I’ll make an error in an invoice or that someone will question an item or two.

My Solution

I decided to work on the October invoices and create a set of instructions as I worked. Next month I won’t have to remember all the details, I’ll just follow the written steps. I’ll also look for ways to make things easier for myself.

I built a few error checking steps into the instructions, so that alleviates some of my fear. I also realized that my time sheet data entry needs a bit of improvement, so I can clearly describe the work that’s being invoiced for on-going projects. That should reduce any questions about the invoices.

Break Down the List

The other thing I realized is that several of the things on my to do list need to be broken into smaller pieces. That will make those items less scary, and make it easier to know how to do them.

I keep my list visible on the computer desktop all the time, so that helps me stay focused. Instead of wandering off to explore something new, the minute I think of it, I add it to the list to work on later. When I’m between billable work sessions, I can pick something non-billable from the list to work on. So far, it’s going pretty well.

The Verdict

The Never Procrastinate Again course helped me get started on a few things, even though I didn’t follow all of the author’s suggestions. Maybe the self talk and role playing works for some people, but I’ll pass on those. However, the checklist is great for identifying problems, and for realizing that some tasks are too big to tackle in one piece. Next time a task makes my stomach knot, I’ll run it through the checklist.

Filed under: Productivity, Time Management | 6 Comments »

While working on client projects, I enter all my timesheet data in Excel because:

• Excel’s usually open, so it’s the easiest program for me to use
• that’s the way I’ve always done it
• other important reasons that are long forgotten.

However, I create my invoices in Access, so I have to move the data from Excel to Access, usually at the end of the workday. Last week, JP asked about the code that I use, so here’s how it works.

Filter the Completed Items

On the Excel timesheet there’s a Send to DB button that runs a macro to filter the completed rows to a different worksheet. The Advanced Filter extract range has just the columns that I need for the export, in the order that I want them.

Send Data to Access

Once the data’s on the export sheet, I give it a quick glance, to make sure everything looks okay. Then I click the Send to Access button at the top of that sheet. It runs a macro that opens an ADO connection to the database, inserts the Excel data, and closes the connection. Finally, it clears the export range, to remove the data.

How It Works

For the export code, the connection string and command text string are on the QueryStrings worksheet in the the Excel workbook. I enter the info in the green cells, and the strings for the macro are calculated in the white cells.

This makes it easy to modify the connection strings. For example, if the database moves to a different folder, I just type the new address in the Database cell.

I’m not a connection expert, so perhaps this can be improved, but here’s my code:

‘=======================

Sub SendDataToAccess()

Dim wsQS As Worksheet
Dim sConnect As String
Dim sCommand As String
Dim adoCn As ADODB.Connection

Set wsQS = Worksheets(”QueryStrings”)
Set adoCn = New ADODB.Connection
sConnect = wsQS.Range(”rngConnect”).Value
sCommand = wsQS.Range(”rngCommand”).Value

‘ Get ADO connection to the workbook
adoCn.Open sConnect
‘ Append data from Excel worksheet
adoCn.Execute sCommand

‘ Close the connection to the workbook
adoCn.Close
Set adoCn = Nothing
Worksheets(”CopyToDB”).Range(”DataToExport”).Offset(1, 0).ClearContents
Worksheets(”Proj DB”).Activate

Set wsQS = Nothing

End Sub

‘========================

Filed under: Access, Data Entry, Excel, Time Management | 8 Comments »

When I open Google Reader first thing in the morning, there are a few blogs that I always read, if they’ve posted any new articles. Most of those I read within Google Reader, and occasionally click the link to go to the blog, to read the comments or find the end of a partial feed’s article.

Mark Them

Then I click the All Items link, skim through the articles in List view, and add a star to any items that look interesting. This keeps me from spending a couple of hours reading blogs, when I should be getting down to work.

Read Them

Later in the day I view the list of Starred items, and click on an item to skim through it.

After reading an article (or the first couple of sentences), I usually remove the star, but leave a few marked so I can go back to them. This helps me when I don’t have enough time to read a really long or complex article, or return to articles where I’ve left a comment.

Review Them

If you’re really obsessive compulsive, you can use the Trends feature to analyze the stars you’ve added. To me, it’s slightly interesting, but not too useful. I use the Read page when trimming my feed list, since it gives me a good idea of what I’ve found useful over the past few months, and what’s been clogging up the feed list.

How Do You Use Google Reader?

• Do you use Starred items in Google Reader?
• Trends?
• Any of the other features?

Filed under: Blogging, Productivity | 2 Comments »

November? How did that happen? Again this week there were lots of tweets about the AC/DC video distributed in an Excel workbook, but I’ll spare you from reading them. There was even a timely USA election related tweet, and I hope it’s the last one for another four years or so!

And although I’m a Virgo, that first one isn’t from me. What kind of Virgo posts a typo?

Thrilling

• am making lists. Gleeful, exhaustive, Virgotastc lists. In Excel.
• Back to crunching numbers. I can’t imagine doing this in the days before Excel. Must’ve been hell- candle wax dripping onto ledger books…
• I get a sick thrill from financial modeling on excel spreadsheets. I love the tidy perfection and instant formulaic gratification.
• who would’ve thought you could have this much fun with excel pivot tables.
• 2nd fav feature of Excel 2007 - having ‘wrap text’ on home ribbon. Saves oodles of time when doing my biz plans
• new personal record for largest Excel workbook - 1,533,492 cells used!!! o.0

Macro Magic

• Why is it every time I record an Excel macro and think that’ll be good ‘nuf…I find myself re-writing the whole thing?
• VBA in excel can be the coolest thing ever when you get it to work. Otherwise it can send a sane man to insanity über fast.
• Loves the person that wrote the Excel Plugin for Find and Replace within cell comments.

Make It Do This

• Who knew that working a voter hotline actually meant tackling big ol’ excel spreadsheets? The lengths I’ll go to for Barack…
• I am on excel writing Italian American Places in Chicago!! CIAO A TUTTI!! i wish i COULD add more people on this thing
• When an IT leader suggests using Excel for long term data storage in a company with multi-TB SQL servers, it’s time to panic.
• lots of things that i’m not sure that excel is prepared to do. basically my boss drew something and said: make it do this.

Can I Go Blind?

• thinking that making timelines in excel is nearly impossible. giving up on that and trying to find something new.
• staring at balance sheets that don’t balance and thinking that there has to be a better way of managing projections than Excel spreadsheets.
• Nice. Excel 2007 just corrupted another document I was working directly in. Great. Such an advancement over the last version.
• Can I go blind from looking at Excel for 8 hours a day, every day?
• I hate excel. It shouldn’t take longer to make a graph than to do the experiment the data comes from.

Filed under: Excel, Twitter | No Comments »

I keep track of my tasks in an Excel file, where I have macros that export the completed data to Access for invoicing.

I check the list every morning, and at the end of the day, but not very often throughout the day. So, while I might have an excellent plan for the day, it can go off the rails if I get distracted.

NextAction is a little program in which you can list your activities, then mark them as done throughout the day. It’s a free download available on the TimeSnapper website. I’m testing it to see if it will help me keep on track.

When it’s not active, it’s still visible on my desktop, in the bottom corner of the second monitor.

So far, the benefits seem to outweigh the negatives.

Benefits

• I can copy and paste the current day’s task list from Excel, so there’s no time wasted in retyping anything.
• The window stays on top of other programs (optional setting) with adjustable transparency. This keeps my task list visible, no matter what else I’m doing, and reminds me of the day’s list.
• To keep track of start and end times, I can mark the task as Done when I start it, to create a time stamp. When I’m finished, I mark it Done again, to get another time stamp.

Negatives

• There’s no way to export the data, so I have to copy and paste the completed tasks back to Excel. If I decide to keep using NextAction, I’ll write an Excel macro to clean up the pasted data and calculated the time spent on each task.
• No drag and drop in the text editing, so I had to cut and paste to move things around.
• After changing a couple of the settings, it lost its Stay On Top capability, even though that setting was still turned on. Exiting and reopening seemed to fix it.

Filed under: Excel, Time Management | 5 Comments »

As you work in Excel, the files that you’ve used most recently appear at the bottom of the File menu. The default number of files that appear is 4, and you can increase this to 9, in the Tools>Options dialog box, on the General tab.

If you’d like more than 9 entries, and some control of the list order and names, you can download and install Ken Puls’ XLG Favourites Add-in. Stephen mentioned this add-in yesterday in the comments, and I’ve installed it to try it out.

The add-in creates an XLG item in your File menu, where you can open and manage a list of favourite files.

You can quickly add the current workbook to the list, and use the Manage XLG Favourites command to rename the items, and change the order of the list.

I’ve got several files that I uses every day, and this should make it easy to find and open them quickly.

Filed under: Excel, File Management, Toolbars | 2 Comments »