• 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

Excel Factor 12 Secret EVALUATE Function

You are here: Home / Excel Formulas / Excel Factor 12 Secret EVALUATE Function
Excel Factor 12 Secret EVALUATE Function
August 29, 2012 by Mynda Treacy
This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee.

Suppose you receive a spreadsheet with calculations, but the calculations are stored as text, for display purposes only, and not used to actually derive answers.

Without wanting to reinvent the wheel, you decide to try and use the documented versions of the formulas as actual number crunching tools.

Let’s set the stage for how this is accomplished.

The spreadsheet looks something along these lines:

Excel EVALUATE function

Since there are no equal signs in front of any of the equations, Excel interprets them as simple text strings.

If you would like to know the answers to these equations, but retain the original text in Column A, a common approach would be to use the CONCATENATE function to produce the following functions in Column B:

Excel EVALUATE function

OR

Excel EVALUATE function

 

Unfortunately, this does not work and yields the following result.

Excel EVALUATE function

Because the original data was seen as text, the CONCATENATE function does not attempt to perform any sort of calculation and merely connects the two text strings together.

This is where an old, forgotten Excel 4.0 function comes into use: the EVALUATE Function.

EVALUATE is an Excel v4.0 macro function which is still packaged and supported in Excel 2010. The EVALUATE function allows for the evaluation of a text equation as an algebraic equation.Β 

The odd thing about the EVALUATE function is that it cannot be used directly in a cell, like SUM or AVERAGE.Β The function can only be utilized with the confines of a NAMED RANGE. If you attempted to use it in the following manner, =EVALUATE(A1), you will be presented the below error message.

Excel EVALUATE function

The trick is to program the EVALUATE function as a NAMED RANGE and then call the name from the target cell.Β This is how the process works:

  1. Select cell B1
  2. Go to the FORMULAS tab and click Define Name
  3.  

    Excel EVALUATE function

  4. In the New Name dialog box, type the name Result (this can be any valid range name you like)
  5. In the Refers to: box type: =EVALUATE($A1)
  6.  

    Excel EVALUATE function

  7. Click Add then OK.

It is very important to note that you select cell B1 and used a Relative Row reference for $A1.

Now enter =Result into cell B1 and copy it down for the remaining cells.

Excel EVALUATE function Excel EVALUATE function Excel EVALUATE function

(1) First Pass

(2) First Answer

(3) Fill Down

Keep in mind, since this is actually an embedded Excel macro, you must save the file as an Excel Macro-Enabled Workbook (.XLSM) file.

USING THIS IN EVERYDAY SITUATIONS

Now that we understand this new (albeit old) power, how can it be used in more typical scenarios?

Excel 2007 introduced us to an improved version of Tables which have this wonderful ability to create formulas on the Totals line with convenient drop-down lists.

That’s great and easy, but what if we want that same drop-down functionality in a traditional table, or we want to have the drop-down located at the top of the data (or anywhere for that matter?)

Excel 2007/2010 Data Tables function selection drop-down

Excel EVALUATE function

Data Table functionality at top of sheet

Excel EVALUATE function
 

We want to be able to go from one set of formula choices…

Excel EVALUATE function

…access a drop-down list of alternate choices…

Excel EVALUATE function

…and dynamically create a new summary of calculations.

Excel EVALUATE function

The steps are the same as outlined above, but in this case we need to distinguish between the Cost (column "G" data) calculation and the Sales (column "H" data) calculation.

To simplify matters, we will add the entire column so the user can add data to the table and the calculations will still work.

Excel EVALUATE function

Place your cursor in cell L5 and create the following Named Range

Excel EVALUATE function
 

Place your cursor in cell L6 and create the following Named Range

Excel EVALUATE function

 

IMPORTANT: Because we will not be filling this functionality down cells, and each cell points to a different data range, the reference pointers must be completely absolute.Β  $-signs in front of the column letter and the row number.

The final step is to type =Cost_Result in cell L5 and =Sales_Result in cell L6

Excel EVALUATE function

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 .xlsm example file.

Thanks again, Bryon for putting in so much effort to write this tutorial and include loads of images to help us follow along. We appreciate you sharing your knowledge.

Bryon is from Bristol, Tennessee and has been teaching Excel since version 7 which was included with Office 95. He is currently a Technical Training Analyst for one of the largest coal companies in the world.Β  His key responsibility is to conduct all Microsoft Office training, but in addition he also serves as a technical consultant for any and all projects involving Microsoft Office applications.

Bryon says β€œExcel is by far my favorite among all the Office applications due to the almost infinite number of situations it can be used.Β  There literally seems to be no end to its usefulness.

My favorite Excel tools are difficult to narrow down.Β  Without getting into third party add-ins, I would have to say PivotTables (especially with the addition of Slicers.Β  WOW!Β  Are those things awesome!!), the Text to Columns tool (what a time saver), and Macros.”

Vote for Bryon

If you’d like to vote for Bryon’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment to thank Bryon for taking the time to share his knowledge….or all of the above πŸ™‚

Excel Factor 12 Secret EVALUATE Function

More Excel_Factor Posts

Excel Factor Voting Roundup

Excel Factor Voting Roundup

Excel Factor 21 Hyperlink Triptych

Excel Factor 21 Hyperlink Triptych

Tips and tricks with hyperlinks to make them dynamically update as the selection in the sheet changes. Sample workbook available.
Excel Factor 20 Custom Number Format Disguise

Excel Factor 20 Custom Number Format Disguise

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

The Excel HYPERLINK function creates links to places inside or outside of your Excel file. I'll show you a shortcut for creating them that few people know.
Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 13 Handy Tips & Tricks

Excel Factor 13 Handy Tips & Tricks

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: Excel_Factor
Previous Post:Excel Factor 11 Forecasting TricksExcel Factor 11 Forecasting Tricks
Next Post:Excel Factor 13 Handy Tips & TricksExcel Factor 13 Handy Tips & Tricks

Reader Interactions

