Random Number Generator for Excel, Revisited

Another, simpler way to do it.

Nearly five years ago, I wrote a short article about creating a simple spreadsheet to generate random numbers in Excel. Lately, it’s been the most popular post on this site.

Trouble is, it’s a bit outdated. There’s actually an easier way to do the same thing: with the RANDBETWEEN function.

The RANDBETWEEN(bottom,top) function “calculates” a random number between the bottom number and the top number. Just provide those two numbers and Excel does the rest.

The benefit of this function is that you don’t need to go through a complex calculation to tell Excel you want a whole number within a range. RANDBETWEEN returns whole numbers automatically. It can also output the top and bottom values in the range. And it might actually be more random than my original solution because you’re not dependent on rounding rules to get the final number.

ScreenshotSo which would you prefer? The old way, with a formula like this:

=ROUND(RAND()*B6-B5)+B5,0)

or the new way, with a formula like this:

=RANDBETWEEN(B5,B6)

I know which one I prefer.

Download the revised sample worksheet here.

Sorting Excel Data: The Basics & Beyond

A definitive guide to sorting data managed in Microsoft Excel.

Sorting Excel Data cover

This guide takes the mystery and confusion out of Excel’s sorting features. It starts by covering the basics of simple, one-column sorts. It then builds on that information to explain multi-column sorting, setting up and using custom sort orders, sorting based on cell colors or icons, performing case-sensitive sorts, and sorting by rows instead of columns. Step-by-step, fully illustrated instructions make it clear what you need to do. Sample files make it easy to repeat exercises so you can see the same results.

Although this book concentrates on Microsoft Excel 2010 for Windows and Microsoft Excel 2011 for Mac OS, it also provides useful tips and instructions for previous versions of Excel.

Buy Kindle Edition
Buy iBooks Edition
Buy NOOK Edition

I’m really pleased to announce that the second book in the Maria’s Guides series — Sorting Excel Data: The Basics & Beyond — is now out and available in three ebook formats, with a print edition on the way.

About the Book

It all started as a question asked by a friend in Facebook. An experienced computer professional, she didn’t know how to perform a four-column sort in Microsoft Excel. I thought back to my computer applications training days and remembered how my students struggled with Excel’s sorting features. I decided it would make a good topic for a Maria’s Guide book.

While researching and writing the book, I realized just how much Excel’s sorting feature has changed since I wrote my last Excel book several years ago. While it was obviously important for me to cover the most recent Windows and Mac OS versions of Excel, I also wanted to explain complex sorting to folks who haven’t yet upgraded. I think the book does a great job of completely covering how to sort data managed in Excel.

Inside, you’ll find the following chapters:

Table of Contents
Before We Begin: Introduction
Chapter 1: Sorting Basics
Chapter 2: Quick Sorts
Chapter 3: Multiple-Column Sorts
Chapter 4: Sorting by Color & Icon
Chapter 5: Using Custom Sort Orders
Chapter 6: Exploring Sort Options
Chapter 7: Sorting with Filters & Tables
Conclusion: That’s Everything

The printed version of the book runs 114 pages, including front matter, table of contents, and index.

The book uses several example worksheets, all of which are contained in a single workbook file. Readers are encouraged to download the sample file and follow along. This ensures understanding, since readers get the same results that appear in the book.

Buy the Book

The book is currently available as an ebook from three sources (so far):

The print edition is currently going through the proofing process. Once approved, it will be available on Amazon.com and BN.com, as well as by special order through your favorite bookstore.

Additional Material, Feedback, and Support

You can find additional material about Excel on this site. Just follow the Excel topic link.

You can also post questions and read questions and answers on the book’s support page. That’s also where you can find the sample workbook file used throughout the book.

Category Feeds Being Removed

As part of the site revision process, I’ve decided to do away with the category-specific feeds. These feeds, which cover Excel, Mac OS, Word, and WordPress content, are being utilized by less than 100 people. If you’re reading this message in your feed reader, YOU might be one of them.

Within a month or so, these feeds will simply not work. Delete them from your reader.

If you want to continue receiving content from this site via RSS, please subscribe to the main feed, using one of the following URLs:

Creating a Time-Lapse Calculator with Excel

A quick way to perform movie-making calculations.

One of my hobbies is photography and I dabble occasionally with time-lapse. In time-lapse photography, you set up a camera on a tripod to take a photo at a set interval, like every 15 seconds, over a long period of time, like hours. When you’re finished, you take the resulting images and compile them into a movie using each photo as a movie frame. The length of your movie is dependent on the number of shots and the number of frames per second (fps) at which they are compiled.

I wanted to be able to easily calculate various values for a time-lapse movie project based on certain values I provide, which I call “assumptions.” For example, how many seconds between shots if I want 1200 shots over 3-1/2 hours? How long would a movie be if I took shots over 10 hours with 15 seconds between shots and compiled them at 30 fps?

This is basic math, but with a twist. I wanted to be able to solve for any one of three source photo values given the other two values:

  • Time period, in hours
  • Seconds between shots
  • Number of shots

Given that information, I also wanted to be able to solve for either of two resulting movie values:

  • Frames per second
  • Movie length, in seconds

Time-Lapse CalculatorThe resulting Time-Lapse Calculator shown here does the job.

The formulas I put in the green cells are shown below. The IF function tests to see if cells are empty and uses the test result to determine whether it needs to perform and display a calculation. For example, in cell D6, it checks to see if B6 is empty; if it is, it calculates the result based on B7 and B8. Because the last two formulas require data from either cell B8 or D8, they also test to see which one contains data. The result is a nestled IF statement.


D6=IF(B6="",B8*B7/60/60,"")
D7=IF(B7="",60/(B8/B6/60),"")
D8=IF(B8="",B6*60*60/B7,"")

D11=IF(B11="",IF(B8<>"",B8,D8)/B12,"")
D12=IF(B12="",IF(B8<>"",B8,D8)/B11,"")

You can download a password-protected copy of the worksheet here. (The password is not available for distribution.)

After completing this worksheet and beginning to write about it, I realized that it’s not everything I envisioned. What I really wanted was to calculate one of the following based on the other three:

  • Time period, in hours
  • Seconds between shots
  • Frames per second
  • Movie length, in seconds

I’ll likely work on this in the future. If I finish it, it’ll appear here on Maria’s Guides.

Want to learn more about Excel?

Check out my most recent Excel books and video training materials:

And be sure to use the Excel link in the sidebar to track down other Excel articles like this one on Maria’s Guides.

Spelling Checkers Don’t Work if You Ignore Them

Reminding my brain not to block out the red underlines that indicate potential spelling problems.

Check Spelling as You Type has been a built-in feature of many word processors for years. It’s now in most applications I use — including my Web browsers, for Pete’s sake! — and the red squiggly or dotted underlines are an integral part of my writing life.

If you’re not sure what I’m talking about — do you live in a cave? — I’m referring to the feature that indicates when a word you’ve typed may have been spelled wrong. This is supposed to flag the word so you can check and, if necessary, correct it.

A Blessing…

I vaguely remember when this feature first appeared in Microsoft Word years ago. It was a blessing — and a curse.

I initially loved the feature because it often identified my typos. I’m a touch typist and can get up to 80 words per minute when I’m tuned in. But those aren’t always error-free words. Check Spelling as You Type was a great feature for finding typos as I worked, eliminating the need to run a spell check periodically or at the end of document creation.

As you might expect, it also found spelling errors. My spelling was always pretty good, so it usually found more typos than actual spelling mistakes. But that’s okay. An error is an error and I want to remove all of them from my work, whenever I can.

…and a Curse

But over the years, I’ve found some problems with the Check Spelling as You Type feature — and spelling checkers in general.

The feature does not identify all typos or spelling errors as errors. For example, suppose you type bit but you really meant but. A spelling checker doesn’t see any problem with that, so it won’t flag it. That means you can’t depend on a spelling checker to proofread your work. (And yes, in case you’re wondering, a grammar checker would likely identify this as a problem. As well as the sentence you’re reading right now, because it isn’t really a proper sentence. And this one, too.)

So you’ve got a feature that makes you lazy by doing about 90% of the proofreading work for you, as you type. If you neglect to do the other 10% of the proofreading work, you could be very embarrassed — especially if you write professionally and editors expect your work to be error-free.

The unflagged error that zaps me most often? Typing it’s instead of its. At least I know what it’s supposed to be.

It’s worse, however, for people who don’t know the correct word. How many times have you seen people use then instead of than? There instead of they’re or their?

The feature has degraded my spelling skills. In the old days, before spelling checkers, I simply knew how to spell. If I wasn’t sure of the spelling of a word I needed, I looked it up in — can you imagine? — a dictionary. It made it worthwhile for me to actually learn how to spell words. Knowing the proper spelling saved me time in the long run.

But now, I simply type the word as I think it might be spelled and wait to see if it’s flagged. If it is, I use a context menu — Control-click or right click the word — to choose the word I meant to type. Yes, it’s convenient. But I seem to be doing it an awful lot more than I used to use a dictionary.

(Perhaps it’s also expanding my vocabulary by making it easier to use words I’m not as familiar with? There’s something there.)

The feature identifies any word it does not know as a potential spelling error. That means that if your document is filled with jargon, technical terms, place names, or other words that do not appear in a dictionary, those words will be flagged as possible errors. The word unflagged, which appeared earlier in this post, was also flagged. Is it an error? Or does my spelling checker simply not recognize it? Seems like a word to me, so I let it go.

And herein lies my biggest problem: I’m so accustomed to seeing words flagged in my documents that I’ve managed to tune out the red underline. (It’s kind of like the way we all tune out advertisements on Web pages these days.) This happened to me just the other day. I typed the word emmerse in a blog post. My offline editing tool flagged it with a red dotted underline — as it just did here. But for some reason, I didn’t see it. I published the post with the error in it. A friend of mine, who referred to himself as a “spelling Nazi,” e-mailed me to point out my error. I meant immerse, of course. He knew that. Readers likely knew that. But I got it wrong and I shouldn’t have. How embarrassing!

Spelling Checker

Here’s a look at the spelling check feature in ecto, my offline blog composition tool of choice. It works just like any other spelling checker. (And yes, I do compose in HTML mode.)

The correct way to go about this is to look for every single possible spelling error and resolve it so those red lines go away. That means learning or adding the unknown word so it’s never flagged again or ignoring it so it doesn’t bother you in this document. All of this should be done with the appropriate menu command. Simply telling your brain to ignore a problem just sets you up to be blind to it when it occurs. That’s not how the software was designed to work.

The Point

This post has a point — most of mine do — and here it is: spelling checkers, including any Check Spelling As You Type feature, are only as good as allow them to be. Use them, but don’t depend on them. Follow up on any flagged words and resolve them using the software so the red underlines go away.

Spelling checkers are just a tool. Like any other tool, it won’t help you if you don’t use it correctly.

Office 2008 Installer Needs Rosetta? Duh-oh!

Just something idiotic I wanted to share.

I rolled off a book project with a tight deadline right into a video project with an even tighter deadline, so I don’t really have time to blog, share new articles here, or even tweet. But I did run across this the other day while I was installing Microsoft Office 2008 on my 13-inch iMac running Snow Leopard:

Office Installer Needs Rosetta

Yes, the Office 2008 installer requires Rosetta to run. Office 2008 doesn’t need Rosetta. Just the installer does.

Hello? Microsoft? You want to make your installer compatible with current hardware and software?

Word 2004 Does Not Like Mac OS X 10.5.8

It may be time to update Office.

I just started work on a new book revision. The project requires me to take relatively lengthy, style-laden Word documents, turn on the Track Changes feature, and edit like crazy. It wasn’t long before I was pulling my hair out.

You see, the other day, I updated my iMac from 10.5.7 to 10.5.8. I suspect that something in that update just didn’t sit well with Word 2004, which I was still running on that computer. After all, the iMac has an Intel dual core processor. Office 2004 was written for the old PowerPC processor that came in older Macs. Whether the problem was Mac OS X’s inability to run the old PowerPC application or Word’s inability to run on the 10.5.8 update is a mystery to me. All I know is what I experienced: text editing so slow that I could type faster than Word could display the characters.

Revisions, RevisionsAt first I thought it might be the document itself. It’s 40 pages of text that utilizes about 20 styles and fields for automatically numbering figures and illustrations. The document was originally created about 10 years ago and has been revised and saved periodically for every edition of this book. It pops from my Mac to an editor’s PC and back at least five times during each revision process. I thought it might have some internal problems. So I used the Save As command to create a new version of the document. The new file was about 5% smaller in size, but had the same symptoms as the original.

