Why Do I Want to Use Excel, Anyway

Why do I want to use Excel, anyway?

You really do want to use Excel when you have data and you want to calculate things or make graphs, which pretty much means whenever we do experiments with even a small amount of data.

Please remember that this program was written for business, not science, and that it has no brain. YOU are the one with the grey matter and 'garbage in, garbage out' applies. Always think 'Does that look reasonable?' first, before trusting it or printing it out.

Contents:

Why do I want to use Excel, anyway?

How do I open Excel?

How do I save my work?

How do I put data into my sheet?

How do I get it to calculate stuff for me?

What is the 'Nifty Short Cut' for copying a formula down a column?

How do I use Excels built in formulae?

How do I do graphs?

How do I change it so it plots V against I or P against I?

Hmm. Is there a less fiddly way?

Let's get fancy…

How do I open Excel?

Open Excel using the Start menu, then Programs then Microsoft Excel (or Microsoft Office then Microsoft Excel).

You will get this (depending on the version):

How do I save my work?

From the File menu, click Save As, type a file name for the workbook. Click Save.

How do I put data into my sheet?

You make tables in Excel just like the ones that you would draw for data when doing your experiment.

Let's imagine that you have the following data for a resistor - type it into Excel so it looks like this:

You should notice that the numbers change so that 0.00 changes to 0. You can change this back by highlighting the column and then pressing the 'increase decimal place' button.

How do I get it to calculate stuff for me?

