Viewing the Formula in Another Cell in Excel

Applicable For: This tip works in Microsoft Excel 2013 & Excel 2016, and Office Excel 365 (for both Windows & Mac Editions)

Prior to the Excel 2013 edition, to view all the formulas in a given worksheet in Excel, we had to use the View Formulas button, or use the nifty shortcut I highlighted in another post on this topic – View All Formulas in Excel with a Single Click.

But from the Excel 2013 and onward editions (including Excel 2016 – for Windows & for Mac), we have another better way to view the formulas used in calculations. This method is fantastic, because it allows you to see the formulas, without having to flip the switch, and see only values or only formulas.

In this technique, the original values and formulas can stay where they are. We can simply use the newly introduced Excel function in a new cell, which will then show you the formula of any given cell pretty easily.

This new function is called FORMULATEXT.

=FORMULATEXT(cell_reference to a cell containing a formula)

FormulaText Demo from ExcelChamp.net - By Vinai Prakash

With this new function, you can see the formula within any other Excel cell, without flipping up the on/off option. It allows you to see the value and see the formula, all at the same time. Much better than the chicken only or egg only options…

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


This has been specially great while teaching or showing off stuff to someone. Now you can use complex formulas, and the FormulaText function will show the formula, while the original value stays put, making it easier to understand the formula and its working, while having the value displayed directly.

Great, simple tip. I hope you like it!

Cheers,
Vinai Prakash,
Founder: ExcelChamp.Net – Effective Tips to Simplify Excel, Every Day!

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

New, Free, Excel Pivot Table Training For You

Want to learn more about Excel Pivot Tables to Analyze Business Data?

I am in the process of completing a short, free online training on Pivot Tables (8 Lessons are already available online). The course is being published by Udemy, an online training portal.

This course is called Mastering Pivot Tables to Analyze Business Data in Excel 2010 / 2013.

I have recorded 8 lessons so far. They are:

  1. What is a Pivot table?
  2. Formatting a Pivot table
  3. Customizing a Pivot table to Get it Ready for Data Analysis
  4. Adding Sub Totals in a Pivot Table
  5. Sorting Pivot Table data in multiple ways
  6. Filtering Pivot data in Unique ways
  7. Creating New Calculations in Pivot Tables
  8. Analyze data with Percentages in Excel.

Do check it out, and give me some feedback about this business data analysis with Excel training .

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


I need specific help with these 2 questions:

  1. What other topics would you like to be covered in this Pivot table mastery training.
  2. What can be improved in this Pivot Table training video?
  3. How would you rate this training, on a scale of 1-5 (1 is lowest, 5 is highest)

If you have any other feedback, simply post it at the bottom of this post. I will surely appreciate it.

And thanks to all my supporters, friends, and family, who have given me the strength, feedback, and waited patiently while I recorded these Excel training video sessions.

Cheers & All the Best – Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Top 10 Time Saving Tips in Using Microsoft Excel

If you are a data warrior, chances are that you use Excel extensively in your day to day work.

And there are many ways to boost productivity within Excel. Use these nifty tricks and improve productivity.

1. View All Formulas in Excel With a Single Click: I wrote about this trick some time back. It allows you to view all the formula with a single click of Control + ` Key. This acts like a toggle key. This can save a lot of time in finding where are the formulas to edit/view them.

2. Clean up Your Data  – Identify the Duplicates, and Remove Duplicates: There is no point in having duplicates in your tables. There are multiple ways to do this. You can identify the duplicates, and then choose to remove them manually,  or you can request Excel to remove the duplicate rows completely, automatically.

3. Colour Alternate Rows and Make it Easier to Read Large Data Sets: Prior to Excel 2007, we used to write formulas to colour alternate rows in Excel. This makes it easier to read the data.

=MOD(ROW(),2)=0

This function would tell us if the row is an even row or an odd row, and then we could colour it.

But since Excel 2007, we can do this in multiple ways. We can convert the data into a table. And in the table options, you can then colour alternate rows or columns as bands.

And then you can also use the formula above, and use conditional formatting.

4. Learn The In-Built Functions in Excel: Many people are amazed at the plethora of in-built functions available in Excel. But you must know that they exist, and also know the syntax to make a good use of them.

5. Learn and Use Pivot Tables Effectively: Pivot tables summarize data quickly. Learn to use them effectively.  Simple techniques like displaying both values and percentages in a Pivot Table adds extra value.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Similarly, learn to show different grouping for different pivot tables, even though it is based on the same data source. Also, creating a calculated field in a pivot table adds extra value.

If you are new to pivot tables, this introduction on how to analyze data using Excel pivots will be useful.

6. Charting Techniques Can Make People Notice Your Work: Creating nifty charts like the 2 Axis chart, or the PivotChart can be useful in the board room. Learn these techniques. Similarly, using Slicers in Pivot Charts can make dynamic Charts that can wow the audience. They are simple to use.

7. Get Back to Your Selection: Many times you will select or highlight some cells, but then move about the Excel file to find something. Then your are lost. Now you don’t remember where exactly is your selection. Simply click Control + Backspace. It will take you to the highlighted area in a blink!

8. Use Range Names to Refer to Cells: Instead of using G3, C3 or F3, you can give each cell a name. Not only single cells, you can actually define a name for a group of cells too. Then you can refer this group of cells by a particular name.

So instead of saying =G3+C3-F3, you can now calculate Salary as

=Basic_Pay + Allowance – Tax

This is much easier to read, and comprehend. No need to look up where the formulas are coming from, as the names are self evident.

9. Learn Excel Shortcuts: There are hundreds of shortcut combinations in Excel. It is impossible to remember all of them. But you must know a few to really speed up your work. Some of my favorites are:

  • Control N: New workbook
  • Control `: View All Formulas
  • Control Backspace: Jump to selected Cells
  • Alt + F1: Creates a inserts a chart based on the currently selected data as an embedded chart object.
  • Alt + =: Auto sum formula is inserted
  • Ctrl + Shift + Enter: Enter an Array Formula
  • F9: Calculates all the worksheets in all open workbooks

10. Learn to Use Dates Effectively: In Excel, dates are stored as numbers. They can be displayed in a wide variety of Formats. Understand these formats to display them in the way that suits the applications.

Also, you can calculate using dates, for example, you can find the difference between any two dates. You can add a fixed number of days to a date.

Hope you enjoyed these tips, and will use them to improve your productivity in using Excel. Do post a comment below to talk about your favorite tips in Excel.

If you wish, you can get a good book on Excel from the Excel BookStore. These books are usually quite inexpensive, and will give you many ideas to improve too.

Cheers,
Vinai Prakash, PMP, MBA, ITIL, GAP, Six Sigma

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

P.S.  : You can contact us to conduct a workshop on Excel Tips and Tricks at your office.

And I hope you subscribed to the ExcelChamp Excel Tips Newsletter already. 🙂