Next I sent it over my network to my new 13-inch MacBook Pro. That computer’s processor isn’t as quick as my iMac’s and it has the same amount of RAM. The software on that computer was different, though. I had a developer preview version of Snow Leopard installed and, in preparation for a Microsoft Office 2008 project I’ll be starting in the fall, I’d installed Office 2008 with both major updates. I opened the file on that machine and it worked just fine. Great editing and scrolling speed. Exactly what I needed.

So I bit the bullet and installed Office 2008 on my iMac. And the two major updates. And two smaller updates that became available on August 5. It took hours — the updates totaled over 400 MB of downloads and I’m connected to the internet on a horrible 600-800 Kbps connection that likes to drop. (I’m living in a motel right now, traveling for my helicopter business.)

The result: All the performance issues are gone. Word is snappy yet again on my iMac.

You might ask why a person who writes about Microsoft Office applications had not yet upgraded to Office 2008. This all goes back to last year’s revision on this project. I actually did upgrade but then I downgraded. It was mostly because I needed the macro feature of Word, which wasn’t available on Word 2008. I’d upgraded my iMac last year, but when I decided to reformat my hard disk to ward off computer issues I was having (which were apparently caused by a bad logic board), I reinstalled Office 2004 instead of 2008. You see, I liked the old version better.

But it’s obvious to me now that I need to keep moving forward with the rest of my technology if I want it to perform as designed. Everything must be in sync. If I want to keep using Word 2004, I should use it on a computer that has the system software available during Word 2004′s lifespan. My old 12-inch PowerBook would be a good example. It has a G4 processor and runs Tiger. That’s as advanced as it will ever get. Office 2004 is a perfect match for it.

If there’s a moral to be taken away from this story, it’s simply that if you want your hardware and system software to be new or up-to-date, there will come a time when you’ll have to update the applications that run on it. Bite the bullet and do what you have to. It’ll be worth it.

90 Days on a Wednesday

An Excel solution.

A while back, someone I know here in Wickenburg called me with an Excel question. He needed to calculate the date of the Wednesday least 90 days from an entered date.

So, for example, if he entered 2/14/2009 in a worksheet cell, he didn’t want 5/15/2009 (a Friday), which was 90 days later. He wanted 5/20/2009, which is the Wednesday at least 90 days after the date he entered.

The solution was to use nested IF statements to evaluate whether the day of the week 90 days after the entered date is a Wednesday. If it is, it uses that date. if it isn’t, it does the same evaluation for 91 days after the entered date. And then 92 days. And so on.

90OnWednesdayThe spreadsheet I came up with can be downloaded here. This screenshot shows what it looks like. It has a lot of other stuff in it that I used to test the formula. Notes in the worksheet explain. I saved it just to share it here. You might find it useful if you have a similar need.

Just watch those parentheses.

In Defense of Microsoft Word

It does the whole job.

About a month ago, I was having trouble with my Mac and decided to head off any serious problems by reformatting my hard disk and reinstalling all my software from original program discs. In the old days, before we all had hard drives measured in gigabytes, I did this every single time there was a major system software update. Nowadays, it’s a lot of work and I avoid doing it if I can. My 24″ iMac is just over a year old and shouldn’t have been giving me problems, but I figured I’d try the reformat before bringing it to a genius. (Turns out, it was the swapping out of 2 GB of RAM for 4 GB of RAM that probably fixed the problem.)

For some reason, I didn’t do a typical install of Microsoft Office 2004. I thought I’d save disk space by omitting the proofing tools for the languages I don’t speak — which is every language except English. Word, which I use daily, worked fine — until I noticed that it wasn’t checking spelling as I type. Although my spelling is above average, I count on Word to put red squiggly underlines under my misspellings and typos. No matter what I did, I couldn’t get this feature to start working.

I sent an update to my Twitter account about this as I went about troubleshooting the problem. The result was an outpouring of suggestions from my Twitter friends for replacing Word or Office with other software, ranging from Open Source Word or Office replacements to Google Docs.

Whoa!

I fixed the problem by uninstalling and then reinstalling Word. Life went on. But it got me thinking about Office and Word and why so many people go out of their way to avoid both.

Word and Me

I should probably start off by saying that I have been using Microsoft Word since 1989 or 1990. Although I got Microsoft Works with my first Mac, I soon learned Word and began teaching it in a classroom setting. It was Word 4 for the Mac in those days; I don’t know what the corresponding version in Windows was because I didn’t use it or teach it. I’m not even sure if Microsoft Windows was a player back then.

I’ve used every version of Word for the Mac since then.

My first book about Microsoft Word was The Macintosh Bible Guide to Word 6. Word 6 sucked. It was a processor hog. I remember working with it in beta as I wrote my book about it. I remember whining to my editor, asking if he thought they’d fix the performance issues before the software went out. They did, but not very well. I disliked Word 6 and the way it handled outlines and “master documents.” Everything seemed to be “embedded.” It seemed as if they’d prettied up Word to look more Mac-like and had done the job by pouring maple syrup all over the inside of my computer, bogging things down.

Word 98 was a vast improvement. From then on, each version of Word was an improvement. The interface remained basically the same but features were added and solidified. Some of the features worked with Microsoft server software, which I didn’t have, didn’t want, and certainly didn’t need. All I cared about was that Word did what I needed it to do, using the same interface I knew from years of experience as a user.

The End of the World as We Know It: Office 2007

Then Office 2007 for Windows came out with its ridiculous “ribbon” interface. What the hell was Microsoft thinking? Take a standardized interface that your existing user base knows by heart and throw it out the window. Force them to learn a whole new interface. Keep telling them that it’s easier and maybe a handful of morons will believe you.

I had to use Office 2007 for two Excel books. The only good thing I can say about it is that the complete, radical interface change — I’m talking menus vs. ribbon here, not spreadsheet basics — made a book about the software necessary. How else would users figure out how to get the job done? Fortunately (for users, not authors) Office 2007 adoption is slow.

Woe is Me: Office 2008

Word 2008 Splash ScreenOf course, I’m a Mac user and use the Mac version of Office. I held my breath when Office 2008 came out. Thank heaven they didn’t get rid of the menu bar — although I don’t understand how they could. Office 2008 retains much of the Office 2004 interface. It just adds what Microsoft calls “Element Galleries” and the usual collection of features that 1% of the computing world cares about. Fortunately, you can ignore them and continue using Office applications with the same old menus and shortcut keys we all know.

