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

9.2 Text and Numbers in Microsoft Excel

Workplace Software and Skills9.2 Text and Numbers in Microsoft Excel

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:

  • Explain the basics of data and data analysis
  • Enter and format numeric data in a worksheet cell
  • Use the Text and General formats for cell data
  • Use the commands in the Page Layout and Review tabs

WorldCorp receives many orders every day, and they rely on databases for recording all data. Large corporations like WorldCorp also keep an inventory of direct materials for manufacturing. These materials, such as bolts and screws, come from many suppliers on multiple orders. They need to store all this inventory information in the accounting system. Finished products—the products that the company packages for shipping and sells—are built with various components and parts, and they record a list of all this information for every item that is manufactured. When a client places an order from a sales agent, these transactions are recorded in a customer relationship management (CRM) database. Although many large corporations use Oracle or SAP databases, many other software companies furnish solutions to big, medium, and small organizations. Medium-sized companies may use Microsoft Access and/or Microsoft Dynamics for their database purposes and for the financial accounting. Organizations of all sizes can utilize Microsoft Excel.

Here, you will learn some of the basic sales order database capabilities in Excel. For WorldCorp, Excel is an appropriate tool to gather, organize, and analyze information within the smaller business units such as by region or product line. The company-wide information might be stored in another type of program suited for large corporations, but at the business-unit level, Excel can be a powerful tool to feed into the corporate information database.

Data Fundamentals

For the purpose of spreadsheet programs, data can be textual (such as a product description) or numerical (such as a price per unit), and it represents what an organization has determined is valuable. If you decide to compare the prices of oranges at different grocery stores, then you are researching and creating data. You can write your data on paper, but a more efficient and common method today for capturing and storing data is with computers. Data is collected primarily to use later in data analysis, a scientific discipline that uses mathematical and statistical tools to measure trends. For example, a company could track the sales trends of three locations over the span of a few months. Watching the data could reveal slumps in sales, which could trigger the company to introduce a corrective action.

There are different kinds of data that businesses like to collect from their clients. Demographic data can segment a growing list of clients so that a company can give them better service based on their likes or dislikes. Financial and accounting data can keep track of a business’s sales, unit costs, overhead costs, and so on. In addition to client data, machines are constantly generating data related to their performance. All manufacturing plants have machines that output data to computers for engineers and managers to analyze.

Excel can manage many types of datasets but works better when the dataset is relatively small. Access databases are appropriate for large sets of data or data that will continue to grow. There are other industrial-sized data storage and analysis solutions like SAP and Oracle, as well as others that might work better for larger data files. Programs such as Sage and QuickBooks that are specific to accounting and finance provide additional features for financial analysis that are not offered in Excel.

Entering and Formatting Numeric Data in a Worksheet Cell

The information made of numerals that Excel reads like a calculator to perform mathematical equations is called numeric data. There are many purposes of numerical values in spreadsheets. The numbers can represent quantities, currency, dates, time stamps, percentages, and more. Table 9.1 summarizes the numerical values Excel uses.

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.
Table 9.1 Numerical Values in Excel

Mac Tip

Percentages are shown with two decimal points by default.

You can choose the type of numerical value appropriate for your data on the Home tab. Figure 9.10 shows the drop-down menu in the Number command group where you can choose the type of numerical data you have. You can also access more details about the number type by selecting the arrow in the lower-right corner of the Number command group. This will open up the Format Cells dialog box that allows you to specify many more types of number formatting, including the number of decimal places. If you’re starting from a blank worksheet, you can plan in your mind what kind of values your tables will contain, so that you can design them to be informative and display logical information. You can even do this prewrite of the table on a piece of paper, or as you go through the process of designing the document in Excel.

A Format Cells pane opens to the Number tab selected. Options for Category (Number is selected), Sample, Decimal places, Negative numbers (-1234.10 is selected) are visible with drops-downs for selection.
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)

The Format Cells dialog box allows you to choose the category and formatting for your cell, based on the data. For example, you can determine the number of decimal places you want to show with your Accounting data. You can also use the Custom formatting category. There are numerous complicated formatting options in the Custom category, such as combining a date with a time, but you can also design your own by modifying an existing one to meet your needs.

Say a sales report table contains a set of orders and the name of the salesperson who fulfilled them. This worksheet contains a variety of types of data: dates, text, general, number, accounting, and more. In order to determine the type of data, click on the cell, and check the drop-down menu in the Number command group. Figure 9.11 shows that cell A2 is a Date. You can convert any number to currency by selecting a cell and then clicking on the “$” button on the Home tab.

