Excel Roundup 20160321

Excel Roundup 20160321

In this week’s roundup, Data Analysis, Power BI, combining functions, and more Excel tips.

Excel Roundup http://blog.contextures.com/

1. Excel Navigation

Last week on this blog, I showed how to go to a specific sheet in an Excel workbook, without using macros. Just select from a drop down list of sheet names, then click the hyperlink in the cell below.

2. Combining Excel Functions

They’re powerful on their own, and at PC World, JD Sartain shows how to combine INDEX and MATCH with other functions, such as SUM.

3. Data Insights

If you aren’t going to Seattle this week, you can register to watch the free online sessions, live-streaming from Microsoft’s Data Insights Summit, on March 22-23 (this Tuesday and Wednesday). I couldn’t make it to the conference, so this will be the next best thing.

4. How Does Excel Make You Feel?

At the Code4Lib 2016 conference (code for libraries), Mark Matienzo talked about spreadsheets, how they make you feel, and why they’re used so extensively. The article isn’t too long, but it was a bit of an academic read, so I started with the summary at the bottom, and worked my way back to the top. Do you ever do that?

In addition to reading his notes, you can also see his pivot table to analyze some Canadian bilingualism data.

6. Power Pivot Updates

There were several Relationship Edit improvements, and other changes, in the latest update for Power Pivot in Excel 2016.

7. Excel Dashboards

Before you start building a dashboard, see what Chandoo and the Excel TV team have to say about the requirements gathering process for dashboards.

There’s also a new Power BI book, by Kasper de Jonge: Dashboarding and Reporting with Power BI Desktop and Excel. The book’s subtitle is, “How to Design and Create a Financial Dashboard with PowerPivot – End to End”.

I haven’t read it yet, but here’s a brief description of the book: “Written by a member of Microsoft’s Power Pivot team, this book provides a practical step-by-step guide on creating a financial dashboard. The book covers in detail how to combine and shape the relevant data, build the dashboard in Excel, providing layout and design tips and tricks, prepare the model to work with fiscal dates, and show values used in many financial reports, including year-to-date, variance-to-target, percentage-of-total, and running totals reports”

Get Weekly Excel News By Email

To get Excel news and tips by email, add your name for the Contextures Excel newsletter.

________________

hyperlinknavigation09a

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.