File Excel In Form C/O – Document Collaboration And Co

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.

Đang xem: File excel in form c/o

Every Excel grandmaster needs to start somewhere. In this chapter, you’ll learn how to create a basic spreadsheet. First, you’ll find out how to move around Excel’s grid of cells, typing in numbers and text as you go. Next, you’ll take a quick tour of the Excel ribbon, the tabbed toolbar of commands that sits above your spreadsheet. You’ll learn how to trigger the ribbon with a keyboard shortcut, and collapse it out of the way when you don’t need it. Finally, you’ll go to Excel’s backstage view, the file-management hub where you can save your work for posterity, open recent files, and tweak Excel options.

Starting a Workbook

When you first fire up Excel, you’ll see a welcome page where you can choose to open an existing Excel spreadsheet or create a new one (Figure 1-1).

*

Figure 1-1. Excel’s welcome page lets you create a new, blank worksheet or a ready-made workbook from a template. For now, click the “Blank workbook” picture to create a new spreadsheet with no formatting or data.

Excel fills most of the welcome page with templates, spreadsheet files preconfigured for a specific type of data. For example, if you want to create an expense report, you might choose Excel’s “Travel expense report” template as a starting point. You’ll learn lots more about templates in Chapter 16, but for now, just click “Blank workbook” to start with a brand-spanking-new spreadsheet with no information in it.

Note

Workbook is Excel lingo for “spreadsheet.” Excel uses this term to emphasize the fact that a single workbook can contain multiple worksheets, each with its own grid of data. You’ll learn about this feature in Chapter 4, but for now, each workbook you create will have just a single worksheet of information.

You don’t get to name your workbook when you first create it. That happens later, when you save your workbook (Saving Files). For now, you start with a blank canvas that’s ready to receive your numerical insights.

Adding Information to a Worksheet

When you click “Blank workbook,” Excel closes the welcome page and opens a new, blank worksheet, as shown in Figure 1-2. A worksheet is a grid of cells where you type in information and formulas. This grid takes up most of the Excel window. It’s where you’ll perform all your work, such as entering data, writing formulas, and reviewing the results.

*

Figure 1-2. The largest part of the Excel window is the worksheet grid, where you type in your information.

Here are a few basics about Excel’s grid:

The grid divides your worksheet into rows and columns. Excel names columns using letters (A, B, C…), and labels rows using numbers (1, 2, 3…).

Note

Obviously, once you go beyond 26 columns, you run out of letters. Excel handles this by doubling up (and then tripling up) letters. For example, after column Z is column AA, then AB, then AC, all the way to AZ and then BA, BB, BC—you get the picture. And if you create a ridiculously large worksheet, you’ll find that column ZZ is followed by AAA, AAB, AAC, and so on.

*

Figure 1-3. In this spreadsheet, the active cell is C6. You can recognize an active (or current) cell by its heavy black border. You’ll also notice that Excel highlights the corresponding column letter (C) and row number (6) at the edges of the worksheet. Just above the worksheet, on the left side of the window, the formula bar gives you the active cell’s address.

The best way to get a feel for Excel is to dive right in and start putting together a worksheet. The following sections cover each step that goes into assembling a simple worksheet. This one tracks household expenses, but you can use the same approach with any basic worksheet.

Adding Column Titles

Excel lets you arrange information in whatever way you like. There’s nothing to stop you from scattering numbers left and right, across as many cells as you want. However, one of the most common (and most useful) ways to arrange information is in a table, with headings for each column.

It’s important to remember that with even the simplest worksheet, the decisions you make about what’s going to go in each column can have a big effect on how easy it is to manipulate your information. For example, in a worksheet that stores a mailing list, you could have two columns: one for names and another for addresses. But if you create more than two columns, your life will probably be easier because you can separate first names from street addresses from ZIP codes, and so on. Figure 1-4 shows the difference.

*

Figure 1-4. Top: If you enter both first and last names in a single column, you can sort the column only by first name. And if you clump the addresses and ZIP codes together, you have no way to count the number of people in a certain town or neighborhood. Bottom: The benefit of a six-column table is significant: It lets you break down (and therefore analyze) information granularly, For example, you can sort your list according to people’s last names or where they live. This arrangement also lets you filter out individual bits of information when you start using functions later in this book.

