This website or its third-party tools use cookies which are necessary to its functioning and required to improve your experience. By clicking the consent button, you agree to allow the site to use, collect and/or store cookies.
The platform this site runs on doesn't have the option to turn off cookies server side, but you can do that in your browser and then return to the site.
I accept
Deny cookies Go Back

Annielytics.com

I make data sexy

  • About
  • Resources
  • Services
  • Blog
  • Contact
  • Log In

Aug 29 2014

The INDEX Function: Excel’s More Stable Alternative To OFFSET [VIDEO]

The index vs the offset function in Excel

Back in March I did a post on three marketing uses for the OFFSET function. But, truth be told, I avoid the OFFSET function like the plague. The reason is that it’s very unstable and can bring large Excel files to their knees.

Why? Every time you modify the file the OFFSET function takes that as a cue to go ahead and run again.

It’s not alone. There are a few more functions that are volatile in this way, namely:  

  • NOW()
  • TODAY()
  • CELL()
  • INDIRECT()
  • ROWS()
  • COLUMNS()

REMIX!

So what I decided to do was take all of the marketing uses I demonstrated in that video and show the INDEX alternative.

Demo File

If you’d like to see how these look out in the wild, you can download the Excel file I used.

Learn More

To learn about Excel functions — and more — check out out my Annielytics Dashboard Course offerings.

Written by Annie Cushing · Categorized: Excel · Tagged: Excel, Excel Charts, Excel Formulas, Google Analytics

Comments

  1. Diana says

    October 3, 2014 at 12:42 PM

    Thanks for the wonderful understanding information. Will require some more guidelines from you on excel and analytic.

    Reply
    • Annie Cushing says

      October 3, 2014 at 12:47 PM

      You’re very welcome! The [data] doctor is in. https://www.annielytics.com/services/ 🙂

      Reply
  2. Diana says

    October 3, 2014 at 12:44 PM

    Wonderful understanding. Thanks a lot

    Reply
    • Annie Cushing says

      October 5, 2014 at 11:40 AM

      My pleasure! Glad it helped.

      Reply
  3. Lynne says

    October 4, 2014 at 7:54 PM

    Fantastic – very clear and super useful.

    Reply
    • Annie Cushing says

      April 2, 2015 at 11:10 PM

      Thanks!

      Reply
  4. Aliza says

    October 7, 2014 at 4:53 PM

    Great post — I’ve been using the offset function a lot but haven’t changed it for fear of destroying all of my hard work. While I use pivot tables a lot, sometimes I just like using a plain table for certain reports, which makes using the offsetfunction crucial when dealing with a lot of data. I will be trying out the index function this week! Thanks 🙂

    Reply
    • Annie Cushing says

      October 7, 2014 at 9:07 PM

      Your spreadsheets will thank you!. 🙂

      Reply
  5. Martin says

    November 20, 2015 at 4:29 PM

    This really helped me resolve a very slow trading dashboard. Much appreciated

    Reply
    • Annie Cushing says

      November 22, 2015 at 7:26 PM

      Excellent! Happy to help!

      Reply
  6. Bill says

    June 2, 2018 at 11:50 PM

    Hi Annie. Great tutorial.

    I have a large (and very sluggish) workbook that uses many dynamic ranges with OFFSET. Recoding it with INDEX will be a lot of work. I am inclided to do this but you warn against not going overboard with extended range. In my case, the number of data rows could be 30,000. So I would have to use a very large range to cover this. Would the performance still be improverd with such large ranges?

    Reply
    • Annie Cushing says

      June 4, 2018 at 7:17 PM

      Thanks! There’s no way for me to know without testing it, unfortunately. There are so many factors involved.

      Reply
      • Bill says

        June 4, 2018 at 10:36 PM

        Well I tried it Annie. Changed all the OFFSETs to INDEX equivalents and it made a major improvement in performance. Thanks for the tip.

        Reply
        • Bill says

          June 4, 2018 at 10:53 PM

          BTW, I used the most extreme extension flexibility. My dynamic range extends potentially to the full size of the worksheet. Here is a sample:

          Data refrers the the range Data!$A$3:INDEX(Data!$3:$1048576,COUNTA(Data!$A:$A),COUNTA(Data!$3:$3))

          This selects names the range “Data” to be all rows from 3 and below until the first blank, and similarly all the columns from A until the first blank. Since my header row and first column have no missing data cells, this selects my entire table of data. I name some columns as follows:

          Account_Name refers to the range
          Data!$D$4:INDEX(Data!$D$4:$D$1048576,COUNTA(Data!$A:$A)-1,1)
          (starting at row 4 to remove the header)
          Works great! Thanks again.

          Reply
          • David says

            March 22, 2019 at 5:47 AM

            If your data is in an excel Table, then you can reference its entire extent by selecting the Table, and it will automatically extend the range as you add more rows, or columns. Problems I have found with the dynamic range using count, is if you forget the count is there and accidentally add some data in those rows that don’t belong in the data table.
            I have used this for Pivot ranges and your data set suddenly includes blanks column titles and crashes. Tables are just a bit more resilient.

        • Annie Cushing says

          June 6, 2018 at 3:46 PM

          Great! Glad it helped.

          Reply
  7. Bill says

    June 2, 2018 at 11:57 PM

    Also, I note that in your tutorial at 7:28, you show that the range reference when entered in the Excel worksheet returns a #Value! error. I don’t get that on my Mac (Office 365). I get the first cell of the array referenced. I can see the whole array of course by using the forumla editor, selecting the formula, and hitting Cmd + “=”. How come you get an error and I get the array?

    Reply
    • Annie Cushing says

      June 4, 2018 at 7:16 PM

      I have no idea. Maybe it’s a difference with how 2016 processes an array function.

      Reply

Leave a Reply Cancel reply

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

CAN I HELP YOU FIND SOMETHING?

‘MAKING DATA SEXY’ IS LIVE ?

Want to learn how to create compelling, aesthetically pleasing charts in Excel? Learn how with Making Data Sexy.

Note: Also offered for Mac

STUFF I BLOG ABOUT

LEARN HOW TO USE ALL THE TOOLS

marketing strategy guide

DIY marketing strategy guide. This guide provides step-by-step instructions on how to perform 66 unique marketing tasks using 15 reputable marketing tools (both free and paid). Steal it for $295! Learn more.

LEARN TO DO A SITE AUDIT

site audit template

DIY site audit template. 20 sections, 215 checkpoints, 100+ explainer graphics, 218 pages, step-by-step instructions. Steal it for $295! Learn more.

LEARN TO DO AN ANALYTICS AUDIT

analytics audit template

DIY analytics audit template. 8 sections, 61 checkpoints, 100+ explainer graphics, 205 pages, step-by-step instructions. Steal it for $295! Learn more.

TO THIS DATA I DO THEE WED

dashboard course

Learn to build dynamic dashboards in Excel with Google Analytics data. 16 hours of video, 3 sample dashboards, 142-page workbook, practice Excel file, and more! Learn more.

FOLLOW ME ON TWITTER

SUBSCRIBE TO MY YOUTUBE CHANNEL

Privacy Policy
  • Email
  • LinkedIn
  • Twitter
  • YouTube

© 2023 annielytics.com