Excel; getting a single value from a large data feed


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Maybe you don’t want a full list of stock prices or exchange rates to lookup various values.

All you want is a single stock price or exchange rate to update your worksheet.

That’s certainly possible by narrowing down the query so it returns a single value and no more.  Select a filter to narrow down the data selection to a single row then select a single column from that row.

For our example, we’ll use the exchange rate data feed demonstrated here.  Make a new version of the feed by selecting the existing feed and choosing Duplicate.

img 57fa7323b6cd8 - Excel; getting a single value from a large data feed

Then rename the duplicated query so it’s clearer than just adding a number at the end (Excel’s default).  In the Query Editor choose Properties and update the query name.

excel getting a single value from a large data feed 10915 - Excel; getting a single value from a large data feed

To select a filter, choose the column to filter by and select the value you want to appear.  In this case, the targetCurrency column and the GBP (UK Sterling) value.  The search option makes it easy to find what you want from a long list.

excel getting a single value from a large data feed 10916 - Excel; getting a single value from a large data feed

Now we only have a single row containing the value you want.

excel getting a single value from a large data feed 10917 - Excel; getting a single value from a large data feed

To reduce the columns list, go to Manage Columns and de-select all but one column.

img 57fa735de52e1 - Excel; getting a single value from a large data feed

And now there’s a single value in the Query.

excel getting a single value from a large data feed 10919 - Excel; getting a single value from a large data feed

Close the Query Editor from Close & Load to drop the value into a worksheet, as usual.

excel getting a single value from a large data feed 10920 - Excel; getting a single value from a large data feed

Then link to that cell from other worksheets.

excel getting a single value from a large data feed 10921 - Excel; getting a single value from a large data feed

subs profile e1563205311409 - Excel; getting a single value from a large data feed
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address