By Mark Dawes (February 2025)
I have used Microsoft Excel for years. One of the things I like about it is how easy it is to get to know it. A new user can start with a handful of basic functions and can then learn new things as and when they are ready to branch out into something more sophisticated (often because they want to do something new).
Microsoft continue to implement new features in Excel, and while I don’t currently have a use for some of them, in this blog I will write about two new things that I am currently finding useful, particularly when preparing lessons for, and teaching, Core Maths classes.
Data from image
Getting data from an image into Excel has been frustrating in the past. Retyping the numbers is boring and prone to error.
Here is a screenshot of the populations of the “top 10 most populous countries”, on the United States Census Bureau website. I want to have these in Excel.
I find it straightforward to copy the image and to paste it into Excel (so I can check the results are accurate). Then, click on a cell and select Data -> From Picture -> Picture from Clipboard (as shown in the image below).
Excel asks about cells if it is unsure of the correct figures, and these can be edited individually if necessary. Then it puts it into the spreadsheet. When I did this with the population data shown above, the spreadsheet looked like this:
There are no errors, and it takes only a few seconds to do it.
The ‘checking the data’ stage is an important one, because it isn’t (yet?) perfect. For example, here is a screenshot of table from a Daily Telegraph article about air passenger duty:
The original (above) was rendered like this:
It clearly couldn’t cope with the pound signs! These were, however, easy to edit, either before or after inserting the data, and this was still a time-saver, because I didn’t need to type the text.
Convert to Geography
If you start typing place names in Excel, after seeing three of them it offers ‘Convert to Geography’ as an option.
If this is clicked, the places are given a ‘map’ symbol to show that they are being treated as geographical places. More can then be added to the list:
This works for countries, cities, towns and villages, both in the UK and elsewhere in the world.
You will see two places that lack the geography signifier: that is because one is extra-terrestrial and the other is fictional (though beware of typing ‘Narnia’, because Excel auto-corrects this to ‘Narni’, a town in Italy!).
Excitingly, we can now do some things with these ‘geography’ data types.
Clicking on the map symbol in a cell pulls up information about the place. More usefully, selecting all of the cells gives a List symbol in the top right, and this has lots of information about the places. From this list we can easily select the information we want. Some of this is particularly relevant to core maths, such as population, area, GDP, etc.
Here is the population of each of these places:
I love the way it has this information regardless of the size of the place (whether a country or a village).
We can combine this with other information (such as the area, given directly by Excel too) and then carry out calculations (here I have divided to give the population density).
It is clear here that area is not available for every place (perhaps only large towns upwards?). Is it a surprise that the population density of Cambridge is higher than that of China?
Units are not mentioned here. The areas are actually in square kilometres, so perhaps this is done according to the settings on the computer.
If you only want a single place then we can tell it to make it geographical by going to Data -> Data Types -> Geography:
Putting it all together
I saw a graphic on X (formerly Twitter), that claimed to show how many bicycles there are in different countries. Unsurprisingly, China (with its huge population) had the most. My Core Maths class wanted to compare this figure to the population.
I used the ‘data from image’ feature of Excel to get the number of bicycles in each country (and checked it matched the original image) and then used the ‘geography’ feature of Excel to get the population. The students then divided and adjusted the display to show 2dp. Here is what they found:
They were not surprised that the Netherlands came top when we looked at ‘bikes per person’ (lots of good cycling infrastructure there). The figure for India seemed really rather low. This led to further exploration (and alternative sources of data) to see whether this could possibly be accurate.
Finally, I mentioned a hit song (from 2005) from Katie Melua called Nine Million Bicycles. This has the lyrics: There are nine million bicycles in Beijing, That's a fact, It's a thing we can't deny.
We were then able to use Excel to check this out.
This figure of 0.41 bikes per person in Beijing is fairly similar to the figure for China as a whole (0.32), so it seems plausible.
One student in the class divided the population of Beijing by that of China and found that fraction of the bikes in the whole of China. The result was 6975482.703, and he decided this should be rounded to 7 million (which, as he pointed out, doesn’t scan as well as 9 million!).
I would love to hear how colleagues make use of these tools, and about other new features that are useful for Core Maths teachers.
Links:
コメント