Excel Off The Grid’s 2nd Birthday

Excel Off The Grid is now two years old.  With approximately 65,000 words over 50 new posts it has certainly been a busy year.

One of my goals for 2018 was to write more in-depth content, which I believe I have done.  In the first year, the average content length was approx 800 words.  During this second year, the average increased to approx 1,300 words.  Length does not always equate to quality, but I’m happy with it.

Favorite posts

To celebrate Excel Off The Grid’s 2nd birthday, I wanted to share my favorites posts from the last 12 months.

The real reason INDEX / MATCH is better than VLOOKUP

If you read a lot of Excel blogs and sites, you will see a common topic arise which encourages us all to use the INDEX MATCH formula combination, rather than VLOOKUP.  I’m not going to disagree with that recommendation, however I feel the question of “Why is INDEX MATCH better than VLOOKUP?” is often not fully explored.  In my opinion, answers normally provide an overly harsh view of VLOOKUP and an overly simple view of INDEX MATCH.

AGGREGATE: the best Excel function you’re not using

In Excel 2010, Microsoft introduced a new function called AGGREGATE. It is one of the most powerful functions in Excel, yet I’ve rarely seen it used in a real-life scenario.

AGGREGATE can COUNT, AVERAGE, MAX, SMALL and SUM, to name just a few.  But it’s better than those functions because it performs all of those whilst ignoring errors, hidden cells and other cells containing AGGREGATE  and SUBTOTAL functions.

Set chart axis min and max based on a cell value

Changing the minimum and maximum values of a chart axis is such a tedious task.  It only takes a few seconds, but all that time starts to add up.

I decided to build a more dynamic solution.  I turned to my old friend VBA, and started to tinker.  In this post you’ll find the result of that tinkering; a formula which exists on the worksheet to control the min and max values of a chart axis.  Link that formula to a cell and suddenly it is possible to set the chart axis based on a cell value.

Getting values from a closed Excel workbook

So many things can go wrong with linked files, yet unsuspecting users tend to use them with pride.  “Look, I’ve linked this so that I don’t have to update a cell, isn’t this cool!”. If they knew the risks they might not be so quick.

In this post, I demonstrate why it’s so risky, and identify the hidden cache of linked data contained within very linked Excel file.

There is nothing more frustrating than finding out a previous employee has used passwords to protect an Excel file, or maybe you set the password and have now forgotten it.  Either way… you’re stuffed!  However, before you give in, in this post I share some of the methods I use to remove passwords.

Other highlights

There have been some other highlights from the last 12 months,

Top blog

The site has been included in a number of “Top Excel Blog” type lists:

The internet is a living place; pages can easily be changed and updated.  By the time you read this and click the link, the site may have been removed, but I promise you it was there. 🙂

It’s nice and humbling to know that people are reading the site and enjoying so much that it has been included in a list of top Excel sites.

Podcast

John Michaloudis asked me to be part of one of his podcasts, entitled The best Excel tips of 2017 from 20 Excel experts.  Having enjoyed listening to this podcast in the past it was a tremendous honor to be included with so many Excel legends.

So, there you have it, my favorite parts of the last 12 months. It was difficult to choose, I had to leave out a lot of good stuff. Go and explore the site yourself, who knows what else you might find.

Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are: