Format data and Align text effectively

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Cell formatting is all about how your data in cells is displayed – in terms of the number format (e.g. currency, percentage), the font/background fill colour of cells and how the text in cells is aligned. This is crucial for ensuring that your workbooks are usable and intuitive in terms of how it should be used and is particularly important for complex workbooks which will be used by different people. Not every cell will contain the same type of data or is intended to be used in the same way, so every cell shouldn’t be formatted in the same way! Moreover, number formats are important for ensuring formulas work correctly – for example text values are ignored in a SUM function, so if some of the numbers in a range are formatted as text these won’t be included in a SUM function that is based on that range.

Understanding how to align text (either within a cell or across multiple cells ) might sound trivial but where you have several lines of text you need to set the alignment options correctly so it displays in the way intended. Merging cells together to ensure your text spans a particular width/height isn’t an efficient way of displaying data – it makes the cells much harder to work with (including restrictions on copying and pasting the cells and referencing them in a formula).

Excel not only provides functionality for setting detailed formatting options but also a quick and easy way to apply and re-use them by using cell styles which we will also look at in this topic.

To format a cell or group of cells, highlight the cell(s), and then right click and choose ‘Format Cells’ or use the Keyboard Shortcut Ctrl + 1. You will then be presented with the Format cells dialog box shown below. Each of these are explained below

• • Number: allows you to set the number format
  • Alignment : Set how text is aligned and displayed within/across cells
  • Font: Set the colour, properties (Bold/italic etc.) of the text
  • Border: allows you to put lines around the cell(s)
  • Fill: allows you to specify a background fill colour for the cell(s)
  • Protection: When Worksheet protection is applied, locked cells cannot be edited. All cells are locked by default, so for input cells that you want to be editable this needs to be changed to make them unlocked.

The basic premise of cell formatting is that cells should be formatted according the data they contain/are expected to contain. If a cell is to contain monetary amounts it should generally be formatted as a currency to 2 decimal places for example. It is also useful to use formatting to highlight the different types of cells within the workbook – for example to let people know which cells they can type into versus those that are locked.

Very broadly speaking, in an Excel model there are three main types of cells in a workbook which should each have a different format applied:

Input Cells: These are cells which users type into. These should be visibly marked as being editable so that this is clear to users, e.g. by having a yellow background and perhaps a border. The cells should specifically be set to be unlocked so that they can be edited if the worksheet is protected. A number format should be applied for these which reflect the type of input expected (For example a currency format for monetary amounts). Data validation should also be applied to enforce the correct inputs, including providing an input message so that the user knows which format to enter cells in.

Calculation Cells: These contain formulas which take the inputs entered by the user and perform calculations to arrive at the outputs. These cells should be locked so that they not editable, and should be formatted to indicate this e.g. by having a grey background. The number format should be ‘General’ or specific to the type of result the formula evaluates to.

Output Cells : These are the cells which show the results of the calculations. They should consist of cell link references pointing to the calculation cells and should have a number format relevant to the output type (percentage, number etc). The cells should generally have a light or plain background and should be locked so that they cannot be edited by users

The number format of cells is very important for making the cell contents readable and Excel provides several default number formats for those most commonly required such as showing percentages (which for example would display the number 0.5 as 50%), currency (which would show e.g. 5000 as $5,000.00) and Date (which would convert any date or number into a date format such as ‘05/06/2012’). A list of some common number formats is shown in the Topic Example workbook in the ‘Basic number formats’ tab. To display cell values using a number format which isn’t available as one of the default options, you will need to choose or define a custom number format.

Clearly Excel isn’t the most suitable application for typing large amounts of text into (that is what Word is for!), but sometimes even displaying a couple of lines of text in Excel can be awkward to format so that it displays correctly. Though by setting the alignment options for the cells (the second tab in the Format Cells dialog box) Excel is perfectly capable of showing text in the way intended.

