IT Support Manchester

The alignment section of the Home Tab of the Excel ribbon contains a number of options relating to the way in which your data is position within the cell. Perhaps the most familiar and the most frequently used buttons in this section are the three relating to the horizontal position your data: left, centre and right. However you’ll notice that when you click in the cells of an unformatted worksheet, none of these three icons is highlighted, which indicates that none of them is the default. The reason for this is that Excel treats data differently depending on the data type.

If you type text in a cell, your text is aligned on the left; if you type a number, the number is aligned on the right; if you type a date, it is also aligned on the right. To change the horizontal alignment, either select a range of cells or click on a column letter to highlight the entire column then click on one of the alignment icons.

Haven chosen one type of horizontal alignment, you can change it in two ways. You can either click on a different form of alignment or click again on the already selected alignment. For example, if your text is centred and you click on the Centre button a second time, this deactivates centre alignment and returns you to the default alignment which, for text, is left. Thus we have, effectively, four types of horizontal alignment: left, centre, right and unspecified (or default), which is the alignment that applies when none of the alignment buttons is highlighted.

Microsoft Excel also allows you to specify the vertical alignment of your text. This setting becomes apparent when you increase the height of the cell and there is a definite default; text is normally aligned at the bottom of the cell. This default vertical alignment applies to text, dates and numbers alike.

To set the vertical alignment, either make a selection or click on the row number to select the entire row then click on one of the buttons to make the change: align middle, align top and so forth.

The alignment option also includes the ability to change the orientation of text within the cell. This is particularly useful in those situations where the headings are wider than the data within the cells. To change the vertical orientation of your text, you simply select the cells in question and then choose the appropriate orientation in the Alignment dialogue.

If you rotate your column headings by 90 degrees, you can usually make the columns much narrower. Excel has a very useful way of doing this: simply select all the columns that contain data then in the Cell group of the Home Tab of the Excel Ribbon, choose Format then AutoFit Columns. This command makes each of the highlighted columns no wider than it needs to be in order to display all the data it contains.

Almost every Exel user has encountered the SUM function. It is one of the most widely used functions of the Excel functions. And most Excel users will also have used the IF function. SUMIF function is a combination of the SUM and If functions which allows us to calculate the total of all cells within a given range that match a certain condition.

For example, let’s say that, in a “Sales” worksheet, we have a breakdown of the sales of each individual salesperson. We then want to create a summary of these figures in a “Branches” worksheet in which we want to create a total sales figure for each branch; so our “Branches” worksheet would have two columns: “Branch” and “Total Sales”. We can use the SUMIF function to calculate the figures in the “Total Sales” column.

A good place to begin would be to create named cells so that we can refer to these names in our formula. To name a range of cells we begin by selecting the range then we click on the name box in the top left of the worksheet, enter the name then press the Enter key.

Once we have inserted the names of all the branches in the first column of our “Branches” worksheet, we would highlight the first cell in the “Total Sales” column, adjacent to the cell containing the name of our first branch; let’s say our first branch is “Birmingham”. When using functions for the first time, it’s always useful to use Excel’s Insert Function facility. To access this, click the Insert Function button on left of the formula bar. The SUMIF function is to be found in the “Math and Trig” category. Scroll down the list, highlight SUMIF and then click OK. Excel will then prompt us for the three arguments required by the SUMIF function.

The first argument is the range of cells that we want to evaluate. In our branch sales example it would be the column containing the names of the branches. If we have named this column, we can insert this name by clicking on “Use In Formula” in the Formulas Tab at the Excel Ribbon. This is a drop-down menu containing all the names in the workbook.

The second argument is the criteria we want to match. In our example, is simply the contents of the cell in the adjacent “Branch” column, which in this case contains “Birmingham”. We can click in the cell to pick up the reference.

The final argument is the SUM range; the column that contains the cells that we want to actually total; namely, the sales figures. Again, if we have named this column, we would click on “Use In Formula” and choose the name. Once we have specified the three arguments, we click OK and Excel creates the formula.

It’s now safe to copy the formula down. The cell reference of the adjacent column containing the branch name will change but the two named ranges will remain the same. To copy the formula down, simply position the cursor on the AutoFill handle in the bottom right of the cell and then either drag or simply double-click.

