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