Excel sorting, filtering & formulas explained

 Excel is up to bat this week. I like Excel. It's a versatile program—straightforward, no-nonsense, and linear(ish)—and one that many people use. With Excel you know what you're getting (whereas in Visio or in Publisher, you sometimes feel like you're in a large hardware or fabric store with all those patterns and widgets to choose from).

 But don't get me wrong: Excel is as versatile as the next Office program. In fact it's a pretty powerful tool for a variety of tasks, from coming up with charts and graphs, to keeping track of lots…and lots of numbers.

And since Excel is just about the most widely used spreadsheet software around, it's a good idea to know some of its basic terms, because just like with any other product or topic matter, it's not a good idea to assume that everyone around us understands the jargon and vocabulary that we use when talking about Excel.

In other words, I don't want you feeling lonely and isolated, standing there with a smile pasted on your face, when your coworkers start talking about formulas and PivotTable reports. There's only so much faking you can do before everyone is on to you. So today: the basics. Another day: Beyond the basics.

I know that some of this seems like pretty basic stuff. But if you're new to Excel, perhaps it isn't. So once again, let's begin at the beguine. I'm being sincere: Learn what some of the terms mean below—the basics—so that you can move forward and make use of a tool that really is quite useful.

Columns

Columns run vertically down a worksheet and are lettered: A through Z, then AA through AZ, BA through BZ, and so on and so forth until you have way too much data anyway. In fact, in Excel 2010 (and 2007), you can have up to 16,384 columns.

Rows

Rows are numbered and run horizontally. In Excel 2010 and 2007, you can have up to 1,048,576 rows.

Cells

Cells are the basic building blocks for anything in an Excel workbook: They are the little rectangular boxes where you enter your data. In Excel 2010 and 2007, you can have 32,767 characters in a single cell. (You certainly have a lot to say, now don't you?) In Excel 2003, it's 255 characters. (Hmm. Guess if you have a lot to say, you should maybe consider upgrading....)

(Just as an aside, I'd like to meet the person has been able to make use of all the 16,384 columns, 1,048,576 rows, and 32,767 in each of those cells—in ONE spreadsheet. If that's you...you must let me know.)

Sorting

Sorting in Excel is the same as sorting anything else: laundry, contact lists, worst boyfriends ever... In Excel, you might want to put a list of e-mailers with stalking tendencies in alphabetical order or maybe organize—by color or icon—a list of the blog posts that are totally bombing each month. Sorting information (data) helps you to visualize and understand your information better and, thus, helps you to organize. You can sort by text, by dates and times—really by anything at all. You can even sort by a custom list that you create (such as the good, the bad, the ugly), or by format (such as cell color, font size, whatever). Sorting just helps you to see what you want to see in the order you want to see it. Excel makes it very easy—it's basically a click or two of a button, and you get the information you want.

Filtering

Filtering is a close relative of sorting. In fact, filtering helps get rid of stuff you'd rather not see any longer. When you filter something ( a row, a column, a list, whatever), you show what you need to show and hide the rest. Think of a swimming pool filter: It keeps the water flowing but traps the stuff you don't want floating in your pool, like, for example a relative you no longer want swimming with the other genes in there. Or, if my personal family issues are making you uncomfortable, think Jolly Ranchers (my very favorite candy): Filter out the orange and grape ones and keep the cherry, blue raspberry, and watermelon ones. Same goes with filtering data: You see what you want to see, and you hide the rest.

(Bonus: If you know me—and now you do a little more—you know that when I die, I'd like to be encased in a cherry Jolly Rancher, kind of like a Crabby insect in amber, only red and too large to wear as jewelry.)

Read more about filtering and sorting data in Excel

Formulas

Using a formula in Excel is basically why people use Excel and not, say, Word and a calculator. A formula is the good gremlin inside your computer: It does YOUR bidding.

Formulas in Excel are equations that perform calculations and values on your worksheet. In other words, formulas do the math—or other sorts of calculations—for you. A formula starts with an equal sign (=) and then is followed by numbers, cell references, or operators. Some of the more common operators are add (+), subtract (-), multiply (*), and divide (/), but operators can get much, much more complex. I know it seems complicated—and it would be if you had to figure this all out for yourself—but if you have the data, Excel does the work.

Get an overview of formulas.

That's it for today; in the next post about demystifying Excel jargon (which I'll write in the next couple of weeks), I'll dig a little bit deeper into it and include more about formulas, PivotTables and PivotCharts, macros, references, controls and some other things I feel like torturing you with...

— Crabby

Not finding the help you need from the various channels you've tried? Microsoft Answers is where you're most likely to solve your nagging problem. I explain all about it here, & I strongly suggest—yes, my finger pointed right at YOU—that you consider heading over there.