Cell settings

On this page, we will look at the most typical cell settings in spreadsheets:

  • preset document templates
  • specification of data type
  • alignment
  • layout
  • borders and backgrounds
  • formatting and removing formats

Preset document templates

Excel 2013 offers an abundance of pre-defined online templates for effortlessly recording your monthly income and expenses, work or, say, gardening schedules. The online templates appear when you open Excel. (click to enlargen the image below).

excel2016_newworkbook

However, it is highly probable that you will need to edit the cell settings and other properties of your worksheet at some point. Additional information on this is presented further on.

Specifying cell data types

Since different kinds of data (e.g. dates, currency, percentages, etc) are often entered into spreadsheets, you will have to tell the spreadsheet program what kind of data the cell you are modifying contains. If you do not specify the type of data correctly, the program will interpret the cell data in its own way; e.g. the value “1.10” become “1 Oct” in Excel if the data type is not specified. The data type can be specified in the following ways, for example:

  • as a number: you can change the number of decimals shown or show/hide the thousand marker.
  • as currency: the program will add the currency unit to the cell automatically
  • as a date: the program will alter a date you enter as “dd.mm.yyyy” into a more readable format
  • as percentages: the program will convert a decimal number you have entered into percents

To change the presentation of cell data, select the cells you want to format (it often makes sense to select a row or column at a time), then select Format Cells from the Format menu in the Cells group on the Home tab.

Go to the tab Number and select the presentation you want from the list under Category. In the example below, the cell is defined as a number. Click on OK to accept.

You can choose the number of decimals shown for Number or Currency from the box Decimal places. In the list Negative numbers, you can choose the formatting for negatives. The box Sample shows how your choices will affect the number in the cell you have selected for formatting.

excel2016_formatnumber

If you see a row of #### signs in the cell after formatting, the number is too long to fit in the cell. In such cases, widen the column or change the font to a smaller one.

Write dates and times so that the program understands what they stand for. A stop is usually used to separate the parts of a date (25.8.2002), and a colon to separate the hours from the minutes (15:35). you can change the formatting for dates or times in the Category list under Date. If you add hours and their total is over 24, choose the formatting [h]:mm:ss from the Custom list. If you do not want to include seconds, , remove the :ss in the Type box.

The per-cent formatting multiplies the numbers with 100. if you write % after a number, the program will divide it by 100, e.g. 1 % = 0.01.

Cell alignment

Most spreadsheet programs will align numbers to the right and text to the left automatically. In addition, it will centre truth and error values.

If you wish, you can change the alignment of numbers and text. With Excel, select the cells to align, then select Format Cells from the Format menu in the Cells group on the Home tab.

excel2016_formatalignment

Change horizontal alignment with Horizontal and vertical alignment with Vertical. You can change the orientation of the data by turning the red sign in the Orientation box. With Wrap text you can wrap a long cell text on several lines inside the cell.

Cell layout

You can change the font, size, font style, colour, underlining, and other forms of emphasis for text and numbers in a spreadsheet.

The above-mentioned formatting can be done by selecting Format Cells from the Format menu in the Cells group on the Home tab. The go to the Font tab. Make the desired format changes and click on OK.

excel2016_formatfont

The Font list shows all available fonts in alphabetical order. Beside it are the Font style and Size lists. In the Underline list there are different alternatives for underlining, and in Color there are colour alternatives. In Effects you can choose to overline text or format it as upper or lower index. The Preview box will show you how your choices affect the characters.

Cell borders and backgrounds

Most spreadsheet programs do not create borders automatically; though the borders are visible on-screen, they are not printed by default. This means that you have to insert the borders manually with most programs. you can check what the borders look like with the print-preview function.

With Excel, you can specify borders as follows: select the cells or spreadsheet area you want to format and select Format Cells on the Format menu in the Cells group on the Home tab. Go to the Border tab. Make the formatting choices you want and click on OK.

excel2016_formatborder

You can select the style from the Style box and the colour of the border from Color. You can choose on which side of the cell you want the border, among other things, in the Border box.

In Excel, there is a button Borders in the Font group on the Home tab. It contains ready borders and the most commonly used border types:
excel2016_bordersmenu

You can also emphasize cells and areas of cells by inserting different patterns and colours. Select the patterns and colours carefully, so that the spreadsheet does not become too ‘busy’!

You can add colours and patterns by selecting the cells you want to format, then selecting Format Cells from the Format menu in the Cells group on the Home tab. Then go to the Fill tab and select the colours and patterns you want. Then click on OK.

excel2016_formatfill

You can choose a colour from the palette and rastering in the Pattern Style list. You can select colour nuances from the Fill Effects window. The Sample box will show how your choices will affect the cells.

If you want to delete all formatting from a cell or area, select the area and then open the Clear menu in the Editing menu on the Home tab. Select Clear Formats from the menu that opens. Please observe that this will really remove all formatting from the selected area at once. To delete one formatting at a time, use the alternatives in the Format Cells window in Format or the buttons on the Home tab.

Using ready styles and formatting

You can also save time on formatting by using the program’s own document formats. As with Word, you can use (and modify) e.g. header styles by selecting the area or cell you want to format, and then opening the Cell Styles menu in the Styles group on the Home tab. Then select the style you want (see image below). if you roll your mouse over the alternatives, you can see how it will affect the area you have selected in the spreadsheet.

excel2016_cellstyle

Alternatively, you can select a style for the whole document among the ready formats in Excel. First select the area or cell you want to format, then open the Format as Table menu in the Styles group on the Home tab. Then select the formatting you want from the list (see image below).

excel2016_formatastable

When you use the Format as Table feature, the table you have selected will receive default headers. You can click on these headers to alter e.g. how the data is managed or presented.