You can, of course, always add or remove columns. But you can avoid getting gray hairs by starting a worksheet with all the columns you think you’ll need.

The first step in creating a worksheet is to add your headings in the row of cells at the top of the sheet (row 1). Technically, you don’t need to start right in the first row, but unless you want to add more information before your table—like a title for the chart or today’s date—there’s no point in wasting space. Adding information is easy—just click the cell you want and start typing. When you finish, hit Tab to complete your entry and move to the cell to the right, or click Enter to head to the cell just underneath.

Note

The information you put in an Excel worksheet doesn’t need to be in neat, ordered columns. Nothing stops you from scattering numbers and text in random cells. However, most Excel worksheets resemble some sort of table, because that’s the easiest and most effective way to manage large amounts of structured information.

For a simple expense worksheet designed to keep a record of your most prudent and extravagant purchases, try the following three headings:

Date Purchased. Stores the date when you spent the money.

Item. Stores the name of the product that you bought.

Right away, you face your first glitch: awkwardly crowded text. Figure 1-5 shows how to adjust the column width for proper breathing room.

*

Figure 1-5. Top: The standard width of an Excel column is 8.43 characters, which hardly allows you to get a word in edgewise. Here’s how to give yourself some more room. First, position your mouse on the right border of the column header you want to expand so that the mouse pointer changes to the resize icon (it looks like a double-headed arrow). Now drag the column border to the right as far as you want. As you drag, a tooltip appears, telling you the character size and pixel width of the column. Both of these pieces of information play the same role—they tell you how wide the column is. Only the unit of measurement changes. Bottom: When you release the mouse, Excel resizes the entire column of cells to the new width.

Xem thêm: đồ án cân điện tử

Note

A column’s character width doesn’t really reflect how many characters (or letters) fit in a cell. Excel uses proportional fonts, in which different letters take up different amounts of room. For example, the letter W is typically much wider than the letter I. All this means is that the character width Excel shows you isn’t a real indication of how many letters can fit in the column, but it’s a useful way to compare column widths.

Adding Data

You can now begin adding your data: Simply fill in the rows under the column titles. Each row in the expense worksheet represents a separate purchase. (If you’re familiar with databases, you can think of each row as a separate record.)

As Figure 1-6 shows, the first column is for dates, the second stores text, and the third holds numbers. Keep in mind that Excel doesn’t impose any rules on what you type, so you’re free to put text in the Price column. But if you don’t keep a consistent kind of data in each column, you won’t be able to easily analyze (or understand) your information later.

Figure 1-6. This rudimentary expense list has three items in it (in rows 2, 3, and 4). By default, Excel aligns the items in a column according to their data type. It aligns numbers and dates on the right, and text on the left.

That’s it. You’ve now created a living, breathing worksheet. The next section explains how you can edit the data you just entered.

Editing Data

Every time you start typing in a cell, Excel erases any existing content in that cell. (You can also quickly remove the contents of a cell by moving to the cell and pressing Delete, which clears its contents.)

If you want to edit cell data instead of replacing it, you need to put the cell in edit mode, like this:

Move to the cell you want to edit.

Use the mouse or the arrow keys to get to the correct cell.

Put the cell in edit mode by pressing F2 or by double-clicking inside it.

Edit mode looks like ordinary text-entry mode, but you can use the arrow keys to position your cursor in the text you’re editing. (When you aren’t in edit mode, pressing these keys just moves you to another cell.)

Complete your edit.

Once you modify the cell content, press Enter to confirm your changes or Esc to cancel your edit and leave the old value in the cell. Alternatively, you can click on another cell to accept the current value and go somewhere else. But while you’re in edit mode, you can’t use the arrow keys to move out of the cell.

Tip

If you start typing new information into a cell and you decide you want to move to an earlier position in your entry (to make an alteration, for instance), just press F2. The cell box still looks the same, but now you’re in edit mode, which means that you can use the arrow keys to move within the cell (instead of going from cell to cell). Press F2 again to return to data entry mode, where you can use the arrow keys to move to other cells.