When changing the appearance of text within an Excel worksheet, you have the choice of working on the entire cell or at the text level. To work at the cell level you simply highlight one or more cells and choose your formats. The formats that you choose will then apply to all text within the cell or cells.

For example, to change the font size of your data in Microsoft Excel, you can of course use the drop-down menu next to the font name and choose one of the preset sizes. Another way of changing the size is to use the Grow Fond and Shrink Font buttons located on the right of the font size. These two buttons simply take you up and down the same preset sizes available on the preset size drop-down. You can also use the cursor keys on your keyboard to move through different sizes. Yet another option is to enter a particular size then press the enter key. When doing so, you are permitted to enter decimals, such as 10.5.

In a similar way, when you want to choose a font, you can highlight the current name and start to type the name of font you have in mind, for example Times new Roman. Once you have typed enough to let Excel know which font you mean, the font name will be displayed and you simply press the Enter key.

Colour can be applied in the same two ways in your worksheets, either as a background to the cell or to the text inside the cell. Since background colour applies to the cell itself, it can’t be applied at the text level. With the introduction of Themes in Excel 2007, Microsoft have made it very easy to come up with colour combinations which look good together.

If you wish to work at the text level, you need to be in Edit mode. You can access Edit mode in two ways: the first is to double-click on any cell; the second is to click once on the cell and then click anywhere in the formula bar. You can know when you’re in Edit mode by the presence of the Cancel and Enter buttons on the left of the formula bar.

When working in Edit mode, you can highlight individual characters and change their attributes. For example, if a cell contains the text “Google”, you could highlight each letter individually and change it to the colour used in the Google logo.

The facility of using different formatting within the same cell is very useful for headings. It is not quite so useful for the rest of the data in your worksheets since it slows down data entry and makes editing difficult.

When entering numbers into a spreadsheet, you sometimes need to ensure that the number format is consistent. For example, if the numbers in question represent prices, you may want to display a currency symbol or you may simply want to make sure that the number of decimals displayed is consistent..

Unless you specify otherwise, all numbers in Excel are rendered in the “General” format. This means that numbers are displayed exactly as you enter them: if you enter two decimals, two decimals are displayed; if you went to one decimal, one decimal is displayed; and so on.

When specifying the number format, the best idea is usually to select the whole column. To do this, click on the letter or letters representing the column. (Any text contained in the selection will not be affected by the number format you specify.)

Number formats are displayed in the “Numbers” section of the Home Tab of the Ribbon. There are three main formats related to numbers: the first is called “Number”, the second “Currency” and the third “Accounting”. To gain access to the full range of number formats, choose “More Number Formats” from the drop-down menu. Another way of accessing the same dialog box is to click on the launch button in the “Numbers” section of the Home Tab.

When you click on each of the number formats, you are presented with a series of choices which enable you to refine the way that the format will work. For example, if our numbers refer to an hourly rate, we would probably click the “Number” category in the left column and then specify two decimal places. The option labelled “Use Thousands Separator” will insert the appropriate separator to demarcate thousands. The separator which Excel uses will depend on your locality: for example, if you are in the UK or USA, a comma will be used; if you are in a European country, a dot will be used.

The final option in the “Number” category lets you control the display of negative numbers. The default is to preced the number with a minus sign and to leave the colour of the text unchanged. However, you can also suppress the minus sign and change the colour of negative numbers to red; or, if you prefer, you can change the colour of negative numbers to red as well as displaying the minus sign.

When you click on the “Currency” category, you have pretty much the same choices with the addition of a currency symbol. One can specify which currency symbol is used or one can dispense with the currency symbol altogether.

The “Accounting” category is pretty much the same as “Currency”. Once again, you can choose a particular currency symbol. However, you will notice that you do not have any choices relating to negative numbers. The convention in accountancy circles is to always place negative numbers in brackets.

In addition to using the number dialog box, you’ll notice that there are a series of handy buttons which can apply each of the appropriate formats with one click. There are also two buttons for increasing and decreasing the number of decimals displayed in the selected numbers.

Finally, there may be times where, although you enter a number into a cell, you do not want Excel to treat it as a number. For example, if you have a column of data containing a client ID, although the ID may be numeric, you may not want Excel to treat it like a number or to modify it in any way. You will probably want it to stay exactly the way it was entered. Whenever this is the case, it is best to format the number as “Text”. The simplest way of doing this is to select the appropriate column and in the number dialog box choose “Text” as the format.