I would have switched to Office 2008 — I even had it installed on my MacBook Pro — except for two things:

  • Its default document formats are not compatible with versions of office prior to Office 2007. That means someone using Word 2003 for Windows or Word 2004 for Mac can’t open my documents unless I save them in an Office 2004-compatible format. This isn’t a huge deal, but it is something I’d have to remember every single time I saved a document. I’d also have to remember not to use any Office feature that only worked with Office 2007 or 2008.
  • It does not support Visual Basic Macros. One of my publishers makes me use a manuscript template that’s chock-full of these macros. Can’t access the macros, can’t use the template. Can’t use the template, can’t use Office 2008.

(I wrote about these frustrations extensively in a Maria’s Guides article.)

So I’m apparently stuck with Office 2004 — at least for a while.

But do you know what? I’m perfectly happy with it.

Why I Like Word

I like Word. I really do. It does everything I need it to do and it does it well.

Sure, it has a bunch of default options that are set stupidly. I wrote about how to set them more intelligently in an article for Informit.com. (Read “Three Ways Word Can Drive You Crazy[er] and What You Can Do About Them.”) It certainly includes far more features than the average writer needs or uses. And despite what Microsoft might tell you, it’s probably not the best tool for page layout (I prefer InDesign) or mail merge (I prefer FileMaker Pro). But it does these things if you need to.

I use all of the basic word processing features. I use the spelling checker — both as I type and to correct errors. I like smart cut and paste, although I have the ridiculous Paste Options button turned off. I like AutoComplete and love AutoCorrect (when set up properly). I use all kinds of formatting, including paragraph and character styles, tables, and bulleted lists. I rely on the outlining features when preparing to write a book or script for video training material. I use the thesaurus occasionally when I can’t get my mind around the exact word I’m looking for, although the word I want is usually not listed.

I’ve used some of the advanced features, such as table of contents generation, indexing, and cross-references. These are great document automation features. Trouble is, I don’t usually use Word to create documents that require these features. I use InDesign for laying out my books, which are usually illustrated. (And I admit that I’m looking forward to trying out the new cross-referencing feature in InDesign CS4 for my next book.)

I don’t jump on board with every new Word feature. I prefer the Formatting toolbar over the Formatting Palette. I write in Normal view rather than Page Layout view. I create my own templates but don’t use the ones that come with Word.

I don’t use the grammar checker; I think it’s a piece of crap designed for people who know neither grammar nor writing style. I don’t like URLs formatted as links. (Who the hell wants links underlined in printed documents?) I don’t use any of the Web publishing features; I’d rather code raw HTML than trust Word to do it for me. I very seldom insert images or objects or anything other than text in my documents. I have InDesign for serious layout work. I don’t use wizards. WordArt is UglyI think WordArt is ugly and amateurish. I keep the silly Office Assistant feature turned off.

I admit that I don’t use any of the project features that work with Entourage — although I’d like to. I decided a while back to switch to Apple’s e-mail, calendar, and contact management solutions (Mail, iCal, and Address Book respectively) because they’d synchronize with .Mac (now MobileMe) and my Treo. Entourage probably does this now, but I really don’t feel like switching again. Am still thinking about this.

The point is, I use a bunch of Word features and I completely ignore a bunch of others. The features are there if I need them but, in Word 2004, they’re not in your face, screaming for attention. (Wish I could say the same about Word 2008.)

iWork with Apple Computers

iWork '09Lots of people think that just because I’m a Macintosh user — an enthusiast, in fact — I should be using Apple’s business productivity solution: iWork. For a while, I thought so, too.

I own iWork ’08. I just bought iWork ’09. I’ve tried Pages. I’ve really tried Pages. I wanted to use it. I wanted to break free of Microsoft Word.

But old habits are hard to break. No matter how much I tried to use Pages each time I needed to create a document, when I was rushed, I reached for Word. No learning curve — I already know it. After a while, I just stopped trying to use Pages.

Why Use a Bunch of One Trick Ponies?

I know a bunch of writers who swear by one software program or another for meeting their writing needs. They use special outliners to create outlines. They use special “writing software” that covers the entire screen with a blank writing surface so they’re not distracted by other things on their desktops. They use special software to brainstorm, footnote, and index.

I’ve tried these solutions and do you know what? They don’t make my life easier. Instead, they just give me another piece of software to learn and keep up to date and interface with other software. They make more work for me.

I’m not going to forget my Word skills and Word isn’t going to suddenly disappear off the face of the planet anytime soon. In fact, it’s far more likely for one of these one-trick ponies to disappear than a powerhouse with millions of users worldwide like Microsoft Office.

Thought PatternI remember ThoughtPattern, a program by Bananafish Software. I saw it demoed at a Macworld Expo in the early 1990s and thought it was the greatest thing in the world for organizing my thoughts and ideas. I was sure it would make me a better writer. I was so convinced, I bought it — and it wasn’t cheap. I used it for a while and rather liked it. Evidently, I was one of very few people who’d joined the ThoughtPattern revolution. In April 1993, it was discontinued. I was left with software that wouldn’t work with subsequent versions of the Macintosh system software. Worst of all, the documents I created with ThoughtPattern were in their own proprietary format. When the software stopped working, the contents of those documents were lost. (Do you think it was easy to find a screenshot from software that was discontinued 16 years ago?)

So perhaps you can understand my aversion to one-trick ponies that promise a better writing experience.

Will the same thing happen with Microsoft Word? I don’t think so.

I Don’t Compute in the Cloud

Google Docs was one of the solutions suggested to me by my Twitter friends. I guess they think it’s better to avoid the evil Microsoft empire in favor of the “we’re not evil” Google empire. Along the way, I should give up the interface and features I know from almost 20 years of experience with the software and rely on an online application that could change its interface daily. Oh, yeah — and keep my documents on someone else’s computer.

Yeah. Right. Good idea.

Not.

Until I’m part of a multinational corporation that requires its employees and consultants to keep all their documents on some remote server for collaboration purposes, I will not be computing in the cloud.

One of the things I like about keeping my documents on my own computer — rather than a remote server accessible by the Internet — is that the Internet is not always available. What do I do then? Stop working?