Type 'Resistance' in the heading of the next column. [Check out the 'Let's get fancy' section at the end to work out how to put () in as well]

Click on cell C2 (underneath where you have typed resistance) and then press the = sign on the left of the formula bar. Then click on cell A2, then / on the keyboard then on cell B2 so that it looks like this:

Click OK and you will get a nasty looking thing that says: #/DIV/0! That's because it doesn't like dividing by zero, and neither should you. We'll let it ride for now.

We want to find out the resistance for the other pairs of values and you have a choice. You can repeat that for the rest of the cells (ok if you like that kind of thing and don't have anywhere else that you should be for a while), or you could do the good old-fashioned copy and paste - it works the same but slower OR you can do the 'Nifty Short Cut'.

What is the 'Nifty Short Cut' for copying a formula down a column?

You should by now have seen that your mouse looks like three things:

The mouse pointer looks like this when used for selecting.

The mouse pointer looks like this when used for copying/filling.

The mouse pointer looks like this when used for moving.

Move the pointer so that it rests on the little black box in the bottom right hand corner of cell C2 and looks like the copying/filling pointer above. Then double click. Hey presto, the formula is copied all the way down your table.

Except that you now have a horrendous number of decimal places in most of the cells. If you are saying 'What is horrendous about that number of decimal places' imagine measuring the circumference of the Earth to the nearest centimetre. Fairly unlikely, huh?

If you just click the 'decrease decimal place' button again nothing will happen because of the #/DIV/0! You can get rid of the offending item by just clicking on the cell and pressing delete or highlighting and deleting #/DIV/0! in the formula bar. Now highlight the numbers and reduce the decimal places to a decent number [2 or 3 because that is what was given in the data - probably 2].

How do I use Excels built in formulae?

Right, now we want the to work out the resistance of our resistor. We'll need the average, but Excel has that function, along with many others, built in.

Click on cell B9 and type 'Average'.

Click on C9 and hit the = again to the left of the formula bar.

Click the button to the right of 'SUM' and select 'AVERAGE'.

Excel looks for the likeliest set of numbers you might be wanting to average and says is that it. If you look at what it has done, it has selected C3:C8.

Click OK. The resistance should be 4.0Well done! That's the tricky bit over.

Formula symbols you will need in the future:

How do I do graphs?

Select the cells you wish to use for your graph - here it would be A2 to B8 - highlight them.

Select 'Chart Wizard' (if the button isn't there go to Insert then Chart.)

999,999 times out of 1,000,000 we will want an x-y scatter graph because it's scientific. I know 3D doughnut shaped graphs look much sexier but don't be distracted.

Step 1. Select X-Y (scatter). Select the points without the line (top box). Click Next.

Step 2. Click Next.

Step 3. In Chart Title highlight 'Current (A)' and delete it. Type ' Current /Voltage graph for a Resistor' then Enter. In Value (X) axis type 'Voltage (V)' then Enter, in Value (Y) axis type 'Current (A)' then Enter. Click on the Legend tab at the top of this box and click on the box with the tick in it (show legend). It should now not have a tick in it. Click Next.

Step 4. Select top dot - as new sheet. Highlight Chart 1 and delete it. Type 'IV graph'. Click Finish.

You can play with your chart and make it pretty. If you right click anywhere on your chart and select Format Plot area then you can change the background area (none is good!)

Now we can add a line and see what the equation is.

Click on any point and all the points should be highlighted. Click Chart on the menu line, then Add Trendline. 'Linear' should be selected already. Click the Options tab at the top of the box. Select 'Set Intercept = 0' and 'Display equation on chart'. Click OK.

The equation should be y = 0.2511x.

How do I change it so it plots V against I or P against I?

1. V against I instead of I against V.

Select the cells you wish to use for your graph - here it is A2 to B8 again- highlight them.

Select 'Chart Wizard'

Step 1. Select X-Y (scatter). Select the points without the line (top box). Click Next.

Step 2. Click on the Series tab at the top of the box then click on the funny looking button the right of 'X values=Sheet1!$A$2:$A$8'. This takes you to this screen:

Highlight the numbers in the Current column not including the title or the word 'Average'. Click the funny looking button again. Click on the funny looking button the right of 'Y =Sheet1!$B$2:$B$8'. This time highlight the numbers in the Voltage column but not the title then click that button again. Click Next.

Step 3. Add titles as before and remove legend. Click Next.

Step 4. Select top dot - as new sheet. Highlight Chart 1 and delete it. Type 'VI graph'. Click Finish.

If you now add a trendline, make sure that it goes through 0, you should find that the gradient is 4, as you would expect.

2. P against I instead of I against V.

Select the cells you wish to use for your graph - here it is B1 to D8 because that selection includes just current and power. Highlight them.

Select 'Chart Wizard'

Step 1. Select X-Y (scatter). Select the points without the line (top box). Click Next.

Step 2. Click on the Series tab at the top of the box then in the 'Series' box make sure that 'Resistance' is highlighted then click on the Remove as below. Click Next.

Step 3. Add titles as before and remove legend. Click Next.

Step 4. Select top dot - as new sheet. Highlight Chart 1 and delete it. Type 'PI graph'. Click Finish.

Hmm. Is there a less fiddly way?

If you like you can highlight the column that contains the numbers that you want on the bottom (x-values) and copy and paste them to a place below your original table. Then copy what you want to be up the side and paste it next to it. The only problem you will have will be if your columns contain formulae. Then it will come up with #REF! To fix it try again but don't paste. From the Edit menu select Paste Special, then Values. Then just do chart wizard and you won't have to mess around with the series bit at all.

Let's get fancy…

1.  and other fancy symbol. These, just like in Word, are Greek letters. Click on your Resistance box, the click in the formula bar. Type (W) - make sure that it is capital W - then highlight the W. Go to Format, Cells, Font andselect Symbol. A weird thing will happen. It will still say W in the formula line, but it will say in the column heading.
2. Renaming sheets. Point to where it says 'Sheet 1' then right click and select Rename (or go to Format, Sheet, Rename). Call it what you like.
3. Changing the lines on my chart. Instead of the statutory horizontal lines on your chart you can change it.

Point at anywhere outside of the chart and right click and select Chart Options. On the Gridlines tab select Value X axis Major gridlines.

Point at any of the grid lines and right click and select Format Gridlines. On the Scale tab try changing the values.

Point at the x axis line and right click and select Format Axis. On the Patterns tab select Minor Tick Mark type Outside. See what happens.