A LinkedIn user has made waves by showing how real-time data can be inserted into spreadsheets – and furthermore, just how easy it is.
A video uploaded to the ‘Excel Daily Hack’ account purported that an intern was adding population data for US states into a spreadsheet. The intern was doing just what most other people would (by Googling the answer then copying and pasting each value in).
However, the video went on to show another way that’s not only easier but vastly quicker as well.
In the video, the user first highlights the list of states, then in the main navigation clicks ‘Data’, and then ‘Geography’ (illustrated by an icon representing an unfolded map). Once this has been done, the map icon appears next to the name of each state, meaning the data has been formatted correctly and without conflicts or issues.
Something that’s not shown in the video is what happens when there are issues with the dataset. If Excel cannot identify the data correctly, the map icon will be replaced with a question mark. This is typically for one of two instances. The first is when the place has been spelled incorrectly; simply make the amend to turn the question mark into a map. The second arises when there are two (or more) places sharing the same name. For this, Excel will provide the user with the options so they can select the correct one.
Once all the data ready to go, an Insert Data box displays near the top of the list of places that, when clicked, opens a drop-down menu offering a host of geographic datasets, including country/region, largest city and households – among others.
In the LinkedIn video from Excel Daily Hack, the user simply selects the ‘Population’ option and the column auto-completes – with figures down to an individual level (i.e. Nebraska, for example, has a population value not of 2 million but of specifically 1,929,268).
The user in the video then goes on to use the steps above to auto-fill the Capital column (by selecting ‘Capital/Major City’ and even the state flag (by clicking ‘Image’) – all done in very little time and without having to skip between windows to research and manually add in each one.
