Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel FAQs: Font formats

by
11th Oct 2011
Save content
Have you found this content useful? Use the button above to save it to your profile.

Excel numbers and general formatting techniques are worth studying in some depth. In this article, Simon Hurst focuses on font-related questions.

Introduction

The standard font commands in Excel are fairly obvious and straightforward whichever version you are using but what happens if you want to tick a bank reconciliation or display a row of mobile phones? Also, with the existence of Excel styles being much more obvious in Excel 2007 and 2010, can they be used to improve the appearance and consistency of spreadsheets?

How do I apply the standard font commands in the different versions?

In Excel 2003 the formatting toolbar includes the main font formatting options. These are also available via the Format, Cells dialog, Font section. In Excel 2007 and 2010 the Font group of the Home tab includes these commands with the Font dialog still being available. In addition, as well as the right-click menu including the Format Cells option as it does in Excel 2003, it also displays the 'mini toolbar' containing a set of formatting commands including the main font formats:

The control key shortcuts for Bold (Control+b), Italic (Control+i) and Underlined (Control+u) can also be used.

Can I apply different formats to different characters within a single cell?

If an Excel cell contains text, then editing directly in the cell or using the formula bar allows you to select one or more characters and apply different font attributes:

How do I insert a tick next to a bank reconciliation?

In Excel 2003 Insert, Symbol and in Excel 2007 and 2010 the Symbol option in the Symbols group of the Insert ribbon tab, display the Symbol dialog. This allows you to choose a font and then a particular character within that font. In this example we have used the ‘Webdings’ font and found a tick:

Note that the character code for the chosen character is displayed at the bottom of the screen. This can be used to enter the code directly (type Alt+97 using the number pad, not the main keyboard numbers). It could also be used to incorporate non-printing characters in a formula, although if you change the cell to the required font before you start typing the formula it might be best to concentrate on the formula bar rather than the cell contents…

The CHAR() function returns the character for the given character number. The CODE() function does the opposite – displaying the character number of the character or the first character within a cell:

How do I wrap text within a cell?

It’s often better to wrap text labels within a single cell rather than creating multiple rows or columns for headings. In Excel 2007 and 2010 the ‘Wrap Text’ option is very visible in the Alignment group of the Home ribbon tab:

In Excel 2003 it is available via the Format Cells dialog, Alignment tab.

There is an alternative to using the Wrap Text options that gives you increased control over where your line break will be positioned within your text. The Alt+Enter keyboard shortcut inserts a line break and also turns on Wrap Text for that cell.

Are Excel styles new?

Excel styles do exist in Excel 2003 but they are less easy to find than in Excel 2007 and 2010. In Excel 2003 Format, Style… displays a rather uninspiring Style dialog:

Styles are a lot more obvious and colourful in Excel 2007 and 2010. The Cell Styles button in the Styles group of the Home ribbon tab displays a much more visual approach to styles:

In both sets of versions it is possible to create new styles and modify existing styles. The Modify button in the Excel 2003 dialog or right-clicking on a particular style and choosing the Modify option in Excel 2007 and 2010 and then clicking on the Format button displays the Format Cells dialog.

Why isn’t the new style I set up available in all my workbooks?

If you are familiar with the way that Word styles work, then you might be disappointed to find that Excel styles aren’t automatically saved to a global Excel template. If you want particular styles to be more generally available then you either need to create them within an Excel template, or merge them into your workbook.

The Merge button in the Excel 2003 dialog, or the Merge Styles… option at the bottom of the Excel 2007 and 2010 styles list allows you to copy styles from any open workbook into the current workbook. Note that this will potentially overwrite styles in the target workbook that have the same name.

Office 2007 and 2010 themes

Excel 2007 and 2010 includes the ability to apply Themes. Office themes are common to all the office applications and allow the application of a consistent set of colours, fonts and effects across different types of Office ‘document’ with a single click. In Excel Themes are available from the Page Layout ribbon tab:

