Introducing Spreadsheets in Google Docs

This chapter is from the book

Google Docs 4 Everyone

Learn More Buy

This chapter is from the book

This chapter is from the book 

Google Docs 4 Everyone

Learn More Buy

Formatting a Spreadsheet

As you've seen, a blank spreadsheet is a huge grid of rows, columns, and cells. In addition, that blank spreadsheet treats all cell content in the same way, using the Normal format. Normal format can be fine for numbers (it treats them as everyday, run-of-the-mill numbers), but sometimes you're using numbers in a more specialized sense, and you want your spreadsheet to reflect that. If you're planning the family budget, for example, you're going to want to treat expenditures as dollar amounts. And that means you have to apply formatting to certain parts of the budget spreadsheet so that Google will know those parts refer to money, not to (for example) quantity.

So as you work with a spreadsheet, you'll want to apply different kinds of formatting. Besides formatting different kinds of numbers in different ways (currency, percentage, and so on), you can choose a standard format for dates and times. You might want to call attention to a particular row, column, or cell through formatting or highlighting. This section tells you how to do all that and then goes on to explain how you can set up formatting rules to look for certain criteria and, when these are met, to apply formatting automatically, such as displaying a date in red to get viewers' attention.

Formatting Numbers

Spreadsheets hold many different kinds of numbers—dates, dollar amounts, percentages, and so on—and you want to distinguish among these different kinds. Doing so makes your spreadsheet much easier to read and understand at a glance. In addition, using the right numeric format makes sure that your formulas (Chapter 7) are accurate.

When you format a column's numbers, you're telling Google how to treat those numbers—whether to add a dollar or a percent sign, for example, and whether to use a decimal point or round numbers up or down. Table 6.1 shows the different number formats you can use in Google Docs spreadsheets, along with an example of how each format looks in practice.

Table 6.1. Number Formats for Google Docs Spreadsheets

Choose the cell or range of cells you want to format. For example, to apply a format to all the cells in a column, click the letter above the column you want (this selects the entire column). If you want to quick-format the column as a rounded currency amount or a percentage, click the Format as Currency or Format as Percentage button, respectively, on the toolbar above the spreadsheet (see Figure 6.4).

Figure 6.4 Use these buttons to format numbers in your spreadsheet.

Otherwise, click the More Formats button on the toolbar: 123 with a down arrow next to it. From the menu that appears, select the format you want. Google immediately applies it to the column you chose.

Formatting Dates and Times

If your spreadsheet will contain dates, times, or both, you'll want to standardize their formats to avoid confusion. For example, maybe you format a date as 3/31/09, but folks in the London office use 31-Mar-2009. And while you're used to thinking in terms of AM and PM, your London counterparts use a 24-hour clock—for you, quitting time is 5:00 PM, but for them it's 17:00. It's much easier for everyone if you choose a standard format for dates and for times and use them consistently.

To choose and apply a format for dates or times, select the cell or cell range to which you're applying the format and then click the More Formats button. You can choose a format from the menu's date and time section or click More Formats to see the options shown in Figure 6.5. For each date or time format style, Google shows an example, so you know what the format will look like in the spreadsheet. Click the format you want, and Google applies it.

Figure 6.5 Google offers many styles for formatting dates and times.

Formatting Appearance

In a sea of numbers and other data, it can be easy for important information to get lost. You can make sure that doesn't happen by formatting that important info in a way that makes it stand out.

As Figure 6.6 shows, the spreadsheet editor has a toolbar above the spreadsheet itself. This toolbar has these formatting buttons:

• Font Size—When you click this button, your options range from 6 to 36 points. Google's standard of 10 points is good for most cell data. Smaller can work well for notes and larger for emphasis.
• Bold—Make a cell or cell range stand out by formatting it in bold.
• Strikethrough—This formatting puts a horizontal line through the text or numbers in the cells you've selected. You might want to use this, for example, to emphasize that a deadline has changed, striking through the old deadline and highlighting the new one in bold or with color.
• Text Color—Click this button and then choose from a palette of colors to change the text in the cell(s) from black to the color you select.
• Background Color—Define cell ranges or highlight important information by clicking this button and selecting the background color you want.
• Borders—This is another good way to set off a cell or range of cells by outlining them with a border. Click this button and then choose from eight border styles.
• Align—This button gives you options for aligning a cell's contents horizontally (left, center, or right alignment) or vertically (top, middle, or bottom alignment).
• Merge Across/Break Apart—It can be helpful to identify sections of a spreadsheet by merging several cells and then typing in a title for that section. When you merge cells across, a single cell stretches across several columns, instead of a being the intersection of one row and one column. If you select a merged cell, this button changes to Break Across; clicking it will break the merged cell into individual cells again.
• Wrap Text—If a cell holds a lot of text, some of that text may not display. When you tell Google to wrap the text, it means that the cell lengthens to display all the text it holds. So instead of being tall enough to display a single line of text, the cell (and its row) expands so that it's tall enough to display two or more rows of text. This button toggles text wrapping on and off.

