This post may contain affiliate links. Please read my disclosure for more info:

Excel Off the Grid’s 1st Birthday

Excel Off the Grid first birthday Thumb

Excel Off the Grid first birthday

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.

Build your own Add-in: calculation timer

Build your own Excel Add-in

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.

 

The ultimate guide to Slopegraphs in Excel

Ultimate Guide Slopegraphs - thumb

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

 

Creating custom Map Charts using shapes and VBA

Custom Map Charts Thumb

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.

 

How to format multiple charts quickly

format multiple charts quickly

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.

 

Creating a Top 10 using formulas

Create top 10 using formulas

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.

 

Do you have to use Dim to declare variables?

Do you have to use DIM

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.

 

VLOOKUP: List all the matching items

VLOOKUP list all matches

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.

 

What the experts wish they had known earlier

What the experts wish they had known earlier

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).

 

Cell ranges: basic things 99% of users don’t know

Cell range basics - thumb

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 #1 Dashboard tip nobody tells you

No 1 Dashboard tip nobody ever tells you

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.

2 thoughts on “Excel Off the Grid’s 1st Birthday

Leave a Reply

Your email address will not be published. Required fields are marked *