• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Writing Excel Formulas Efficiently

You are here: Home / Excel Formulas / Writing Excel Formulas Efficiently
Writing Excel Formulas Efficiently
May 27, 2014 by Mynda Treacy

I get a lot of questions from members and visitors to our site asking for help with formulas. It’s not surprising given they are one of the most useful Excel features.

However, one of the repeating problems I’ve noticed recently are formulas that are not written efficiently.

When I say β€˜efficient’ I mean efficient for you to write and maintain as opposed to efficient for Excel to calculate, the latter is a topic for another day.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Here’s an example of an inefficient formula, below in columns A and B we have some employee data, and in columns E and F we’ve set up a summary table which counts the number of employees in each department using a COUNTIF formula (we can see the actual formula from column F in column G):

inefficient countif formula

While this formula is technically correct, after all it returns the correct result, it's inefficient to write.

Let me explain; the β€˜criteria’ argument (in the orange boxes in the image above), has been entered as text and this means each formula in column F had to be modified for each department. That's way too much work.

A more efficient way to write the formula is to reference a cell for the criteria argument as you can see in the example in column H below (especially since it’s right there in column E):

efficient sumifs formula

Formulas written this way are:

  1. Quick: with the efficient formula (as shown in column H above) you enter your formula in cell F2 and then copy it down the column and your job is done.
  2. Easy to Update: if you need to change the Dept. names in column E, your formula will automatically pick up the new name without the need to also edit the formula.
  3. Intuitive: where there are formulas in contiguous cells experienced Excel users will expect that the formula can be copied and pasted to all adjacent cells. So if someone inherits your file (or helps you with a formula ;-)) and they edit the formula, they will do so in the top left cell in the range of formulas and then copy and paste it to the remaining cells in the table. However, this could result in errors if, for example, they don't notice that your formulae 15 rows down and 3 columns to the right are subtly different because you hard keyed some criteria!

Below is an example where this ability to copy and paste one formula to many cells earns its weight in gold (i.e. the more cells you copy it to the more it weighs :-)); the SUMIFS formula in cell G2 (as seen in the formula bar below) can be written once and copied to the remaining 14 cells in the summary table:

copy formulas

As opposed to this inefficiently written version of the formula in cell G2:

=SUMIFS($D$2:$D$64,$A$2:$A$64,"Admin",$C$2:$C$64,2012)

Notice the difference between the efficient formula and the inefficient one. The inefficient formula would require you to enter 15 different formulas, whereas the efficient formula is entered once and then copied and pasted to the remaining cells in the table.

Absolute vs Relative References

The other feature of an efficient formula is leveraging absolute and relative references. Those $ signs you see in the formula are stipulating when a reference is absolute or relative.

