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 https://www.youtube.com/watch?v=YCaQ-hHJvcE’ rel=nofollow>https://www.youtube.com/watch?v=YCaQ-hHJvcE’ rel=nofollow>presentation and the panel discussion on YouTube. His talk starts at the 4:15 mark, and ends at 18:17. The panel discussion starts at 1:04:06.

5. Data Analysis

Canada and pivot tables are both awesome, so what could be better than putting them together? Abbott Katz uses an Excel 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.