Excel .xls files Module 4To help in conjunction with your elearning and your .diag tests to help prepare you for the European driving license exams along with teacher and student demos, help files within excel and use of youtube for homework revision on the ‘basics of microsoft office’ e.g ‘Excel for beginners’

Spreadsheets

Rows go side to side and columns go up and down. Cells are the individual boxes. Type in text, numbers or formula once the area is set up for each function.

Title bar is the top one. Cell address bar is where u type in data. Active cells are the ones that are highlighted. You have sheets that u click at the bottom to see the active sheet and u can add extra sheets. By right clicking.

Before you begin click view menu and make sure in toolbars that standard, formatting and drawing are ticked.

When u type something into a cell hit return or the sideway arrows tab button

Working with cells On the bottom right of a cell u’ll see a small square.

When u hover over it, it gives you automatic filling in options.

Tools then options to show all menus + when u see this cursor it means u can copy or

complete a sequence A Fatter + means u can select cells | the beam pointer on formula toolbar when u edit a cell When u c the double sided cursor u can resize cols and

rows Home brings u to col A. Ctrl Home A1. f5 then go to a

cell Hash by 4 means cell doesn’t fit. Double click the

dividing line. CTrl and left click will select random cells

Formating cells Select the cells first, click format

then the type of cell u want e.g number format for number only entries.

If putting in dates choose number and then in the number tab choose custom

Using Formulae All formulae must start with an =

sign Add two numbers by typing = in

empty cell and then click first number then + then click second number and hit return or the green tick.

To see formula rather than the result click ctrl and `

To centre text click cells then the button with a inside called merge or the align drop down arrow.

Order of Operations

Order of operations are as follows when entering formulae.

1. B rackets first2. Exponential – e.g number3. Multiplication4. Division5. Addition6. Subtraction7. Click ctrl wavy line on your keyboard

On screen Errors #Name? if the text in the cell is not

recognised #Value! Impossible calc using text #num invalid number values in

formula #div/0! Contains 0 or empty add and auto fills and sorting text control in format align tab for

merging cells.

Extra functions Rows and columns insert and

deleting and auto formats. Ctrl and z is for undo. For Todays date type =now() or

=today()– changes with the change on the computer clock– Ctrl ; gives u todays date only

Using functions Type number in, then on blank cell

click formula then the function key, click sum, click red arrow box, highlight numbers u want to add, then click red arrow box again, click ok.

Relative copy By clicking and dragging a formula

with autofill e.g my class template if will increase both values by one

Fixed values or absolute cell

E.g if you want vat to stay the same but added to differing numbers.

To fix or lock the value we click f4 on top of the cell name $ sign show that its fixed

Or put a $ in front of the letter and then the number u want locked. E.g $H$4

Cell protection If u want a formula to never change

click format, cells, protection tab and tick lock

Freezing panes. Click the cell after u want the split and click window freeze frame

move sheets by click and drag Save file as a webpage .html

If statements If(a4>17, ‘allowed to vote’,’ not

allowed to vote’) First comma then allowed to vote yes

into this cell, otherwise ‘not .. Goes into this cell

Click function button and click if the type in formula e.g c7=b7 false

If true “correct”. If false “incorrect”

Printing Tools page set up tick gridlines,

headings of rows and col tick? File page set up to change margins Set print area before printing and

print page 1 to 1 first to test

Charts Select chart Select data before use chart wizard Chart options how presented Location on its own sheet or current sheet see

sheet Chart title click to change title Legend click to change position of the words

and colors Data labels tab to enter diff words and % in the

name Click any part of the graph to change the

format of that part

Charts To change chart type click chart, then

chart Custom types for two axes Click series tab to keep a set of

figures to a certain axis Click collapse box and select correct

cells

Common Errors To change user info go to tools, then options, then general Per year in descending not ascending order using sort button. It is considered good practise when creating lists to ensure that

cells bordering the list are left blank To insert col between F and G columns click on G column

and insert from menu tab To set row one so always visible: click row heading 2 to select

the row, click window and freeze panes. To count cells click insert, function, count To round to 0 Decimal places click insert, function, round, in

num-digits list type 0 To display YES Insert function, select IF function, in logical

test list type e24>e25. In the value_if_true list type yes. In the value_if_false list type no. Press enter

Common Errors To get the reference not to change cell e28 to

e$28, enter To apply thousand separator select the range then

click the comma button. To copy only formatting select cell 31 click format

painter, the mouse changes to a paintbrush icon, select the new cell range by using the mouse

To create chart use insert, chart, standard and then bar, click and drag chart to better position.

To enter a word to left section of header go to page set up, header, custom header, type draft on the left section, click ok x2

To insert page number in centre of footer: Custom footer, click the second button from the left #

Common Errors To get the row 1 to be shown on top

of all pages simply: page set up, sheet tab, click rows to repeat at top, click anywhere in row 1 enter and then ok

Where to click to display how worksheet will appear when printed? File menu, Print preview /the system menu

Use the paint brush symbol to copy the formatting but not the text

Use insert function if() logical put in yes then no for a range : or < > question

Conclusions

Understanding cells Formats Formulae Operations Functions Printing Charts Common student errors for the ECDL

exam

Questions Can you create a new presentation

with 3 sheets? Can you do basic maths using excel? Can you use auto sum function Sort a list alphabetically ascend and

descending? Print a set area? Do a basic graphs and change it into

a new graph format? Formulae using IF and Fixed values?

Objective Type this presentation into your own words

and try and demo after each slide. Create a teacher planner using complex

formulae Create and income and expenditure account

using excel Create a chart using excel. Be able to sort ascend and decend, fix cells

and do basic maths using excel. Demonstrate use of If and fixed values Understand some new key terms in Excel?

Excel Projects and Tutorials

10 min Excel Tuorial Full Excel Tutorial