Home > Articles > Home & Office Computing

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

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

Format

Example

Rounded

2,500

2 Decimals

2,500.00

Financial rounded

(2,500)

Financial

(2,500.00)

Scientific (for large numbers)

1.25E+12 (This number is equivalent to 1,250,000,000,000.)

Currency rounded

$2,500

Currency

$2,500.00

Percent rounded

25%

Percent

25.00%

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

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

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

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

    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

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).
  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020