In Excel For Mac Font Changes To Symbol Without Wanting To

In this tutorial, you will learn full details about Excel AutoFit and the most efficient ways to use it in your worksheets. Microsoft Excel provides a handful of different ways to change column width and adjust row height.The easiest way to resize cells is to have Excel automatically determine how much to widen or narrow the column and to expand or collapse the row to match the data size. When I set format of a cell as%, it automatically multiply the value with 100 & show the value. So 5.66 becomes 566%. I want to apply% format on a column but want to keep the values same as before. I just want% sign to be shown along with the value. To achieve this, I used following format 0.00##% Now I want to show negavtive values in RED.

Note

Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.

Summary

Microsoft Excel lets you change many of the ways it displays data in a cell. For example, you can specify the number of digits to the right of a decimal point, or you can add a pattern and border to the cell. You can access and modify the majority of these settings in the Format Cells dialog box (on the Format menu, click Cells).

The 'More Information' section of this article provides information about each of the settings available in the Format Cells dialog box and how each of these settings can affect the way your data is presented.

More Information

There are six tabs in the Format Cells dialog box: Number, Alignment, Font, Border, Patterns, and Protection. The following sections describe the settings available in each tab.

Number Tab

Auto Number Formatting

By default, all worksheet cells are formatted with the General number format. With the General format, anything you type into the cell is usually left as-is. For example, if you type 36526 into a cell and then press ENTER, the cell contents are displayed as 36526. This is because the cell remains in the General number format. However, if you first format the cell as a date (for example, d/d/yyyy) and then type the number 36526, the cell displays 1/1/2000.

There are also other situations where Excel leaves the number format as General, but the cell contents are not displayed exactly as they were typed. For example, if you have a narrow column and you type a long string of digits like 123456789, the cell might instead display something like 1.2E+08. If you check the number format in this situation, it remains as General.

Finally, there are scenarios where Excel may automatically change the number format from General to something else, based on the characters that you typed into the cell. This feature saves you from having to manually make the easily recognized number format changes. The following table outlines a few examples where this can occur:

Generally speaking, Excel applies automatic number formatting whenever you type the following types of data into a cell:

• Currency
• Percentage
• Date
• Time
• Fraction
• Scientific

Built-in Number Formats

Excel has a large array of built-in number formats from which you can choose. To use one of these formats, click any one of the categories below General and then select the option that you want for that format. When you select a format from the list, Excel automatically displays an example of the output in the Sample box on the Number tab. For example, if you type 1.23 in the cell and you select Number in the category list, with three decimal places, the number 1.230 is displayed in the cell.

These built-in number formats actually use a predefined combination of the symbols listed below in the 'Custom Number Formats' section. However, the underlying custom number format is transparent to you.

The following table lists all of the available built-in number formats:

Custom Number Formats

If one of the built-in number formats does not display the data in the format that you require, you can create your own custom number format. You can create these custom number formats by modifying the built-in formats or by combining the formatting symbols into your own combination.

Before you create your own custom number format, you need to be aware of a few simple rules governing the syntax for number formats:

• Each format that you create can have up to three sections for numbers and a fourth section for text.

• The first section is the format for positive numbers, the second for negative numbers, and the third for zero values.

• These sections are separated by semicolons.

• If you have only one section, all numbers (positive, negative, and zero) are formatted with that format.

• You can prevent any of the number types (positive, negative, zero) from being displayed by not typing symbols in the corresponding section. For example, the following number format prevents any negative or zero values from being displayed:

  0.00;;

• To set the color for any section in the custom format, type the name of the color in brackets in the section. For example, the following number format formats positive numbers blue and negative numbers red:

  [BLUE]#,##0;[RED]#,##0

• Instead of the default positive, negative and zero sections in the format, you can specify custom criteria that must be met for each section. The conditional statements that you specify must be contained within brackets. For example, the following number format formats all numbers greater than 100 as green, all numbers less than or equal to -100 as yellow, and all other numbers as cyan:

  [>100][GREEN]#,##0;[<=-100][YELLOW]#,##0;[CYAN]#,##0

