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.
In case you missed them, here are the articles that I posted recently:
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
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
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
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
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
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
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
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
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.
Last updated: January 2, 2020 3:39 PM