We all have down time while we wait in line or while we commute. How do you use this time? In the past two years I’ve used part of my down time to learn Excel by reading tweets.

As I do live support using Microsoft Excel it’s important for me to always be improving my skills. Over the years I have learned from: colleagues, books, videos, blogs, etc. I now consider Twitter to be an important source of Excel knowledge.

Why Twitter?

There are many Excel sites full of knowledge. However, I don’t have time to visit everyone’s Excel site every week. Twitter gives me a bird’s eye view of what’s going on and allows me to swoop down to the details in the areas that are the most relevant. Some Tweets contain a formula or tip directly in the tweet while others include a brief description with a link.

Who do I follow?

I follow a lot of people in the Excel community. Two of my favorite Twitter sources for Excel knowledge are @TomUrtis and @Bill Jelen.  Many of Tom’s and Bill’s tweets are from automated feeds that circulate content including excerpts from their books and websites.

Bill Jelen

Tom Urtis

What can you learn?

Here are some of my favorite Excel tweets from Tom and Bill. Click the picture to see their original tweet.

Lookup Function Magic!

The Lookup function can accept an array of values.

In only 1 formula you can look for the closest match for each individual number and then sum all of the independent answers. Don’t forget to press Control Shift Enter. =SUM(LOOKUP(C2:C26,E2:E6,F2:F6))

Sum All Numbers in a cell

It’s rare but I’ve actually had this question a few times. Given a string like “K3M2” we need to sum the numbers.

Instead of extracting each single character and then testing to see if it’s a number this single formula loops through the cell to extract and then sum the numbers. This is also entered with Control Shift Enter.

Simple Formula Avoids Array Formula

I like this formula for the simplicity. It would be easy to overthink this and create a much longer and slower array formula.

In this case it’s just a Vlookup and a zero inside of a Max function. If the Vlookup returns a negative value then the zero wins as the max value.

Or Clause inside of Sumif

I first saw this only a couple of years ago. Normally, a Sumif can’t handle an OR clause. However, if you add an array constant like this {“Bill”,”Tom”,”Jim”}   and then wrap it with the Sumproduct function it works like magic! Without the Sumproduct around it the answers are stuck inside of the Sumif. Highlight just the Sumif part of the formula and press the F9 key to see what I mean.

Round Time Value Up To Next Minute

Working with date and time values can be tricky.

If you need to round a time value up to the next minute this is a very easy way to do it. No need to create a complex formula.

Keep Your Data Clean

Add a formula inside of data validation to ensure that a certain character (i.e. “@”) is included in the cell.

This is a great way to keep your data clean. Don’t forget to add an ‘Input Message’ and an ‘Error Alert’ in your data validation to guide your users.

Dance Around the Corners of Your Selection

After you work with Excel for a long time you can appreciate shortcuts that make life easier.

Select a range, hold down the ‘Ctrl’ key and press the “.” key to move around the corners of your selection.

 Simple and Brilliant! No formulas

Adding a blank row between every row of data could be accomplished with vba code or with formulas.

This tip is brilliant because it’s so simple and quick to implement. Just add a counter (i.e. 1,2,3,4) alongside your data and immediately copy/paste theses numbers below the last number. A quick sort on the column using this counter column will leave you with a blank row between each row of data (double spaced data set).

Community Involvement for an Excel Book

And finally, this tweet was a unique one. Bill asked us to vote for our favorite tweets.  The winning tips were included in the book “MrExcel XL Book – 40 Greatest Excel Tricks”. I’ve read the book a couple of times already and it’s full of many great tips and tricks!

Get Started with Twitter

• Sign up
• Type in the Search Bar to find content
• Click ‘Follow’ to subscribe to people
• Click the little heart to favorite a tweet
• Re-tweet content to your followers

What Do I Do on Twitter?

I tweet about my videos and blog posts.

I also re-tweet my favorite content from those I follow.