9.2 Text and Numbers in Microsoft Excel

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.

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.

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.

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.

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.

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.

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.

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.

Figure 9.16 The Review tab lets you manage comments, sharing settings, and spelling tools. (Used with permission from Microsoft)