Security is an issue, too. While I don’t usually write much of a confidential nature, I don’t like the idea of not having control over my documents. Servers get hacked. I don’t want my work suddenly accessible to people who I don’t want seeing it.

I will admit that I use MobileMe’s iDisk feature to keep some documents on an Apple server. This makes it a tiny bit easier to access them from my laptop when I’m away from home. But I’ve recently moved to a new strategy. I bought a pocket hard drive that’s bigger than my computer’s Home folder. Before I hit the road with my laptop on a trip for business or pleasure, I sync this portable drive with my Home folder. I then have every single document on my computer with me when I’m away. The added benefit: complete offsite backup.

That’s My Case

That’s my defense of Microsoft Word. I rest my case.

Please understand that I’m not trying to convince a non-Word user to switch to Word. If you’re happy with something else, stick with it! That’s the precise reason I’m sticking with Word. I’m happy with it.

I guess the reason I wrote this post was to assure other people like me that there’s no reason to be ashamed of being a Word user. You do what’s right for you. There’s nothing really wrong with Word. If it makes your life easier, why switch?

Why I Downgraded to Word 2004

I said I’d never upgrade to 2008 and I lied. Then I paid for it.

There’s nothing wrong with Office 2004 that’s fixed in Office 2008. It’s the same software with some Interface tweaks. After all, at this point, what can Microsoft do to change Office?

The answer, of course, is to create an abomination like Office 2007 for Windows. They said they made it more intuitive, but for who? People who have never seen a computer before? As a long-time Office user, the new “ribbon” interface drives me batty. I can’t find any of the commands I need.

I thank my lucky stars that they didn’t destroy the Mac version, too.

But although I didn’t need or want to update to Office 2008 for Macintosh, I soon realized that if I wanted to record videos about the software and write the occasional article or Maria’s Guides post about it, I should bite the bullet and put it on at least one of my computers. That lucky computer was my MacBook Pro, which I have with me in Washington State this summer.

The installer warned me that the sky would fall (or something equally horrible would happen) if I didn’t uninstall previous versions of Word. So, like a real sucker, I let the installer uninstall Office 2004. I’d learn Office 2008 with a thorough emersion.

Compatibility Woes

I began running into problems almost immediately. The first was compatibility. For some reason, the geniuses in Redmond decided that Office 2007 (Windows) and 2008 (Mac OS) should have a different file format. So when you save a document from Word 2008, by default, it saves in the .docx format. Unfortunately, the vast majority of my editors and other people I share files with did not upgrade to Office 2007 or 2008. These people couldn’t read the files without installing a special converter, which was not immediately available.

And if I happened to open a Word 2003 or 2004 file and save it, I got all kinds of worrisome warnings about document formatting possibly being lost.

Word 2008 Save PrefsThe solution, of course, was to save in Word 97-2004 format. And so I didn’t have to deal with choosing a file type option every time I saved a document, I set that as the default file type. That would pretty much guarantee that my file could be read by anyone using any modern version of Word.

Macro Woes

Things got worse.

Word MacrosI began work on a book I revise each year. (I’m not allowed to say what it is because I’m under nondisclosure, but you can probably figure it out if you consult my Books list.) That book is written in Word, which my editors use. We make extensive use of the Track Changes feature. All of my editors are on Word 2003 (Windows). While I assume that the Track Changes feature works the same in both versions, I know the macro feature does not.

Contains VB MacrosYou see, the geniuses in Redmond decided to disable Word’s Visual Basic macro feature in Word 2008 for Macintosh. Sure, there’s a dialog full of predefined macros that are probably preprogrammed into the software. I haven’t fiddled with the feature yet, so I can’t say much more about it.

But I can tell you that if you try to open a Word document or template that contains macros with Word 2008, Word wants to strip them out. Yes, you can open the file without stripping out the macros, but you can’t use the macros.

While for most folks, this might not be such a big deal, it’s a huge deal for this book project. Each book file has to be based on a template that includes these macros. The macros automate several tasks and really do make my life easier. I want to use the macros.

While I do have my old 12-in PowerBook handy and it has Word 2004 on it, it doesn’t have Photoshop on it. I need Photoshop, which is on my MacBook Pro, to process the screenshots. My MacBook Pro also has my e-mail software so I can send out the finished chapter files and art.

I was looking at a workflow nightmare. My options were as follows:

  • Run the software I’m writing about on my Dell PC (it’s a Windows software program), write the book on my PowerBook, and edit the screenshots on my MacBook Pro. That meant having three laptops open while I worked and shuffling the files among all three computers before putting them on the MacBook Pro to send them out.
  • Reinstall Word 2004 on my MacBook Pro. Then run the software I’m writing about on my Dell laptop and do all the text and image editing, as well as e-mailing final files, from my MacBook Pro.

Which option would you choose?

So that’s why I’m downgrading to Word 2004 on my MacBook Pro.

Removing Words from Word’s Custom Dictionary

It’s a bit involved, but doable.

Yesterday, I explained how you can “unlearn” a word that you mistakenly added to Mac OS X’s user dictionary. Of course, applications other than those developed by Apple probably don’t use Apple’s dictionary.

Microsoft Word is a good example. It stores the words you add to it in a Custom dictionary file. To remove a word you added by mistake, you have to manually edit this dictionary. Here’s how you can do this in Word 2004 for Mac OS; the technique is similar in Word 2003 for Windows.

  1. In Word, choose Microsoft Word > Preferences. (Note that a document window must be open to choose this command.)
  2. In the Preferences dialog that appears, choose Spelling and Grammar.
    Spelling Preferences
  3. Click the Dictionaries button.
  4. In the Custom Dictionaries dialog that appears, select the name of the dictionary you want to edit.
    Custom Dictionaries
  5. Click the Edit button.
  6. A dialog like the one shown next appears. Click OK.
    Spelling Turned Off
  7. A Word window named Custom Dictionary opens. It contains all the words in your dictionary, in alphabetical order. Make changes as desired in this file, adding or removing words as you see fit.
    Custom Dictionary
  8. Choose File > Save to save the modified file.
  9. If a dialog like the one shown next appears, click Yes.
    Save Changes
  10. Click the Custom Dictionary’s close button to dismiss it.
  11. If you had Check spelling as you type turned on before you started this process, go back into the Spelling and Grammar preferences and turn it back on.

