In this week’s roundup, look for formula inconsistencies, save time with keyboard shortcuts, use online Power BI and many more tips.
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
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 there is a small series in a chart, it can be difficult to select, if you want to format it. I’ve always used the Ribbon commands to select them, but Chandoo shares a keyboard shortcut, that is much quicker.
On the Data Hero blog, Paxton Gray calculated how much time you can save by using keyboard shortcuts in Excel.
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 available in a long list of countries.
A tutorial on the website will help you get started, and to learn more about it, before you register, read Jon Acampora’s overview, and watch his video demonstration.
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.
5. Lookup User Defined Functions
Doug Jenkins has written a couple of Lookup User Defined Functions (UDFs), after reading Jeff Weir’s post on VLOOKUP speed. He compares the speed of these UDFs to formulas that use VLOOKUP or INDEX/MATCH.
6. 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.
7. 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.
8. 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.
9. 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.
10. Functions for Architects
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.
______________