A new Excel dashboard

I have been working on an Excel dashboard for turnover, and recently completed it. As with my other side projects, this one rose out of a need to have complete, accurate, and up-to-date turnover data at my fingertips. The rest of the team also needs the same data, so it will be useful for everyone.

This is my second big dashboard. I made my first one last year, then spent a lot of time tweaking it and making it more user-friendly, with more data and snapshots. At the beginning of this year, I found a way to simplify it, although it doesn’t go as far as I would like it to go.

I started on the turnover dashboard a few months back, maybe mid-April, and worked on it in fits and starts, between my other work. There were a number of false starts, until as recently as a week before I finally wrapped it up. Now that it’s done, these are my reflections.

  1. The structure matters. I knew I had to have the data in tables, and use different sheets for the raw data, calculations and display. But this is still the part that took longest. I wanted to include historic data, at the same time use Power Query to update the data automatically. I cleaned the data in tables after extracting them in PQ, but each refresh led to all the extraneous data getting populated again. I finally gave up on PQ for automatic refresh, but I will use the query to get new data and clean it before adding it to the dashboard. And then the question of how to calculate the data – go with pivot tables (yay, slicers! but can be unwieldy) vs formulas (no slicers, easier to manage) vs macros.
  2. Decisions, so many decisions! There’s a ton of ways you can slice turnover data – demographics, trends, overall vs voluntary vs non-voluntary, actual numbers vs ratios, regrettable vs non-regrettable… I had to decide what I would show. Technically I could include all of it, but there would be a trade-off against file size and performance. Plus, the more formulas and pivots, the more chances for errors to creep in and make my dash lose credibility. Do I use slicers, even though they take up too much valuable dashboard real estate? Do I try my hand at dropdowns and macros, knowing some of my remote team wouldn’t be comfortable with macro-enabled files? Don’t even get me started on the display – what is ‘good’? How do I fit everything in, without making it too cluttered, and with all the information and context that is needed? So many decisions! I did this project for myself, so I had the luxury of deciding what I needed to see (ideally, everything!), but I also had to balance that with what was feasible in the limited time I had to work on this, my limited dashboard skills, and making this a sustainable dash. I did not want to spend days and weeks culling this data again.
  3. Excel. I am an Excel geek, I love fiddling around with it, and I love how much it can do! I am thrilled when I discover new features and simple solutions to it’s inherent limitations. However – it is a tool, and while I could spend days playing with it and learning more, and getting to the bottom of VBA and macros – that is not what I am paid to do. I refreshed my Excel knowledge, went to the wonderful online communities that have been so helpful to me. I definitely learn more each time I create a dashboard, and I tried a bunch of new things while creating this one. Yes, I took a lot longer than an Excel expert would, but I learnt how to create solutions that work for me.
  4. The expert eye. In the process of creating this dashboard, I explored a lot of options. I knew what data I wanted to see, and what was relevant and meaningful for me. I knew what data I had, to work with. I knew some Excel features, and I spent a lot of time looking at what else is out there, that I could use. I chose to use number formatting because conditional formatting slows down the file. I chose to download and run a macro or two (especially one on applying ‘IfError’ to selected formulas – I’ve used that every time I do something like this). I used pivots, named ranges, and formulas, and came to the sad realization that if I had Excel 2013, I could do so much more! I looked up dozens of dashboards, understanding how they were structured. Then I looked up a few more, looking for displays that looked good, and that I could use. It was a process of taking what I know, exploring and learning what I did not know, and creating a solution that works.

 

I started this project with an idea of what I wanted – turnover data at my fingertips. I did not know exactly how I would get there, but I kept trying until I found my way through. I had to try many different approaches, suffer plenty of Excel crashes, and generally get used to a sense of feeling stuck for days on end. Many times, I thought I was close, only to get stymied again. Finally, when the structure fell into place, that when I knew I’d wrap this up quickly. It only took two days after that. When I look at my handiwork now – it all seems so simple and easy! 🙂

And you know what – I would do this all over again! I enjoyed the challenge, and I am celebrating the win. It will be easier next time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s