From that point forward, Word will recognize only the words in its main dictionary and the custom dictionary as you edited it.

Page References

Product ImageMicrosoft Word 2004 for Mac OS X: Visual QuickStart Guide offers additional information related to this topic:

  • Enabling Automatic Spelling Checking, page 143
  • Checking Spelling as You Type, page 144
  • Setting Spelling and Grammar Preferences, pages 380-382

One Way to Speed up Word’s Launch Time

It works but it’s not for everyone.

Tired of waiting for Word to optimize its font menu each time it launches? Tell it not to. This will speed up the launch process — especially if you have many fonts installed — but it will also stop Word from displaying font names in their typefaces.

Give it a try and see if it’s worthwhile for you:

  1. With any Word document open, choose Microsoft Word > Preferences.
  2. In the Preferences dialog that appears, click the General option in the list of categories on the left side of the window.
    Word General Preferences
  3. Turn off the WYSIWYG font and style menus option.
  4. Click OK.

Try this for a while and see if missing out on the WYSIWYG menus bothers you. If it does, you can always turn this feature back on.

A Computer without Word?

I try an experiment to see if I can stay productive without Microsoft Word.

Last week, in an effort to get my old 12″ PowerBook to run more efficiently, I decided to wipe the hard disk clean and reinstall Tiger with a clean install. After installing it, I got a little crazy and decided to upgrade to Leopard. So far, the machine works fine.

The computer does have a problem, however. Its aged and well-used CD-R/DVD drive has decided to be a bit picky about which CDs and DVDs it recognizes. It liked the Tiger and Leopard install discs, which are both DVDs. But it didn’t seem to like the Office 2004 disc, which is a CD.

(A real Apple fangirl might point out here that the computer has taste. But I wouldn’t dream of making such a statement.)

It’s a Bother

I can get Microsoft Office on the computer by other means. I can insert the disc in another computer and have the PowerBook access the CD via my AirPort network. Or I can create a disk image of the CD, transfer that to the PowerBook over the network, and install from that. Or I can put a disk image of the CD on an 80GB Firewire drive I have sitting around and attach that drive to the PowerBook to install. Any of these methods should work.

Or, if I want to get really radical, I can install the beta of Office 2008 on the PowerBook and let it become my official testing machine.

But let’s face it: it’s a bother. And this morning I began asking myself if it was worth it.

The PowerBook is in semi-retirement these days. It spends much of its time in my kitchen, where I use it to create my morning blog entry. I sometimes use it to look up something on the Internet when I’m doing things around the house. And, of course, I take it traveling with me, primarily because it’s relatively small and lightweight and old enough that I don’t have to feel too bad if it breaks.

But the real question is: Do I need Microsoft Office on it?

An Alternative to Word

I definitely need a Word processor. I’m a writer and I use the computer to write. (I use most of my computers to write.)

But Mac OS X comes with a perfectly good word processor: TextEdit.

TextEdit ExampleDon’t laugh until you’ve tried it. The version of TextEdit that comes with Mac OS X 10.5 Leopard is a kick-butt little word processor. It supports all kinds of formatting, including basic text formatting, tabs, indentation, lists, and even named styles. It has built-in spelling check that can check spelling as you type and supports hyphenation. It can open and read all Word formats — including Word 2007 for Windows with its new file format (which Word 2004 for Mac can’t even do) — and can save to Word formats, Rich Text Format (RTF), and plain text format. That makes it possible to use TextEdit, even when the people you are sharing files with are using Word.

Clearly, this ain’t your father’s SimpleText.

Okay, so it doesn’t support cell tables and Word fields and its support for inserted graphics is rather limited. And there are some Word formatting options that TextEdit might simply choke on.

But do I really need all those Word features? Isn’t the ability to enter and edit text and apply simple text formatting what word processing is all about?

Do We Need All those Bells and Whistles?

And that brings up a point that’s been nagging at me for years — since Microsoft Office 6 for Macintosh came out, in fact.

My first Word book was the Macintosh Bible Guide to Word 6. The Macintosh Bible series of books was a short-lived seris of big fat books about specific software packages. I did one for Word, then did another for Excel.

I clearly remember installing and using the beta of Word 6. My first impression was: this sucks. Microsoft had decided to pack in a lot of new features. And, if I recall correctly, Microsoft had also decided not to use Apple’s built-in routines for drawing windows and dialogs. Instead, they decided to write their own routines to draw their own windows and dialogs. So not only did the computer have to deal with all the programming stuff for word processor operations, but it had to deal with all the extra Microsoft-brand programming for the interface — the same kind of programming that made early versions of Windows so sluggish to DOS fans.

The feature bloat and poor performance of Word 6 — at least in the beta stage; they managed to fix it up a bit for the release version — got me thinking about the features that should be in a word processor. Clearly, Microsoft had gone over the top. (And it continued to do so for most subsequent releases, although I do recall Office 2001 being a very good version.)

Today’s TextEdit is not too far off from Microsoft Word 4, which is the first version of Word I used (back in 1989). And frankly, it did everything I needed it to do to prepare my manuscripts.

And if you’re a Windows user reading this, you have WordPad (I think that’s what it’s called), don’t you? I’m pretty sure that these days that is Word 4.

Compatibility Issues

Today, things are different. Although I write all my Peachpit Press books in InDesign, Adobe’s page layout program, I still write each year’s edition of my Quicken for Windows book in Microsoft Word. My editors make extensive use of Word’s revisions feature, with each of them (and me) marking up the manuscript so it’s nearly impossible to read. I need to use Word to share files with these editors. There’s no way around it.

But that doesn’t mean that I need it on my little PowerBook. And it also doesn’t mean that you need it.

Other Alternatives for Mac Users

And then there’s the iWork suite of products. With the addition of Numbers, a good alternative to Excel, Mac users now have all the pieces they need to replace Office on their Macs. I have iWork around here somewhere, but I haven’t had time to experiment with it. I’d love to get feedback from users; use the Comments link or form below to share your thoughts.

Back to My Original Question

Do I need Microsoft Office on my semi-retired PowerBook?

At this point, I think the answer is no. Let’s see how long I can last without it.

Excel Splitting Digits Example

Another quick spreadsheet to show off the capabilities of Excel.

Site visitor Jamie left the following comment on my recent post, “Random Number Generator for Excel“:

