Excel - Show Formula Inputs Next to Answer - Episode 1740
2,051 views · Published 28 June 2013 · 7:31 · Indexed 12 November 2026
Channel: MrExcel.com · 2013 · Education
Microsoft Excel Tutorial: Show Cell References used in a formula in the grid. Welcome to another exciting episode of Dueling Excel, where we pit our Excel skills against each other to solve real-life problems. I'm Bill Jelen from MrExcel, and joining me is Mike Girvin from ExcelIsFun. In this episode, we tackle a question from Elissa Elissa on YouTube, who wants to know how to display the cells used in a formula next to the answer. Let's dive in and see how we can solve this problem. At first, I thought of using the FORMULATEXT function in Excel 2013, but that would only show the formula itself, not the values. Then, I remembered a trick I used for a client where we had to deal with a circular reference. We used the N function to document the formula and its values, and it worked like a charm. However, for this simple problem, we can use the concatenate symbol (&) to join the values and display them next to the answer. This method is straightforward and gets the job done. But wait, there's more! Mike suggests using the CONCATENATE function, which is essentially the same as using the ampersand symbol. However, he shows us a cool trick where we can use the function argument dialog box to avoid typing in the double quotes. This is especially useful when dealing with longer formulas. Mike also demonstrates how to use the ADDRESS function to display the cell reference dynamically, so even if we move the formula, it will still show the correct cell reference. This is a great tip for those who want to take their Excel skills to the next level. And just when you thought we couldn't make this episode any more exciting, we reveal that this is the first Dueling Excel podcast in 4.7 months where we didn't use an array formula! But don't worry, we have plenty of other tricks up our sleeves. Also, don't forget to check out our new book, "Ctrl+Shift+Enter - Mastering Excel Array Formulas," which will be available in just three weeks. Thank you for tuning in, and we'll see you next week for another exciting episode of Dueling Excel from MrExcel and ExcelIsFun! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Introduction to Dueling Excel podcast (00:11) Question from Elissa Elissa at YouTube (00:21) Simple formula with desired output in C2 (00:31) Possible solutions using CONCATENATE and N functions (01:45) Mike's alternative solution using CONCATENATE function and ADDRESS function (06:30) Bill's final thoughts and announcement about upcoming book release (07:16) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: ADDRESS function AMPERSAND symbol Circular reference CONCATENATE function Ctrl+F3 shortcut FORMULATEXT function Macro N function Return cells composing a formula Show How the Answer is Calculated Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153562/ Today's question: a formula is showing an answer in B2. Can you somehow repeat the input values in C2 so the person reading the spreadsheet can see the answer as well as what went into the answer. Bill and Mike duel it out with the & operator and the CONCATENATE function. Mike throws in a little ADDRESS function, and then Shift+F3 or Ctrl+A.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920