UserZen

When integrating web analytics, usability testing data and other dynamic data, I need to keep my numbers fresh and up to date.  It is important that the numbers are updated regularly. Excel can import the data every time you open the file or even every minute.

Here is an article on how to set up your spreadsheets to include online data automatically.

Originally posted on  How-To Geek

Want to use live, updated data from the web in your spreadsheets?  Here’s how you can import data from the web into Excel 2010 to keep your spreadsheets up to date quickly and easily.

Make a Webified Spreadsheet

To add dynamic data from a website to a spreadsheet, click the From Web button under the Get External Data section of the Data tab in Excel.

Enter a website address that you want to get data from, and click Go. The page will load in the preview box, and you might have to scroll to find the data you want on the page.

You’ll see a small arrow beside any web tables you can import into Excel.  Click the arrow to select the data you want, and then click the Import button on the bottom of the dialog.

You’ll see a Downloading message as Excel gets the initial table data from the site.

Select where you want Excel to place your web data, and click Ok.

You’ll see a message in the spreadsheet that Excel is getting the data.

After a few moments, your web data will appear in Excel just like normal.  You may end up with a few extra cells and columns with unnecessary data, so feel free to remove any data you don’t want to use.

Now you can manipulate the dynamic data just like you would any other Excel data.  You can use it in Graphs, Sparklines, and Formulas. Sparklines are a new feature in Excel 2010 and you might want to check out how to use them.  The great thing is, all of these will will automatically update whenever your web data is updated.

Refresh Your Data

If you’re concerned your data might be stale, click the Refresh All in the Data tab.  This will query the website for the latest data and update your spreadsheets.

Or, if you’d like to make sure the data is automatically refreshed more often, select one of your dynamic cells in Excel and then click the click the Properties button under Connections in the Data tab.

Check the Refresh every box, and enter the number of minutes you want.  By default, Excel will refresh the data every 60 minutes, but you can make it update much more often.  You can also select to have Excel update the data every time you open the file.  This way you’ll always have the latest data.

If you’re using static data from the web in Excel, such as the weights of minerals or the land area of states, you can even turn off the background refresh so Excel won’t be connecting to the internet unnecessarily.

Conclusion

The internet provides treasure-troves of data ready for you to manipulate and use as you want, and with this feature you can use Excel to help you use online data for your work.  From sports scores to melting points of metals to up-to-date exchange rates around the world, this is a great way to always have the data you need without having to enter it by hand or update it when something changes.

If you’re using Excel 2007, here’s our tutorial on Copying Website Tables Into Excel 2007 Spreadsheets.

Posted by Patrick Neeman at Usability Counts

One of my friends runs an online marketing education conference. Social Media is the new hot thing. I think a couple of years ago, it was search engine optimization, and please don’t ask about my opinion on that. Anyway, his blog network is teeming with posts about Social Media. The top request for education is that newfangled “Twitter thing” and tips about “Facebook.”

I met one of the social characters at one of these events, which I thought was the coolest thing. Nevertheless, the mass market was indifferent, and still doesn’t get the whole CNN call for tweets. Whatever. Nobody cares, right?

I like posting on Facebook as much as the next social media geek. I think last Monday, I talked about my new haircut. But, I recognize that posting about what I’m going to eat on Twitter doesn’t make me some kind of expert. It just means I use it. I don’t charge an arm and a leg for my advice, and I’m still amazed at the impact of Social Media on sites even though some of my friends consider me ahead of the curve. Myself and a few of my friends have been lucky to work in some Social Media environments (MySpace, for example), and even we don’t consider ourselves experts.

Social Media and even User Experience experts shouldn’t be able to call themselves that if they’ve been on one or two panels and read a book. They should have some successes and failures behind them and grown to tell the story. The truly great experts not only know how to leverage their personal brand, but point out the obvious while doing it, for free. Here are some ways to tell if your Social Media Expert is full of crap:

Your Social Media Expert spends more time blogging than working.

Self promotion can be high art on the web. Tila Tequila? Dane Cook? I mean, who really laughs at Dane Cook’s jokes? My friends talk about all the Ringo Starrs out there. You know them; they were with one company as someone inconsequential. The company made it big and found a way to parlay it into selling several books and evangelizing ideas. They are their own personal brand.