As you enter data, you may discover the Bigtime Excel Display Problem (known to aficionados as BEDP): Cells in adjacent columns can overlap one another. Figure 1-7 illustrates the problem. One way to fix BEDP is to manually resize the column, as shown in Figure 1-5. Another option is to turn on text wrapping so you can fit multiple lines of text in a single cell, as described on Alignment and Orientation.

Figure 1-7. Overlapping cells can create big headaches. For example, if you type a large amount of text into A1 and then you type some text into B1, you see only part of A1’s data in your worksheet (as shown here). The rest is hidden from view. But if, say, A3 contains a large amount of text and B3 is empty, Excel displays the content in A3 over both columns, and you don’t have a problem.

Editing Cells with the Formula Bar

Just above the worksheet grid but under the ribbon is an indispensable editing tool called the formula bar (Figure 1-8). It displays the address of the active cell (like A1) on the left edge, and it shows you the current cell’s contents.

Figure 1-8. The formula bar (just above the grid) displays information about the active cell. In this example, you can see that the current cell is B4 and it contains the number 592. Instead of editing this value in the cell, you can click anywhere in the formula bar and make your changes there.

You can use the formula bar to enter and edit data instead of editing directly in your worksheet. This is particularly useful when a cell contains a formula or a large amount of information. That’s because the formula bar gives you more work room than a typical cell. Just as with in-cell edits, you press Enter to confirm formula bar edits or Esc to cancel them. Or you can use the mouse: When you start typing in the formula bar, a checkmark and an “X” icon appear just to the left of the box where you’re typing. Click the checkmark to confirm your entry or “X” to roll it back.

Ordinarily, the formula bar is a single line. If you have a really long entry in a cell (like a paragraph’s worth of text), you need to scroll from one side to the other. However, there’s another option—you can resize the formula bar so that it fits more information, as shown in Figure 1-9.

Figure 1-9. To enlarge the formula bar, click the bottom edge and pull down. You can make it two, three, four, or many more lines large. Best of all, once you get the size you want, you can use the expand/collapse button to the right of the formula bar to quickly expand it to your preferred size and collapse it back to the single-line view.

POWER USERS’ CLINIC: Using R1C1 Reference Style

Most people like to identify columns with letters and rows with numbers. This system makes it easy to tell the difference between the two, and it lets you use short cell addresses like A10, B4, and H99. When you first install Excel, it uses this style of cell addressing.

However, Excel lets you use another cell addressing system called R1C1. In R1C1 style, Excel identifies both rows and columns with numbers. That means the cell address A10 becomes R10C1 (read this as Row 10, Column 1). The letters R and C tell you which part of the address represents the row number and which part is the column number. The R1C1 format reverses the order of conventional cell addressing.

R1C1 addressing isn’t all that common, but it can be useful if you need to deal with worksheets that have more than 26 columns. With normal cell addressing, Excel runs out of letters after column 26, and it starts using two-letter column names (as in AA, AB, and so on). But this approach can get awkward. For example, if you want to find cell AX1, it isn’t immediately obvious that cell AX1 is in column 50. On the other hand, the R1C1 address for the same cell—R1C50—gives you a clearer idea of where to find the cell.

To use R1C1 for a spreadsheet, select File→Options. This shows the Excel Options window, where you can change a wide array of settings. In the list on the left, choose Formulas to hone in on the section you need. Then, look under the “Working with formulas” heading, and turn on the “R1C1 reference style” checkbox.

Xem thêm: Hướng Dẫn Cách Xuống Dòng Trong Excel Và Google Spreadsheets Dễ Đơn Giản Nhất

R1C1 is a file-specific setting, which means that if someone sends you a spreadsheet saved using R1C1, you’ll see the R1C1 cell addresses when you open the file, regardless of what type of cell addressing you use in your own spreadsheets. Fortunately, you can change cell addressing at any time using the Excel Options window.

Xem thêm bài viết thuộc chuyên mục: Excel