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

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)

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
  • 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
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.