Formulas in Excel are the backbone of efficient data management and analysis, enabling you to perform complex calculations, automate tasks, and derive insights with ease. By mastering formulas in Excel, you unlock the potential to streamline your workflow, reduce errors, and make data-driven decisions. Whether you’re crunching numbers for financial reports, analyzing trends, or managing large datasets, the versatility and power of Excel formulas are indispensable. Embrace the full capabilities of formulas in Excel to elevate your productivity and enhance the accuracy of your work. Transform your spreadsheets into powerful tools that not only store data but also provide actionable insights and drive success in your projects.
This Tutorial Covers:
- Show Formulas in Excel Instead of the Values
- How to Print Formulas in Excel
- In Excel, display formulas rather than just the value of the currently selected cells
- How to Deal With Excel Displaying Formulas Rather Than Calculated Values
1. Show Formulas in Excel Instead of the Valuescv
Step 1: The “Formulas” Tab will appear in the ribbon. Select “Show Formulas” from the “Formula Auditing” group.
The formulas in the worksheet will become visible as soon as you click Show Formulas.
Since it is a toggle button, you can click on it once more to have the computed result take the place of the formulas.
The Excel keyboard shortcut Control +’is another option (you will find this key in the top-left part of the keyboard, under the Escape key).
Note: This is a sheet level technique, please note. As a result, only the formulae in the active sheet will be displayed when you use the shortcut or option to Show Formulas. The rest of the worksheets won’t be impacted. You must access that worksheet and utilize this shortcut to display formulas in other worksheets (or ribbon button).
You might wish to display the formulas in every worksheet in a workbook if you have a lot of worksheets.
The following procedures will display all of Excel’s worksheets’ formulas:
Step 1: Select the “File” tab. Go to Office by clicking the Excel 2007 button.
Step 2: Toggle to “Options.”
Step 3: Select Advanced from the left pane. Scroll down to the “Display options for this worksheet” box on the right. Choose the worksheet you wish to display the formulae rather than the values from the drop-down menu.
Step 4: Select “Show formulas in cells instead of their calculated outcomes” from the drop-down menu. Select OK.
As previously stated, even though it may appear like there are more steps than a shortcut or the “Show Formulae” button in the ribbon, this method is helpful if you have numerous worksheets and want to display the formulas in each worksheet.
2. How to Print Formulas in Excel
The steps to print formulas in Excel are as follows:
Step 1: The “Formulas” Tab will appear in the ribbon. Select “Show Formulas” from the “Formula Auditing” group.
Step 2: Select the “File” tab then go to “Print” option.
The aforementioned steps would guarantee that the formulas and not the values are printed.
3. In Excel, display formulas rather than just the value of the currently selected cells
All of the worksheet’s formulas would be visible using the methods discussed so far.
However, you could simply want to display the formulas in a few certain cells.
As an Excel trainer, for instance, I frequently develop templates that display the formula in one cell and the result in another cell (as shown below).
The steps to only display formulas in particular cells in Excel are as follows:
Step 1: Choose the cell where you wish the formula to appear rather than the value.
Step 2: Go to the “Home” tab and select “Find & Select”. After that, choose “Replace” (keyboard shortcut – Control + H).
Step 3: Put = and ‘= in the “Find what” and “Replace with” fields, respectively, of the replace tab in the Find and Replace dialog box. Simply select Replace All.
This will display formulas in every cell in the selected cell while leaving the other cells alone.
Note: The space character is seen before the equal to sign when the formula is entered with a space before it to make it a text string. The apostrophe isn’t displayed in the cell when the equal to sign is preceded by an apostrophe, which turns the formula into a text string (it shows up only in the formula bar and in the edit mode).
4. How to Deal With Excel Displaying Formulas Rather Than Calculated Values
In Excel, the cells occasionally display the formula rather than the value.
There are a few causes for this possibility. They are:
- It’s possible that you accidentally pressed the Control + shortcut or that the “Show Formulas” feature is active. Simply use the shortcut once more or select the “Show Formula” option under the Formulas tab to make it inactive.
- It can be because the formula contains a space or an apostrophe before the equal to sign. When these are present before the equal to sign, the cell assumes a text format and the formula appears in place of the value. Simply take these out to manage this. To accomplish this, utilize find and replace.
- When you insert a formula into a cell that has “Text” formatting applied and press “Enter,” the formula is shown in place of the calculated value. Go to the Home tab and switch the formatting for the Number group to General to resolve this problem.
These are a few methods to show Excel formulas.
Application of Formulas in Excel Instead of the Values
- Financial Calculations: Use formulas in Excel to perform complex financial calculations, such as ROI, NPV, and amortization schedules, streamlining financial analysis and planning.
- Data Analysis: Employ Excel formulas to analyze data sets by calculating averages, sums, medians, and standard deviations, helping to uncover trends and insights.
- Conditional Logic: Utilize formulas like IF, AND, and OR to apply conditional logic, enabling dynamic decision-making and data validation within your spreadsheets.
- Automation: Automate repetitive tasks with formulas, reducing manual effort and minimizing the risk of errors, enhancing overall efficiency and accuracy.
- Lookup Functions: Implement lookup functions like VLOOKUP, HLOOKUP, and INDEX-MATCH to retrieve and display data from different parts of your worksheet, facilitating better data management and reference.
- Text Manipulation: Use text functions such as CONCATENATE, LEFT, RIGHT, and MID to manipulate and clean text data, ensuring consistency and improving the readability of your information.
For ready-to-use Dashboard Templates:
