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

Watch on YouTube

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