Category Archives for Analysis
How to: Power Query File From Folder
Get file from FolderGet File From Folder is what Microsoft has named the functionality that let’s you take multiple files and bring them together as one using Power Query. Back in the day, the process to do something like this was very much VBA driven. You would use something the File System Object to read every spreadsheet […]
Continue reading
Highlighted Timeline Chart in Excel Without VBA: Raw and Uncut
IntroductionI built a chart that let’s you highlight a series from a smaller chart (take a look at that the chart under the timeline) and show it in more detail on a larger chart. Yahoo! Finance used to have something similar, although I can’t find it anymore. But anyone who has looked up stocks online […]
Continue reading
Excel Data Visualization: Presidential Approval Ratings with Slicers & Power Query – Chart Tricks
No matter where you fall on the political spectrum, you’ll love the chart I’ve created, which you can adapt and use in your own work. The instructions are a bit complicated, so rather than go through every element with figures, I’ve written up what I would do assuming you’re watching and following along in the […]
Continue readingExcel Requests – HTTP for Humans
Written by Bjoern Stiel APIs are the lifeblood of the internet, providing access to bits and bytes that make our personal and professional worlds work. For example, Quandl provide a vast amount of financial and economic data through their API, while GitHub and StackOverflow let you pull out all sort of user and repo data through their API. But how do we communicate […]
Continue readingAnother way to do bullet graphs in Excel
Those of you who know me, know I’m a huge fan of Stephen Few. (I mean, I had him autograph my copy of Information Dashboard Design!) Few is highly regarded as an expert on data visualization and dashboard design. He is the original designer of bullet graphs. Bullet graphs are not native to Excel’s chart […]
Continue readingNewspapers – Read Your Sources!
Update – Apologies for the bad proofreading job in the original blog post. Before writing this, I had spent the entire day writing a book proposal for a technical publisher. I was a bit burned-out. It’s an exciting time for the United States. In the forthcoming days (possibly tomorrow), the Supreme Court of the United States […]
Continue readingEasy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 2)
Wow, it’s been a while since my last article. I’ve actually been pretty busy the last few weeks putting the finishing touches on an Excel deliverable for one of my company’s clients. We delivered the product about a week ago and things have just now started to slow. So now it’s back to blogging. If […]
Continue readingEasy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 1)
This article describes what I like to call “Easy One Way Sensitivity Analysis” in Excel. I’ve used it before in several dashboards. If you want to try it out for yourself before reading ahead, both Healthcare dashboards on the Fun Downloads page implement my Easy One Way Sensitivity Analysis method. Here’s a pic of one of […]
Continue readingNumbering Grouped Data for Pivot Tables
We’ve all had some experience with Excel’s automatic numbering. For example, if you have a simple list and you would like to add consecutive numbers to a column next to your data, you’d simply select the cells containing your first two numbers and then drag accordingly. Take a look at the sheet below to see […]
Continue readingA Dynamic Dashboard for Project Durations and Costs in Excel
Make sure to checkout A Dynamic Dashboard for Project Durations and Costs in Excel (part 2) for the example file! Let’s say you are the manager of a portfolio of projects at your company. While each project under your aegis is different, they have a few things in common; specifically – and for the sake of our […]
Continue reading