Using Excel for your assignments

(1)

Using Excel for your assignments

This document covers the basics of using Excel to perform simple data analysis and represent that data visually.

Excel is a very powerful data analysis tool. More detailed training material for using more features in Excel can be found on https://staff.brighton.ac.uk/is/training/Pages/Excel or on www.office.com/support

Last updated By Faye Brockwell On Feb 2015

(2)

What can you use Excel for?

Excel is used for data analysis. Some things you can use Excel for in your studies include:



Analysing survey responses



Analysing test results



Create a chart or graph to represent your data visually for inclusion in a written report or in presentation slides

How to open Excel

On a university computer double-click to open the Applications folder and then double click on Excel 2013).

How to save an Excel file to your university network drive

1. Click on the File tab and click on Save As.

2. Double click on Computer

3. On the left of the pop-up window, scroll down and click on Computer.

4. Click on your username.

5. Type a name for your file and click Save.

How to save an Excel file to your memory (USB) stick

Memory sticks can be bought at the Computer Store or at Library Help Desks. To save your Excel file to a memory stick:

1. Insert your USB stick into the appropriate slot on the computer.

2. Click on the File tab and click on Save As.

3. Double-click on Computer.

4. Double click on the icon representing your USB stick or device in the window on the right and click on Save.

(4)

Worksheet basics

When you open Excel, an empty worksheet is displayed.



The worksheet is made up of columns (marked with letters) and rows (marked with numbers).



The box where a row and a column intersect is called a cell.



Each cell holds one piece of data.



Each cell has a unique cell reference made up of its column letter and row number (e.g. cell B2 is in column B, row 2).

Each Excel file can have a number of different worksheets. Think of each worksheet as a separate page. Each worksheet has a tab at the bottom left of the screen.

You can rename each sheet to keep track of its content. To rename a worksheet:



Double click on the worksheet name



Type the new worksheet name and press ENTER on your keyboard.

Typing data into your Excel sheet



Click once in the cell and type what you want to appear in the cell.



Press ENTER to move to the next cell down column OR press the TAB key to move to the next cell in the row to the right.

To edit data in a cell



Double click in the cell.



Click to position the insertion point where you want to start typing.

Column A

Row 1 Cell B2

Row 1

Cell B2

(5)



Type your correction.



Press ENTER on your keyboard.

To delete the content of a cell



Click once on the cell.



Press DELETE on the keyboard.

Using Excel to analyse data

Excel is very useful for performing calculations on large sets of data, such as survey responses or test results. To analyse such data:



Set up a table to record your survey or test results (see instructions below).



Use a pivot table to summarise those results (see instructions below).

Recording your survey or test results



Type the results as a table on a new worksheet (as shown below).



Each column will represent one question on your survey or one variable on your test.



Each column will have a unique name (try to keep it concise).



Each row will contain the response for one person on your survey or the result from one test. See the example below for a survey.



When typing your results,

be consistent with how you type your data.

For instance, if the answer to a question can be “yes” or “no”, choose whether to type Y for yes, or “yes” and stick to it. Do not sometimes type

“Y” and sometimes type “Yes” as this will make it difficult to analyse the results.

(6)

Analysing your test results

Excel is very powerful and can do lots of things. Here we will show you the basics of analysing data taking the example of counting the survey results shown above. The easiest way to do this is to use an Excel pivot table.



Click anywhere in your table of data (e.g. click anywhere on your survey results)



Click on the Insert tab and click on the Pivot Table icon.



On the pop-up window that appears, click on OK.

Excel will display a new worksheet with a pivot table field list on the right of the screen.

The fields listed at the top of the pivot table field list correspond to the column headings in your table of survey results.

To use the pivot table:

1. Drag any one of the fields from the top of the pivot table field list to the VALUES box on the bottom right.

This will display the count of the total number of responses in your survey on the left of the worksheet.

Our survey has 8 responses.

Note: in this example we have started by dragging the Smoker? field down to the values field. This shows as “Count of smoker?” in our pivot table. This is misleading, as it looks like it is telling me how many people in my survey were smokers. This is not the case. “Count of smoker”

here is showing me the count of how many responses appear in my

“Smoker?” column on my table of survey results. In the survey response table there are 8 responses in the Smoker? column – some are “yes”,

(7)

some are “no”, but we cannot see how many in our pivot table until we do step 2 below.

2. Drag the field you want to analyse from the top of the pivot table field list to either the COLUMNS or ROWS box on the bottom right.

E.g. if you want to count how many smokers responded, drag the Smokers? field down.

If you want show the breakdown of your results by age group, drag the Age field down.

This will break down the total count displayed on the left of the worksheet according to the survey results in that column.

In this example, we have 8 responses, of which 3 were non- smokers and 5 were smokers.In this example, we have 8

responses, of which 4 are in the 18-25 age group.

3. To break your data down even further, simply drag another field from the top of the pivot table field list to either the Column labels or

Row labels box on the bottom right.