That said, there’s a really bad sign if your Social Media Expert spends more time blogging than working. Think about this: one post of this length takes about an hour of write. It’s like the cook with the great cookies; the last thing they are going to do is tell everyone the recipe, right?

If they are spending all their time writing blog posts about how much they know about Social Media, they aren’t helping your company do Social Media.

Your Social Media Expert thinks social media started with Facebook and Twitter.

The reality is that the core foundation of social media has been around since two people talked around the campfire about inventing the wheel. It’s just moved to a different medium, and that medium as we know it, the Internet, started on October 29, 1969. A lot of us older people — you know, the one’s that had jobs before an email address — remember bulletin board systems.

Technically, my first social media message that I sent to a friend of mine on Usenet was in 1987. Seriously, that’s longer ago than the age some of the experts I’ve seen. The message took four days to get there. The distance traveled was from Irvine, California to Claremont, California.

Real Social Media Experts understand conversations, and how those conversations interact on whatever medium they are on. That could mean a letter to the editor sent via a mail carrier in the 1950’s or a page established on Facebook in 2009. It’s the conversation that’s important.

Your Social Media Expert thinks that Twitter is the start of your brand.

One of the great aspects of Social Media is that, if you do it right, your customers have the conversation for you, promote your business and make you lots of money all for the cost of good service. One of the biggest mistakes we all make is where brand starts.

What is brand? Is your your name and the experiences tied to your name. It’s not a twitter post or a blog entry or the color you have or the logo you are designing. It’s the name of your company, and how every representative of your company is associated with it.

If a stupid Twitter post goes out about how Memphis sucks or 15,000 people complain on Facebook that your company uses slave labor, that hurts your brand. Social Media conversations shouldn’t be measured in just metrics but also in quality of the conversation, because that relates back to your brand. The linked article is a good example, because it talks about the success of Comcast. Ask any of their customers.

Your Social Media Expert always has a clown in the pocket.

This a famous phrase I’m going to attribute to a friend of mine. Whenever a company was going down the drain, especially during the late 1990’s, there was always a skunk-works project that was shown off in front of the venture capitalists. This was to distract them from the fact that the company was burning $15 million a month, they were surrounded by $1,000 Herman Miller Aeron chairs, the core product still hadn’t launched, and the CEO was doing coke.

Look, online video! We can put that on our Geocities pages!

Real consultants offer some kind of road-map, including what the deliverables are, what they are going to do and what should be the result, hopefully. It doesn’t always have to succeed, and sometimes you can’t always measure it. Even the biggest agencies have a hard time generating good numbers around social media. At the end of the day, if sales go up, it’s a good campaign.

It’s about the strategy, kids. Plan. Plan. Plan, again. It’s not rocket science, and it doesn’t take a 25-year-old to tell you otherwise.

Your Social Media Expert speaks in 140 character sentences.

If the only way they promote themselves is through Twitter, fire them.

Yesterday.

Why?

Twitter is the Apple of the Internet without the cool products. Their market reach is under two percent, which is interesting because MySpace is still in the 30’s and Facebook is way, way over that in the 50’s. I look at it as the “mom test.” If my mom has heard about it, it’s gone mainstream. We had dinner a few weeks ago, and the conversation started something like, “They wanted me to get on Facebook, but I don’t see time for it. I wish they would have called me up.” Twitter never entered the conversation.

The truth is that Twitter has some great uses, including being the new RSS feed and a great way to watch conversations around specific topics or events.

Your Social Media Expert recommends Delicious and Stumble Upon for an audience of seniors.

It’s all about the audience, right? If your consultant doesn’t know who to talk to, then how can they have a conversation. That’s what blow my mind about some of the people that recommend Twitter for everyone. The first question asked should be, “Where can I have a conversation with this audience?” For example, Email is still relevant. About 46 percent of all embedded links are still through email, yet the Social Media Expert wants you to use hashtags.

Figure out where your audience is, and talk to it. For some, it’s Foursquare. For others, it’s Facebook. For other people, it’s Etsy.

For every audience  there’s a proper venue, and your Social Media Expert should know where to look.

What to do? What to do?

If you really need a Social Media Expert that is one, email me, even if the “expert” doesn’t call himself one.