Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

9.8 Formatting and Templates in Google Sheets

Workplace Software and Skills9.8 Formatting and Templates in Google Sheets

Table of contents
  1. Preface
  2. 1 Technology in Everyday Life and Business
    1. Chapter Scenario
    2. 1.1 Computing from Inception to Today
    3. 1.2 Computer Hardware and Networks
    4. 1.3 The Internet, Cloud Computing, and the Internet of Things
    5. 1.4 Safety, Security, Privacy, and the Ethical Use of Technology
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  3. 2 Essentials of Software Applications for Business
    1. Chapter Scenario
    2. 2.1 Software Basics
    3. 2.2 Files and Folders
    4. 2.3 Communication and Calendar Applications
    5. 2.4 Essentials of Microsoft 365
    6. 2.5 Essentials of Google Workspace
    7. 2.6 Collaboration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  4. 3 Creating and Working in Documents
    1. Chapter Scenario
    2. 3.1 Navigating Microsoft Word
    3. 3.2 Formatting Document Layout in Microsoft Word
    4. 3.3 Formatting Document Content in Microsoft Word
    5. 3.4 Collaborative Editing and Reviewing in Microsoft Word
    6. 3.5 Document Design
    7. 3.6 Navigating Google Docs
    8. 3.7 Formatting Layout and Content in Google Docs
    9. 3.8 Collaborative Editing and Reviewing in Google Docs
    10. 3.9 Versions and Version History
    11. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  5. 4 Document Preparation
    1. Chapter Scenario
    2. 4.1 Microsoft Word: Advanced Formatting Features
    3. 4.2 Working with Graphics and Text Tools in Microsoft Word
    4. 4.3 Managing Long Documents in Microsoft Word
    5. 4.4 Google Docs: Enhanced Formatting Features
    6. 4.5 Working with Graphics and Text Tools in Google Docs
    7. 4.6 Managing Long Documents in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  6. 5 Advanced Document Preparation
    1. Chapter Scenario
    2. 5.1 Creating Different Document Types in Microsoft Word
    3. 5.2 Mail Merge in Microsoft Word
    4. 5.3 Creating Forms in Microsoft Word
    5. 5.4 Creating Different Document Types in Google Docs
    6. 5.5 Creating Forms in Google Docs
    7. 5.6 Advanced Collaboration in Google Docs
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  7. 6 Preparing Presentations
    1. Chapter Scenario
    2. 6.1 Presentation and Design Essentials
    3. 6.2 Designing a Presentation in Microsoft PowerPoint
    4. 6.3 Formatting Microsoft PowerPoint Slides: Layout and Design Principles
    5. 6.4 Adding Visuals and Features to Microsoft PowerPoint Slides
    6. 6.5 Designing a Presentation in Google Slides
    7. 6.6 Creating Google Slides: Layout and Text
    8. 6.7 Adding Visuals and Features to Google Slides
    9. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  8. 7 Advanced Presentation Skills
    1. Chapter Scenario
    2. 7.1 Effective Presentation Skills
    3. 7.2 Finalizing a Slide Collection
    4. 7.3 Preparing a Microsoft PowerPoint Collection for Presentation
    5. 7.4 Preparing a Google Slides Collection for Presentation
    6. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  9. 8 Content Management Systems and Social Media in Business
    1. Chapter Scenario
    2. 8.1 What Are Content Management Systems?
    3. 8.2 Common Content Management Systems
    4. 8.3 Creating Content with a Content Management System
    5. 8.4 Search Engine Optimization
    6. 8.5 Social Media in Business
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  10. 9 Working with Spreadsheets
    1. Chapter Scenario
    2. 9.1 Microsoft Excel Basics
    3. 9.2 Text and Numbers in Microsoft Excel
    4. 9.3 Calculations and Basic Formulas in Microsoft Excel
    5. 9.4 Formatting and Templates in Microsoft Excel
    6. 9.5 Google Sheets Basics
    7. 9.6 Text and Numbers in Google Sheets
    8. 9.7 Calculations and Basic Formulas in Google Sheets
    9. 9.8 Formatting and Templates in Google Sheets
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  11. 10 Advanced Excel Formulas, Functions, and Techniques
    1. Chapter Scenario
    2. 10.1 Data Tables and Ranges
    3. 10.2 More About Formulas
    4. 10.3 Using Arithmetic, Statistical, and Logical Functions
    5. 10.4 PivotTables
    6. 10.5 Auditing Formulas and Fixing Errors
    7. 10.6 Advanced Formatting Techniques
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  12. 11 Advanced Excel Spreadsheets: Statistical and Data Analysis
    1. Chapter Scenario
    2. 11.1 Understanding Data, Data Validation, and Data Tables
    3. 11.2 Statistical Functions
    4. 11.3 What-If Analysis
    5. 11.4 PivotTables/Charts
    6. 11.5 Data Analysis Charts
    7. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  13. 12 Using Excel in Accounting and Financial Reporting
    1. Chapter Scenario
    2. 12.1 Basic Accounting
    3. 12.2 Financial Functions in Microsoft Excel
    4. 12.3 Integrating Microsoft Excel and Accounting Programs
    5. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  14. 13 Understanding and Using Databases
    1. Chapter Scenario
    2. 13.1 What Is a Database?
    3. 13.2 Microsoft Access: Main Features and Navigation
    4. 13.3 Querying a Database
    5. 13.4 Maintaining Records in a Database
    6. 13.5 Creating Reports in Microsoft Access
    7. 13.6 Creating Forms in Microsoft Access
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  15. 14 Advanced Database Use
    1. Chapter Scenario
    2. 14.1 Advanced Queries in Microsoft Access
    3. 14.2 Multiple Table Forms
    4. 14.3 Customizing Forms
    5. 14.4 Customizing Reports
    6. 14.5 Using Macros
    7. 14.6 Data Analysis and Integration
    8. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
      6. Case Exercises
  16. 15 Integrating Applications
    1. Chapter Scenario
    2. 15.1 Microsoft 365: Collaboration and Integration
    3. 15.2 Microsoft Word: Integration with Microsoft Excel and Microsoft Access
    4. 15.3 Microsoft Word and Microsoft PowerPoint Integration
    5. 15.4 Microsoft Excel and Microsoft PowerPoint Integration
    6. 15.5 Microsoft Excel and Microsoft Access Integration
    7. 15.6 Integrating Data from Other Programs into Google Workspace
    8. 15.7 New Developments: The Role of Artificial Intelligence
    9. 15.8 Mastering Workplace Software Skills: A Project
    10. Chapter Review
      1. Key Terms
      2. Summary
      3. Review Questions
      4. Practice Exercises
      5. Written Questions
  17. Index

