Excel Off the Grid has reached its first birthday. 12 months ago, when I started the site, I had no idea what I was doing (to be honest, I still don’t), but it’s been fun.
There have been over 100,000 words, across 120 posts all about Excel (and sometimes PowerPoint or Word). So, it’s certainly been a busy year. To celebrate, I wanted to share with you my 10 favorite posts from the site.
Creating an Add-in is not as hard as you might think.
This post covers how to create your own custom Ribbon for Excel. Using the RibbonX Visual Designer, I show you how to create new tabs and buttons, then how to assign macros. If you master this, then who knows what great tools you’ll be able to create.
Slopegraphs are NOT just line charts with 2 points, they are much more useful than that.
Slopegraphs have become one of my favorite data visualizations. This tutorial shows how to create them with 3 different scenarios:
- Single measure Slopegraphs
- Automatic highlighting Slopegrahs
- Dual measure Slopegraphs
Promotional images for expensive BI software always seem to show a map with countries or regions highlighted in different colors. Until Excel 2016, these Map Charts were not available natively in Excel. As a result, the BI software stood out as something which was more advanced, it had the “WOW” factor. This tutorial will teach you how to create your own Map Charts to give your Excel dashboards the “WOW” factor too.
I used to spend hours formatting and re-formatting charts over and over again. That was until I learned this Paste Special trick. It now takes seconds, to reformat lots of charts to all look the same.
This is one of those tricks you can’t believe you didn’t know it earlier.
Auto Filter, Tables and Pivot Tables all have Top 10 as a default option. But, sometimes you need to create a Top 10 using formulas. This post will show you how in a step-by-step process. It actually works in the real world, as it covers how to deal with duplicate values.
In VBA, you don’t have to declare variables, but that doesn’t mean you shouldn’t.
Understanding this can save hours of VBA debugging time. Dim does not necessarily create better code, but it forces me to write better code.
When VLOOKUP is pushed as far as it can go you will be able to list all the matching items, not just the first.
The formula in this tutorial is very complex. It includes IFERROR, VLOOKUP, SMALL, IF, ISERROR, CHOOSE and ROW all in a single formula. I’ve broken down into smaller understandable sections. If you can master this, you’ll be a true VLOOKUP ninja.
This was the first time I reached out to the wider Excel community. My goal was to have 10 answers to the question “What is the one thing about Excel you wish you had known earlier?”.
I was expecting to struggle to get enough responses. Yet, the response was amazing. I was amazed at how kind everybody was, and how willing people were to give up the time to provide an answer. I quickly had enough responses, I had to stop reaching out. Thank you to everybody who provided an answer, and apologies to everybody who I didn’t get a chance to ask (hopefully next time).
Colons and dollar signs is all most Excel users know about cell ranges. But that’s just the start point – there is so much more.
This post covers Range, Union and Intersection operators along with the functions which return ranges.
The hardest part about Excel dashboards is the layout. I discovered a flexible grid-based approach which makes layouts so much easier to deal with.
I used to fear changes to dashboards templates, adding an additional column to a table could easily break the whole design. But with this flexible approach it is much easier to incorporate that extra column, or any other changes for that matter.
So, there you have it, my 10 favorite posts. It was difficult to choose, there are so many good posts I had to leave out. Go and explore the site yourself, who knows what else you might find.