Comments

  1. Samuel

    June 23, 2022 at 10:56 pm

    I defined a name Like This:

    =EVALUATE ( OFFSET ( INDIRECT( “RC”,FALSE), 0 , -1 ) )

    It evaluates from the left cell wherever you use it.

    Reply
    • Mynda Treacy

      June 24, 2022 at 8:18 am

      Thanks for sharing, Samuel!

      Reply
  2. Andrew

    November 7, 2019 at 1:24 am

    I have Office 365 and Evaluate doesn’t seem to work the way you describe anymore. I can only specify what I want to evaluate in the Name Manager. Naming a specific reference while calling the defined name in a cell doesn’t work. Example: if I define a name Result with Refers to: Evaluate($A1) and then type in cell B1 =Result(A1) I get an error #VALUE!. However if I just call =Result it will properly evaluate the formula in A1 however I am thus unable to evaluate a formula in any other cell with this same named function.

    Reply
    • Mynda Treacy

      November 7, 2019 at 2:18 pm

      Hi Andrew,

      I think you’re a little confused. It doesn’t say to use =Result(A1), it says =Result

      You also need to ensure you select cell B1 before you define the name because this is a relative named range i.e. it’s relative to the cell in which you use it. More on relative named ranges here.

      I hope that clarifies things, but shout if you have more questions.

      Mynda

      Reply
  3. Srth

    May 1, 2019 at 4:07 am

    Doesn’t seem to work well with workbook reference. My original formula works without opening source file, but once I use Evaluate method it shows #Ref error until I open source.
    (There are other reasons why I can’t use original formula)

    Reply
    • Mynda Treacy

      May 1, 2019 at 8:55 am

      EVALUATE can’t reference a closed workbook. Perhaps you can post your question and sample Excel file on our forum where we can help you find an alternate solution.

      Mynda

      Reply
  4. Sean Duffy

    October 24, 2018 at 9:13 am

    Hi,

    This tool is great, and is very close to solving my problem – it its automatically updating ie when the target workbook cell is updated and both documents saved the =Evaluate named range does not update. I have to double click the cell with the =evaluate named range to get it to update. Any ideas how to fix this?

    Reply
    • Catalin Bombea

      October 25, 2018 at 2:50 pm

      Hi Sean,
      Try to add a volatile function to the Evaluate function, like =YourFormula+Now()*0
      The NOW function is volatile, will be recalculated at each excel recalculation.
      Cheers,
      Catalin

      Reply
  5. Hoang

    April 24, 2018 at 6:56 pm

    Thank you for sharing πŸ™‚

    Reply
  6. Didson New Pereira

    July 8, 2017 at 11:32 am

    Thank you very much for the well explained topic as well as the details in others questions below.
    I tested this solution and it works like a charm for what I intend to do while I am using simple funtions, however, when I try to put on some matrix referencing formulas, or even quite a few conditionals, it starts to give me #value errors.
    Other thing that would be really really awesome is if I was able to use this kind of funtion on other areas such as in imput formula for conditional dropdown lists in cells, or conditional formatting.
    Would anybody know if there are limitations of arguments to use the Evaluate funtion, and if there is any way to implement it in lists or conditional formatting?

    Thanks !!

    Reply
    • Catalin Bombea

      July 8, 2017 at 3:22 pm

      Yes, there is a limit of 255 chars per string evaluated, if your formula text exceeds this limit, the Evaluate function will fail.
      You can use it anywhere you like, but I’ve never seen this function in a conditional formatting formula, you can build very complex formulas without the need of Evaluate. However, you know best what scenario you have, if you feel it’s easier with Evaluate, then use it, the final result is what matters.
      Catalin

      Reply
  7. JeteMc

    June 27, 2017 at 1:53 am

    Thank You for taking the time to post this most helpful tip. I hope that you have a blessed day.

    Reply
  8. Kohlman

    April 1, 2017 at 4:42 am

    Does this work on Excel for Office 365 on a Mac? I have version 15.32 and it looks like they have done away with the ability to have the referenced range ($A1 in the case above) be a formula.

    I also wasn’t able to find the evaluate formula on my version of Excel.

    Any thoughts would be appreciated.

    Thanks!

    Reply
    • Catalin Bombea

      April 1, 2017 at 11:06 pm

      Hi,
      As described in this article, Evaluate is not a sheet function, it’s an Excel 4 macro function, that can be used in a defined name, not in a cell directly.
      I am not aware if Microsoft still supports old macro functions for Mac 2016, but you can test that very easy: Type in cell A1 the value 1, in cell A2 type 2, then define a new name(TestEvaluate), with this formula in the Refers To field: =Evaluate(“=A1+A2”). In any cell, type this formula: =TestEvaluate . If this cell displays the correct result, you have support for excel 4 macro functions.

      Reply
      • Kohlman

        April 14, 2017 at 6:12 am

        Mac 2016 does not support Evaluate function. Terribly unfortunate.

        I am using it in a Virtual Desktop now, and the Evaluate function is supported, and I was able to get the correct answer per your TestEvaluation idea above.

        However, I am now trying to evaluate a named range that refers to =EVALUATE (Sheet1!L17) when the contents of cell L17 is (text) “= ‘\\apkvf01\ tsredirect$\ kohlman\ Desktop\ [Test 2.xlsx] Sheet1’!A4”.

        I am trying to pull data out of a closed document, and I cannot open the document. It is part of a bigger project.

        Thanks!

        Reply
        • Mynda Treacy

          April 14, 2017 at 8:31 am

          Hi Kohlman,

          I’m pretty sure the EVALUATE function doesn’t work with closed workbooks. I say ‘pretty sure’ because there is very little documentation on this function since it’s not officially supported.

          Mynda

          Reply
          • Didson New Pereira

            July 8, 2017 at 11:17 am

            Hey Kohlman,
            Seems that what you’re trying to do can be accomplished by using power query. I was struggling with having to get data from several workbooks without needing to open them. Well, power query does just that!

          • Mynda Treacy

            July 8, 2017 at 2:18 pm

            Hi Didson,

            Indeed, Power Query can do that and more, now. This post was written in 2012, before Power Query was around πŸ™‚

            Mynda

  9. Dan

    February 4, 2017 at 9:18 am

    I’m trying to use this method with a formula that contains a file path to an outside workbook. I can get your example to work using as you outlined but when I try the following I get a value error:

    AVERAGE( IF (‘R:\CRC\ AA-DL-UA Stopsale\ DL Stopsale\ [02-02-17 DL Stopsale.xlsx] Stopsale’!$C:$C = C2, IF ( ‘R:\CRC\AA-DL-UA Stopsale\ DL Stopsale\ [02-02-17 DL Stopsale.xlsx] Stopsale’!$D:$D = D2, ‘R:\CRC\ AA-DL-UA Stopsale\ DL Stopsale\ [02-02-17 DL Stopsale.xlsx] Stopsale’!$Q:$Q,””) ) )

    Reply
    • Catalin Bombea

      February 4, 2017 at 3:14 pm

      Hi Dan,
      Have you tried with the other workbook open? From your formula, seems that the workbook is closed.
      Catalin

      Reply
  10. Ronnel

    September 26, 2016 at 12:36 pm

    I used this to method to create a formula string to reference a cell in another spreadsheet.

    =CONCATENATE( “=VLOOKUP(Q1,'[DriverLoans-“, A4, “.xlsx]Sheet1′!$A$1:$D$100, 3, FALSE)” )

    Where A4 is the name of the driver. It works fine as long as I go to a cell, enter the defined name that EVALUATES the above formula, then hit Enter. But I expected it to AUTO REFRESH each time the DriverLoans-[driver].xlsx is updated, without having double click or F2 (edit) the cell then Enter. At the least, I expected it to REFRESH each time I open the spreadsheet or manually refresh.

    I even created a Macro to record a series of F2, then Enter, to mimic a manual update, but the Macro resulted in putting blank in the cells. Which is bizarre because manually doing it works. But recording the manual steps in a Macro causes it to put blank in the cells.

    I already saved it as a .xlsm and turned all all automatic updates in the FILE OPTIONS menu.
    Is there a way to make this automatically update or is there another way that allows for automatic update?

    Reply
    • Catalin Bombea

      September 27, 2016 at 3:05 pm

      Hi Ronnel,
      You can try adding a volatile function like Now(), these type of functions are recalculated each time. Adding NOW()*0 to your formula will not change the result of your calculations. If your formula is expected to return text strings, not numbers, then adjust it to your needs, to add a zero length string instead of a zero: IF(Now()*0=0,””,0)
      Catalin

      Reply
  11. Chirag

    July 17, 2015 at 3:26 pm

    Hi Mynda

    Very helpful trick for Dashboard. I am facing one issue, I have a name range for few cells and I wants to use this EVALUATE, however I am not able to get correct result. For Example, Range Name is MyRange for cell A1:A10 value of which is 1, 2, 3,…, 10 I wants to calculate SUM, MAX, MIN, AVERAGE, COUNT – selection from Data Validation List

    I created Name Range EVAL with following formula:
    =EVALUATE (“=”& Sheet2!$B$2&” ( “&MyRange)

    I selected SUM from List, I wrote in “C2” as =EVAL and I got SUM result as 2 instead of 55 and if I write =EVAL in “C3” then I am getting SUM value as 3 instead of 55

    I modified EVAL as follows
    =EVALUATE (“=” & Sheet2!$B$2& ” ( “&MyRange&” ) “)
    But same result

    Will you please help?
    Thanks
    Chirag

    Reply
    • Catalin Bombea

      July 17, 2015 at 6:21 pm

      Hi Chirag,
      Can you please upload a sample file on Help Desk? It will be very helpful to see what you did there and to understand where the problem is.
      Thanks for understanding.
      Catalin

      Reply
    • Catalin Bombea

      July 17, 2015 at 11:07 pm

      Hi Chirag,

      The Evaluate function will not accept a named range, you have to provide the reference as a text: A1:A10.
      If you provide a named range instead of a text reference, Excel will create your function for the A1:A10 range (which contains numbers from 1 to 10) like this:
      {“=SUM(1)” ; “=SUM(2)” ; “=SUM(3)” ; “=SUM(4)” ; “=SUM(5)” ; “=SUM(6)” ; “=SUM(7)” ; “=SUM(8)” ; “=SUM(9)” ; “=SUM(10)”}

      There is a way to convert back the name to a text reference, but it involves another excel 4 macro function:
      Use this in your EVAL name:
      =EVALUATE(“=”&Sheet1!$B$2&” ( “&REFTEXT(MyRange,Sheet1!$A$1) &” ) “)
      The REFTEXT function will convert your named range to a text, and the Evaluate function will be able to calculate the entire range, not only the implicit intersection:
      This is what Evaluate function will receive from REFTEXT function:
      {“=”&SUM&” ( “&”A1:A10″&”)”}
      This way, you will be able to use a dynamic range inside Evaluate function.
      Cheers,
      Catalin

      Reply
  12. Joe Mallaci

    January 23, 2015 at 1:24 am

    Love this function, wish it were a regular function that allowed nesting.

    However, Evaluate function does not seem to auto-calculate. Does anybody have a solution for this?

    Joe

    Reply
    • Catalin Bombea

      January 23, 2015 at 5:02 am

      Hi Joe,
      Use a simple trick: use a volatile function to force a recalculation every time excel recalculates, without changing the result of your calculations: =EVALUATE($A1) +NOW()*0
      NOW()*0 will always be zero, so it will not affect your result, but this function is volatile, so it will be recalculated every time a cell changes.
      Cheers,
      Catalin

      Reply
  13. Martin

    October 18, 2014 at 8:42 pm

    Thank you very much. But I have this problem: If it is written A2+A3 in cell A1 (in your example) and I change value in source cell (A2 or A3 in your example), the value in B2 is not re-evaluated automaticly. I must press Ctrl+Alt+F9. I would like to make set of formula choices. But I dont want to press Ctrl+Alt+F9 after every change in source cells. Can you help me, please?

    Thank you once more.
    Martin

    Reply
    • Catalin Bombea

      October 18, 2014 at 10:43 pm

      Hi Martin,
      Is the calculation mode set to Automatic? Check this setting, can be found on Formulas tab, Calculation section, select Automatic from Calculation Options.
      Let us know if this was the problem.
      Cheers,
      Catalin

      Reply
      • Martin

        October 21, 2014 at 6:59 am

        Hi Catalin, thanks for your answer. The calculation mode is set to Automatic. All of formulas are calculated automatically, only formulas with Evaluate function aren’t.

        Best regards

        Martin

        Reply
        • Catalin Bombea

          October 21, 2014 at 2:28 pm

          Hi Martin,
          A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. EVALUATE is a nonvolatile function, it will be recalculated only when you make changes to the text in A1, as it’s not directly dependant to A2 or A3!
          In VBA, it’s easy, we can add Application.Volatile to make an UDF volatile:
          Function EVALUATEString(ref As String)
          Application.Volatile
          EVALUATEString = EVALUATE(ref)
          End Function

          You can try this version of VBA EVALUATE function. Use it in sheet cells like this: =EVALUATEString(A1)
          But there is a way to make a nonvolatile function volatile: for this, we have to use a VOLATILE function (like NOW() function )in our defined name:
          =EVALUATE(Sheet6!$A1) + NOW()*0
          Of course, i had to multiply NOW with 0, to keep the original calculation intact.
          Hope it’s clear πŸ™‚
          Regards,
          Catalin

          Reply
          • Martin

            October 29, 2014 at 7:56 am

            ItΒ΄s super trick! (…+NOW()*0).
            Using VBA Function EVALUATEString helped me too.
            Thank you very much, my problem is solved and I learned new knowledge of Excel.

            Martin

          • Catalin Bombea

            October 29, 2014 at 1:12 pm

            You’re welcome Martin πŸ™‚

  14. Cary Bondoc

    August 14, 2014 at 11:13 am

    Thank you so much for this, you really saved us!

    Reply
    • Mynda Treacy

      August 14, 2014 at 1:37 pm

      You’re welcome, Cary. Glad we could help.

      Reply
  15. s

    April 28, 2014 at 6:09 am

    Thank you. It was excellent and helped me much.

    Reply
    • Mynda Treacy

      April 28, 2014 at 9:13 am

      You’re welcome, S πŸ™‚

      Reply
  16. MMI

    August 20, 2013 at 10:48 pm

    NICE THANKS,BUT SAME NAME IS NOT WORKING IN OTHER SHEET (MULTIPLE) WITH IN SAME WORKBOOK

    Reply
    • Mynda Treacy

      August 21, 2013 at 1:43 pm

      Hi MMI,

      I’m not sure what you mean. If you’d like to send me your workbook via the help desk I can take a look at the problem.

      Kind regards,

      Mynda.

      Reply
      • MMI

        August 21, 2013 at 10:29 pm

        HI MYNDA
        THANKS, FOR YOUR PROMPT RESPONSE.I WILL SEND YOU MY FILE.

        Reply
  17. Amirali Makan

    April 25, 2013 at 3:07 am

    Could have never guessed! Has been of great help in my work. Thank you.

    Reply
  18. Ricardo Hernandez Tablas

    April 5, 2013 at 1:30 am

    When you create formulas with text functions, and you added to the formula the equal sign, for example =25+34, like in the fourth figure that is in the post. Also works if you use command find and substitute de equal sign for another equal sign, then Excel will transform the text formula into a formula.

    Reply
    • Carlo Estopia

      April 6, 2013 at 11:29 pm

      Hi Ricardo,

      Nice Info right there.

      Thank you.

      CarloE

      Reply
  19. Kris

    October 19, 2012 at 1:58 am

    Thanks, Bryon and Mynda!

    For VBA developers only – you can read an interesting fact about Evaluate function and UDFs here:
    Formula to change another cell

    Cheers,
    Kris

    Reply
  20. Gordon de Beer

    September 3, 2012 at 10:02 pm

    Thanks, very informative to know about old formula quirks.

    Often though I resolve this problem by doing the following:

    1. Select the column that has the concatenated values such as =21*25
    2. Do a Copy paste special values of selection
    3. Data text-to-columns and finish

    Thanks,
    Gordon

    Reply
    • Mynda Treacy

      September 3, 2012 at 10:19 pm

      Cheers, Gordon.

      I agree, Text to Columns is a great tool and the one I would probably use to fix the formatting too.

      Kind regards,

      Mynda.

      Reply
    • Bryon Smedley

      September 6, 2012 at 12:50 am

      But doesn’t that remove the entire dynamic nature from the spreadsheet? Wouldn’t you need to re-Copy/Paste and re-Text to Columns every time the formulas changed?

      If it was a single pass through the data, I can see your point, but my purpose was to provide a means of changing the questions (re: formulas) with no further work on the user’s part.

      Have a great day!

      Reply
      • Mynda Treacy

        September 6, 2012 at 6:57 am

        Correct. I was considering a one-off change to fix the data but, if the intention is to add more data then your solution would be better. Good point.

        Cheers.

        Reply
  21. Lupe Jau

    August 31, 2012 at 12:52 am

    Thanks for sharing, I have been learning a lot about excel. God bless you
    Sincerely
    Lupe Jau
    Computer Instructor

    Reply
    • Mynda Treacy

      August 31, 2012 at 9:28 am

      Thanks, Lupe. Glad you’re enjoying learning from our site πŸ™‚

      Reply
  22. Ade

    August 29, 2012 at 6:20 pm

    Worth reading, thanks for sharing! πŸ™‚

    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