Learning Objectives

By the end of this section, you will be able to:

  • Format and manipulate a cell or group of cells
  • Design column and row header
  • Use conditional formatting
  • Work with templates to format worksheets

Google Sheets has the same formatting features that Microsoft Excel does. The cell formatting of fonts and shading of cells is part of everyday use when making headers. Text wrapping is also a feature in Sheets. Like Excel, there are numerous Sheets templates available online and within the application. All of these features make Sheets a valuable spreadsheet application for WorldCorp.

Formatting Cells

Sheets has the same functionality for formatting cells as Excel does. For example, you can use contrasting color combinations to make text easier to read. The main difference in formatting cells between Sheets and Excel is the location of the tools.

Using Color and Fonts

The tables in Figure 9.57 have the standard blue headers we’ve been using. Starting with these tables, we will apply formatting changes. First, let’s change the font of the headers and the body of the tables. Click on the header row and choose Courier New from the Font drop-down box (Figure 9.71a). Next, change the color of the font. Since the headers are still selected after the font change, go to the action bar and click on the Text color command. Select dark orange (last option in the third column from the left) as the text color. You can then change the background color to a lighter blue using the Fill color command on the action bar (Figure 9.71b). In this case, changing the color of the background or the color of the font can make the column headers stand out more and is more visually appealing.

