Chapter 5

Getting Started with Excel Chapter-5: Formatting Worksheet in MS Excel Structure 1.0 Learning Objectives 1.1 Introduction 1.2 Formatting cell values 1.3 Formatting numbers 1.4 Formatting Dates & times 1.5 Summary 1.6 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain formatting in MS Excel  Explain different types of formatting in MS Excel 1.1 Introduction Creating a basic worksheet is only the first step toward mastering Excel. If you plan to print your worksheet, email it to colleagues, or show it off to friends, you need to think about whether your worksheet is formatted in a viewer-friendly way. A careful use of color, shading, borders, and fonts can make the difference between a messy glob of data and a worksheet that’s easy to work with and understand. But formatting isn’t just about deciding, say, where and how to make your text bold. Excel also lets you control the way numerical values are formatted. In fact, there are really two fundamental aspects of formatting in any worksheet:  Cell appearance: Cell appearance includes cosmetic details like color, typeface, alignment, and borders. When most people think of formatting, they think of cell appearance first.  Cell values: Cell value formatting controls the way Excel displays numbers, dates, and times. For numbers, this includes details like Page 33 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel whether to use scientific notation, the number of decimal places displayed, and the use of currency symbols, percent signs, and commas. With dates, cell value formatting determines what parts of the date are shown in the cell, and in what order. Cell value formatting is in many ways more significant than cell appearance, because it can change the meaning of your data. 1.2 Formatting cell values Cell value formatting is one aspect of worksheet design you don’t want to ignore, because the values Excel stores can differ from the numbers that it displays in the worksheet. In many cases, it makes sense to have the numbers that appear in your worksheet differ from Excel’s underlying values, since a worksheet that’s displaying numbers to, say, 13 decimal places, can look pretty cluttered. To format a cell’s value, follow these steps: 1. Select the cells you want to format. You can apply formatting to individual cells, or to a collection of cells. Usually, you’ll want to format an entire column at once, because all the values in a column typically contain the same type of data. Remember, to select a column, you simply need to click the column header (the gray box at the top with the column letter). 2. Select Format → Cells, or just right-click the selection, and choose Format Cells. 3. Set the format options, and then click OK to apply them. Page 34 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel 1.3 Formatting Numbers The Number tab in the Format Cells dialog box lets you control how Excel displays numeric data in a cell. Excel gives you a lengthy list of predefined formats (as shown in Figure 4-3), and it also lets you design your own formats. Remember, Excel uses number formats when the cell contains numeric information only. Otherwise, Excel simply ignores the number format (although the format is still there and will still be used if you change the cell content to a number, date, or time). When you create a new spreadsheet, every cell starts out with the same number format: General. This format comes with a couple of basic rules:  If a number has any decimal places, Excel displays them, provided they fit in the column. If there are more decimal places than Excel can display, it leaves out the ones that don’t fit. (It rounds up the last displayed digit, when appropriate). If you change a column width, Excel automatically adjusts the amount of digits it displays.  Excel removes leading and trailing zeros. Thus, 004.00 becomes 4. The only exception to this rule occurs with numbers between -1 and 1, which retain the 0 before the decimal point. For example, Excel displays the number .42 as 0.42. Page 35 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel 1.4 Formatting Dates & Times To format dates and times, in the Format Cells dialog box (Format → Cells), choose Date or Time from the column on the left and then choose the format from the list on the right. Date and Time both provide a slew of options. You can use everything from compact styles like 3/13/05 to longer formats that include the day of the week, like Sunday, March 13, 2005. Time formats give you a similar range of options, including the ability to use a 12-hour or 24-hour clock, show seconds, show fractional seconds, and include the date information. There are essentially two types of date and time formats:  Formats that take the regional settings of the computer you’re using into account. With these formats, dates display differently depending on the computer that’s running Excel. This is a good choice because it lets everyone see dates in just the way they want to, which means no time-consuming arguments about month-day-year or day-month- year ordering.  Formats that ignore the regional settings of individual computers. These formats define a fixed pattern for month, day, year, and time components, and display date-related information in exactly the same way on all computers. If you need to absolutely make sure a date is in a certain format, you should use this choice. Page 36 of 59 All Rights Reserved. Vol. TLE001/03-2022

Getting Started with Excel 1.5 Summary  Cell appearance includes cosmetic details like color, typeface, alignment, and borders.  Cell value formatting controls the way Excel displays numbers, dates, and times.  The Number tab in the Format Cells dialog box lets you control how Excel displays numeric data in a cell.  To format dates and times, in the Format Cells dialog box, choose Date or Time from the column on the left and then choose the format from the list on the right. 1.6 References https://www.oreilly.com/library/view/excel-2003- the/0596006640/ch04.html https://www.statsdirect.com/help/workbook/formatting_worksheets.ht m https://support.microsoft.com/en-us/office/quick-start-format-a- worksheet-d70f75a2-23e6-4c92-83d6-2f219e4ad42e Page 37 of 59 All Rights Reserved. Vol. TLE001/03-2022