maria dont spose u know how to split a four digit number like 2365 into 4 different numbers 2 3 6 5 in all different cells in excel without having to do it manually just wandering please tell me if you know a way thanks

I had an idea of how to do this with the MID function, which is a text function. I just didn’t know if I had to convert the starting number to text before extracting the digits.

So I whipped up the attached worksheet and popped in the formulas. The answer: no, you don’t need to convert the number to text first.

Split Digits worksheet

The MID function has 3 arguments:

  • text is the text you want to extract characters from. For this example, it’s the four-digit number.
  • start_num is the number of the first character in the string you want to extract. For this example, it’s the digit number (1 though 4).
  • num_chars is the number of characters you want to extract. For this example, it’s 1.

You can examine the formulas in the spreadsheet for yourself to learn more. Download it here.

Hope this helps, Jamie!

Random Number Generator for Excel

A quick little project.

I just happened to visit a Web site that used another Web site to generate random numbers for contest giveaways. To me, an Excel user, that seemed like a silly place to go to get a random number when you could easily generate one on the fly within Excel.

ScreenshotSo I whipped up a tiny Excel spreadsheet to do the job. You just put the minimum value in one box and the maximum value in the other and Excel generates a random number between the two for you. To generate additional random numbers in the range, just press Command-= (on a Mac) or Control-= in Windows. The worksheet is protected so you can’t accidentally delete the formula that does the magic.

Although I created the worksheet in Excel 2004 for Mac OS, it can be opened in Excel 2003 and Excel 2007 for Windows, too.

Want a copy? Download it here.

Update 4/22/11: Apparently, the file has been lost in a server transfer. I’m updating it now and will restore the above link when it’s online. Sorry, folks!

Later: I recreated the file and put it in a new location on the server. The above link should work. Also, please note that there is now an easier way to do this with Excel; use the RANDBETWEEN function instead.

Update 2/6/12: This post has become very popular. But wouldn’t you rather do this the easier way? I explained RANDBETWEEN in a new post today.

Microsoft Releases a Word 2007 Conversion Tool for Mac Users

Oh. Gee. Thanks.

I got an e-mail message from my editor, Cliff, today. He wanted me to send him a Word 2007 file to test a converter on his Mac.

I don’t use Word 2007 regularly. That’s a Windows program. I’m primarily a Mac user. I still use Word 2004 on my Mac. But I do have Office 2007 installed on my Windows PC, which is running Vista. I needed it to write about Excel 2007 for Windows. Of course, Word is part of the Office 2007 package, so I had that, too. I guess no one else at Peachpit does. But that doesn’t surprise me, because Peachpit is a publisher and uses mostly Macs.

I fired up my PC, started Word, and created a short document with some formatted text. I saved it to my Public folder, which I then opened from my Mac. I called Cliff into iChat, I pasted the document’s icon into iChat, and sent it to Cliff.

The whole time I was wondering why I didn’t know that Word 2007 for Windows files couldn’t be opened from Word 2004 for Macintosh. Could it be because my Word for Windows books were not revised for Word 2007? (Too many other Word books out there.)

The Converter

Cliff sent me a link for more information about the converter. I followed it to an entry on the Mac Mojo blog, “Mac Mojo : Get converted“:

…We are developing the product [Office] for versions beyond 2008 with broad investment in native Mac OS X architecture as well as adoption of a new, next generation document format: Office Open XML.

We’ve made great progress, and as previously promised we’re releasing some of this new functionality in a form that you can start using right away: Beta release #1 of the Microsoft Office Open XML File Format Converter for Mac is now available for download.

So the converter is just a beta.

I followed the link and found myself on the Microsoft Macintosh Downloads page. A link marked Office File Format Converter Beta (Word) brought me to a page with details about the converter. The information around the Download link told me that the file was 24.9 MB in size and that it would take 2 hours and 10 minutes to download with a 56K modem. Since I only have a 512K connection to the Internet (remember, I live on the edge of nowhere), I started that download right away.

The resulting file is a disk image that, when opened, displays an installer. When I got the installer going, I was quick to notice that it was installing over 8,000 files on my hard disk. Can I repeat that? Eight thousand files.

What ever happened to plugin-like converters that hid as single files in a specific folder where you could find and delete them when you had to?

Office ConverterThe application, which is only 3.9 MB in size, is installed in the Applications folder. I double-clicked it, saw a little information screen that told me what it did, and was presented with the Office Converter window. I dragged the Word 2007 file I’d created for Cliff into the window, and waited. About 3 seconds later, I was told that the conversion was successful. (The test file had 18 words in it and two kinds of formatting were applied to some of them.)

The Converted File in TextEditThe test file was not converted to a Word 2004 format file. That might make too much sense. Instead, it was converted to an RTF file. When I opened it, I found all formatting intact. There was also a blank area for a header and footer, which I didn’t even know RTF supported. Here’s what it looks like in TextEdit.

Why the Wait?

If you can read the sarcasm in this post — I really have tried to tone it down — it’s because I don’t understand why Microsoft had to change Word’s file format in the first place. I also can’t understand why they waited nearly five months from Office 2007′s initial release to come up with a converter for Mac users and the rest of the world. Remember, Word for Mac users aren’t the only ones who can’t read the new Word 2007 format; none of the existing Word converters for Mac and Windows work for Word 2007 files.

Perhaps Microsoft believes that its user base should take the extra effort to save files in previous Word formats when they need to share with non 2007 users. If so, Microsoft needs to understand that the average user doesn’t know the first thing about the type options available in the Save As dialog — no matter how many authors write about it. They save it in the default format and they’re done. What’s worse is that if they open a document created in an older format and attempt to save it with a different name, Word defaults to the new format, then displays a dialog that could easily confuse many users.

Warning Dialog

In case you can’t read that (I had to reduce its size to fit it in the text column here), it says:

You are about to save your document to one of the new file formats. This action will allow you to use all the new features in Word 2007, but may cause changes in the layout of the document.

Click OK to continue, or click Cancel and select the “Maintain compatibility with Word 97-2003″ checkbox to preserve the layout of your document.

So you do click OK and possibly have changes in the document’s layout? Or do you miss out on the new features? And what about Word 2004 for Macintosh?

Word 2004 isn’t even mentioned here. But if you use the Word 97-2003 format, Word 2004 on a Mac can open the file. (Why didn’t they just call it Word 97-2004 format?)

