Tools and Features of Excel

Working With the "Format Cells" Dialog Box

Excel 3-Do the Work that is in RED

Although the Formatting toolbar provides you ways to format many aspects of the spreadsheet, you need to be aware of the Format dialog cells. which gives you many more options. To activate this feature, highlight one or more cells and either (1) right-click on the highlighted area and pick Format Cells from the shortcut menu, or (2) click on Format on the menu bar and select Cells from its drop-down list.

The resulting dialog box shows six tabs that control different aspects: Number, Alignment, Font, Border, Patterns, and Protection.

We will look at four of these tabs here and the border tab in another section. The protection tab will not be covered in these lessons.

NUMBER TAB

The Number tab display shows categories on the left of the window and format code options to the right. Select the category on the left and then the specific type of coding to the right. Then click on the [OK] button to close the dialog box and apply the coding. You need to experiment with these codes to fully understand them.

Exercise 1

Open up the file PRACTICE.XLS from Lesson 2 and use File, Save As and save the file using the name of FORMAT.XLS. Change the number of pencils sold by various people so you have four digit numbers in places. Highlight all the numbers from C4 through H13. Right-click on the highlighted area and pick Format Cells from the shortcut menu. Click on the category Number and select the third choice from the top, which is #,##0

(The dialog box offers some what different choices if you are using Excel 7.0. You'll need to change the "Decimal Place" to "0" and put a checkmark in the box "Use 1000 Separator.")

Click on [OK] to close the dialog box and look at the resulting numbers. The ones that are long enough have commas displayed but there is no decimal places showing. The 0 at the end of the format tells Excel to round to the nearest whole number.

Highlight cell H17, which is a date. Again, right-click and pick Format Cells from the shortcut menu. Click on the category Date and select the second option, which is d-mmm-yy

Close the dialog box and you will see the resulting date format of 8-Oct-96. You may need to widen the column to see the date.

Take a look at some of the other format options, like under Accounting, Time, Percentage, and Currency. Note in the Currency category, some of the options have negative numbers displaying in red.

You can create your own formats through this dialog box, which you will actually do in one of the exercises at the end of this lesson.

ALIGNMENT TAB

 

This set of options duplicates some of the buttons on the Formatting toolbar, but there are also new options here. Wrap Text takes text that is too wide for a column and wraps it to next lines within the same cell. This, of course, makes the row taller. Fill repeats the contents of the cell as many times as possible. There are also options to change orientation and make information print "vertically"; this is another way to deal with wide pieces of information without making the column wider. Note: if you have applied a trait like center across columns, you would need to bring up the "Alignment" display and change the alignment back to left to turn off this feature.

 

Exercise 2

Still working with the FORMAT.XLS file, make the active cell A1, which is where the title is located (even though it appears in the middle of the spreadsheet because in Exercise 2 you centered it across the columns). Right-click on A1 and select Format Cells from the shortcut menu. Select the "Alignment" tab. Change the alignment to Left and close the window.

 

Insert a blank row at row 2. Then in cell A2 type one * asterisk). Open the "Format Cells" dialog box and pick the Alignment tab. Make this cell Fill and close the box and look at the results. Widen column A so the asterisks go under all of the text.

Highlight the pencil color column headings which are now C4 through H4 and open the "Format Cells" dialog box, Alignment tab display. Select one of the orientation options so the text is "stacked vertically." Be sure that the vertical position is Bottom. Close the dialog box and look at the results. Reduce the column widths so you are taking up less space.

Save this new version of FORMAT.XLS.

FONT TAB

The Font Tab Sheet  gives options for changing the font style and font name, as well as applying colors and picking different types of underlining. Color changes and font changes, remember, can be done through Formatting toolbar buttons. The sizes for fonts in the drop-down list are the same as on the toolbar; however, you can highlight the font size in its window and type in another number not in the listing, for example 15, 25, or 40.

 

A comment about color seems appropriate here. If you have a color printer, you need to experiment with applying color shading and color text and see how the various colors print. You will often have much better color choices on your screen than on your printer, depending on how much you paid for the color printer. If you have a black and white (regular) printer, colors are translated into shades of gray. Again, you need to determine which colors you can use and still have "readable" printed pages. Lastly, even if you have a regular printer, you may want to add color to your spreadsheet to help draw attention to parts of the screen. This would be particularly useful if you are entering numbers and getting results from the screen display, rather than always working with a printed copy.

 

Exercise 3

Put your cursor on cell A1 in FORMAT.XLS and right-click; then select Format Cells and pick the Font tab. Change the font size to 15. Pick a different color for the text, close the dialog box, and look at the results.

Highlight the summary data from A11 through H14 and apply a color to this text, using either the "Format Cells" dialog box or the Font Color button on the Formatting toolbar.

You do not have to save this file.

 

PATTERNS TAB

One of the buttons on the Formatting toolbar is "Color" which makes the selected cell background the color chosen. However, the under the "Format Cells" dialog box allows you to pick colors AND patterns. Besides changing (or not changing) the colors, you can pick patterns to help highlight information.

 

Exercise 4

Open up the clean version of PRACTICE.XLS from Lesson 2 and save it as FORMAT2.XLS. Experiment with different colors and patterns, for example medium blue but using a light dot pattern. These are found under the "Format Cells" dialog box, Patterns tab. Apply this color and pattern to the summary section in the lower part of the spreadsheet; make the title cell A1 a red color and try printing the file. Practice changing a colored area back to No color and turning off the pattern. Save this file and close it.