An extensive list of built-in Themes is available, but it is also possible to create your own custom Theme which will then be available not only to all workbooks, but also the other Office applications that use themes.

How do I find out more about font formats?

For further information and ideas have a look at:

About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping accountants make effective use of technology. He is a regular contributor to AccountingWEB's ExcelZone and the author of '100 Time-saving Tips for Microsoft Office'. For more information, visit The Knowledge Base website.

Tags:

Replies (5)

Please login or register to join the discussion.

avatar
By James Dyer
14th Oct 2011 15:00

Colour text default

Thanks for the interesting article.

Can anyone show me how to change the default text to red, for example, so that I can add text to an existing spreadsheet in various cells without having to change back to red each time I move cell.  I know I can do them all in one go at the end by selecting all the cells but then I have to remember where they all are.  Excel version 2007.

Thanks.

Thanks (0)
Replying to should_be_working:
By sysmod
14th Oct 2011 15:11

 

 

If you're going to click in each cell to enter the text, you may as well do one more click on the Font colour button, change it to red once, and then it stays at red for any subsequent click. Just one more click per cell. Or Ctrl+Y if you prefer to use the keyboard.

 

BTW :

If you change the Normal style, that changes the display of all cells.

If you define a new style, you still have to remember to apply it, so that's still a click,

 

 

Thanks (1)
By sysmod
14th Oct 2011 15:05

Common problems with Styles

Very often styles multiply when cells are copied & pasted from other workbooks or other instances of Excel. If you wait till you get the error message "too many cell formats", it's too late, you have over 4000 different cell formats. I've seen workbooks with over 60,000 styles, but not all were in use.

 It may be useful to have a macro that checks for too many styles (eg over 100) and deletes the unused ones. This code

Sub CleanStyles()

Dim sty As Style, wbTemp As Workbook

 

' First, remove all styles other than Excel's own.

' they may have arrived from pasting from other workbooks

For Each sty In ThisWorkbook.Styles

   If Not sty.BuiltIn Then sty.Delete

Next

 

'Second, revert the remaining styles to Excel's default for a new workbook

Set wbTemp = Workbooks.Add

Application.DisplayAlerts = False

ThisWorkbook.Styles.Merge wbTemp

wbTemp.Close SaveChanges:=False

Application.DisplayAlerts = True

 

End Sub

 

 

Another idea is to check whether the Numberformat of the Normal style is not General. I have seen workbook style corruption where it acquires a Date format.

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/excel-2007-spontaneously-formats-entire-work-book/1ae533af-ecee-41fa-866a-2590343a111d

 

if activeworkbook.styles("Normal").NumberFormat <> "General" then show some message and offer to reset it to General

 

 

Thanks (0)
avatar
By James Dyer
14th Oct 2011 16:37

keyboard shortcut

Ctrl+Y is a help as I don't have to reach for the mouse and can navigate around using the keyboard.

Thanks (0)
avatar
By duncanphilpstate
27th Oct 2011 15:11

I know it wasn't the main thrust of the article, but discussion of inserting ticks and comments about doing things repeatedly got me thinking.

Below is a little macro I recorded to insert a tick from the wingdings font into the current cell - nothing clever about it, just to show it can be done..

What I also wanted to point out is that you can associate this macro with a button on the Quick Access Toolbar (2007 onwards) AND despite the many deficiencies of the QAT, you can tell it during the customisation that the button is for the current workbook only - meaning that it is to some extent context sensitive. To do this, right-click on the QAT, select customise... and then in the dropdown at the top right of the dialogue select the name of the current workbook (rather than "for all documents (default)".

In other words you can create a QAT button within a re-used spreadsheet (or template?) to insert a tick or other symbol with one mouse click.

Hope that helps.

 

Sub InsertTick()
' InsertTick Macro
'
    ActiveCell.FormulaR1C1 = "ü"
    With ActiveCell.Characters(Start:=1, Length:=1).Font
        .Name = "Wingdings"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
End Sub

Thanks (0)