Figure 6.6 Use the toolbar to format cells in your spreadsheet.

To apply any of these kinds of formatting, choose the cell or cell range you want to format and then click the appropriate toolbar button. If you make a mistake, click the toolbar's Undo button.

The menu bar's Format button repeats some of the formats in the toolbar and offers a few others. Select a cell or range, click Format, and then choose from one of these options: Font (six font styles), Bold, Italic, Underline, Strikethrough.

Creating Formatting Rules

Formatting rules let you set up criteria that determine when to apply color to certain cells—automatically. For example, you might want to highlight in red due dates that have passed or expenses that go over budget. Sure, you can hunt down data and highlight it yourself, but why spend the time when you can tell Google to do that for you?

Setting up a formatting rule tells Google to apply specific formatting—text or background color—to a cell or a range of cells under certain conditions. To write a formatting rule, follow these steps:

1. In the spreadsheet for which you're creating the rule, select the cell or cell range to which the rule will apply.
2. Select Format, Change Colors with Rules. This opens the dialog box shown in Figure 6.7.
   

   Figure 6.7 When a cell or cell range meets conditions you set, Google applies the color formatting you specify here.

3. Set the condition for applying the color change. In the first drop-down, choose a condition for text, dates, or numbers, such as Text Contains, Text Does Not Contain, Date Is After, Is Equal To, Is Between, and so on. You can also apply a color change when a cell is empty.
4. Set the specifics for applying the color change. These depend on what you choose for the first drop-down list. For example, if you choose Is Between from the first drop-down, the dialog box presents two text boxes, where you can enter two numbers or dates that define the range. Or if you select Date Is After, the dialog box presents a drop-down list from which you can choose a date such as Today, Tomorrow, In the Past Week, or an exact date that you specify.
5. Select the color change you want to apply. When you check the Text or the Background box, Google displays its color palette. Click a color to select it. You can change the text color, the background color, or both.
6. If you want to create another rule for the same cell or cell range, click Add Another Rule, and the dialog box expands. Repeat steps 3 through 5. When you're done, click the Save Rules button to apply the rule or rules you've created.

If you don't need a particular rule anymore—for example, a deadline has been met and no longer needs highlighting—you can remove that rule. Open the spreadsheet and select Format, Change Colors with Rules. In the Change Colors Based on Rules dialog box, find the rule you want to delete and click the x to its right. Google deletes the rule immediately. Click Save Rules to close the dialog box.

Working with Multiple Sheets

When you create a new Google Docs spreadsheet, you start off with a single sheet. As you work on a spreadsheet, however, you may find that you need more than one sheet to collect separate but related data. If you use a spreadsheet to schedule employees, for example, you may want to use one sheet per month to make the schedule easy to read.

You can tell how many individual sheets a spreadsheet has by looking in the lower-left part of the screen. As Figure 6.8 shows, the current sheet appears as a tab; other sheets appear as links. Click a link to select that sheet. To add a new sheet, click the Add Sheet button. Google adds the new sheet to the right of the currently selected one.

Figure 6.8 Work with multiple sheets in the lower-left part of the screen.

When you double-click an individual sheet's name (or click the selected sheet's tab), you can choose one of these actions from the context menu that appears:

• Delete—When you choose Delete, a dialog box appears, asking whether you're sure you want to delete the sheet and all its data. If you are, click OK. (If you delete the sheet by mistake, immediately click the toolbar's Undo button to bring it back.)
• Duplicate—This makes an exact copy of the current sheet, including its data, and inserts it to the right of the current sheet. Google names the new sheet Copy of <sheet name>, so if you're copying a sheet called Quarter 1 Grades, for example, the new sheet's name is Copy of Quarter 1 Grades. Copying a sheet is useful when you want to use the existing sheet's setup; make a duplicate and then clear its data (see upcoming section), leaving just the existing structure, ready for new information.
• Rename—When you choose this option, a dialog box appears. Type in the sheet's new name and click OK. This option is handy when you discover a typo or you want to give a duplicate sheet its own name, rather than Copy of <sheet name>.
• Move Left/Move Right—Choose one of these options to move the sheet in the direction you specify. If the sheet you're moving is first or last among the sheets, you'll see only one direction (because the sheet can't move any farther in the other direction).