Microsoft Excel — PowerQuery to Flexible Content Dashboard

Report List Dashboard in Excel 2013

Resource Dashboard Overview

I maintain lists in Google Sheets (which are either auto populated via IFTTT or maintained via my mobile devices), which I use as a back-end “dataset” for sharing and accessing content.

The dashboard above was developed in Excel 2013. The following concepts were applied:

• Data import using PowerQuery from Google Sheets into Excel (need PowerQuery add-in — download here).
• Sorted in descending date order — most recent content always at top
• Categories assigned (in Google Sheets) to enable easy filtering based on user interests (especially as content list grows!).
• Excel slicers are applied to the data table imported by PowerQuery
• Macros and buttons used to enable simple data update and clearing slicers
• =HYPERLINK function to create live clickable link to content (the imported URL column is hidden for report purposes.

Dashboard in Action

• Open Excel file and refresh query so you have the latest content.

Open view of Dashboard — Click “Update Query” button

• Filter list by clicking Slicer — in this case, November is the filter

Filtered View — Just November

• Click on URL link to open desired document

Article opens in Web Browser

I hope this gives you a few ideas on how to leverage Excel to share content in your organization!

About Don

“On a mission to challenge the status quo to a more productive and effective end…”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

I frequently get this question. My response (it’s not what you think!)? Check it out here!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere