Lesson 1

Preview

Formatting Data

Formatting data is the process of changing the appearance of values contained in cells. There are a wide variety of formatting options.

Usually, the goal of formatting a value a certain way is to either make the data more aesthetically pleasing or sometimes to make the data more understandable. Values that you enter into cells are unformatted. They are simply a string of numerals. Your goal, of course is to format the data so it’s easier to read or more consistent in terms of the decimal places shown. An important consideration to remember is that even though you apply formatting to numbers or text, Excel still considers the data as unformatted when it is called upon to process the data. This last statement will make more sense as we venture into more of the course. 

Practice Exercise 1: Formatting Numbers

STEP 1A: Add an additional sheet to your workbook (FIGURE 1). At the bottom of your cell area are your Sheets created thus far. Press the + symbol to create a new sheet. You will now see 2 sheets, Sheet1 and Sheet2.

Figure 1

STEP 1B: (FIGURE 2) To Rename Sheet1, click on Sheet1, right-button mouse click it, select Rename.

Figure 2

​STEP 1C: ​(FIGURE 3) ​Rename Sheet1, “Formulas & AutoFill Table”.

Figure 3

STEP 2: Make cell A1 the active cell. Click on cell A3 with mouse or use keys to navigate to it. 
Enter into the cell: 12.2%
Notice that Excel automatically formatted this cell as a percentage.
View the Ribbon, Number Section, window contains “Percentage” (FIGURE 4).

Figure 4

STEP 3: Make cell A2 the active cell.
Enter into the cell: 1000
View the Ribbon, Number Section, window contains “General” (FIGURE 5).

Figure 5

STEP 4: Make cell A3 the active cell.
Enter into the cell: 1000
Excel automatically formatted this cell as a number.
View the Ribbon, Number Section, window contains “Number” (FIGURE 6).

Figure 6

STEP 5: Make cell A4 the active cell.
Enter into the cell: $1000
​Notice that Excel automatically formatted this cell as currency.
View the Ribbon, Number Section, window contains “Currency” (FIGURE 7).

Figure 7

STEP 6: (FIGURE 8) Widen column A to a size of 15

Figure 8

STEP 7A: Make cell A5 the active cell. Enter: $1000

STEP 7B: Go to formatting window and change format to “Accounting” (FIGURE 9).

Notice how the dollar sign has moved over to the far-left of the column (FIGURE 10). 

Accounting format ensures that the dollar sign and the decimal points will line-up with numbers similarly formatted. 

Figure 9
Figure 10

STEP 8: (FIGURE 11) Make cell A5 the active cell. Add Bold and Red Font formatting to cell.

Figure 11

STEP 9: ​(FIGURE 12) On the Ribbon Editing section, select Clear. Select the arrow; on drop down menu, select “Clear Formats”.

Figure 12

STEP 10: ​(FIGURE 13) Observe cell A5.

Besides removing the Bold and Red Font, what else was removed? What does the Number formatting window reflect? (FIGURE 14)

​We clearly see that when we apply the “Clear” Formats, besides eliminating all signs of an “Accounting” format, that action also changed the number format from Accounting to General, which is the default number format for Excel.

Figure 13
Figure 14

STEP 11: (FIGURE 15) Rename Sheet2, “Number Formatting”. To Rename it, click on Sheet2, right-button mouse click it, select Rename. 

Figure 15

END OF LESSON

Back to: Intermediate Excel 2016 > Module 1 – Intermediate Excel 2016

Leave a Reply