(a) Row 2 selected and Font opens to Recent fonts (Courier New selected). (b) Fill color is selected and opens to options for Custom, another Custom, and rows of color option selections.
Figure 9.71 In Sheets, you can format headers by (a) changing the font, changing the color of the font, and (b) changing the cell background color, just as in Excel. (Google Sheets is a trademark of Google LLC.)

Borders

The Borders command lets users modify the borders on each cell. Using the same table, let’s change the thickness of the borders around the headers to differentiate them from the body. You will also add an outside border for the body of the table. First, select the headers and click on the Borders command on the action bar, then click on the Border style drop-down menu and select the third thickest (Figure 9.72a). To apply the thicker border, click on the command for All borders (Figure 9.72b).

(a) Borders is selected and opens to options available for location, color, line thickness (selected -opens to thickness options (thick line is selected)). (b) All borders is selected from the Borders options.
Figure 9.72 You can change the borders from the action bar. Click on the Borders command and choose Border style. (a) There are several border styles to choose from in the list. (b) After choosing the style, apply it to all borders. (Google Sheets is a trademark of Google LLC.)

For the border around the table body, repeat the same first step, selecting the third thickest. Then, instead of applying it to all borders, choose the Outer borders option (Figure 9.73).

Borders opens to Outer borders selected. Spreadsheets displays thick black border around the outside and around Row 2. No borders encase the individual cells in rows 3-11.
Figure 9.73 The header borders are formatted, and the same styling can be used to add a border to the outside of the table. Click on the Borders command, choose the border style, and then apply it to the outer borders. The final table looks neat and professional with borders. (Google Sheets is a trademark of Google LLC.)

Wrapping Text

You may have noticed that because you changed the font, the header in column C does not fit the width of the column. This is easily fixed by using the Text wrapping command on the action bar. First, select cell C2, then click the Text wrapping command, and select the middle command for Wrap (Figure 9.74a). Now all of the text is visible, as seen in Figure 9.74b. If the text is still a little tight, widen the column slightly by hovering over the edge of the column until the cursor changes to a double arrow, then drag the edge of the column to the right to accommodate the text.

(a) Text in cell C2 is cut off, lacking space. Text wrapping opens to options for Wrap selected. (b) The words Item Description in cell C2 show completely on two lines.
Figure 9.74 When the text does not fit in a cell, one option is to wrap the text. (a) Select the cell and use the Wrap command. (b) The text should automatically fit in the cell. (Google Sheets is a trademark of Google LLC.)

Merging Cells

As with Excel, Sheets allows cell merging. This enables information to be grouped to make it more readable for the user. For example, the Merge cells command could group sales agents by region. To use this function, first select the cells you would like to merge. Then, use the Merge cells tool in the action bar, as shown in Figure 9.75. You have several options with the tool: Merge all, Merge vertically (working with rows), Merge horizontally (working with columns), and Unmerge.

A blank spreadsheet shows cells A1 and B1 selected and the Merge icon selected on the toolbar. The drop down menu shows the options: Merge all, Merge vertically, Merge horizontally, and Unmerge.
Figure 9.75 The Merge cells tool is found on the action bar. First, select the cells to be merged, then select the Merge cells tool. (Google Sheets is a trademark of Google LLC.)

Designing Column and Row Headers

You have learned how to change the font, font color, background color, and width for header styling. Header text can be bolded and centered to make them easier to read, and background colors can be applied and changed. Figure 9.76 shows the formatting applied to cell B2 in the header. The font is bold and the text is centered.

(a) Row 2 is selected and Bold selected. (b) Data in Row 2 is bolded. Row 2 is still selected. Alignment icon is selected and Center is applied to row 2.
Figure 9.76 Most professionally designed tables (a) use bold font on the headers and (b) are center aligned. (Google Sheets is a trademark of Google LLC.)

