ideas to work in excel the basics (multiplying, sum function) spinners data validation conditional...
TRANSCRIPT
![Page 1: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/1.jpg)
Ideas to work in excel
The basics (multiplying, SUM function)
Spinners
Data validation
Conditional formatting
![Page 2: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/2.jpg)
The basics - multiplication
If we have 100 tickets at £10 each then we have a revenue of £1000 - we simply multiply them together
=b2*c2
![Page 3: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/3.jpg)
Taking it a step further
Now at a nightclub there is more than 1 kind of ticket - you’ll have a guest list and student discounts as well.
=b2*c2=b3*c3=b4*c4
![Page 4: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/4.jpg)
The SUM function
Now we have worked out all the revenues from differenttickets then we can work out the total revenue
The icon that looks like a big letter “E” is called “autosum”
We’ll use this icon on the next slide
![Page 5: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/5.jpg)
We have written the word “total” in cell C5
Now that we are in cell D5 - we just click the “E” button.
A flashing dotty line appears around the cells that are to be added up
and then clicked on cell D5
![Page 6: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/6.jpg)
Our current position
So far we have managed to create a simple spreadsheet
Let’s try and make it look good now!!
![Page 7: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/7.jpg)
Back to main menu
Carry on
![Page 8: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/8.jpg)
The spinner
We are going to create a little button that will control cell b3 - it will look like this:
By pressing the arrows up and down theprice will change in cell b3
![Page 9: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/9.jpg)
To create spinners you’ll need to use a new toolbar
From the drop down menus select “view” then “toolbars”and finally “forms” – a new toolbar will appear.
Press the “spinner” button and then you can draw a spinner on the spreadsheet – your cursor will turn into “crosshairs”
Now draw a spinnerlike in this image
![Page 10: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/10.jpg)
Formatting a spinner
Just because we have drawn a spinner doesn’t mean that it is working yet we have to tell it what to do!!
Put your cursor on top of the spinner
Now right click your mouse and select “format control”A new window will open like on the next slide
![Page 11: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/11.jpg)
The format control window
For our spinner we’llset the min price at£3
and the max price at £15.
Incremental change means how much it goes up by when you click the spinnerarrows. Set it to 1
3
151
![Page 12: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/12.jpg)
Finally – “cell linking”
So we have a spinner, we’ve set the values, now wejust need to tell it which cell it applies to.
In this case we want the spinnerto change the value of b3
So on the format control click on this button
![Page 13: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/13.jpg)
Finishing off
You’ll now have a screen that looks like this
Just click on cell b3
You’ll notice what happens up here
$b$3
Finally click this button
You now have a fully functional spinner – try it
![Page 14: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/14.jpg)
Back to main menu
Carry on
![Page 15: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/15.jpg)
Data validation
We are going to set up the spreadsheet so only certain data can be entered in certain cells.
Let’s imagine ticket prices will always be between £5 and £15
We need to set cell B3so that we can’t enter impossible data
![Page 16: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/16.jpg)
Setting it up
Click on cell B3
Then select “data” from the drop down menus and click on “validation”.
You’ll now see a window that looks like this
![Page 17: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/17.jpg)
Changing the validation
At the moment we are allowing “any value”. Click on the arrow
and change this to “decimal”
Set the minimum to 5
5
Set the maximum to 15
15
Press “ok” then try enteringthat isn’t a number between 5 and 15
![Page 18: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/18.jpg)
Back to main menu
Carry on
![Page 19: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/19.jpg)
Conditional formatting
Consider the spreadsheet below
We are going to set it up so that if the profit figure in cell C2
is negative then cell C2 will turn red
![Page 20: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/20.jpg)
Making it happen
Click on cell C2 Then go to the drop down menu “format”
Then select “conditional formatting”, a window will appear
![Page 21: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/21.jpg)
Doing the settings
First of all we need to change “between” to “less than”
Simply click this arrow and select “less than”
Then type in a zero
0
![Page 22: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/22.jpg)
Changing the format
Simply select the format button
Then go to “patterns”
And finally set the colour as red,pressing “OK” to confirm
That’s it - simple, but makes agood GCSE project
![Page 23: Ideas to work in excel The basics (multiplying, SUM function) Spinners Data validation Conditional formatting](https://reader035.vdocuments.us/reader035/viewer/2022062307/551ba19c55034675548b4683/html5/thumbnails/23.jpg)
Back to main menu
All material copyright
www.ictgcse.org.uk © 2002