|
Formatting CellsBelow are several Frequently Asked Questions and answers on the subject of Formatting Cells in Excel. |
A temporary person at our company somehow did many hours of numeric data entry into
Excel worksheets after giving the worksheets a text format. Now Excel thinks all of these
numbers are text and it won't recognize them for use in any formulas. I have tried
reformatting the worksheets as numeric, but it doesn't make any difference. What can I do?
Keywords: Text, Formatting
Posted December 17, 1996
Here's a little-known trick in Excel that will make quick work of this problem. Just follow these directions:
This will force Excel into recognizing these entries as real numbers again.
I need to add the current date using the mmmm-dd-yyyy format to the top of all my
reports. The problem is that Excel gives me the month name in proper case (i.e.
December-20-1996) when I need it in all upper case (i.e. DECEMBER-20-1996). How can I get
my dates to be shown in upper case?
Keywords: Functions Dates
Posted November 19, 1996
You can’t accomplish this with a standard date format alone, but you can do this with a combination of worksheet functions as follows:
=UPPER(TEXT(NOW(),"mmmm-dd-yyyy"))
The NOW function returns today’s date, the TEXT function converts that date into text with the proper format and the UPPER function converts all the letters into upper case.
What is a quick and easy way to format cells?
Keywords: Format Cells Worksheet
Posted January 30, 1996
Normally your cell formatting is done through the Menu bar using the Format - Cells Option. The most common and quickest way to format features are through the Formatting toolbar. These tool buttons have drop down boxes that display your formatting choices. Click on the drop down arrow on any of the "Drop-Down Drag-Off" tools. These are tools on the toolbar which are slightly wider than a normal tool and have a small down-pointing arrow on the right side. Tools for font color, fill formatting and cell borders as well as others are available. See the Customize Toolbar dialog for all of them.
For more convenient access, the drop down boxes can be moved closer to your work area. When you do this, you are creating a copy of the tool which can be closed without affecting your toolbar. Click on the arrow for the drop down box.
For your convenience, you can now move the floating formatting box closer to your work area. Just cancel the box to remove it from your worksheet.
Hint: If you do not drag a copy of the tool into your worksheet, but drop the arrow and make a choice (depending on the tool), that choice becomes the default of the button. This means that to make the same choice again you don't have to drop the arrow again, just click on the left side of the displayed part of the tool. In this way Excel is anticipating your next move.
Can I customize the borders of the cells in my worksheet?
Keywords: Customize Borders Cells
Posted January 19, 1996
To customize the cell border, from the menu bar choose Format - Cells - Borders. This tab is divided into three sections: Border, Style, Color.
Hint: To see your borders better, turn off the worksheet gridlines. From the menu bar, choose Tools - Options - View and deactivate the Gridlines check box.
The Border section contains five options: Outline, Left, Right, Top, Bottom. Select the option you desire and then consider the Style option. It contains eight styles from which to choose.
Hint: To put an outline border around a selected cell, press Ctrl+Shift+& (ampersand). To remove all borders from the selected cell, press Ctrl+Shift+_ (underscore).
Shortcut: For some of the options on the Border menu is to use the Borders tool from the Formatting toolbar. This can be used two ways: As a drop-down list, click on the arrow to display a box of formatting options; As a floating toolbar, if you are using Excel 5, position the mouse pointer anywhere inside the drop-down box; if you are using Excel 7, the pointer must be on the edge of the box. Hold down the left mouse button, and drag the pointer outside the box. When you release the mouse button you will see the box as a floating toolbar.
Can I customize the number formats in the cells of my worksheet?
Keywords: Customize Number Format Cells
Posted January 18, 1996
When you enter numbers in a worksheet, Excel automatically removes any leading or trailing zeros. The only exception is when you enter a number that is wider than the cell. Excel then rounds off the number or uses scientific notation. The number is changed for display purposes only as Excel retains the original number internally. This is the general number format and is the default setting. You can change this default setting. From the menu bar choose Format - Cells.
Shortcut: To open the Format Cells dialog box quickly either press Crtl+1 or right-click on a cell and select the Format Cells command. Choose the Number tab. You will see other built-in numeric functions from which to choose. These formats use special symbols to create format codes. The box is divided into four groups: Category contains the various categories of the numeric formats. Format Codes displays the formats associated with each category. Code displays the currently selected format. Sample shows you what your entry will look like with the currently selected format.
Shortcut Keys for numeric functions:
You can also use shortcuts from the Standard toolbar:
Can I customize the time formats in Excel?
Keywords: Customize Time Format
Posted January 18, 1996
Excel has several built-in date and time formats acceptable for most applications. However, there may be a need to create your own custom format. Excel 5 and 7 handle this slightly differently.
To do this in Excel 5:
To do this in Excel 7:
Your new format will translate to the specific cell. Following is a list of date and time formatting symbols.
How can I customize the date formats in my worksheet?
Keywords: Customize Date Format
Posted January 18, 1996
Excel has several built-in date and time formats acceptable for most applications. However, there may be a need to create your own custom format. Excel 5 and 7 handle this differently. Once you have created your custom format, it can be applied to any range of cells.
In Excel 5:
In Excel 7:
Following is a list of date and time formatting symbols.
What is "Wrap Text" and how do I use it?
Keywords: Wrap Text
Posted January 18, 1996
Excel has the ability to make text wrap onto several lines, expanding the height of a
row to accomidate all the text so it is visible. When you have anything in the cell to the
right of your text, sometimes it will not show everything if the column is not wide
enough. You can use Text Wrap to see the entire contents of your cells.
To customize the alignment, and turn Text Wrap on:
You can left-align, center, right-align, or justify wrapped entries. Simply type in your complete text and it will all appear in one cell as the cell will automatically adjust its height.
Hint: You can enter carriage returns and tabs in your wrapped cells. To enter a carriage return, position the cursor in the cell and press Alt+Enter. To enter a tab, press Ctrl+Alt+Tab.
How can I standardize a font?
Keywords: Format Font Standardize
Posted January 18, 1996
To change the default font for workbooks created in future sessions, from the Menu, choose Tools - Options. Then change the Standard Font and Size options on the General tab. Select the Font you want in the Standard Font box. This will change the standard font for the startup workbook or any new workbook once you restart Microsoft Excel.
How do I apply font colors to a cell's contents?
Keywords: Font Color Cell
Posted January 18, 1996
When creating a worksheet, adding color will enhance it by allowing you to emphasize certain results or add subtle effects. Excel offers 56 different colors from which to choose. If you are using the standard VGA driver or another 16-color driver, the first two rows of colors in the pallet of 56 are true colors. The rest are dithered, which means they are true colors with alternate colored dots added to "create" another color.
Shortcut: You can also use the Font Color drop-down box from the Formatting toolbar to change a color in the cell.
Is there a wide variety of fonts from which to choose in Excel?
Keywords: Font Choices Variety
Posted January 18, 1996
Excel allows you to use a wide variety of font styles, sizes, options, and effects when preparing a worksheet. To utilize these best, from the menu bar, select Format - Cells and then the Font tab. Choose the appropriate options.
Shortcut: You can use the Formatting Toolbar for several of the above options. Use the Font drop-down box to select a type-face. Use the Font Size drop-down box to select a font size. Click on the Bold tool to apply the Bold font style. Click on the Italic tool to apply the italic font style. Click on the Underline tool to apply the underline font style.
Shortcut Keys:
How can I change the alignment of the text in the cells on my worksheet?
Keywords: Alignment Text Cells Worksheet Edit
Posted January 18, 1996
When you place data in an unformatted cell, Excel aligns text entries with the left edge of the cell, numbers and dates with the right edge of the cell, and error and logical values in the center of the cell. This is the default General alignment scheme.
To customize the alignment, choose Format - Cells, and then the Alignment tab from the menu bar. Two of the sections are Horizontal Alignment and Vertical Alignment.
How can I change the orientation of the text in the cells on my worksheet?
Keywords: Orientation Cells Text Worksheet Edit
Posted January 18, 1996
When you place data in an unformatted cell, Excel aligns text entries with the left edge of the cell, numbers and dates with the right edge of the cell, and error and logical values in the center of the cell. This is the default General alignment scheme.
To customize the alignment, choose Format - Cells from the Menu bar, and then click on the Alignment tab.
The Orientation section of the Alignment tab enables you to orient your cell entries in four ways: left-to-right (normal); vertically; sideways with characters running from bottom to top; sideways with characters running from top to bottom.
If you choose either the vertical or sideways orientation with a long text entry, you have to adjust the height of the cell to see all the text.
Hint: If the Orientation section is something used frequently, you can customize your toolbar to include the buttons for vertical alignment and sideways alignments. See our question on Customizing Toolbars.
How can I apply color to the cells and borders on my worksheet?
Keywords: Color Border Worksheet Gridlines
Posted January 17, 1996
Cell borders and patterns allow your worksheet to appear more organized and professional. Adding color enhances the worksheet even more.
Select the cell or range of cells. From the menu bar, choose Format - Cells.
Shortcut: You can use the Color drop down box in the formatting toolbar to apply a background color to a cell pattern.
How do I quickly copy cell formatting?
Keywords: Copy Format Painter Cell
Posted January 16, 1996
The Format Painter on the standard toolbar is the fastest method:
Select the cell(s) containing the formatting you wish to copy and then click on the Format Painter. The next cell you select (with the mouse) will be "painted" with all the formatting attributes contained.
Back to the FAQ Table of Contents
 24 hours in a day...24 cans in a case...coincidence? |
Copyright© 1996-1999, Baarns Consulting Group, Inc. - All rights reserved. |