Cell A2 is selected and includes 3/22/2021. A zoomed-in view of the Ribbon shows that “Date” is selected in the Number tab.
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.12a shows a summary of the sales figures of all agents. You will notice that the first row is shaded blue and the text is bolded. This is an effective way to differentiate the headings for each column, often referred to as the column header, title row, or header row. More about formatting cells will be covered in Formatting and Templates in Excel. For now, to make the header row stand out, you can highlight (select) the row and use the commands in the Font command group on the Home tab to change the formatting, such as bolding, shading (fill), or text color.

Figure 9.12a includes a column for each agent’s percentage of the FOB $ total, but by default, Excel formats cell contents as General, so it displays the calculation as a decimal number. You can format it as a Percentage by selecting the cell and changing the number format in the drop-down menu or by clicking on the button in the Number command group. We can use the same process to display the next cell as a fraction. Figure 9.12b shows the finished table.

(a) Percentage and Fraction columns in a spreadsheet display with decimals (0.2197). (b) Percentage column in a spreadsheet display with % sign (21.97%) and Fraction column displays fractions (2/9).
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)

Using the Text and General Cell Formats

When your worksheet uses text data, which is information made up of words, letters, numerals, or a combination of those things, you can use the General or the Text cell format. For example, say you have salespersons’ names in Text format, and item descriptions in the General format. In a blank worksheet, you can enter your data using the default General format, and Excel will automatically format it according to the type of data you’re entering. For example, if you type a date in a cell formatted as General, Excel will identify it as a date and reformat it accordingly. If Excel detects a number or text, it will remain in the General format. General formatting is flexible and can accommodate numbers or text. Because Excel may automatically change the format of your cell contents if you leave the format as General, consider formatting all numbers as Number or Accounting, as appropriate.

If you format your words or letters as Text, then Excel will not try to autoformat them. Figure 9.13 compares the same data formatted as Text and formatted as Number. The Text format is flexible in that it can store numbers, letters, symbols, and words.

Spreadsheet displays Text and Number columns filled with various numbers in both. Last cell in Text column displays =4*5 and last cell in Number column displays 20.
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)

You are not restricted to the autoformatting that occurs when you type your data into a cell with General formatting. If you enter a number using the General format (Figure 9.14a), but you want the number to display as currency, for example, simply select the cell or column and then choose Currency from the Number command group’s drop-down menu (Figure 9.14b). Excel will automatically add the currency symbol and two decimal places. All cells in a new workbook are by default set to General format, but you can design your tables with the correct format for your purpose.

(a) Price per Unit column displays plain numbers/decimals. General is selected in Number command group. (b) Price per Unit column displays monetary numbers (i.e., $48.00). Currency is selected in Number command group.
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)

Page Layout and Review Tabs

The Page Layout tab (Figure 9.15) lets the user control the page setup for printing, such as the paper size, the vertical or horizontal orientation, and the print margins. It also contains the settings for arranging graphical objects on a worksheet. One of the most useful commands here is the Breaks drop-down menu, where you can insert page breaks, just like in Microsoft Word, so that when you print your worksheet, the page breaks occur where you want them to.

Excel Page Layout tab command groups: Themes (Themes, Colors, Fonts, Effects), Page Setup (Margins, Orientation, Size, Print Area, Breaks, Background, Print Titles), Scale to Fit (Width, Height, Scale), Sheet Options (Gridlines, Headline).
Figure 9.15 The Page Layout tab includes commands for page setup and manipulation of objects. (Used with permission from Microsoft)

The Review tab contains the commands for adding and reviewing comments and activating the track-changes tool, as shown in Figure 9.16. It also has password protection options, where you can restrict others’ editing capabilities when you are collaborating on the same project. The spell checker and thesaurus tools are also included in the Review tab. Your Review tab might look somewhat different based on the version of Excel that you are using. If these commands are not included on your ribbon, you can add those using the process outlined in Essentials of Business Software Applications for Business.

Excel Review tab command groups: Proofing (Spelling, Thesaurus, Workbook Statistics), Accessibility (Check Accessibility), Insights (Smart Lookup), Language (Translate), Changes (Show Changes), Comments (New Comment, Delete, Previous Comment, Next Comment, Show Comments).
Figure 9.16 The Review tab lets you manage comments, sharing settings, and spelling tools. (Used with permission from Microsoft)
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.