• For each part of the format, type symbols that represent how you want the number to look. See the table below for details on all the available symbols.

To create a custom number format, click Custom in the Category list on the Number tab in the Format Cells dialog box. Then, type your custom number format in the Type box.

The following table outlines the different symbols available for use in custom number formats.

Displayed Value versus Stored Value

Microsoft Excel displays a number according to the format of the cell that contains it. Therefore, the number that you see in the cell may differ from the number stored by Excel and from the number used in calculations that refer to the cell. For example, if you type 1.2345 in a cell where you only want two digits to the right of the decimal to be displayed, the cell displays the value 1.23. Note however, if you use that cell in a calculation, the full four digits to the right of the decimal are used.

Alignment Tab

You can position text and numbers, change the orientation and specify text control in cells by using the Alignment tab in the Format Cells dialog box.

Text Alignment

Under Text alignment, you control the horizontal, vertical alignment and indention. The following is a list of available settings for text alignment:

Text Control

There are some additional miscellaneous text alignment controls in the Text Control section of the Alignment tab. These controls are Wrap Text, Shrink to Fit and Merge Cells.

Select Wrap Text to wrap the text in the selected cell. The number of wrapped lines depends on the width of the column and the length of the cell contents.

Note

To start a new line when the Wrap Text option is selected, press ALT+ENTER while typing in the formula bar.

Selecting the Shrink to Fit option decreases the font size of the text in a cell until all the contents of the cell can be displayed. This feature is helpful when you want to avoid changing the column width for the entire column. The applied font size is not changed.

The Merge Cells option combines two or more selected cells into a single cell. A 'merged cell' is a single cell created by combining two or more selected cells. The cell reference for a merged cell is the upper-left cell in the original selected range.

Orientation

You can set the amount of text rotation in the selected cell by using the Orientation section. Use a positive number in the Degree box to rotate the selected text from lower left to upper right in the cell. Use negative degrees to rotate text from upper left to lower right in the selected cell.

To display text vertically from top to bottom, click the vertical Text box under Orientation. This gives a stacked appearance to text, numbers and formulas in the cell.

Font Tab

The term font refers to a typeface (for example, Arial), along with its attributes (point size, font style, underlining, color, and effects). Use the Font tab in the Format Cells dialog box to control these settings. You can see a preview of your settings by reviewing the Preview section of the dialog box.

Note

You can use this same Font tab to format individual characters. To do this, select the characters in the formula bar and click Cells on the Format menu.

Typeface, Font Style, and Size

The Font option on the Font tab allows you to choose a typeface. You choose your typeface for the selected cell by clicking a name in the Font list or typing a name in the Font box. There are three types of typefaces you can use, as described in the following table:

After you select a typeface in the Font list, the Size list displays the available point sizes. Keep in mind that each point is 1/72 of an inch. If you type a number in the Size box that is not in the Size list, you see the following text at the bottom of the Font tab:

'This font's size is not installed on the system. The closest available font will be used.'

Typeface Styles

The list of choices in the Font Style list varies depending on the font that is selected in the Font list. Most fonts include the following styles:

• Regular
• Italic
• Bold
• Bold italic

Underline

In the Underline list, you can select an underlining option to format the selected text. The following table describes each underlining option:

Color, Effects, and Normal Font Settings

Choose a color for the font by clicking a color in the Color list. You can rest the mouse over a color to see a ToolTip with the color name. The Automatic color is always black unless you change the window font color on the Appearance tab of the Display Properties dialog box. (Double-click the Display icon in the Control Panel to open the Display Properties dialog box.)

Select the Normal font check box to set the font, font style, size, and effects to the Normal style. This is essentially resetting the cell formatting to defaults.

Select the Strikethrough check box to draw a line through selected text or numbers. Select the Superscript check box to format the selected text or numbers as superscripts (above). Select the Subscript check box to format the selected text or numbers as subscripts (below). You typically want to use subscripts and superscripts for individual characters in a cell. To do this, select the characters in the formula bar and click Cells on the Format menu.

Border Tab

In Excel, you can put a border around a single cell or a range of cells. You can also have a line drawn from the upper-left corner of the cell to the lower-right corner, or from the lower-left corner of the cell to the upper-right corner.

You can customize these cells' borders from their default settings by changing the line style, line thickness or line color.

The following settings are available on the Border tab of the Format Cells dialog box:

Applying Borders

To add a border to a single cell or a range of cells, follow these steps:

1. Select the cells that you want to format.

2. On the Format menu, click Cells.

3. In the Format Cells dialog box, click the Border tab.

   Note

   Some buttons on the Border tab are unavailable (dimmed) when you only have a single cell selected. This is because these settings are only applicable when you apply borders to a range of cells.

4. Select any one of the line styles in the Style list.

5. Click the Color drop-down arrow and select any one of the colors.

6. Click any one of buttons listed under Presets or Border.

   This displays a line with your settings in the sample region.

7. If you want to remove a specific border, click the button for that border a second time.

8. If you want to change the line color or style, click the style or color that you want, and then click the button for the border again.

Patterns Tab

Use the Patterns tab in the Format Cells dialog box to set the background color of the selected cells. You can also use the Pattern list to apply two-color patterns or shading for the background of the cell.

Note

The color palette on the Patterns tab is the same color palette from the Color tab of the Options dialog box. Click Options on the Tools menu to access the Options dialog box.

To shade cells with patterns, follow these steps:

1. Select the cells to which you want to apply shading.
2. On the Format menu, click Cells, and then click the Patterns tab.
3. To include a background color with the pattern, click a color in the Cell shading box.
4. Click the arrow next to the Pattern box, and then click the pattern style and color that you want.

If you do not select a pattern color, the pattern is black.

You can return the background color formatting for the selected cells to their default state by clicking No Color.

Protection Tab

The Protection tab offers you two options for protecting your worksheet data and formulas:

• Locked
• Hidden

However, neither of these two options takes effect unless you also protect your worksheet. To protect a worksheet, point to Protection on the Tools menu, click Protect Sheet, and then select the Contents check box.

Locked

By default, all cells in a worksheet have the Locked option turned on. When this option is turned on (and the worksheet is protected), you cannot do the following:

• Change the cell data or formulas.
• Type data in an empty cell.
• Move the cell.
• Resize the cell.
• Delete the cell or its contents.

Note

If you want to be able to type data in some cells after protecting the worksheet, make sure to clear the Locked check box for those cells.

Hidden

By default, all cells in a worksheet have the Hidden option turned off. If you turn on this option (and the worksheet is protected) the formula in a cell does not appear in the formula bar. However, you do see the results of the formula in the cell.

Important

The Locked and Hidden settings enable specific collaboration scenarios to function correctly in collaboration environments that do not include users who have malicious intent. You cannot enable a strong encryption file by using these settings.

To protect the document or the file from a user who has malicious intent, use Information Rights Management (IRM) to set permissions that will protect the document or the file.

For more information about the Office features that help enable collaboration, see Description of Office features that are intended to enable collaboration and that are not intended to increase security .

References

For more information about cell formatting, click Microsoft Excel Help on the Help menu, type worksheet formatting in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

The tutorial explains how to effectively use AutoCorrect in Excel and how to stop it completely or only disable for specific words.

Excel AutoCorrect is designed to correct misspelled words automatically as you type, but in fact it is more than just correction. You can use this feature to change abbreviations to full text or replace short codes with longer phrases. It can even insert check marks, bullet points and other special symbols on the fly without you having to access anything. This tutorial will teach you how to do all this and more.

Excel AutoCorrect options

To have more control over how Excel performs autocorrection in your worksheets, open the AutoCorrect dialog:

• In Excel 2016, Excel 2013 and Excel 2010, click File > Options, select Proofing on the left-hand pane, and click AutoCorrect Options.
• In Excel 2007, click the Office button > Options > Proofing > AutoCorrect Options.

