9.6 Text and Numbers in Google Sheets

Using the Copy-and-Paste and Insert Functions

Before learning to design and build a table from a blank worksheet, you need to understand how to use formatting from an existing table, as with Excel. Say a table to be copied to a new blank worksheet. Follow the same basic procedure as with Excel. First, select the table (Figure 9.48), then choose Copy from the Edit menu or press Ctrl+C on the keyboard. Next, go to the new worksheet and choose Paste from the Edit menu or press Ctrl+V. There will be are a few minor differences between your new table and the original. For example, the column widths of the original were adjusted to their contents, whereas in the pasted table, all columns have the same width. To adjust the widths, you can double-click on the edges of the header row between two columns you want to adjust.

You have now created a new table using the formatting of an existing table, but the table isn’t exactly what we need. Your manager asks you to add a new column between existing columns for the destination. To do this, first click on the top of column C to highlight the whole column, because you will insert the new column to its left. Then, click on the Insert menu to select Column Left (Figure 9.49a), which will insert a blank column (Figure 9.49b) where you can input your new data.

Figure 9.48 Using the formatting of an existing table can make it easier to create a new one. The table being copied has formatted headers and cells. First, highlight the table and copy it. Then, paste it into the new workbook. Since the columns are all the same size, they need to be resized. (Google Sheets is a trademark of Google LLC.)

Figure 9.49 (a) Insert a column to the left of the item description. (b) The empty column keeps the visual formatting in the header. Then, you can enter the data in the new column. (Google Sheets is a trademark of Google LLC.)

Working with Numeric and Text Data in a Sheets Cell

Copying the formatting from an existing table is helpful, but it is not always an option. In some cases, you will need to create a table from scratch. The first step is to establish the headers in the first row (Figure 9.50a). Based on the headers, you can then choose the most appropriate format for the data (Figure 9.50b).

Figure 9.50 Creating a table from scratch requires many steps. First, enter your headers in the first row. (a) Then, format each column for the type of data it will contain. Adjust the column width when necessary. Format your header row by bolding and centering the content. (b) Finally, add the background fill from the custom color menu. (Google Sheets is a trademark of Google LLC.)

The format types Sheets uses are similar to the ones Excel uses. Table 9.3 compares the formats between Sheets and Excel. The names differ slightly, but many mean almost the same thing. There are minor differences like the order the format types appear in the menu, and there are some formats that are unique to Sheets. Sheets has the Financial, Currency rounded, Date time, and Duration formats that are not available in Excel.

Once you have your headers in place, you can apply the appropriate format to the cells below. Select all of column A and go to the More formats drop-down menu, and use the Date format (Figure 9.50a). You can set the next three columns as Plain Text and leave columns E and F as Automatic format. Set column G as Accounting. Then, you can fill in the table by adding each order. With all of the data entered, you can make any final formatting changes. First, adjust the width of the columns by double-clicking at the edge of each column. Next, drag the edge of column F to make it narrower so that the header has one word per line. Then, center align and apply bold font on all headers. Finally, apply the background color from the Custom set of colors, which shows recently used colors (Figure 9.50b).

Using Numeric and Text Data to Create Basic Graphs

To create a chart or graph in Sheets, the process is slightly different from Excel. First, select the data as you would do when creating a graph or chart in Excel. Then, go to the Insert menu and choose Chart. The chart will appear on the screen in the spreadsheet you have created. Google will analyze the type of data and choose the chart that seems appropriate for the data you have selected. The Chart Editor window will appear to the right. Here, you can adjust the type of chart you want to use and customize the chart with titles and other formatting options. Use the sales report created in Figure 9.50b to make a graph that compares the FOB $ to the Date. In other words, it will show the amount of money traded on each day. Sheets analyzes the data before creating the graph and will manipulate the data to be chronological, if necessary. It will also use its built-in artificial intelligence to choose the type of graph most appropriate for the data. You can override the graph type Sheets selects if there is one you feel is more appropriate. PivotTables/Charts goes into more detail about creating graphs and charts.

To create a basic graph, first select the columns you want to compare (Figure 9.51), using the Ctrl key to select two noncontiguous columns. Then, click on Insert Chart, and Sheets will create the graph it determines is the most appropriate. You can change the type of graph if you prefer a different type. Using the Chart Type drop-down menu, you can choose a different layout (Figure 9.52). Two other options for this data are a bar chart with the chronological order inversed, and an area chart, which is similar to the line chart. When choosing any chart option, Sheets will automatically reorder the dates.

Figure 9.51 Creating graphs in Sheets is simple because of the built-in analysis the application performs. First, choose the columns you want to compare and click on Insert Chart. Sheets automatically chose a line chart for the data. (Google Sheets is a trademark of Google LLC.)

Figure 9.52 The Chart Editor sidebar gives other options for the type of chart. Another option for this set of data is a bar chart. (Google Sheets is a trademark of Google LLC.)

Page Layout and Review Menus

Sheets does not have menus that directly compare to the Page Layout and Review tabs in Excel, but it does have many of the same features. One feature Sheets does not have is Page Setup. The items in the Page Setup command group such as margins and page orientation are managed in the Print Setting in Google. Similar to the Themes command group in Excel, Sheets has a Theme option found in the Format menu (Figure 9.53). Excel’s Page Layout tab also has the Background command that allows you to choose an image for the background of your worksheet. This feature is partly available in Sheets in the Insert menu, which allows you to insert an “Image in cell” or “Image over cells” (Figure 9.54a). The result of placing an image in a cell is shown in Figure 9.54b. The inserted image (either in a cell or over cells) sits on top of the information in the cells, unlike in Excel, where the image is in the background. The other functions, like moving an object forward or backward, adding gridlines, or scaling settings, are partly available in Sheets. You can insert objects such as Google Draw files, Google Forms, charts, and images into the worksheets, but moving them around may be more cumbersome and less robust compared with Excel.

Figure 9.53 Page layout features in Sheets differ somewhat from those in Excel. Themes are used to set a color scheme and/or font type for your spreadsheet. (Google Sheets is a trademark of Google LLC.)

Figure 9.54 (a) You can insert an image in a single cell or over all of the cells in a worksheet. (b) Inserting an image in a single cell fills the cell with the image. (Google Sheets is a trademark of Google LLC.)

The review features in Sheets differ somewhat from the Review tab commands in Excel. Sheets allows you to insert a comment (Figure 9.55) and view all the comments on the document by clicking on the Comment History button on the top. The Tools menu in Sheets has a spelling and grammar check (Figure 9.56).

Figure 9.55 Some review features in Sheets are similar to those of Excel. Comments are available in the Insert menu. You can also review all comments in a workbook. (Google Sheets is a trademark of Google LLC.)

Figure 9.56 The spelling and grammar check are in the Tools menu. (Google Sheets is a trademark of Google LLC.)