Tools and Features of Excel

Adding Lines Selectively

In the printing exercises so far, you have probably noticed that gridlines print as the default. Later in this lesson, we will talk about how to turn off the gridlines; if you do this, then you may want to add selective vertical and horizontal lines in your layout, again to help draw attention to parts of the spreadsheet.

If you want to turn off gridlines appearing on the screen, so borders are more distinct, click on Tools on the menu bar, then Options; select the View tab and clear the Gridlines check box. This change is saved with the current spreadsheet but will not affect other spreadsheets.

There are three ways to add selective lines:

1. through the font tab options
2. by using the "Borders" button on the toolbar
3. by using the Border tab options under Format ----> Cells."

USING THE FONT TAB

We have already mentioned already the font tab options under the "Format Cells" dialog box. Underline is one of the settings in this window, with choices of single, double, single accounting, and double accounting. The "accounting" choices add space between the line and its text, while the single and double choices have the lines touching the text. Selecting this trait for one or more cells places underlining across the entire width of the cell. If you use the "Underline" button on the Formatting toolbar, only the words are underlined, not the entire cell.

 

USING THE BORDERS BUTTON

Next to the decimal precision buttons on the Formatting toolbar is the "Borders" button. Highlight the cell or cells to be bordered and click on the down arrow of the "Borders" button.

A grid of 12 border choices appears. Examples of choices here are no border (to clear borders), single and double underline across the bottom, thick line across the bottom and thin line across the top, and a thin line outside the highlighted cells. If you have several borders to apply, click on the down arrow and then keep holding down the left mouse button while you drag the window onto the spreadsheet area. This gives you a border palette to use until you close the window.

 

Exercise 5

Bring up a copy of the PRACTICE.XLS file from Lesson 2 and highlight cells A3 through H3 (the column headings area). Click on the "Borders" button on the Formatting toolbar and drag the palette onto your spreadsheet area. Apply the single top and single bottom border (on the second row of options) to this selection. Highlight the summary data from A10 through H13 and apply the thick outside border  Click on Tools on the menu bar, then Options, and select the View tab. Uncheck the Gridlines box and close the window. LOOK. Highlight the entire spreadsheet area and turn off the borders, by using the "Borders" button, no border option.

USING THE BORDER TAB UNDER FORMAT CELLS

The greatest number of choices for bordering are found under the Border tab in the "Format Cells" dialog box. This dialog box takes some experimenting with to understand.

 

The Outline border refers to the outline around the highlighted area. The Left border is the left side of all the cells highlighted, the Right border is the right side of all highlighted cells. For each of the places where lines can occur, you can select the style of line, including dotted lines, single, thick, and double lines. You can also assign a color to the line. This is the only line option that gives you a way to place vertical lines in all or parts of your spreadsheet.

 

Exercise 6

Still using the PRACTICE.XLS from Exercise 5 (in which the lines have been turned off), select cells A3 through H13 and bring up the "Format Cells" dialog box. Select the Border tab. Click in the Outline border window and select a double border style. Click on the Left border window and select a single line style. Close the dialog box and look at your resluts.

Highlight the total row (A10 through H10) and bring up the Border tab window again. Select a color of red, and then make the top border a thick red line. Close the dialog box and look at the results. You do not need to save these changes.

Tools and Features of Excel

Using Autoformat

You have been examining ways to change formatting via the toolbar and "Format Cells" dialog box. AutoFormat is yet another way to apply formatting. It is found under the Format menu item.

This dialog box has table formats listed in the left of the dialog box with a sample of the formatting in the middle display. You can apply formatting this way by either highlighting the cells to be affected, or by positioning the active cell somewhere inside a block of cells. These formatting options can affect number formats, lines, shading, justification, column widths, and colors for text. The [OPTIONS >>] button in the "AutoFormat" dialog box results in formatting choices appearing in the bottom of the dialog box. You can turn off one or more particular formatting features, for example Patterns and Number, to not change your current settings. If you do not like the results from AutoFormat, simply click on the

"Undo" button on the Standard toolbar.

You may find this feature quite useful or you may not like it. You will see that a lot of the sample formats are oriented to a total column at the right and on the bottom row. If a particular effect, like a shaded row for a total row, is in the AutoFormat, Excel applies that feature to any summary rows at the bottom of your layout.

Exercise 7

Open up a clean copy of the PRACTICE.XLS file from Lesson 2. Save this file as FORMAT3.XLS and make the active cell a cell in the middle of the data area, like D7. Click on Format on the menu bar, then AutoFormat. Scroll through the format options on the left and look at the samples. Go back and select the format type "Classic 3." This has shading on the column row, and shading in the data area, with the total row white and a line above it. Click on "OK" to apply this formatting. What happen to the bottom rows in the spreadsheet? Click on the "Undo" button to remove the formatting.

 

Select the main data area (cells A3 through H13) and apply center justification. Now bring up the "AutoFormat" dialog box and apply the option of "Accounting 3."

 

 What happened to the justification?

Again, click on "Undo" to remove the formatting. Bring up the "AutoFormat" dialog box and select "Accounting 3" again, but this time click on the [OPTIONS >>] button in the right of the dialog box. Uncheck the Number box and the Alignment box (to remove number formatting and the alignment used in this style). Click on "OK" to look at spread sheet

You do not need to save this file.

  Turn in assignment