The AutoCorrect dialog will show up and you can switch between the 4 tabs to enable or disable specific corrections.

AutoCorrect

On this tab, you can view the list of typical typos, misspellings and symbols that AutoCorrect uses by default. You can change and delete any of the existing entries as well as add your own ones. Additionally, you can turn options on or off the following options.

The first option controls the autocorrect logo (lightning bolt) that appears after each automatic correction:

• Show AutoCorrect Options buttons - shows or hides the autocorrect logo.

Please note that the autocorrect button does not appear in Excel anyway, clearing this box prevents the lightning bolt from appearing in Word and some other applications.

The next 4 options control the automatic correction of capitalization:

• Correct Two Initial Capitals - changes the second capital letter to lowercase.
• Capitalize first letter of sentence - capitalizes the first letter after a period (full stop).
• Capitalize names of days - self-explanatory
• Correct accidental use of cAPS LOCK key - fixes words in which the first letter is lowercase and the other letters are uppercase.

The last option enables or disables all automatic corrections:

• Replace text as you type - turns AutoCorrect off and on.

Tips and notes:

• Text included in formulas and hyperlinks is not automatically corrected.
• Every change you made in Excel AutoCorrect options applies to all workbooks.
• To prevent automatic capitalization after some abbreviation or acronym that ends with a period, add it to the Exceptions list. For this, click the Exceptions… button, type the abbreviation under Don't capitalize after and click the Add button.
• Not to correct 2 initial capital letters, for example 'IDs', click Exceptions, switch to the INitial CAps tab, type the word under Don't correct, and click Add.

AutoFormat as you type

On this tab, you can disable the following options, which are enabled in Excel by default:

• Internet and network paths with hyperlinks - turns text that represents URLs and network paths into clickable hyperlinks. To disable automatic creation of hyperlinks in Excel, clear this box.
• Include new row and columns in table - once you type anything in a column or row adjacent to your table, such column or row is included in the table automatically. To stop the automatic expansion of tables, clear this box.
• Fill formulas in tables to create calculated columns - uncheck this option if you want to prevent the automatic replication of formulas in Excel tables.

AutoCorrect actions

By default, additional actions are disabled. To turn them on, select the Enable additional actions in the right-click menu box, and then select the action you want to enable in the list.

For Microsoft Excel, only the Date (XML) action is available, which opens your Outlook calendar on a given date:

To trigger the action, right-click a date in a cell, point to Additional Cell Actions, and click Show my Calendar:

Math AutoCorrect

This tab controls the automatic insertion of special symbols in Excel equations (Insert tab > Symbols group > Equation):

Please note that the math conversions only work in equations, but not in cells. However, there is a macro that allows using Math AutoCorrect outside math regions.

How to stop AutoCorrect in Excel

It may sound strange, but AutoCorrect in Excel is not always a benefit. For example, you may want to insert a product code like '1-ANC', but it is automatically changed to '1-CAN' each time because Excel believes you've misspelled the word 'can'.

To prevent all automatic changes made by AutoCorrect, simply turn it off:

1. Open the AutoCorrect dialog by clicking File > Options > Proofing > AutoCorrect Options.
2. Depending on what corrections you want to stop, uncheck the following boxes on the AutoCorrect tab:
   • Clear the Replace text as you type box to disable all automatic replacements of text.
   • Clear some or all check boxes that control automatic capitalization.

How to turn off AutoCorrect for certain words

In many situations, you may not want to stop autocorrect in Excel completely, but disable it for particular words. For example, you can keep Excel from changing (c) to the copyright symbol ©.

To stop auto-correcting a specific word, this is what you need to do:

1. Open the AutoCorrect dialog (File > Options > Proofing > AutoCorrect Options).
2. Select the entry you want to disable and click the Delete button.

The screenshot below shows how to turn off the autocorrect of (c):

Instead of deleting, you can replace (c) with (c). For this, type (c) into the With box, and click Replace.