In this example we have the breakdown of the survey by age group and whether the respondent is a smoker.

4. To ask another question, drag the fields from the Row Labels and Column Labels on the bottom right back up to the list at the top right.

This will leave you just with one field in the Values box at the bottom right and the total number of survey responses

showing on the left of your worksheet.

Follow steps 2 and 3 again to ask another question of your

(8)

data and show the results on the left of the worksheet.

5. To show your results as percentages, rather than actual values, click on any of the numbers shown in the PivotTable.

6. Click on the Pivot Tool Analyze tab on the ribbon.

7. Click on Field Settings.

8. Click on the Show Values As tab.

9. Click on No Calculation and click to choose an option from the drop-down list

(e.g. to show each value in the pivot table as a

% of the overall total, or as a % of the column etc.)

10. Click OK.

The instructions above are a basic introduction to using Excel pivot tables for analysing your data. For more information about using pivot tables, see https://staff.brighton.ac.uk/is/training/Pages/Excel for detailed training guides that you can work through at your own pace.

Creating a graph

With Excel you can create a chart or graph to represent your data visually for inclusion in a written report or in presentation slides.

Common chart types

The types of chart you can create include the following:

Column chart

This type of chart is used to compare two variables.

E.g. how people

responded to the question

“do you smoke?” by

age group.

0 1 2 3 4

18-25 26-35 36-45 46+

Number of people

Age

Q4. Do you smoke?

Yes No

(9)

Pie chart

This type of chart is used to show proportion.

E.g. the breakdown of smokers by age group.

Line chart

This type of chart is used to show trends over time.

E.g. the change in birth rate from 2007-2010.

Source: Office

for National

Statistics¹²

How to create a chart in Excel

1. Type your data into an Excel worksheet (see page 2).

1 Office for National Statistics (2011), Births and Deaths in England and W ales, 2010, available at: http://www.ons.gov.uk/ons/rel/vsob1/birth-summary-tables--england-and- wales/2010/index.html

2 Office for National Statistics (2009), Births and Deaths in England and W ales, 2008, available at: http://www.ons.gov.uk/ons/rel/vsob1/birth-summary-tables--england-and-

660,000 680,000 700,000 720,000 740,000

2007 2008 2009 2010

Birth rate in England & Wales

Number of births 34%

33%

Smokers by age group

18-25 26-35 36-45 46+

(10)

2. Check that your data is summarised.

Your data must be summarised before you can create a chart or graph.

So if you have a list of survey responses or test results, you must first summarise that data, by using a pivot table, for instance (see pages 3- 5).

3. Use your mouse to select the data to include in the chart.



Point at the centre of first cell to include in your chart (this will usually be the empty cell to the top-left of your data). Your mouse pointer will be a white cross.



Hold down your left mouse button and keep it held down whilst you move your mouse to the last cell to include in your chart.



To select 2 ranges in your Excel spreadsheet that are not next to each other, use the technique above to select your first range, then hold down the CTRL key on your keyboard and keep it held down whilst you use the technique above to select the second range.

4. Click on the Insert tab and choose which type of chart you want (or click on

Recommended Charts to see what chart type Excel recommends for your data).

To change the layout of your chart

To add a title or values to the chart, for example.



Click once on the chart border to select the chart.



Click on the Chart Tools Design tab



Click on Quick Layout to open the Chart Layouts gallery.



Click to choose the layout you require.

(11)

To edit text on a chart

The chart or axes titles, for instance.



Choose a chart layout that has a chart title or axes titles (see above)



Click once on the text you want to edit, then triple-click on the same text (this highlights the existing text)



Type the text you require.

To change the size of a chart



Click once on the chart border to select the chart.



Point at one of the corners of the chart border. Your mouse pointer will change to be a double-headed arrow.



Hold your left mouse button down and move your

mouse diagonally up and right to make the chart bigger or diagonally down and left to make the chart smaller.

Copying and pasting into Word and PowerPoint

You can copy and paste data tables and charts from Excel directly into your Word essays and PowerPoint presentations.

To copy a table of data



Use your mouse to select the cells you want to copy (see page 7, step 3)



On the Home tab, click on Copy .



Open your Word document or PowerPoint presentation and click so that your insertion point flashes where you want the Excel table to appear (it is best insert your table into a blank line, so insert a blank line if

necessary).



On the Home tab (in Word or PowerPoint), click on Paste.

To copy a chart



Click once on the border of the chart to select it.



On the Home tab, click on Copy .



Open your Word document or PowerPoint presentation and click so that your insertion point flashes where you want the Excel chart to

(12)

appear (it is best insert your table into a blank line, so insert a blank line if necessary).



On the Home tab (in Word or PowerPoint), click on Past

Where to get help and more information

There is a lot more to Excel than can be covered in this document. More Excel training materials, including detailed training guides for Excel pivot tables and Excel charts can be found on our website:

https://staff.brighton.ac.uk/is/training/Pages/Excel

Using Excel for your assignments