Technology ICT Core Spreadsheets Spreadsheets A spreadsheet is

Technology ICT Core: Spreadsheets

Spreadsheets A spreadsheet is a table consisting of Rows and Columns Where a row and a column meet, the box is called a Cell Each cell has an address consisting of the column name followed by the row name In the image below, the highlighted cell is called B 2

Spreadsheets A cell can contain any of the following: • • Alphanumeric text - a cell label, a date etc A Numeric Value - a number, a currency value etc. • A Formula - a calculation carried out on a range of cells with the result placed in the cell containing the formula Alphanumeric Text A Numeric Value A Formula

Spreadsheets The Interface Excel 2007 is very different than any previous version The Drop-Down menus and Toolbars are gone and are replaced by the Ribbon Above the ribbon you will find the Tabs These are the equivalent of the old Menu Bar When a tab is chosen, Groups of commands associated with the tab are shown in the ribbon

Spreadsheets A Dialog Launcher is included with most groups This is an Arrow at the bottom of the group and it is used to launch the dialog box associated with the group

Spreadsheets The Quick Access Toolbar (QAT) is the only toolbar in Excel 2007 This toolbar gives quick access to frequently used tools

Spreadsheets It can be customised to display any group of commands by clicking More Commands in the drop down list and selecting the required commands

Spreadsheets The Office Button contains most of the old File Menu commands Each of the commands shows the available options when clicked

Spreadsheets Selecting cells Cells in Excel are selected for a number of reasons: • • • Formatting - changing font size, colour, emphasis, typeface etc Formula - selecting the range of cells to apply a formula Printing - selecting a range of cells to print To select cells in Excel: • • A Cell A Row A Column Adjacent rows Adjacent columns Entire Worksheet A Range of Cells Non Adjacent Ranges - click the cell click the row heading click the column heading drag down the row headings drag across the column headings click the select all button click the first cell and drag to the last hold down the Ctrl key while selecting the ranges

Spreadsheets Column Cell Row Range

Spreadsheets Formulas Excel formulas are basically mathematical equations Writing Excel formulas is done differently than formulas in maths Excel formulas are carried out on cells and not on numbers as in maths In excel a cell’s location in the spreadsheet is referred to as its cell reference In the example below, the contents of cell A 1 and cell B 1 are added (SUM) and as cell. C 1 is highlighted when the formula is entered, the result placed in cell C 1

Spreadsheets When you use cell references in Excel formulas, the formulas will automatically update when the data in the spreadsheet changes For example, if you realize that the data in cell A 1 should have been 50 instead of 20, you only need to change the contents of cell A 1 and the spreadsheet recalculates automatically This is the power of a spreadsheet

Spreadsheets • • In formulas, the following operators are used: Symbol Operation + Add - Subtract * Multiply / Divide The following functions are most commonly used: Function Use Sum Add Average Find average

Spreadsheets Formula Examples: • The contents of cell B 6 are multiplied by the contents of cell C 6 and the answer is placed in cell D 6

Spreadsheets It would be very repetitive if this procedure was necessary to calculate each row individually, so excel has a replication feature • Click the bottom right-hand corner of the cell containing the formula and drag down to include the other rows required The formula is replicated

Spreadsheets • The contents of cells D 6 to cell D 10 inclusive (D 6: D 10) are added (SUM). The colon means from the first cell reference to the second cell reference

Spreadsheets • Auto. Sum is a feature that guesses the numbers you want to add and shown a formula in the answer cell. Simply select the cell you want the answer to appear in and click the Auto. Sum button • A marquee (marching ants) is drawn around Auto. Sum’s guess

Spreadsheets If the guess is correct, click the enter button If it is wrong, click the cancel button Cancel Enter

Spreadsheets Charting is used to graphically represent data The trick is to keep it simple and use a number of charts to instead on a single chart with too much data on it • Select the data to chart

Spreadsheets • • • Click the Insert tab Choose the Chart category in the Charts group Select the Chart type in the drop-down list The chart appears on the worksheet. The chart can be edited and the chart category or type changed. It can also be selected and copied and pasted to another application

Spreadsheets Modifying Charts The Design tab: A chart can be moved by simply dragging it to another part of the worksheet. You can also click the Move Chart button in the Design tab to move a chart to another worksheet etc • The Type layout in the Design tab allows a change from a bar to a pie chart etc

Spreadsheets • • The Data group allows you to switch which data should appear in the rows and which should be in the columns The Chart Layouts group and Chart Styles group allows the data to be represented in different styles within the same chart type The Layout tab controls the insertion of objects and the positioning and formatting of chart titles, exes etc. It also controls the display of chart gridlines