If you decide to return autocorrect (c) to copyright in the future, all you'll have to do is open the AutoCorrect dialog and put © in the With box again.

In a similar manner, you can turn off autocorrect for other words and characters, for example, prevent changing (R) to ®.

How to undo AutoCorrect in Excel

Sometimes, you may need to prevent autocorrect of a specific entry just one time. In Microsoft Word, you'd simply press Ctrl + Z to undo the change. In Excel, this deletes the entire cell value instead of reverting the correction. Is there a way to undo AutoCorrect in Excel? Yep, here's how you can do this:

1. Type a space after the value that is auto-corrected.
2. Without doing anything else, press Ctrl + Z to undo the correction.

For instance, to undo the autocorrect of (c) to copyright, type (c) and then type a space. Excel performs the auto-correction, and you immediately press Ctrl + Z to have (c) back:

How to add, change, and delete AutoCorrect entry

In some situations, you may want to extend the standard list of misspellings used by Excel AutoCorrect. As an example, let see how we can force Excel to replace the initials (JS) with the full name (John Smith) automatically.

1. Click File > Options > Proofing > AutoCorrect Options.
2. In the聽AutoCorrect dialog box, enter the text to be replaced in the Replace box, and the text to replace with in the With box.
3. Click the Add button.
4. Click OK twice to close both dialogs.

In this example, we are adding an entry that will automatically replace 'js' or 'JS' with 'John Smith':

If you'd like to change some entry, select it in the list, type the new text in the With box, and click the Replace button:

To delete an AutoCorrect entry (predefined or your own one), select it in the list, and click Delete.

How to insert special symbols using AutoCorrect

To have Excel insert a tick mark, bullet point or some other special symbol for you automatically, just add it to the AutoCorrect list. Here's how:

1. Insert a special symbol of interest in a cell (Insert tab > Symbols group > Symbols).
2. Select the inserted symbol and press Ctrl + C to copy it.
3. Click File > Options > Proofing > AutoCorrect Options.
4. In the AutoCorrect dialog, do the following:
   • In the With box, type the text that you want to associate with the symbol.
   • In the Replace box, press Ctrl + V and paste the copied symbol.
5. Click the Add button.
6. Click OK twice.

The screenshot below shows how you can create an auto-correct entry to insert a bullet point in Excel automatically:

Tech support scams are an industry-wide issue where scammers trick you into paying for unnecessary technical support services. You can help protect yourself from scammers by verifying that the contact is a Microsoft Agent or Microsoft Employee and that the phone number is an official Microsoft global customer service number. Since I wrote a post about problems with Word 2004’s word counting features the other day, I should mention a few word-counting alternatives that are available for Mac OS X. One of them is a stand-alone freeware product called Word Counter.It was mentioned on Mac OS X Hints on Tuesday. In my limited testing, it seems to be doing an OK job. The program includes the option to count. Here’s a table that I have inserted in a Word document by copying and pasting cells from an Excel spreadsheet: As you can see, I have selected the entire table. Now I go to the “ Table ” menu and select the “ AutoFit to Window ” command inside the “ AutoFit to Window ” submenu. Betalogue can't stand microsoft word 2004 for mac. Next Post: Can’t stand Microsoft Word 2004 for Mac OS X? Try WordPerfect in Windows XP for a while Try WordPerfect in Windows XP for a while 5 Responses to “Word 2004: Repeatable crashes caused by Clipboard functions”. 12 Responses to “Can’t stand Microsoft Word 2004 for Mac OS X? Try WordPerfect in Windows XP for a while” SteveH says: April 19th, 2007 at Apr 19, 07 5:13 pm. Would AbiWord give you a simpler round trip as it can read and write WordPerfect files in theory?

And now, whenever you type bullet1 in a cell, it will immediately be replaced with a bullet point:

That's how you use, adjust and stop AutoCorrect in Excel. I thank you for reading and hope to see you on our blog next week!

You may also be interested in: