Crabby Office Lady
Welcome to installment number two of my "demystifying" series, where I'll cover 10 commonly used terms in 10 widely used Office programs. Today, Microsoft Office Excel is the subject, and although many of you use Office Excel, some of the terms are still a mystery to you.
Crabby Office Lady columns
Excel is up to bat this week. I like Excel. It's a versatile program — straightforward, no-nonsense, and linear — 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.
In fact, I believe that one of the reasons people often hesitate to start learning a new program or dig deeper into one they know a little bit about is they're afraid to admit that they don't understand some of these so-called "basic" terms. And what happens? These folks remain forever stuck at their current level of competence when just a little understanding could make all the difference.
So, without further ado (and I do tend to overdo the ado — I know I do) ... let's demystify some Excel terms.
Ready, set … define those terms!
I know; 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.
Note For more information about the terms and topics mentioned here, take a look at the See Also box at the top of this column.
- Rows are numbered and run horizontally. In Excel 2007, you can have up to 1,048,576 rows. In Excel 2003, it's 65,536 rows. That is a whole lot more space for your data. (Although why these numbers aren't rounded off, I cannot tell you. I just explain the programs, folks; I don't create them.)
- 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 2007, you can have up to 16,384 columns. (Why 16,384? Why indeed. Perhaps, Gentle Reader, you know?) Two members of the "Red Sox Nation" (as they call themselves) told me that the 16,384th column would be: XFD. This is a big jump from the number of columns you can have in Excel 2003: 256. (I believe we added so many more because YOU asked for them.)
- Cells are the the basic building blocks for anything in an Excel workbook: They are the little rectangular boxes where you enter your data. In Excel 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. I'm telling you, those Excel creators went totally overboard.
I mentioned my Uncle Phil in last week's column, and here he is again. (He calls me from time to time because he thinks I'm a computer geek and know it all. I know Office, and that is about as far as it goes. Don't tell anyone.) So Uncle Phil called, not to tell me that I was to be the designated driver from the Formula Bar, but that the place that shows what is in his spreadsheet cells … disappeared. It took me a moment to figure out what he meant, and then I realized he meant the formula bar went missing. The formula bar is that little box above all your rows and columns that displays what's in an active cell (and I know you know what an active cell is). The formula bar looks like this:
In Excel, you can edit directly in a cell, or you can do it in the formula bar.
- In Uncle Phil's case (he was using Excel 2003), all he needed to do was go to the View menu and click Formula Bar.
- If you're working in Excel 2007 and your formula bar disappears, on the View tab of the Ribbon (which is part of the Microsoft Office Fluent user interface), in the Show/Hide group, select the Formula Bar check box.
Sorting in Excel is the same as sorting anything else: laundry, books on your shelves, or folders on your computer. In Excel, you might want to put a list of names in alphabetical order, compile a list of the number of hits your column is getting each month (from highest to lowest), or order rows by colors or icons. Sorting 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 the click of a button, and you get the information you want.
- In Excel 2007, on the Home tab, in the Editing group, click Sort & Filter, and then click Sort A to Z or click Sort Z to A. And of course, you can click Custom Sort, too (which is a tad more complicated, but Excel will walk you through it).
- In Excel 2003, on the Data menu, click Sort. (There will be some choices you'll have to make from there, but again, Excel will help out.)
So, as you can see, sorting is the sort of feature that every sort of person can use. It's sort of an organization method of sorts — sort of.
When you filter something, you take (or take out) what you need and hide the rest. Think the pool filter: It keeps the water flowing but traps the stuff you don't want floating in your pool. Or think Jolly Rancher hard candy (my personal favorite): Filter out the orange and grape ones, keep the cherry, apple, and watermelon ones. Same goes with filtering data: You see what you want to see, and you hide the rest.
Here's an example: Let's say I have a worksheet that lists 3,000 rows of customer comments gathered from all my articles from every day in which they've been on the Web site (so that's three columns: A: The comments column; B: The Crabby article the comment came from; and C: The date it was offered so sweetly to me).
In Excel, the best way to filter is to use the AutoFilter feature. For example, I just want to find the comments for a particular column, say, this one. I select column B (the one that lists all my published columns).
- In Excel 2007, on the Home tab, in the Editing group, click Sort & Filter, and then click Filter. A little arrow appears at the top of column B; when I click it, it lists all the articles that are on that worksheet. I select today's article about Excel terms from the list and read your comments (which yes, I do — I read ALL your comments).
- In Excel 2003, on the Data menu, point to Filter, and then click AutoFilter.
AutoFilter is a great example of how Excel works hard so you don't have to.
No, this is not what men develop around their middles when they grow older, don't exercise, and make special trips to the doughnut shop.
- The fill handle in Excel is the small black square in the lower-right corner of a selection (whether your selection be one cell or a collection of cells). It looks like this:
This handy fellow is one of the many different ways to fill data — quickly — into worksheet cells. For example, if you a have a number, word, or phrase in a cell that you'd like to have in one or more cells below or to either side of it, you don't have to copy and paste. Nope. You just select the cell and drag the fill handle. Even better, the fill handle can automatically continue a series of numbers, number and text combinations, dates, or time periods, based on a pattern that you establish. You drag the fill handle to all the cells where you want to continue your series, and after you drag it, the Auto Fill Options button appears so that you can choose how the selection is filled. This is where you tell it if you want to just copy the original cell, continue with the series, and also choose your formatting options. The Auto Fill Options button looks like this:
In a nutshell, 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. (And even on the fly — more on this in a minute.) A formula starts with an equal sign (=) and then is followed by numbers, cell references, or operators. Some of the most common operators are add (+), subtract (-), multiply (*), and divide (/), but they can get much more complex. There are logical, engineering, and Cube formulas, as well as financial, math, and trigonometry formulas. 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.
Here's an example of a very simple formula:
- In cell A1, if you type =7*6, after you press ENTER, Excel will put the number 42 in the cell.
You can take that a step further and do a cell reference:
- You can go to cell A2, refer to the cell for which you've already done the calculation, A1, and do another. It would look like this: =A1*6.
After you press ENTER, cell A2 will show what 42 times 6 is (252). The beauty is this: Any time you update cell A1, cell A2 will update accordingly — on the fly. (This is one time when flies are not pesky.)
The examples above are very simplistic formulas. And note that you don't have to know how to write a formula; Excel will help you figure out what you want to do. In fact, if you want the entire column A to have that same formula (the previous cell's sum multiplied by 6), you can use that handy dandy fill handle we already talked about to accomplish that:
- Just drag the fill handle down the column and your mission is accomplished: A3 will show as 1512 (=A2*6), A4 will show as 9072 (=A3*6), and so on.
Formulas are beautiful things, I tell you, even if math isn't your strong point.
When you have the urge to merge (in Excel, of course) this means that two or more cells become one. They make a family, they grow exponentially, they work as a unit. And when would you merge two or more cells (besides for love — or money, I suppose)? Well, if you have several columns of information over which you'd like to spread one title, you can merge the cells that the title takes up to get that effect.
But of course, people grow apart, communication breaks down, and merging doesn't seem like a good idea anymore. In that case, you can unmerge, or split your merged cells just as easily (and be free to merge with other cells at another time).
To merge and unmerge cells in Excel 2007:
- Select the cells you want to merge, and on the Home tab, in the Alignment group, click Merge & Center. (Or, for more options than just Merge & Center, click the little arrow next to Merge & Center.)
- To continue with divorce proceedings, do the same thing but click the little arrow next to the Merge & Center button, and then click Unmerge Cells instead.
To merge and unmerge cells in Excel 2003:
- To merge the cells into one happy unit, select the cells, and on the Formatting toolbar, click Merge & Center. To change the text alignment in the merged cell, select the cell, and then click Align Left or Align Right on the Formatting toolbar.
- To split up those cells, select the cell. (When you select a merged cell, the Merge and Center button also appears selected on the Formatting toolbar.) Then click Merge and Center.
PivotTable reports and PivotChart reports
I once wrote an entire column about PivotTable reports — I believe it was my fifth column — and I didn't know a thing about PivotTable reports until I started researching. Since then I've learned to love them. (Okay, well, maybe not love them, but at least understand them.)
- A PivotTable report is simple, a way to summarize, analyze, and present a bunch of data. It's like a chart only a lot more interactive. You can gather a lot of data very quickly, you can expand or collapse your data to focus on what you want (a sort of filtering method), and you can move rows to columns and columns to rows (that's the "pivoting" part of this). A PivotTable report is great for comparing several types of facts about a long list of figures and data.
You can get your data for your PivotTable report from a variety of sources: An Excel worksheet, external sources (such as databases and text files), and even another PivotTable report. (And although this last one is not called a Cannibalistic report, I think it should be.)
- A PivotChart report gives you a graphical representation of the data you came up with in the PivotTable report. If you want to change the layout and data in the PivotChart report, you change it in the PivotTable report. As you may have already guessed, these guys are linked — kind of like my twin nephews: They depend on one another (although a PivotTable report sometimes goes off on his own and doesn't stick with his brother). A PivotChart report has series, categories, data markers, and axes — just like standard charts.
This column got a little more complicated than I'd hoped for. Some of these Excel terms can seem quite daunting, so I wanted to be sure I covered my bases. If you like columns like this, let me know, and I'll write more of them. See you next week when I'll be covering Outlook terms (signatures, rules, and the like).
"I never guess. It is a capital mistake to theorize before one has data. Insensibly one begins to twist facts to suit theories, instead of theories to suit facts." — Sir Arthur Conan Doyle
About the author
Annik Stahl, the Crabby Office Lady columnist, takes all of your complaints, compliments, and knee-jerk reactions to heart. Therefore, she graciously asks that you let her know whether this column was useful to you — or not — by entering your feedback using the Did this article help you? feedback tool below. And remember: If you don't vote, you can't complain.
Crabby Office Lady columns