This series of five tutorials will show you how to do five distinct but related tasks in Excel. All five topics are vast in themselves, so the aim cannot be to explain any of them in full. The aim instead, will be to give an elementary overview of the subjects. Taking you through a worked example, which will link the five areas together.
Each topic will lead on from the previous one, so I will recommend starting with part 1.
The five topic areas are:
- Importing data from a web page into Excel.
- A simple example of how you can use Excel to validate data.
- Build a pivot table from the data.
- Build a chart from the pivot table.
- Make changes to the table and the pivot chart.
1. Importing data from a web page into Excel.
In this example, I will import some data from the Met Office website. The data is a table of monthly maximum temperatures. – you can get a copy of the data from here.
The figures start in the year 1910 – January through to December.
For each year, the Met Office have also calculated average temperatures for each season. Winter, Spring, Summer and Autumn, as well as an average annual temperature.
| || |
Select the data you want to copy, by highlighting the data with your mouse. Making sure you include the headers, the names of the columns such as year, JAN, FEB, etc…
Copy the data, and switch over to Excel for the next step.
| ||Once inside Excel, right click on the top left cell called A1 and choose paste special from the menu. |
| || |
A dialogue box will then ask what kind of data you want to paste.
Select ‘Text’ and click the ‘OK’ button.
You will see that Excel has placed all the data into the first column, – not so useful if you want to analyse it.
So now we need to split the data into columns.
| || |
- Select the whole of column ‘A.’
- Choose the tab called ‘Data’ on the ribbon.
- Select ‘Text to columns.’
| || |
In this example, the preview looks OK.
Click the ‘Finish’ button, as you are happy with the way Excel is going to import your data.
| || |
Often you will find that the data columns are too wide or too narrow for your data.
Here’s a useful shortcut to sort this problem out.
- Click in the top left corner of the worksheet, on the black triangle, so the whole sheet is ‘selected.’ (Arrow 1.)
- Scroll to the right, until you can see the last column that has data in it.
- Double click where the arrow 2 is pointing, on the line between the two columns.
All the columns are now the right width.
| We have now imported the data and improved its presentation a little. |
In the next tutorial, we will start validating and checking that the data is correct and accurate.
For a monthly copy of our blog ” Excel, The Universe and Everything” click here.