180 likes | 195 Views
Learn essential Excel skills like formula usage, cell formatting, error handling, and number formatting. Practice selecting cells, applying formatting, and correcting errors. Develop proficiency in basic spreadsheet tasks.
E N D
Spreadsheets Formulae
Working with spreadsheets Must be able to format cells Should be able to use formulae in spreadsheets Could understand and correct error values
Ribbon groups used • You will be using the Font group and the Number group on the Home tab. Character formatting Border Comma style
Using formulae • Follows similar BODMAS rules that you use in maths: Brackets, multiply/divide, add/subtract () * / + -
Create a worksheet to do simple calculations • Open Excel • Enter the following data: • Save the workbook with the name Sums
Selecting cells • Click on single cells to select them. • Click and drag to select a range of cells or hold the shift key while you select the last cell in the block. • Click a row or column header to select either a row or a column. • Click the intersection of the rows/columns to select all cells in the worksheet. • Select non-adjacent cells by holding down the Ctrl key
Formatting cell contents • Bold • Italic • Underline • Double Underline
Inserting a border • Hold down your Ctrl key and select A4, C4, E4 and G4 • Click the arrow to the right of the border tool and select Top and Thick Bottom Border: • Click the right hand side of the Border button again and select More Borders change the line colour to red and select the top and bottom borders:
Using the Format cells dialogue box lets you make many different changes without having to select individual buttons on the ribbon. Dialogue Launcher Save your work as Sums.
Entering formulae • Always start with an equals sign (=) • This tells Excel that you are about to enter a formula. • Enter a formula into cells A4, C4, E4 and G4 which will calculate the totals. • Remember by using formulae, if the numbers change the totals will be recalculated automatically. Change A2 to 75. What is the answer now? • Save your work.
Standard error values and correction actions • If you try to make Excel do a formula it cannot calculate, for example, using a non-numeric value, the error #VALUE! will appear. • Change the formula in C4 to read =C1-C3 • Another error will occur if you if you try and divide a number by zero - #DIV/0! When you divide anything by zero it is infinity so Excel shows the error. • Replace the contents of G3 with 0 • Use the undo/redo buttons on the quick access toolbar to undo or redo an action.
Tip: A space is text (non-numeric) even though it is invisible! Tip: Some other standard error values: ##### This indicates that the cell contents cannot be displayed because the column is too narrow. #NAME? This is displayed if Excel does not recognise the text in a formula. #NUM! This is displayed if invalid numeric values are used in a formula. #REF! This is displayed if a cell referred to in a formula has been deleted.
Starter: This image shows six error values. Explain what each means.
Formatting numbers • Text in a cell is left-justified. • Numeric data is right-justified. • Numbers can be formatted in several ways. An example is to include a comma to indicate thousands. • Click on cell A2 and drag to cell G4 • Right click the selection and choose Format Cells to open the dialogue box.
Choose the Number tab and make sure that Number has been selected from the category list. • Click the checkbox Use 1000 Separator (,). • Click OK • Try entering a value greater than 1000 to see how it is displayed. • Save and close the workbook. These buttons can also be used for the same functions
Assessment on progress • Complete exercise 1 • You will be working independently • Do not copy from anyone • Do not help anyone • Your teacher cannot help you • You may refer to your notes/handouts • You are allowed to use the Microsoft Help feature
Progress checklist 2 Name: Date: • Do I know what an Excel formula is? • Do I know the basic arithmetic symbols used by Excel formulae? • Can I enter a formula in a worksheet cell? • Do I know the different ways to select cells in a worksheet? • Can I apply simple formatting to a worksheet cell? • Can I add borders to a worksheet cell? • Do I know what Excel’s standard error values mean? • Can I apply different formats to numbers and do I know what they mean?