Freezing Columns and Rows

Freezing columns and rows is useful when the data in your sheet does not fit the available viewing space. In Sheets, you can freeze panes anywhere in the worksheet, but it’s certainly most useful to freeze column and row headers. First, select the row that you want to freeze, then select Freeze from the View menu and choose Up to current row. Sheets freezes that row and all rows above it, as shown in Figure 9.77.

Freeze is selected. Options available: No rows, 1 row, 2 rows, Up to row 2, No columns, 1 column, 2 columns, Up to column A. Spreadsheet displays row 7 below row 1.
Figure 9.77 You can freeze panes anywhere on a sheet. Click on the row that you want to freeze and freeze up to that row. That row freezes, and you can scroll through the data below it. (Google Sheets is a trademark of Google LLC.)

Hiding Columns and Rows

Sheets also supports hiding columns and rows. Select the row or column you want to hide. Right-click and then click on Hide row or Hide column (Figure 9.78a). To unhide it, select the two on either side of the hidden row or column, right-click and click on Unhide rows or Unhide columns (Figure 9.78b), or click on one of the two small arrows in the header.

(a) Hide column available on open pane. (b) Unhide column available on open pane. Two black arrows visible in header on right side of Column A and left side of next column.
Figure 9.78 Hiding and unhiding columns in Sheets is similar to the process in Excel. (a) Select the column you want to hide, right-click, and click on Hide column. (b) To unhide, select the columns on either side of the hidden column, right-click, and click on Unhide columns. (Google Sheets is a trademark of Google LLC.)

Conditional Formatting

Conditional formatting allows us to apply preset formatting to data that meets certain criteria. You can find the conditional formatting feature in Sheets on the Format menu (Figure 9.79). First, select the cells to format, and then choose the conditional formatting feature. A sidebar opens where you can set up the conditional formatting. In this example, the conditions are set so that Sheets will highlight in the default color any cells in the range G2:G11 that have values greater than 999. The table updates instantly (Figure 9.80).

Cell G6 selected. Format tab selected and drop-down lists Theme, Number, Text, Alignment, Wrapping, Rotation, Font Size, Merge cells, Conditional formatting, Alternating colors, and Clear formatting. Arrow points to Conditional formatting.
Figure 9.79 In Sheets, you can set up conditional formatting as in Excel. Conditional formatting is in the Format menu. The sidebar opens up to set the criteria. (Google Sheets is a trademark of Google LLC.)
Single color tab selected in Conditional format pane. Format rules for Greater than 999 and Default Formatting style are selected. In column G of spreadsheet, items over 999 are highlighted.
Figure 9.80 When the criteria are set, the table instantly updates. (Google Sheets is a trademark of Google LLC.)

Working with Templates

Sheets offers many templates on the Welcome screen (Figure 9.81). If you do not find what you are looking for in this screen, you can search for other online templates, but you have to use certain criteria. You need to use the Google search engine, and type “X template site:https://docs.google.com.” For example, if you are looking for business budget reports, you would type “business budget report template site:https://docs.google.com.” Searching in this manner will give you results from other Google Docs users’ Google Drive accounts, meaning that they are sharing their templates with other users and they are available for use and not protected or restricted. This also means that the templates have been formatted for Sheets and not for Excel. In some cases, importing Excel files into Sheets can alter the formatting.

When you find the one you want to use, click on it, and a little ball will appear in the middle while you wait for the document to open. You can then edit the document that was generated using the template to customize it for your needs (Figure 9.82).

Templates on the Welcome screen are shown; eight different options are available with a Work heading.
Figure 9.81 Sheets templates are available on the Welcome screen. (Google Sheets is a trademark of Google LLC.)
Monthly budget spreadsheet template displays header and sample charts and infomation boxes for customization.
Figure 9.82 Sheet templates are also available online from other users. (Google Sheets is a trademark of Google LLC.)
Citation/Attribution

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
Citation information

© Jan 3, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.