The alignment options are shown below. Where your text is short enough to fit within a cell (a single cell or merged cells) then the Horizontal/Vertical alignment options allow you to position the text within that cell – at the top/bottom/middle of the cell, and positioned to the left/right/centre. The ‘Text Alignment’ worksheet in the Topic Example Workbook allows you to experiment with this.

Where you have text which doesn’t fit into a single cell but you want to display this text only within that cell (for example a column heading label), then there are two main alignment options:

• Text Wrapping – select the cell, Go to Format Cells and then in the Alignment tab tick ‘Wrap text’. This will automatically spread the text over multiple lines within the cell. To see all the text you may then need to increase the row height – hover over the line in-between the row numbers in the margin and drag it to expand.
• Shrink to fit – selecting this tick box option automatically reduces the display size of the text so that it is shown within the cell. It can be combined with the Wrap text where you want to display all the text within the cell but not increase the row height far enough.

If instead you want to display the text over multiple cells then this can be achieved by using the ‘Centre Across Selection’ option in the horizontal alignment drop-down box, which is a much better alternative to merging cells together. For example to show some text typed in cell A1 along cells A1:M1, then highlight the range A1:M1, and select ‘Centre across selection’ in the options for horizontal alignment. If you want the text to display over multiple lines as well then select the ‘wrap text’ option. The text will then span over the range selected and if the text is wrapped, the row height will be automatically adjusted to reflect the size of the text.

As an example, see how the text in cell E5 of the first tab is displayed – this was centred across the range E5:P5 with text wrapping also selected.

Applying these formatting and alignment options individually to cells across the entire workbook can be quite a tedious process but fortunately Excel provides a quick way for you to define sets of formatting options and then save these down as styles. Once you have defined a set of styles you can quickly apply them to any cell or group of cells within your workbook. These work in the same way as styles in Microsoft Word and anyone who uses these will know what a huge time-saver these are in ensuring a consistent, visually appealing look and feel across your document or workbook.

For example we might want to define a ‘heading’ style that has a large font and is formatted in your company’s corporate colour. To do this we click on ‘Cell Styles’ underneath the styles group of the Home tab and then select ‘New Cell Style’. In the dialog box that appears (shown below), give a name for the style and then click ‘Format’ to bring up the Format Cells Dialog box to determine the formatting options for the style.

At the bottom shows all the formatting options that are applied by the style – for any formatting options you don’t want to be applied when the style is applied to cells then ensure that the element is un-ticked here. Once you have set all the formatting options required, select ‘OK’.

To then apply this heading style to any text within the workbook, select the cell containing the text, go to cell styles button and choose the style (the ones you created are shown at the top under the category ‘Custom’). All the formatting options of that style will then be applied to the cell(s) selected.

If at any point you decide you want to change a style then in the Cell Styles drop-down, right click on a style and select ‘Modify.’ This will then bring up the same Style dialog box as that above from which you can change the formatting options. Note that these changes will automatically be reflected in all cells that the style was applied to which highlights another advantage of cell styles – it’s a quick way to make a formatting change consistently without having to change instances individually.

Excel also comes with a number of in-built styles, including example ones for input/calculation/output cells similar to that defined above. You can use these as they are or modify them to suit your needs. However the biggest advantage to using Excels in-built styles are that they are automatically available in every new workbook you create. In contrast when you create your own custom styles, if you want to have them available in other workbooks you can either put them into a Template file first, and then create new workbooks or insert sheets based on the template, or you can use the ‘Merge styles’ functionality (also available from the cell styles menu) which will allow you to import in styles from any open workbooks.

• • In the first worksheet of the Topic Example Workbook, apply formatting to cells C5, C7, C9 and C11 to match the intended use (some guidance is provided for potential formats that may be suitable).
  • In the ‘ Basic number formats’ sheet, apply the appropriate number format to the yellow cells according to the description in Column A.
  • In the ‘ Text Alignment ‘ sheet apply text alignment options to each of the yellow cells as per the description in the cell.
  • In the ‘ Cell Styles’ Sheet create cell styles/modify the in-built cell styles to reflect the descriptions and then apply them to the cells.

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.