ICT CERTIFICATE OF ACHIEVEMENT UNIT 2 - MICROSOFT EXCEL - REVISION GUIDE
1. ENTER TEXT, NUMBERS AND FORMULAE INTO A CELL
Entering text, numbers and a simple formula means clicking on a cell (a box in Excel) and typing in the data. To edit a formula after you have first entered it, you click on the cell and then edit it in the bar at the top (circled in red above).
2. CONSTRUCT SIMPLE FORMULAE USING BASIC OPERATORS AND SIMPLE BUILT-IN FUNCTIONS, E.G. SUM, AVERAGE, COUNT, MAX, MIN A formula is an instruction to the computer to carry out a task with a set of numbers. For example, you can add up a list in your spreadsheet (SUM) as follows:
In this example, we have a list of numbers in cells A1 to A5. The formula adds, using SUM. Remember: Always start with an equals sign to show it is a formula. Use brackets to go around the cells. If it is a group of cells, separate the first and last with a colon. A comma can be used to separate the cells if they are not in a column or group. So we end up with =SUM(A1:A5). The formula has been entered into cell B8 so that total of 233 appears there when ENTER/RETURN is pressed.
(c) E Parkin - Hatherop Castle School - 2010 / 2011
3. FORMAT CELLS AS DATE AND/OR CURRENCY
The data here is the same in column A and C. The number in column C have been selected and turned into currency. Click the icon on the HOME toolbar
To make data date specific, use the drop down menu on the HOME toolbar and select date.
4. EDIT AND DELETE DATA Data can be edited or deleted in the cells by simply clicking on the cell and editing as needed. When using formula, the number will appear in the cell and the formula in the bar at the top of the screen. Click in to bar to edit the formula.
5. REPLICATE FORMULAE A formula can be copied easily by selecting the cell that the formula is in and then using Copy and Paste. For example, in the first image here, I have found the average of 2,3,6 and 8 using the formula =AVERAGE(A1:A5) and then copied cell A7 to C7 to find the average of 3,6,9,12 and 15.
(c) E Parkin - Hatherop Castle School - 2010 / 2011
REPLICATE FORMULAE – continued Formulae can be replicated in other ways too. You can click and drag the dot on the bottom corner of a cell to copy the formula into other cells, whilst changing the column/row numbers automatically. For example:
Click and drag this dot across to D6
The formula then copies from A6 into B6, C6 and D6, adding the numbers in the cells in that column.
6. SORT DATA (IN ONE COLUMN ONLY)
If you have a column of data that you need to SORT into order, click SORT on the DATA toolbar and the SORT box will appear. Use the order menu to choose if you want to start with the small or largest.
(c) E Parkin - Hatherop Castle School - 2010 / 2011
7. CHANGE THE FONT SIZE, COLOUR AND TYPE OF TEXT IN A CELL
As in Microsoft Word, the size, colour and font can be edited using the icons on the HOME toolbar. 8. ALIGN TEXT IN CELLS
Text and numbers can be aligned in each cell using the icons on the HOME toolbar.
9. ALTER THE ROW HEIGHT AND COLUMN WIDTH OF CELLS Row height and width can be altered by placing the cursor between the row or column. It will change to a ‘slider’ style icon and you can now click and drag the row/column to be bigger or smaller.
10. MERGE CELLS
Cells can be merged by selecting the ones you want to merge and clicking on MERGE & CENTRE on the HOME toolbar.
11. COPY / MOVE CELLS Cells can be copied by selecting them and using Copy & Paste, either from the HOME toolbar or by right clicking. See also more detail in point 5 – replicating formula. To move the contents of a cell, select it, hover over the edge until the icon changes to a ‘cross’ style and then drag.
(c) E Parkin - Hatherop Castle School - 2010 / 2011
12. SET THE BORDER STYLE OF A CELL OR RANGE OF CELLS
The border around any cell or cells can be changed by selecting the cells you want to change, and clicking the border icon on the HOME toolbar.
13. USE LANDSCAPE ORIENTATION
Landscape and portrait print orientation can be altered by using the ORIENTATION icon on the PAGE LAYOUT toolbar.
14. CREATE A PIE CHART OR BAR CHART Graphs and charts can be designed only when the data needed has been entered into the cells on the spreadsheet. First, select the data you wish to use for your chart.
Then use the icons on the INSERT toolbar to choose the type of chart.
(c) E Parkin - Hatherop Castle School - 2010 / 2011
15. PUT A TITLE ON A CHART
When your chart has been produced, click on it and the CHART TOOLS will appear above the toolbar. Click on the LAYOUT icon and lots of options and choices appear for labelling and giving your chart a title. Use these to complete your graph.
16. ADD OR REMOVE A LEGEND FROM A CHART The ‘legend’ on a chart is the small box that says ‘Series 1’ that we often don’t need. To remove it, click on it, press DELETE on the keyboard.
17. ADD DATA LABELS TO A CHART To add data labels, use the same method as point 15 (above). The icons you need to add labels are next to Chart Title –AXIS TITLES / LEGEND / DATA LABELS.
(c) E Parkin - Hatherop Castle School - 2010 / 2011
