Contextures News 20150505

Check Formulas + more Excel tips

May 05, 2015

Look for formula inconsistencies, save time with keyboard shortcuts, use online Power BI, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

1. Contextures Posts

In case you missed them, here are the articles that I posted recently:

• Fix an Excel table that doesn't expand automatically, when you add new data. This is easy to fix, if you know where to look!

• For a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.

2. Select a Chart Series

If one series in a chart has very small numbers, it can be difficult to select its bar or line, if you want to format it. I use the Ribbon commands to select them, but Chandoo shares a keyboard shortcut, that is much quicker.

To show that it's worth the effort to learn shortcuts, Paxton Gray calculated how much time you can save by using keyboard shortcuts in Excel.

Level - Basic

3. Power BI Preview

Power BI Preview is a new (and free) website from Microsoft, where you can try the Power Query, Power Pivot and Power View tools, outside of Excel. It was quick and easy to sign up, and is available in a long list of countries. (Don't try to use any part of your signup name in your password though, or it will be rejected, like my first attempt was.)

To learn more about the Power BI Preview, before you register, read Jon Acampora's overview, where he does a video demonstration.

Level - Intermediate

4. Use Data Tables for Calculations

Christo Kritzinger, a 2014 ModelOff finalist, shows how to use Excel's Data Tables for bond calculations. You could use the same technique for other types of calculations.

Level - Intermediate

5. Better Spreadsheet Models

On the Excel TV blog, Jordan Goldmeier explains how to create better spreadsheet models.  This is a summary of one of his presentations at the recent PASS BA Conference.

Also on the Excel TV blog, Rick Grantham shares guidelines for effective dashboards.

Level - Intermediate/Advanced

6. Tips for Excel Online

In two short videos, Microsoft shares 6 tips for working with Excel Online. The first video covers number formatting, and the second video has tips for creating sums.

Level - Basic

7. Excel Goes to Court

Actual Excel files are being used as evidence in trials now, instead of printouts. In his Excel Esquire blog, Ben J. Kusmin explains how to check the formulas in a workbook, to look for inconsistencies.

Level - Basic

8. Excel Business Intelligence

The PASS Excel BI Virtual Chapter runs free online sessions related to Excel Business Intelligence. The next session is June 18, 2015, and discusses Risk Assessment, using Power BI and Excel. It doesn't look like the sessions are recorded, so you'll have to register for the live event, if you're interested.

Level - Advanced

9. Fun With Functions

Even if you're not an architect, it helps to know the 12 Excel formulas that Michael Kilkelly says every architect should know. He recommends named ranges too.

Of course, Excel can be used for baseball statistics too. In an entertaining article, Kimbo Smash uses "Advanced Statistics" to analyze stats for the Baylor University baseball team.

Level - Basic

10. Garden Work

The weather was beautiful on the weekend, and things are sprouting in the garden, like the chives in the photo below. Of course, the weeds are doing nicely too, and there is lots of clearing out to do. To get started, I left that weeding tool in the garden all weekend, and it didn't remove a single weed! I'd better get to work, before the weeds take over.

That's it for this week! If you have any comments or questions, send me an email.

Debra Dalgleish
dsd@ contextures.com

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.