Linking web data to Excel

Say goodbye to copy and pasting. Connecting Excel to website data

There are times when having live data in Excel can be really useful. You may need the latest currency exchange rates, monitor your stocks, track house prices, the possibilities are endless.

Once you have established the data links you can refresh the data, also Excel will check to see if the data on the website has been updated, if so it will refresh to ensure you have the latest data.

Here we will look at how we can set up Excel to connect to a live website and it’s data. As the UK general election is just around the corner we have decided this will make a good example.

The worksheet we will create will be linked to the following website which is tracking the “most seats betting odds” for the 2015 UK general election.

http://www.oddschecker.com/politics/british-politics/next-uk-general-election/most-seats

To create a link to a website from Excel click the Data tab and in the Get External Data group click From Web.

bets datatab

The Excel web browser will open. In Address enter the website address and press GO.

webbrowser

In the Excel web browser you have to find the table you want to link to Excel. Each table is represented by a yellow box with a black arrow inside. If you hover over the arrow Excel will put a frame around the table to show which data the arrow represents. Click on the arrow and the arrow will change to a tick. Click Import in the lower right corner.

click tick

The below dialogue box will open. Tell Excel where you want the data and click Properties.

Linking web data to Excel

You can now tell Excel how often you want to refresh the data under Refresh Control. You can change more properties if you so wish. When you have finished setting properties click OK and Excel will import the data.

properties 2

The example below is the final result. We have worked with some formatting and created a chart. Now every time the odds change our chart and worksheet will update on refresh.

bets

As we mentioned earlier, this linking web data to Excel has a wide range of applications and should hopefully make copying and pasting website data a thing of the past!

 

 

Show Formulas in Excel

Make sense of someone else’s workbook

When you are given a workbook that has already been created it can be hard to work out which cells contain manually entered values and which cells are the result of formulas. So how do you show formulas in Excel?

Below is an example workbook, at first glance it’s not easy to see how the figures in Qtr 1 and Qtr2 were generated. This is a problem if you need to maintain the workbook or check it’s formulas.

show formulas off

Excel can reveal the true contents of cells – so its easy to locate formulas in the sheet.

show formulas on

How you can show formulas in Excel:

There are two ways to switch this feature on. Using the Ribbon click on the Formulas tab and then click the Show Formulas button

Show Formulas in Excel
Show Formulas in Excel

 

The button is a toggle switch so click it to turn the feature on. Click again to turn it off.

You can also switch this feature on and off using the keyboard shortcut: Ctrl + `  (this symbol is usually on the key to the left of the number 1 key, in the top left corner of the keyboard. It is the grave accent character).

Tip: You can print the sheet with show formulas switched on to make it easier to review the construction of your workbook.

Additional Resources

A Hidden Gem In Excel – Formula Auditing

Hide an Excel formula and protect your workbook from unwanted changes

Introduction to the language of formulas in Excel

Excel formulas not working?