If ever you find a website with a handy table of data you’d like to analyse in Excel, there’s a quick way of importing the data. Seriously, don’t spend ages manually typing out the numbers, or trying to fight with copy & pasting from a website to Excel. Instead, follow these simple steps on importing data from the web. I also show you how to calculate % change in Excel and some other descriptive statistics. Further, and given nursing strikes over pay being a hot topic in the news currently, I use nurses salary as the data source to explore.
Here’s my video run-through on the topic. In the first five minutes, you’ll learn how to get and import the data. In the rest of the video I explore descriptive statistics and analyse the nurses salary information. Read on further for step-by-step explanation and links I’ve used…
3 Simple Steps to Import Website Table into Excel
There’s a heap of websites out there with data to explore in Excel. Whether it’s country comparisons on Wikipedia, data tables in academic publications, or some random factoids you’ve found online. Given recent big news around nurses striking about pay, I was interested in exploring nurses salary information (weirdly, despite the central tenet of the strike being about pay, no news outlet or union spokesperson seemed to be talking about the actual salary figures!). I therefore use this salary information as my web-sourced data for demonstration… more in my video!
It can be frustrating if you don’t know how to grab the info quickly to make it useable in your spreadsheet! Two common yet awful approaches are:
- Trying to highlight the webpage, copy and then paste into Excel
- Manually reviewing and typing out the numbers and data points
Both misguided approaches are prone to errors, lead to disastrous results, headaches, and waste heaps of time!
Below I share three simple steps on how to do it right. If you’d like to follow along with the websites I use, here are the two links (also via screengrabs below): Nurses current 2022/23 pay scales and nurses 2015 salary on RCN website.
STEP 1: Open Get Data from Web Dialogue
Within the ‘Data’ tab of the ribbon, look within the ‘Get & Transform Data’ section. I’ve highlighted below the key button to use, which is ‘From Web’. Click on that and you’ll open up a dialogue box…
In your internet browser, navigate to the website of interest (e.g. see the nurse’s salary sites above). Copy the URL to your clipboard (Ctrl + A to select all, Ctrl + C to copy). Now you’re ready for Step 2…
STEP 2: Insert URL of Data Source
In the pop-up dialogue box, paste (Ctrl + V) the URL from the website which holds the data you wish to import. Then choose ‘OK’.
STEP 3: Choose the Table in the Data Navigator
In the final step, you’ll be presented with a ‘Navigator’ dialogue box. Here you can choose one or more tables to import into your Excel workbook. Again I’ve highlighted the key things to click below.
Some websites will have more than one option indicated by the table icon under the web address ‘folder’, as shown here. You can preview each simply by selecting it and viewing how it looks on the right. For our 2022/23 nurses salary info source, I selected ‘Table 0’.
Then it’s simply a case of clicking ‘Load’, then Excel will magically retrieve your web-based data and load it into a spreadsheet for you as a ready-made table of data (see preview below). In this way, you can then make use of the data for further analyses in Excel, without having to manually enter a ton of datapoints before you even get started. So what are those nurses salaries behind all the news lately?
What Are Nurses Salaries in 2022/23?
News articles have been abound with the Royal College of Nursing’s (RCN) decision for its members to go on strike. The first strikes went ahead in December 2022, then further strikes are occurring now in January 2023.
Why are nurses striking? The RCN have been quite clear: This is all about pay. In particular, they are seeking a 19% pay increase for nurses (5% above inflation), which they deem will “secure fair pay for nursing”. The government as their employers however are not meeting the pay demand. Hence the strike. See below a screenshot from the RCN website about their strike action for example:
Considering these strikes are all about nurses pay, it’s surprising that none of the news sources actually cite what salary nurses are paid. That’s information you have to find for yourself! I decided to review the latest 2022/23 nurse pay scales, plus also how salaries looked in 2015/16 for context, because it takes a new Band 5 nurse seven years to reach the top pay point.
I focused my analysis on the Band 5 to Band 7 range. According to analysis by the Kings Fund, over half of nurses are Band 6 or higher, but the biggest group is Band 5. You can find out more about what each nursing band means, from Band 5 to the top Band 9.
Below I share my tidied and consolidated data from those web sources, as demonstrated in my associated YouTube video. I show the situation in 2015/16 and 2022/23 in the yellow areas, alongside the banding and how many years experience a nurse at that band requires to go up the salary scale. In the final column, I calculate what a 19% pay increase looks like to demonstrate specifically what the RCN are asking for and going on strike about.
A newly qualified nurse on Band 5 in 2015 will have started on a modest £22k. Given increases in the pay bands over the years through negotiated pay awards, plus the movement of that person up through the Band 5 pay scale, that nurse would now be on £33k, a 52% increase. The RCN are seeking to move that to £39k this year. On the other hand, those nurses already with seven years’ experience in 2015, and not progressed their career in the higher bandings with most other nurses, will have seen just a £5k increase in their salary by 2022.
Now you know the salary scales behind the nursing strikes, what are your views on that? Whatever your views, I hope you’ve found this how-to in Excel and analysis of nurse salary info helpful!
For more free support in Excel, PowerPoint, Word, and more, please subscribe to my EATO YouTube channel for more. Liking the content? Please like and share with others who may also find it useful. If so inclined, supporting my work helps keep free content flowing. Want fantastic, time-saving templates for your own business? Then please get in touch to arrange a bespoke quote. Finally, please feel free to leave your feedback below, including what tutorials or templates you’d like created.
Kind Regards, Adrian