Workplace Software and Skills
Chapter 9 WORKING WITH SPREADSHEETS
Chapter Outline
9.1 Microsoft Excel Basics
By the end of this section, you will be able to:
Figure 9.2
An Excel workbook contains individual worksheets made up of cells. (Used with permission from Microsoft)
Figure 9.3
Separating data by columns makes it easier to analyze and perform calculations with the data. (Used with permission from Microsoft)
Figure 9.4
Clicking on a cell reveals the formula above the headers in the Formula Bar, while the result of the calculation shows in the cell. (Used with permission from Microsoft)
Figure 9.5
The AutoFill feature is useful to get the results without retyping or copying and pasting the formula. (a) Column H is empty, except for the first cell, which contains the formula, as seen in the Formula Bar. Clicking and dragging the formula to the end of the column automatically applies the same formula to subsequent rows. (b) By dragging the formula down the column using the AutoFill feature (small black cross sign in the lower-right corner of a cell), the values are calculated for each row. (Used with permission from Microsoft)
Figure 9.6
The Excel interface uses a ribbon to organize commands into tabs, which include (a) the Home tab and (b) the Insert tab, which are similar to other Office programs. The Excel ribbon also includes tabs specific to Excel, including (c) the Formulas tab, and (d) the Data tab. (Used with permission from Microsoft)
Figure 9.7
The copy-and-paste process makes it easy to create multiple tables that are similar to each other. Start with a formatted data table for location X. (a) Highlight (select) the table and copy it. Notice the dotted line around the selection to show that it has been copied. (b) Paste the table in a new worksheet and update it with the data for location Y. (Used with permission from Microsoft)
Figure 9.8
Excel allows formulas to reference data appearing on any worksheet in the workbook. To create a total of the number of units sold at each location, begin the equation with an equals sign. (a) Then, click on the relevant cell in the first worksheet. Insert the mathematical operator and click on another cell you want in the formula. The formula shows the references to other worksheets. AutoFill can complete the table with formulas that reference the relevant cells. (b) The completed table uses data from other worksheets. (Used with permission from Microsoft)
Figure 9.9
The options for printing are on the File tab. Click on Print. To print only a worksheet, leave the default selection of Print Active Sheets and make any necessary changes to the options. To print the whole workbook, select Print Entire Workbook from the first menu in the Settings. On a Mac, the steps are the same, but the appearance will differ slightly. (Used with permission from Microsoft)
9.2 Text and Numbers in Microsoft Excel
By the end of this section, you will be able to:
Table 9.1: Numerical Values in Excel
Type of Numerical Value | Description |
Number | Data has two decimal places by default, but you can add or remove them, depending on what your needs are. It accepts negative numbers as well. You can add a thousands separator. |
Currency | Data has two decimal places by default. It accepts negative numbers and will display them within parentheses or with a negative sign in red or black font. The thousands separator is turned on by default. A zero is displayed with two decimal places. Excel adds the appropriate currency symbol preceding the number, and the numbers are aligned to the right with no spaces to the edges. |
Accounting | Data has two decimal places by default. It accepts negative numbers and will display in parentheses only. The thousands separator is given. The zeros are displayed as “-”. Excel adds a currency symbol aligned to the left. The difference between Currency and Accounting is that with the Accounting format, all currency symbols are aligned. |
Date | Data is displayed in the American format by default (i.e., month/day/year). There are options to change the location or to change the format to one of many different choices, such as day/month/year or year/month/day. |
Time | Data uses the 24-hour clock or AM/PM format by default. |
Percentage | Data analyzes the portion of a whole and the data is displayed as a percentage with the % symbol and no decimal points by default. |
Fraction | Data analyzes the portion of a whole and displays the values as in the percentages. You can convert a fraction to a percentage before entering data. |
Scientific | Data can be shortened numbers written in exponential form. To shorten a number, display the first digit, add a decimal point, and then list the next two digits. Then, add “E” and the number of zeros the whole number contains. For example, 644362623 would be 6.44E+9. These truncated forms of numbers are commonly used in all sorts of sciences. |
Figure 9.10
You can format numbers on the Home tab. The Number command group contains several buttons for commonly used formats and a drop-down menu. The drop-down menu contains an expanded list of common number formats. More options are available in the Format Cells dialog box. (Used with permission from Microsoft)
Figure 9.11
When a worksheet has multiple types of data, make sure to format it as the most appropriate type. Column A is formatted as a Date. (Used with permission from Microsoft)
Figure 9.12
By default, Excel formats cells as General, so formatting may require adjustment for numbers to display properly. (a) The unformatted table shows percentages and fractions as decimals. You can choose the Percentage format for the cells in the Percentage column. You can choose the Fraction format for the cells in the Fraction column. (b) The formatted table displays the percentages and fractions correctly. (Used with permission from Microsoft)
Figure 9.13
Numbers can be stored as text data to retain symbols or leading zeros, which are regularly used in some government reports, identification numbers, and some phone numbers. (Used with permission from Microsoft)
Figure 9.14
The default formatting for cell data is (a) General, but if you change it to (b) Currency, Excel will add two decimal places and a currency symbol. (Used with permission from Microsoft)
Figure 9.15
The Page Layout tab includes commands for page setup and manipulation of objects. (Used with permission from Microsoft)
Figure 9.16
The Review tab lets you manage comments, sharing settings, and spelling tools. (Used with permission from Microsoft)
9.3 Calculations and Basic Formulas in Microsoft Excel
By the end of this section, you will be able to:
Figure 9.17
Formulas must begin with the “=” sign and contain mathematical operators (i.e., +, -, *, /). (Used with permission from Microsoft)
Figure 9.18
You can use a formula to find the sum of a set of cells. (a) The table has a cell for the total sales. First, type an “=” sign, then use the cell references or click on the cells and a plus sign to construct the formula. (b) The finished formula shows all of the cells referenced. (Used with permission from Microsoft)
Figure 9.19
Functions are time-saving methods for performing calculations on data. To find the sum of a range of cells, type the equals sign and “SUM.” (Used with permission from Microsoft)
Figure 9.20
Then, choose the cells you want to include. (Used with permission from Microsoft)
Figure 9.21
Using a range, rather than listing individual cells, can save you time. (Used with permission from Microsoft)
Figure 9.22
Subtraction in Excel uses formulas that are similar to addition formulas, but there is no subtraction function. (Used with permission from Microsoft)
Figure 9.23
For multiplication, use cell references to construct formulas. (Used with permission from Microsoft)
Figure 9.24
You can also use constants to construct formulas. (Used with permission from Microsoft)
Figure 9.25
You can use the PRODUCT function for multiplication. (Used with permission from Microsoft)
Figure 9.26
The division formula uses a slash and returns (a) a quotient with decimal places, but you can format it to be (b) a percentage. (Used with permission from Microsoft)
9.4 Formatting and Templates in Microsoft Excel
By the end of this section, you will be able to:
Figure 9.27
You can easily change the cell fill color, font type, and text color. (a) Select the paint bucket from the Home tab to change the color of the background fill. A drop-down menu gives you many options or you can define your own color using More Colors. The new background fill color is more visually appealing, but the text does not stand out as it would in a different color. (b) Font size, type, and color can also be changed using the commands in the Font command group. (Used with permission from Microsoft)
Figure 9.28
To add borders, select the cells you want to add a border to. (Used with permission from Microsoft)
Figure 9.29
(a) You can use the Borders command drop-down menu or right-click and choose Format Cells. (b) You can select various thickness levels of the border around the cell. (Used with permission from Microsoft)
Figure 9.30
Text wrapping can make long text fit in a column. (a) The header for column E is too long for the width of the column. Double-click or click and drag the line between columns to widen or narrow a column. The Wrap Text feature allows you to see text that is cut off. (b) Text wrapping changes the height of a row to accommodate long text. (Used with permission from Microsoft)
Figure 9.31
Merging cells is useful for presentation and readability purposes. You can (a) group columns by adding a row above the header row, merge and center cells to create a new label above the date and time, (b) merge and center cells to group the quantity and the code, and merge cells next to the total to create a more readable total. You can also add borders. (c) The final table is clear and readable. (Used with permission from Microsoft)
Figure 9.32
Using the Freezing Panes command keeps headers visible when data extends off the screen. (a) Before the header row is frozen, scrolling down removes the header from view. (b) The Freeze Panes options are found on the View tab. Clicking on Freeze Panes allows for freezing the top row, first column, or both. Freezing the header row shows a line that indicates the row is frozen and keeps it in view while scrolling. (Used with permission from Microsoft)
Figure 9.33
Using the Hide command makes rows/columns temporarily not visible in a table. (a) Right-click on the column and select Hide. (b) When columns are hidden, double lines and skipped letters are indicated in the letter designation header. (Used with permission from Microsoft)
Figure 9.34
(a) To reveal the columns again, select the two columns on either side of the hidden one, right-click, and select Unhide. (b) All columns are visible again. (Used with permission from Microsoft)
Figure 9.35
Conditional formatting automatically applies designated formatting to values that meet a certain criterion. The Conditional Formatting drop-down menu lists several commonly used choices. (Used with permission from Microsoft)
Figure 9.36
(a) The dialog box allows you to set the parameters. (b) There are numerous preset conditional formatting choices. (Used with permission from Microsoft)
Figure 9.37
Templates are preformatted and predesigned tables. You can find the templates by clicking on File and New. A sales report has formatting and formulas preset so that all you need to do is enter your data. (Used with permission from Microsoft)
Figure 9.38
Although templates come with their own formatting, you can customize them based on your needs. (Used with permission from Microsoft)
9.5 Google Sheets Basics
By the end of this section, you will be able to:
Figure 9.39
The toolbar in Sheets has one row of commands. (Google Sheets is a trademark of Google LLC.)
Figure 9.40
Formatting a normal sales report table in Sheets is similar to formatting in Excel. The table contains different types of data. First, create the headers. (Google Sheets is a trademark of Google LLC.)
Figure 9.41
Then, highlight the cells you will be filling in to add borders. (Google Sheets is a trademark of Google LLC.)
Figure 9.42
Format each column according to the type of data it will contain. The first column is a date, so you can choose Date from the menu. The next two columns can be formatted as plain text. Columns E and G can be formatted as Accounting. You can use Ctrl+Select to format both of them at the same time. (Google Sheets is a trademark of Google LLC.)
Figure 9.43
First, populate the first row of the table. (a) The date should automatically be formatted as month/day/year. The rest of the data can be copied from the order. (b) The revenue cell needs a formula so that it calculates the product of the price and the quantity. Notice the date is formatted correctly once you press Enter or navigate to another cell. (Google Sheets is a trademark of Google LLC.)
Figure 9.44
After entering a row of data, you can do the final formatting of the table. (a) Double-click at the edge of a column to change the width automatically. (b) Repeat this process for all columns. (Google Sheets is a trademark of Google LLC.)
Figure 9.45
You can change the white fill color of the header row to match the original table by choosing blue from the Fill color command. (Google Sheets is a trademark of Google LLC.)
Figure 9.46
To open a Sheets file from Google Drive, you need to recognize the icon for spreadsheets. The first arrow points to an Excel file, and the second arrow points to a Sheets file. Sheets can open both types. (Google Sheets is a trademark of Google LLC.)
Figure 9.47
Printing from Sheets is similar to printing from Excel. (a) Select Print from the File menu. You can print the current sheet. (b) You can also print the entire workbook. Choose your printer from the Destination drop-down menu or save the file as a .pdf. (Google Sheets is a trademark of Google LLC.)
9.6 Text and Numbers in Google Sheets
By the end of this section, you will be able to:
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.)
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.)
Table 9.2: Data Formats in Google Sheets Compared with Excel
Type of Numerical Value | Difference Compared to Excel |
Number | The same as in Excel |
Percentage | The same as in Excel |
Scientific | The same as in Excel |
Accounting | The same as in Excel |
Financial | Similar to “Accounting,” but without the currency symbol |
Currency | The same as in Excel |
Currency rounded | Similar to “Currency,” but with no decimals |
Date | The same as in Excel |
Time | The same as in Excel |
Date time | Similar same to “Date” and “Time” but together in one cell |
Duration | Unique to Sheets; a new way to measure time |
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.)
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.)
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.)
9.7 Calculations and Basic Formulas in Google Sheets
By the end of this section, you will be able to:
Figure 9.57
The addition formula in Sheets works the same way as in Excel. We can use the addition formula to summarize this information into one shorter table. (Google Sheets is a trademark of Google LLC.)
Figure 9.58
You can use the addition formula to add up all of the sales that occur in a specific month. (Google Sheets is a trademark of Google LLC.)
Figure 9.59
Sheets offers a SUM function that can be used instead of formulas. (Google Sheets is a trademark of Google LLC.)
Figure 9.60
You can perform subtraction calculations in Sheets using formulas, just as in Excel. After finding the average, subtract the average from the quantity to get the distance from average. (Google Sheets is a trademark of Google LLC.)
Figure 9.61
Then, copy the formula to all cells in the same row. (Google Sheets is a trademark of Google LLC.)
Figure 9.62
Do the same calculation to find the distance from average for the FOB $. (Google Sheets is a trademark of Google LLC.)
Figure 9.63
Sheets has the advantage over Excel in that it has a MINUS function. (Google Sheets is a trademark of Google LLC.)
Figure 9.64
You can use cell references and constants for multiplication using formulas in Sheets. (a) The price per unit is the same for all items in this table, so you can multiply the quantity by the price without adding a column. Alternatively, you can add a column for the price per unit and (b) use cell references to perform the calculation. (Google Sheets is a trademark of Google LLC.)
Figure 9.65
The function for multiplication in Sheets is MULTIPLY. (Google Sheets is a trademark of Google LLC.)
Figure 9.66
You can use division to find the proportion of sales by client and by month. First, find the total sales for each month. (Google Sheets is a trademark of Google LLC.)
Figure 9.67
Then, use the division formula to divide the client’s total by the total monthly sales. (Google Sheets is a trademark of Google LLC.)
Figure 9.68
Carry these formulas down the column to get the proportion for each client in each month. (Google Sheets is a trademark of Google LLC.)
Figure 9.69
Reformatting the cells as percentages makes the data more useful. (Google Sheets is a trademark of Google LLC.)
Figure 9.70
You may use DIVIDE as a function in Sheets. (Google Sheets is a trademark of Google LLC.)
9.8 Formatting and Templates in Google Sheets
By the end of this section, you will be able to:
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.)
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.)
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.)
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.)
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.)
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.)
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.)
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.)
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.)
Figure 9.80
When the criteria are set, the table instantly updates. (Google Sheets is a trademark of Google LLC.)
Figure 9.81
Sheets templates are available on the Welcome screen. (Google Sheets is a trademark of Google LLC.)
Figure 9.82
Sheet templates are also available online from other users. (Google Sheets is a trademark of Google LLC.)