When you copy and paste a formula containing row/column references those preceded by a $ sign do not change (i.e. they're absolute), whereas any reference without the $ sign changes relative to the number and direction of cells it has moved.

Notice how some cell references have the column and row references absolute like this: $D$2

Some just have the column reference absolute: $F2

And others just have the row reference absolute: G$1

I won't go into detail on absolute and relative references here; instead you can click here to see some examples.

If you haven’t mastered absolute and relative references yet I recommend you put them at the top of your β€˜Excel To-Learn list’.

Named Ranges

The above tips will help you leverage Excel and absolute/relative references to do a lot of the work for you, and it's a great start but, those cell references make the formula tricky to read and write.

Let's do better by using Named Ranges instead of cell references.

For example our previous SUMIFS formula can also be written like this (see formula bar below):

using named ranges in formulas

Now, isn’t that easier to read? It's also quicker to write since you can type the name of the range into the formula or press F3 to bring up the list of names to choose from.

Named ranges in their simplest form allow us to give a range of cells a name which can then be used in place of the actual cell references.

For example we can give the following ranges used in our SUMIFS formula names:

  • $D$2:$D$64 name is β€˜Salary’,
  • $A$2:$A$64 name is β€˜Dept’
  • $C$2:$C$64 name is β€˜Year’.

So now this formula:

=SUMIFS($D$2:$D$64,$A$2:$A$64,$F2,$C$2:$C$64,G$1)

Can be written like this:

=SUMIFS(Salary,Dept,$F2,Year,G$1)

Click here to see how to set up Named Ranges.

If you’ve mastered Named Ranges then you might be interested in Dynamic Named Ranges. These are ranges that expand and contract automatically (dynamically) as your data expands and contracts, or based on criteria you stipulate.

You can create a dynamic range using the OFFSET function or INDEX function however, if the thought of using those is a bit scary then there is a very easy way to create dynamic ranges using Excel Tables.

Excel Tables

I’m going to be blunt here; if you aren’t familiar with Excel Tables then you are missing out.

These are one of the most useful features that came out with Excel 2007 and yet they are one of the most underused.

The following features of Excel Tables are going to revolutionise the way you write formulas:

  1. Structured References: This is the name given to the way you reference cells in an Excel Table. Structured references work in a similar way to Named Ranges however, since they’re part of the Excel Table features you don’t need to set them up manually.
  2. Dynamic Ranges: The Structured References are dynamic; this means as you add new data to your Table the ranges automatically grow to incorporate that new data.

Structured References are a great alternative if the idea of having to get your head around complicated OFFSET or INDEX formulas to build your dynamic named ranges doesn't appeal.

Working with Structured References

There are various ways to reference the components of the Table, and in the example below you can see these particular references are made up of the table name (Table1), and column label, which we can either type in or choose from a list.

This ability to choose from a list is one of the great features of Excel Tables. As you type in part of the table/column name a list of names you can use becomes available (similar to named ranges), and you simply select the one you want.

using structured references in formulas

With structured references our formula in G2 becomes:

=SUMIFS(Table1[Salary],Table1[Dept.],$F2,Table1[Year],G$1)

The named ranges have been replaced with the table’s structured references (e.g. Table[Salary] etc.)

There are many more features that come with Excel Tables which I consider a bonus:

  • Filter buttons automatically applied
  • Banded row formatting
  • Flexible Total Row formula using Subtotal
  • Automatic Freeze Pane for column labels
  • And more. Check out all the features of Excel Tables here.

Column Number and Row Number Arguments

There are a few functions which have arguments for row_num or column_num, for example:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

INDEX(array, row_num, column_num)

And it’s not unusual to have multiple columns of VLOOKUP formulas all returning different β€˜col_index_num’s from the same table.

Having to manually update the β€˜col_index_num’ argument is not efficient and it breaks the 'write it once and copy & paste' rule of efficient formulas.

Instead we can use the COLUMN or COLUMNS function and a clever combination of absolute and relative references to dynamically return a col_index_num that increases as you copy the formula across to the right.

The COLUMNS function returns the count of the number of columns in the range you give it, likewise the ROWS function returns the count of the number of rows in the range you give it.

For example, the formula in cell C3 below is =COLUMNS($B$1:B1) which =1 i.e. B1:B1 is 1 column wide. And when copied across to columns D and E the range referenced increases by 1 column (i.e. $B$1:C1 and $B$1:D1) and therefore returns the results; 2 and 3 respectively as we can see in the image below:

using columns and rows functions

So, instead of hard coding the col_index_num argument in your VLOOKUP formula you can use the COLUMNS function to return a dynamic result which increases as you copy the formula into columns to the right.

Tip: Since you are only counting columns you don't have to include the row reference in your range, instead you could also write the formula like this:

=COLUMNS($A:B)

Which would return 2

Here is a VLOOKUP and COLUMNS example which uses the COLUMNS function to return the col_index_num argument for VLOOKUP.

Note; while reading the VLOOKUP COLUMNS example don’t forget that for row_num arguments (in HLOOKUP or INDEX), you’d use the ROWS function instead of COLUMNS.

The Upshot

The point I’m trying to make here is that your formulas should and can be quick to write, interpret and update.

You should be aiming to write one formula for the top left cell in your table, or first cell in your column of formulas and then copy it to everywhere it needs to go. Any alterations to ranges or criteria etc. should dynamically update based on the destination of where you paste the formula.

Tools we can use to help:

  1. Absolute and relative references
  2. Named ranges
  3. Excel Tables
  4. COLUMNS/ROWS functions
  5. Troubleshooting Excel Formulas

The above is a solid start to efficiently writing formulas. If you've got a tip related to this please share it in the comments below for all to benefit.

Writing Excel Formulas Efficiently

More Named Ranges Posts

Relative Named Ranges

Relative Named Ranges in Excel allow you to reuse named ranges with different results dependent on the cell you use them in.
Excel Named Ranges

Excel Named Range Shortcut

This Excel Named Range shortcut using the exclamation mark will enable you to create a global name with local scope.
Excel Dynamic Named Ranges

Excel Dynamic Named Ranges

Excel Dynamic Named Ranges update automatically to include new data in the ranges referenced in your formulas, PivotTables and other Excel tools.
excel named ranges explained

Excel Named Ranges Explained

Excel named ranges can be a huge time saver. They're easy to use and even easier to read in your formulas.

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: named ranges
Previous Post:Create Hyperlinked List of Files in SubfoldersCreate Hyperlinked List of Files in Subfolders
Next Post:Sorting in Excel PivotTablesSorting in Excel PivotTables

Reader Interactions

Comments

  1. anil pandey

    January 5, 2016 at 2:36 pm

    There are subject codes and grades scattered in various columns & rows in excel sheet. How can I extract counted grades & codes in one cell of excel sheet, jointly.
    Please workout it and sent my email address. pandey5375@gmail.com
    for your knowledge yellow subject code 41 find out whole sheet and get answer any formula help.
    CANDIDATE NAME SUBJECT CODE MRK GRD SUBJECT CODE MRK GRD SUBJECT CODE MRK GRD SUBJECT CODE MRK GRD
    RAM 30 99 A1 41 95 A1 30 99 A1 41 95 A1
    SHYAM 41 99 A1 48 95 A1 41 99 A1 48 95 A1
    RAJ 30 94 B1 41 88 A1 30 94 B1 41 88 A1
    PHILP 37 92 A2 42 80 B1 37 92 A2 42 80 B1
    PIKASO 41 80 A2 42 91 A1 41 80 A2 42 91 A1
    PHAS 37 93 A1 42 81 B1 37 93 A1 42 81 B1
    PARI 37 90 A2 42 70 B2 37 90 A2 42 70 B2
    RAJ 30 88 A2 41 68 B2 30 88 A2 41 68 B2
    RAMCHARAN 41 93 A1 42 82 A2 41 93 A1 42 82 A2
    ANUPAM 41 77 B1 42 82 A2 41 77 B1 42 82 A2
    VIJAY 41 80 A2 42 78 B1 41 80 A2 42 78 B1
    VISHAL 41 82 A2 42 73 B2 41 82 A2 42 73 B2
    VINAY 37 82 B1 42 65 C1 37 82 B1 42 65 C1
    VIMAL 41 72 B1 42 67 C1 41 72 B1 42 67 C1

    Automatically answer will come what formula use in it.

    L.E.: SUBJECT CODE 41 GET HOW MANY GRADE A1,A2,B1,B2,C1,C2,D1,D2

    Reply
    • Catalin Bombea

      January 5, 2016 at 5:56 pm

      Hi Anil,
      Please upload a sample file with your data and details on our Help Desk (create a new ticket), it will be easier to understand your situation. It will be very helpful if you create a manual result, to show us how the results should be. Also, please use Google translate to provide clear instructions in English.
      Thanks for understanding.
      Catalin

      Reply
  2. Steele

    May 21, 2015 at 5:54 am

    I have a monthly schedule I recreate in Excel every month. Is there a way to automate the dates? I have Sunday, Thursday and 2nd Saturday classes to fill in and I’m trying to get the dates to fill in based on the month.

    Thanks,

    Steele

    Reply
    • Mynda Treacy

      May 21, 2015 at 3:08 pm

      Hi Steele,

      I expect so but without seeing your file or a sample of your data I can’t advise as there are too many details missing. You can upload your file via the Help Desk. Please ask you question again when you raise the help desk ticket.

      Thanks,

      Mynda

      Reply
  3. Steve

    September 9, 2014 at 6:33 am

    Hi, trying to do several things with an excel file..
    Im trying to group items to get the count, as well as the max date and min date for that particular subgroup.. ie for
    A08 id want the result to be count = 7, min =Feb-10, Max = Jan-12
    A10 id want the result to be count = 2, min = May-06, Max = Jun-10

    etc…

    I was trying to use the sub total function, then tried to figure out how to use the sutotal formula that gives the range of the computation (i.e. SUBTOTAL(3,C2:C8)) to then try a Min/MAX function off of the date column, but cant figure out how to get the min/max functinos to accept a cell range from a text field..

    sample data.. note the full set is 6 mm + lines

    Letter Code date
    A08 Feb-10
    A08 Feb-10
    A08 Feb-10
    A08 Mar-11
    A08 Mar-11
    A08 Jan-12
    A08 Jan-12
    A10 May-06
    A10 Jun-10
    A11 Dec-90
    A11 Jun-09
    A11 Apr-11

    Reply
    • Catalin Bombea

      September 9, 2014 at 2:35 pm

      Hi Steve,
      Can you please upload a sample file on our Help Desk, with details? It’s easier to work on your data structure.
      Thanks for understanding.
      Catalin

      Reply
  4. Angie

    August 20, 2014 at 1:06 am

    When calculating the hours of employees I used this formulas based on my excel data:
    =a10-a11+b10-b11+c10-c11 the total hours worked
    Here’s my confusion, this formula worked for 2 of the 5 employees but when using the same formula with the correct cell information it did NOT show total hours. It gave me like 01:00 or it added 10 more hours to the total…so confused and frustrated

    Reply
    • Catalin Bombea

      August 20, 2014 at 2:51 am

      Hi Angie,
      Please upload a sample file with your calculations and details on what are you trying to do, i will gladly help you πŸ™‚
      Use our Help Hesk.
      Catalin

      Reply
  5. Chris Ferreira

    July 25, 2014 at 1:47 pm

    Hi,
    When using the sumifs formula with tables why is it that you cannot drag a formula in a cell across colums but you can drag it down lines. When copying the formula into columns you have to use the copy & past function.

    Reply
    • Mynda Treacy

      July 25, 2014 at 2:34 pm

      Hi Chris,

      I’m not sure why you have a problem doing this. I tested it and I can use the fill handle to drag my SUMIFS formula across the columns of a Table. Are you referring to how the structured references don’t allow you to easily assign them as absolute/relative?

      Perhaps you can send me your workbook so I can see exactly what you’re trying to do. You can send it via the help desk.

      Kind regarsd,

      Mynda

      Reply
  6. manou

    July 6, 2014 at 8:51 am

    Mynda dear
    Hi
    this yours work very trick nice in the excel.
    thanks, kind regards

    Reply
    • Mynda Treacy

      July 6, 2014 at 11:00 am

      Thank you again, Manuou πŸ™‚

      Reply
  7. ahmed

    June 19, 2014 at 5:02 pm

    i need only one formula please help
    in one row student 1 got marks in diffrent subjects .there is another coloum thats is of remarks.where i want tthe formula
    if student 1 got faild in 3 subjects then name of subjects ,and if fails in 4 subjects then fail must come.otherwise pass
    please give me the formula
    thanks and regards
    hafiz muhammed ahmed

    Reply
    • Catalin Bombea

      June 19, 2014 at 11:02 pm

      Hi Ahmed,
      You can try the sample file created on our OneDrive folder.
      If it’s not what you wanted, then you have to create your own data structure and upload it to our Help Desk.
      Catalin Bombea

      Reply
  8. Harry

    June 1, 2014 at 11:11 am

    I received an Excel file of survey data spread across two sheet ( 40 rows and columns A – AE). I would like to create a report with some thing other than multiple column sorts. Any suggestions? Thanks

    Reply
    • Mynda Treacy

      June 1, 2014 at 1:51 pm

      Hi Harry,

      Without seeing the data it’s a bit difficult to suggest ways to analyse it and present it in a report. However, since you’ve collated survey data you should be trying to answer the questions that were the reason for the survey in the first place. i.e. what were you hoping to learn from the survey?

      Also, sometimes it’s good to create some PivotTables from the data to help understand it and from there you might be able to identify patters/relationships.

      Kind regards,

      Mynda.

      Reply
  9. Robin Zang Santos

    May 29, 2014 at 8:44 pm

    On “Writing Excel Formulas Efficiently”; the “SUMIFS” – I didn’t know that if you kept the “$” on just the letter ($F2) or just the number (G$1) of the formula you could copy down or copy across a specific column/row. Thanks for your fantastic site, it has helped me a lot.

    Reply
    • Mynda Treacy

      May 29, 2014 at 8:52 pm

      Absolutely, Robin πŸ˜‰

      Glad we could help.

      Mynda

      Reply
  10. Duncan Williamson

    May 29, 2014 at 11:58 am

    Very good insights, especially including tables: so useful.

    I would like to add one tip I always give: PPP.

    Especially if you are unsure or doing something complex, start with PPP

    Paper
    Pencil
    Plan

    THAT saves a lot of time and frustration.

    Duncan

    Reply
  11. Skip Page

    May 29, 2014 at 8:11 am

    I really like your Formula Tips!

    One of the first things I teach people when first learning to write complex nested formulas is to create the formula one step at a time from the inside to the out side. This is especially true for formulas containing logical functions. Key is to test at each step.

    The advantage is that the writer is more assured that each element of the formula is working properly. Moreover, it is easier to debug when a step is not working as expected.

    Reply
  12. Lori

    May 29, 2014 at 2:55 am

    Once again you provide amazing Tips and Technique to make life in the world of Excel a better place! Great tips and well described with examples so that I can easily adopt them. THANK YOU!

    Reply
  13. Franee

    May 29, 2014 at 12:05 am

    You are one AWESOME bundle of Excel knowledge. Thank you for this great post.

    Reply
  14. GJ Case

    May 28, 2014 at 11:42 pm

    Mynda:

    This is an excellent tutorial, and one to which I plan to refer my employees (and my kids, who often call asking for Excel help).

    One thing you might add to your list, which I find to be a big help, is IFERROR. Occasionally a formula may legitimately result in an error; for instance, a divisor may sometimes be zero or a lookup value may not yet exist in a table. While accurate, these can spoil the looks of a report or table. =A3/B3 will result in #DIV/0! if B3 is zero, but if I use =IFERROR(A3/B3,””) instead, then I get a blank in the results rather than an error. And this is much easier than trying to do the same with just an IF statement.

    In line with trying to breakproof formulae as noted in some of the comments, I have had recent issues using Excel to do lookups on data downloaded from the web. Sometimes the lookup names contain a trailing space which will invalidate the results. I have taken to using a lookup which starts assuming the value has a trailing space, and trimming it if that’s an error. This put an end to my having to rework things periodically:

    =IfError(Vlookup(A1,DTable,2,FALSE),VLOOKUP(TRIM(A1,DTable,2,FALSE)

    Keep up the good work!

    Reply
    • Mynda Treacy

      May 29, 2014 at 12:38 pm

      Great tips, GJ.

      Another way to use TRIM would be to put it in the VLOOKUP so every lookup value is trimmed first e.g.:

      =VLOOKUP(TRIM(A1),DTable,2,FALSE)

      That way the VLOOKUP is only being calculated once. Not a big deal in most cases but if you’ve got a lot of VLOOKUPs you might find Excel is slow to calculate as it does the first VLOOKUP then returns and error, and then completes the second VLOOKUP. Applying TRIM to every lookup value is not a complex calculation in comparison.

      Cheers,

      Mynda

      Reply
  15. Milt

    May 28, 2014 at 11:07 pm

    Hi Mynda,
    Your Tips are great. As a programmer I use tables and ranges because usually table headers and ranges names are easy to understand. You should all ways remember that someone else my have to work on your formulas if you can not. Also Colin is correct you need to write so you are not all ways have to change formulas with data grows.

    Reply
  16. Jef

    May 28, 2014 at 10:20 pm

    Great hints. This is definitely the way to go. Thanks for sharing.

    Reply
  17. Eugene

    May 28, 2014 at 9:43 pm

    all these formulas are helpful especially for me as I am using it on a daily basis with my report.. I mean most of it. I am glad that I had learned a lot from your post and helped me simplify my task..

    Thanks

    Eugene

    Reply
    • Mynda Treacy

      May 29, 2014 at 12:29 pm

      @Franee, Wow! Thanks πŸ™‚

      @Eugene, Thank you. It’s rewarding to know we’re helping real people and not just sending stuff into cyberspace:-)

      @Lori, It’s great to know that you find it easy to digest. when you know a topic it’s sometimes difficult to know how far in the ‘beginning’ to actually begin.

      @Jef, You’re welcome.

      @Milt, Glad to hear you’re one of the minority who have embraced Excel Tables.

      @Skip, writing the formula inside out is a great tip. I also like to start with helper columns and then when each component is working as it should you can consolidate the formulas from the helper columns into one.

      @Duncan, I like your PPP acronym. I also teach this in my Dashboard course, although I didn’t have a snappy acronym for it πŸ™‚

      Reply
  18. Karine

    May 28, 2014 at 7:27 pm

    Hi Mynda,
    Thanks for this useful update. Just one question. When I write formulas in an xls table, as you mentioned the structured ref automatically pops-up but in that case I am confused how I should absolute them. How to insert the $ when you get Table1[Dept.] for example. Where should I put the $ sign if I want to absolute the full cell or only the column or row?
    Regards,
    Karine

    Reply
    • Mynda Treacy

      May 28, 2014 at 8:11 pm

      Hi Karine,

      It can be done and Jon Acampora has written a great tutorial on how to make structured references absolute, he’s also done a video.

      Cheers,

      Mynda.

      Reply
  19. John Michaloudis

    May 28, 2014 at 6:23 pm

    Hi Mynda,

    Great post! I always say that a formula has to be like a living source, being fed continuously to stay alive. The best way to do this is to reference it to an Excel table, just like you mentioned.

    As the Excel Table is fed new data in to it, the formula grows, without having to tweak it.

    Static formulas tend to die off with time, unfortunately.

    A good tip that I find handy for multiple function formulas is to separate them by pressing Alt+Enter, so each function has its own line in the formula bar.

    Cheers, Im off to feed my formulas πŸ™‚

    Reply
    • Mynda Treacy

      May 28, 2014 at 8:06 pm

      Hi John,

      I like the Alt+ENTER in the formula to separate nested functions. Great tip, thanks.

      Hope your formulas appreciate their data dinner πŸ˜‰

      Mynda.

      Reply
  20. Paul

    May 28, 2014 at 5:01 pm

    Just to add to the 3 reasons for not hard coding criteria and to expand on Col Delane’s point, my 4th point would be “Safer”.
    It is too easy to forget to update the criteria or, perhaps worse, only update some of the criteria when there is a need for change. A classic over here in the UK is for VAT (sales tax) rates to be hard coded and then not updated when the rates change.

    Reply
    • Mynda Treacy

      May 28, 2014 at 8:04 pm

      Hi Paul,

      Absolutely, agree. Instead of VAT we call it GST here and the rate hasn’t changed since it was introduced. I imagine when it does change there will be a lot of ‘Find and Replace’ going on, instead of a nice quick edit to one cell.

      As an alternative I also like to put criteria like that in the Name Manager like so:

      named constants

      Then use it in a formula e.g. calculate the GST on the value in cell A1:

      =A1*GST

      Mynda.

      Reply
  21. Gabe M

    May 28, 2014 at 12:08 pm

    Thanks Mynda,

    I agree with most everything, especially regarding the utility of Tables. Using a formula to generate col_index_num (so that it can be copied) is also a great tip when you have a large number of contiguous vlookup references, or if the table is so extensive counting columns is error-prone. However when the number is small, or discontinuous columns are to be referenced, I find it simpler just to hard code the relevant column number.

    Reply
    • Mynda Treacy

      May 28, 2014 at 12:14 pm

      Cheers, Gabe.

      I agree, if your VLOOKUP’s don’t reference contiguous columns then hard keying the column number is the way to go.

      Mynda.

      Reply
      • Colin

        May 28, 2014 at 1:09 pm

        I understand the issue with non-contiguous ranges, but by hard-keying the value you’re still at risk of the index No. becoming invalid through inserting or deleting columns between the lookup value and return value columns. The way I overcome this is to use the following formula to return a dynamic column index value:
        =Columns([cell ref. in lookup column]:[cell ref. in return value column])
        or
        =Column([cell ref. in return value column]-[cell ref. in lookup column]+1)

        Regards
        Col Delane, Perth

        Reply
        • Mynda Treacy

          May 28, 2014 at 1:16 pm

          Good idea to consider making it ‘break proof’. Alternatively you could use MATCH for the col_index_num.

          Reply
      • MF

        May 28, 2014 at 6:26 pm

        If flexibility for your vlookup is not a critical issue and if you are talking about a very small number of columns, to hard-code the column index may not be a big problem.
        But I prefer to use MATCH to identify the column index in vlookup.

        Cheers,

        Reply
        • Mynda Treacy

          May 28, 2014 at 8:09 pm

          Hi MF,

          I (secretly ;-)) prefer MATCH too but if your column labels don’t ‘match’ then COLUMNS is an alternative.

          Cheers,

          Mynda.

          Reply
  22. Colin

    May 28, 2014 at 11:39 am

    Hi Mynda
    Love your work – I’m often forwarding your tips to my work colleagues in an attempt to get them to understand the power of our favourite productivity tool.

    Re Writing Excel Formulae Efficiently, I have a couple of comments about writing formulae that your tip clearly demonstrate:
    – Here’s a question every Excel user should ask themselves every time they write a formula: β€œHow can I write this formula so that I only need to do it once?”
    – Never (well, almost never!) embed constants (e.g. β€œAdmin” or 2012) within formulae, but rather place them in separate input cells and refer to those cells in the formula.

    Regards
    Col Delane, Perth

    Reply
    • Mynda Treacy

      May 28, 2014 at 11:44 am

      Cheers, Col.

      I like your ‘question’ idea. It’s a good way to sum it up and keep it in mind.

      I also agree with your theory on embedding constants. Another great place to store them is as a name in the Name Manager.

      Mynda.

      Reply
  23. Rafi Wartayan

    May 28, 2014 at 11:38 am

    Hello Mynda
    thank you very much for your fantastic information about Excel
    it is really like a miracle information I will teach my son on this
    systems once again I will Highly appreciate to your Kind help
    withe my sinisterly greetings to you

    Reply
    • Mynda Treacy

      May 28, 2014 at 11:42 am

      Thank you, Rafi πŸ™‚ Great to know we can help, and the more the better.

      Reply
  24. pmsocho

    May 28, 2014 at 7:10 am

    Great post! Thanks for sharing!

    Reply
    • Mynda Treacy

      May 28, 2014 at 9:05 am

      Thanks, Pmsocho πŸ™‚

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x