Excel TV ExcelTV
Excel Functions & Formulas

Using Named Formulas In Excel – Like Yesterday()

Updated
Using Named Formulas In Excel – Like Yesterday()

What if there is a way to define new formulas in Excel without using VBA? One would be very skeptical of any such claims. But, believe it or not, it is true. Think about how easy such a technique would make your life. Not only would it allow you save a lot of time, the technique is also very handy in simplifying or restricting complex formulas to stop your audience from making blunders while using your files. Moreover, it’s extremely simple to do!

Our very own Excel expert Szilvia Juhasz has uncovered this life-saving functionality for the fans, giving us all the more reasons to thank her.

So, let’s see what the technique actually is.

1 – Name Manager

We all are aware of how we can name ranges using the Name Manager. Let’s review it:

  1. 1Select a range (or even a cell) with some data.
  2. Go to the Formulas tab, and click on Name Manager.
  3. Now click “New”.
  4. Type in any name, without spaces, for the range. We will use the name “ABC”.
  5. Now click “OK” and then click “Close”.
  6. In any cell outside the range ABC, type a sensible formula which can take ABC as an input. E.g., =PRODUCT(ABC).
  7. And you would see that it works perfectly well.

2 – Advantages of Named Ranges

There are 2 apparent advantages of using named ranges.

  1. We do not have to worry about selecting the range (especially if it is big) repeatedly and carefully. It only has to be done once, followed by naming it.
  2. If our workbook has many sheets, switching between sheets again and again to select different ranges can be a pain. Named ranges eases the process dramatically.

Also, if we give meaningful names to our ranges, worries about getting confused or memorizing the names go out the window.

3 – The Trick

The trick uses the Name Manager and some ingenuity. Let’s take an example: we have the formula TODAY() in Excel which returns today’s date. But we do not have a corresponding equivalent for yesterday, or even tomorrow for that matter. Well, with this trick, we do.

  1. 2Go to the Name Manager and click “New”.
  2. In the ‘Name’ box, type the name to be given to the function. It will be “Yesterday” for the sake of this example.
  • Now, in the ‘Refers to’ box, type the formula, which would be “=TODAY()-1” in this case.
  1. We can write any comments explaining the formula if we wish.
  2. Now click “OK” and then click “Close”.

4 – The Test

Go to any cell and type “=Yesterday”. And, there you have it!

Note that you do not need any parentheses in this case. Also, set the cell to any date format for it to appear like we would want.

What’s next?

Try it out! Now try it some more. Let’s make our lives simpler. And, share, share, share! Help the knowledge of your friends and colleagues grow.

Also, do comment below about any creative uses of this technique you might have come across.

Written by

Jamani Arsalan
Jamani Arsalan

Consultant, International Professional Services

  • Project Management
  • Statistical Analysis
  • Health Analytics
I am a consultant, currently based in Middle East, at an international professional services firm. My work largely revolves around project management, and statistical analysis. And my professional interests include developing my knowledge within the discipline of health analytics.

Read more articles by Jamani Arsalan

Editorial standards

Fact Checking & Editorial Guidelines

Every article on Excel TV is held to a published editorial standard. The goal: accurate, current, and useful — without filler.

  1. Expert review. Drafts on technical Excel topics are reviewed by a contributor with hands-on, working knowledge of the feature being covered.
  2. Source validation. Claims about Excel behavior are tested in current Microsoft 365 builds. Third-party product claims are sourced from the vendor's own documentation.
  3. Disclosure. Affiliate links, sponsorships, and any commercial relationships that influenced a piece are disclosed in-line and at the foot of the article.
  4. Updates. Articles are revisited when Microsoft ships changes that affect the content. The most recent revision date is shown on every post.

Spot a problem? Email editor@excel.tv and we will look at it.

Subject-matter review

Reviewed by Subject Matter Experts

Technical Excel articles are reviewed by contributors with verifiable, hands-on experience in the topic — not generalist editors.

  • Qualified reviewers. Reviewers include Microsoft Excel MVPs, working business-intelligence practitioners, and Excel TV editorial staff. See each author's page for credentials.
  • Current to a known Excel build. Procedural articles state which Excel version they were validated against. Where Microsoft has since changed behavior, the article carries an inline update note.
  • Clarity check. Reviewers verify steps are reproducible by a reader at the assumed skill level — not just technically correct in a vacuum.

Want to contribute or review for Excel TV? See the about page.

Comments (3)

Historical comments preserved from the WordPress archive. Commenting is no longer active.

  1. mike

    Very interesting! Can you imagine any ways to hack in a function parameter? Inquiring minds want to know!

  2. X Szil

    Hi Mike. Yes! One could imagine hacking in function parameters. You could build a UDF using VBA, then use said UDF name in your drop down list. Or, you can use an existing Excel function that pulls argument values from cells on the worksheet, as demonstrated here: https://www.youtube.com/watch?v=G22rA-3PCio

  3. Cad Delworth

    Here’s a good one, which you can give the name SHEETNAME, or (my preference) CURRENTSHEETNAME:

    =MID(CELL(“filename”),FIND(“]”,CELL(“filename”))+1,31)

    You have to enter this PRECISELY the way it appears above, so copy/paste it folks. Also NB: “filename” is NOT a placeholder! it is to be typed as shown or this will not work.

    You will almost certainly need to Save your workbook BEFORE you add this function to it; I’ve only ever typed (or pasted) this into workbooks which had already been saved.