More Testing Needed

Obviously an 18-word file is not much of a test. But I don’t have any very complex files created with Word 2007 to run through the converter. If I do get some time to create and test a complex Word 2007 file with the converter, you can read about it here.

Informit Publishes Excel 2007 Book Excerpt

“Working with Excel Files” goes online.

ImageJust a quick note to let Excel 2007 users out there know that Informit has published an excerpt from my Excel 2007 Visual QuickStart Guide on its Web site. The piece is titled “Working with Excel Files.” Access to the site is free.

I’m pretty sure the book is out, but I haven’t had an author copy in my hot little hands yet.

I think my recent article about using WordPress with Google sitemaps will be online there next week. Stay tuned for more info.

Excel Book Done

That’s book number 68.

ImageI put the finishing touches on Microsoft Office Excel 2007: Visual QuickStart Guide. It’s my 68th book (I just counted) and right now, I feel as if I wrote them all yesterday.

Okay, so not that tired.

I had some trouble with this book. First, there was the beta software situation. Not only did I have to work with the Office 2007 beta, but I had to run it on the Vista beta. Double Microsoft Windows betas for a person who usually works on a Mac! You can imagine my concern.

But everything went pretty smoothly with that and I’ve been using release versions since January, so I know everything in the book is based on the final software.

Motivation slowed me down a bit in the middle of the project. I think I really need an editor cracking a whip over my head to get me to work at my old pace. These days, I’d rather fly than write about Excel. (Can you imagine?) The thing that snapped me out of it was money. If I don’t make milestones, my publisher does not send checks. Although Flying M Air is now paying all of its own bills — thank heaven; you should see some of those bills! — it’s not paying my bills. If I don’t write, I don’t eat. And since I like to eat, I became motivated.

Of course, the killer was my February hard disk crash and the two weeks it took me to get everything back to normal here. What a productivity killer! But it taught me a new valuable lesson about backups — you think I would have learned the last two times — and my old dual G5 is still running, now with a new hard disk to go with last year’s new motherboard. Sheesh. (Now you know why I bought AppleCare for my MacBook Pro.)

I churned through the last few chapters relatively quickly, anxious to meet deadlines tied to promotional opportunities. (I’m not sure of those promos really exist or if my editor has learned to tell me about fantasy promos to get me to work faster. I wouldn’t blame her if she made it up.) I had first pass files done last week and spent the past few days finalizing files based on edits. Today, after fooling around a bit — I’m the queen of procrastination — I laid out the index, created an ad for the book’s companion Web site, and turned it all in. The e-mail message I sent to my editor said:

I think I’m done. Can you ask them to send that final check? (Still waiting for the last one, too.)

The book weighs in at 360 pages, which is about the same as the last edition. It’s got the new VQS cover design. It lists for $21.99, but you can buy it from Amazon.com for $14.95 right now, which is 32% off. (Not a bad deal.) It should be in stores by April 20 or thereabouts.

Meanwhile, life goes on.

Tomorrow, I have to take my helicopter in to the avionics shop in Mesa to see if they can figure out why my radio isn’t working right. I have a meeting with a marketing guy down there at 10 AM. Then a tour of Phoenix for a man and his daughter at 2. Somewhere in between, I’ll have lunch with Mike, who has been away for the past few days. Then a flight home.

Friday I get started on my next book. Those of you who know me should know what that is.

Printing an Excel Function Reference Sheet

A how-to for Excel users.

My Visual QuickStart Guide books for Excel — from the edition for Excel 95 for Windows through the edition for Excel X for Macintosh — have always included an Excel function reference. In an effort to make pages available for other content, however, we’ve cut that appendix from the Excel 2007 edition of the book. I don’t feel badly about the cut, since this information is readily available in Excel Help and on the Web.

Here’s how you can find a function reference for your version of Excel and print it for your own hard-copy reference guide.

In Excel 2007 for Windows

  1. Click the Help button in the upper-right corner of the Excel window to display the Excel Help window.
  2. Enter function list in the search box and press Enter.
  3. Excel Help search resultsAmong the search results that appear, you should see an item titled “List of worksheet functions (by category).” Click its link.
  4. Function List for Excel 2007A help document titled “List of worksheet functions (by category) appears in the help window. It includes a complete list with brief descriptions of all Excel 2007 functions. You can read through this document and click links within it to learn more about specific functions.
  5. To print the reference sheet, click the Print button in the Excel Help window’s toolbar. Use the Print dialog that appears to set printing options and click the Print button.

Note that you may need a connection to the Internet to access the Function List from within Excel Help. And remember that you can always resize the Excel Help window to better read what’s inside it.

Get this Information Online

This reference information is also available online for some versions of Excel. Click this one of these links:

These pages contain clickable links to details about specific functions. They can also be printed from within your Web browser; use the Print command.

Excel Alignment Tips

For perfectionists.

One of the things that always bothered me about Excel was the way it handled right alignment in cells with certain types of number formatting applied.

The Problem

For example, in the following illustration you’ll see that some cells containing values have currency formatting and other cells containing values have comma formatting applied. The line up nicely in a column. But when right-align the column headings as I’ve done here, the headings don’t line up with the numbers:

Excel Alignment Example

This is the case in all recent versions of Excel that I’ve worked with, including Excel 2004 for Macintosh and Excel 2007 and Excel 2003 for Windows. (The illustrations here are from Excel 2007 running on Windows Vista.)

Why It Happens

Before we can fix this problem, we should have a good understanding of why it occurs.

You may have noticed that when you apply currency or comma formatting (for example) the numbers shift to the left. Ever wonder why?

Excel Alignment ExampleWell, you can thank Excel for thinking ahead about something that you might not have in a column of numbers: negative values. As shown here, certain number formatting options place parentheses around negative numbers. To make sure all the numbers in a column line up, positive numbers are shifted by the width of a parentheses character to the left. So every number formatting with currency or comma formatting actually has a tiny bit of space after it.

The Solution

Since you don’t normally apply currency or comma formatting to text, cells containing text are truly aligned against the right side of the cell when you use the right alignment formatting option.

But guess what? You can apply currency or comma formatting to a cell containing text. And when you do, the contents of the cell will shift to the left by the width of one parentheses character. The result: everything is nicely aligned:

Excel Alignment Example