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.

Steve Jobs/Bill Gates D5 Interview

Great to watch.

Jobs/GatesI know it’s been out for a while, but I’ve finally taken the time to download the 997MB video podcast file for the Steve Jobs/Bill Gates interview at the D5 conference. (It takes time when you only have a 512kbps connection; don’t even try it on dialup.)

It’s a great look at the history of the two companies — Apple and Microsoft — from the viewpoints of the people that built them. The intro, which features early clips of the two men together, is a real treat. It’s hard to remember that both men were so young when they started on their paths.

A while back, I wrote a post about the Bill Gates Daily Show interview. In that interview, Gates came off as a real geek. Not so in this interview. He’s extremely articulate, amusing, and informative.

And, of course, Steve Jobs is Steve Jobs.

I highly recommend this interview if you have any interest in histories of Apple and Microsoft and their relationship.

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.

Mouse Wars: Microsoft vs. Logitech

I try out two wireless mice and have a clear winner.

One of the things I don’t like about using a laptop is the trackpad. While I’ll take a trackpad over a trackball any day, neither of them come close to the pointing precision I can achieve with a regular mouse.

A lot of the work I do on my desktop Mac (which is fully recovered now; thanks for asking) is layout work where I commonly drag objects with pixel precision. If I had to do that with a trackpad, I’d go nuts. And while I don’t often do any kind of layout work on my laptops, I really prefer a mouse.

My Microsoft Wireless Mouse

Microsoft Wireless Notebook Optical Mouse 3000 -SlateA while back, as a test, I bought a Microsoft Wireless Notebook Optical Mouse 3000. That’s a big name for a little pointing device. I liked the mouse’s ergonomic design and the way it fit into my hand so nicely. It tracked well, pointed well, clicked well. And the roller ball between the two buttons was soon something I began using regularly. And that’s a lot to say from someone who has been using single-button Apple mice for the past 18 years with no complaints. Best of all, the darn thing retails for only $30.

The only thing I didn’t like about the Microsoft mouse was that it wasn’t Bluetooth. Yes, it was wireless, but to use it I had to stick a small receiver/transmitter do-dad into one of my USB ports. Not a big deal, since I don’t usually have anything connected to my laptop anyway. But the USB thing is also the on/off switch for the mouse. When you’re finished using it, you’re supposed to pull out the USB thing and fit it into a specially-designed slot on the bottom of the mouse. That triggers the off switch, thus cutting power to the device and stopping any drain on the battery. My problem — or perhaps I should say one of my problems — is that I have a mind like a sieve sometimes and simply can’t remember to go through this procedure when I’m finished working.

To be fair, there hasn’t been any penalty yet. I’ve been using the mouse on and off for over a year, forget to turn it off about half the time I use it, and it’s still on its original battery. It has some kind of standby mode that sense when you’ve stopped working and reduces battery drain. So it’s not like I’m going through batteries at an alarming rate. I’m not.

So, in summary, I liked everything about the Microsoft mouse except for this silly USB thing.

My Logitech Mouse

Logitech V270 Cordless Optical Bluetooth Mouse- CharcoalIn an effort to improve the situation, I bought a Logitech v270 Cordless Optical Bluetooth mouse. Now I don’t want you to think I was going to just throw away the Microsoft Mouse. I wasn’t. I happen to have more than one laptop and I also thought that I might start using a wireless mouse with my desktop machine. In fact, that’s what I originally bought it for. I have lots of USB devices and didn’t want to use up one of the ports on my hub. Besides, I’m trying hard to reduce the rat’s nest of wires behind my desk and figured a wireless mouse for everyday use would remove one wire.

Logitech is a company that has been around for a while. They’ve been making input devices perhaps as long as I’ve been using Macs. Their products are usually very good. But this particular mouse — which, at $50, cost almost twice as much as the Microsoft mouse — pretty much sucks.

Okay, so it doesn’t have the same perfect ergonomic shape that fits my hand so well. I wasn’t going to hold that against it. I figured that Microsoft got lucky with that design, or maybe that I got lucky that Microsoft’s design was so perfect for me. I couldn’t expect every mouse to fit so well. So that’s not what drove me to my decision.

It’s the tracking. The mouse is terrible. I can’t put my finger on it (no pun intended), but it just doesn’t seem as smooth. I can’t get the same precision. It’s frustrating and distracting and makes it difficult to get my work done without a lot of extra thought and effort.

To be fair, I tried both mice on several different computers, including my Dual G5 production machine, my 12″ PowerBook G4, my Dell Latitude 820D laptop, and my new 15″ MacBook Pro. The results were the same on every single machine. The only difference is that I had some difficulty pairing up the Logitech Bluetooth mouse on one or two of the machines. The Microsoft mouse worked perfectly on every machine — Mac and PC — as soon as I plugged in the silly USB do-dad. And it tracked perfectly, just like a wired mouse.

Mighty Mouse

Apple Bluetooth Wireless Mighty MouseApple sells a wireless mouse called Mighty Mouse. (It’s amazing to me how Apple comes up with these names.) It’s been discussed in comments elsewhere on this site. I got a chance to work a wired version of this mouse at the Chandler Apple Store a few weeks ago. It’s a two-button mouse without physical buttons. It seems to work well. I’ll be checking out the wireless version soon enough, since I need to write about it for my Leopard book.

The reviews on the Apple Store Web site are pretty much split. Some people absolutely love Mighty Mouse while others absolutely hate it. An Apple person I spoke to admitted in a whisper that he hated it. It’s certainly the most expensive of the bunch, retailing for $70.

I didn’t think there would be a reason to “hate” a wireless mouse until I bought the Logitech mouse. I assumed they all worked as well as the Microsoft Mouse when it came to tracking. But the Logitech mouse proved that they don’t. I don’t know how Mighty Mouse will perform yet for me, but I’ll be writing about it here sometime in the future.

My Ratings

The bottom line is this: I tried two wireless mice. I very much like one (love is too strong a word) and really don’t like the other (hate is also too strong a word). If I had to rate them on a scale of 1 to 5 stars, here’s how I would do it:

Microsoft Wireless Notebook Optical Mouse 3000

[rate 4.5]

Logitech v270 Cordless Optical Bluetooth mouse

[rate 1.5]

Any thoughts? Use the Comments link to share them.

Absolutely the Last Article You Need To Read About Absolute References in Excel

Informit article.

Ever copy a formula you think is perfect and have surprising (and incorrect) results in the destination cells? You might be missing out on a feature of Excel that can make your spreadsheet work easier.

Read more at: Absolutely the Last Article You Need To Read About Absolute References in Excel