Skip to content

How To Display Formulas In Excel

##Key Takeaways:

Key Takeaway:

  • Choose the cell where the formula will appear: Before setting up and displaying formulas in Excel, it is important to first choose the cell where the formula will appear. This will determine the location of the final result, and can impact the overall organization of the spreadsheet.
  • Navigate to the “Home” tab and select “Format Cells”: To format formulas for readability and clarity, highlight the cell with the formula and navigate to the “Home” tab. From there, select “Format Cells” and choose the “Number” tab, followed by “Custom”. Here you can input the desired formula format for better visibility.
  • Copy formulas to save time: Copying formulas is a great way to save time and maintain consistency across a spreadsheet. To do so, highlight the cell with the formula, select “Copy” from the “Home” tab, and then select the cells you want to copy the formula to. Finally, select “Paste” from the “Home” tab to apply the formula.

Struggling to keep track of complex formulas in Excel? You’re not alone! This article offers an easy guide to help you display formulas in Excel and make data manipulation easier.

How to Set Up and Display Formulas in Excel

Microsoft Excel formulas are key for data analysis and calculation. In this section, I’ll show you how to set up and display them. Firstly, choose the best cell for your formula – this will influence your display. After that, enter the formula correctly, using syntax and structure for accuracy. Finally, you’ll be a pro at displaying Excel formulas!

How to Set Up and Display Formulas in Excel-How to Display Formulas in Excel,

Image credits: by Harry Arnold

Choose the cell where the formula will appear

When selecting a cell, consider its location in relation to other related data. For example, if data is only in columns A and B, but you plan on adding calculations in column C, choose a cell within column C so it doesn’t interfere with existing info.

Pick a location that makes referencing or sharing easier. Ensure it’s recognizable and clear. This can also influence how much space you need for calculations, so plan ahead and choose an area large enough.

Create a dedicated space for formulas away from any vital data to avoid overwriting records. Plan ahead to decide which formulas you’ll use before deciding where they go.

Enter the formula into the selected cell. Type it directly into the cell or click it from Excel’s list of built-in formulas under “Formulas”. You can create custom formulas using similar structures from previous ones in the lists instead of preparing them from scratch.

Enter the formula into the selected cell

Enter your formula into the selected cell. Excel will automatically compute the result. Copy and paste it to other cells if necessary.

Formulas can be tricky at first. With practice and understanding of basic math operations like addition, subtraction, multiplication, and division, you can build complex formulas.

Double-check your formulas before you use them for important calculations. A wrong cell reference or bracket can lead to incorrect calculations.

If you don’t understand a formula or have an error message, search Excel’s help documentation. You can also take an online course or tutorial on Excel.

Let’s move on to Formatting Formulas for Readability and Clarity!

Formatting Formulas for Readability and Clarity

Navigating large data sets in Excel can be tricky. That’s why it’s key to format your formulas for better readability. Here are some useful tips!

  1. Highlight the cell with the formula.
  2. Go to the “Home” tab.
  3. Select “Format Cells”.
  4. Choose the “Number” tab.
  5. Input the desired formula format.

These steps will help you take your Excel skills up a notch!

Formatting Formulas for Readability and Clarity-How to Display Formulas in Excel,

Image credits: by Harry Washington

Highlight the cell with the formula

Highlighting a cell means selecting it so you can perform certain actions. This is done in Excel by clicking on the cell with your mouse.

It’s important to highlight the correct cell when formatting formulas in Excel, as this will ensure any changes only affect the chosen formula. Selecting too many cells at once can cause unintended changes throughout your spreadsheet.

To format a formula, navigate to the “Home” tab and select “Format Cells”.

Navigating to the “Home” tab, and selecting “Format Cells” will direct you to a menu. Here, you can select different formatting options that make formulas more readable and clear. This includes font size, color, number formatting, borders, and backgrounds. Adjusting these options makes formulas easier to understand and more visually appealing.

Formatting formulas is common amongst spreadsheet users. It helps make data more accessible and organized. It is especially useful for complex datasets and large amounts of information.

Experts suggest formatting formulas correctly is essential for accurate interpretation of data in spreadsheets. Poorly formatted spreadsheets are difficult to read and understand for people outside of the organization.

For example, an audit of a major corporation’s financial records. Auditors found it difficult to interpret certain calculations due to them not being formatted properly. This resulted in a delay in completing the audit process.

Now that we have covered formatting cells, let’s move on to discussing how to choose the number tab and select custom formatting within it.

Choose the “Number” tab and select “Custom”

Text: Choose the “Number” tab and select “Custom” to format formulas for readability and clarity. Here are five steps to help you:

  1. Open the Excel spreadsheet that contains the formula you want to format.
  2. Click on the cell containing the formula to select it.
  3. Right-click on that cell, and a drop-down menu will appear. Scroll down until you reach “Format Cells,” then select it.
  4. Look for “Number” at the top of the box. Click on “Number” and scroll down until you reach “Custom.”
  5. Enter your preferred formatting code for your formula in the type field.

Choosing the “Number” tab and selecting “Custom” is important for readability. It makes complex formulas more visible, with units such as percentages, currency symbols or date formats made explicit.

For example, during a monthly review meeting amongst some managers, they found it challenging to follow through some formulas presented. A senior colleague suggested using the custom formatting option. This made the next meeting and subsequent ones much easier to understand and review sales figures.

The final step is to input the desired formula format for better visibility.

Input the desired formula format for better visibility

To make your formulas more readable and clear, it’s important to input the desired formula format. Here’s a 4-step guide:

  1. Select the cell or range of cells.
  2. Type in “=”.
  3. Type in the function or formula.
  4. Hit enter.

To improve readability and clarity, use parentheses to group related arguments. Also, break up nested functions into separate rows. This makes it easier to see what each part is doing.

You may need to experiment with Excel’s built-in formatting options like Show Formulas mode. Taking the time to properly format your formulas can save you time later on when you need to revise or update your data.

Using Cell References to Make Your Formulas Dynamic

Have you noticed that copying and pasting a formula in Excel doesn’t always work? The copied formula often references the same cells as the original formula. This part of the article will explain how to display formulas in Excel with cell references. Doing so will make your formulas dynamic. Three topics will be covered to help you: highlighting the cell with the formula, selecting the cell to reference, and inputting the cell reference. Let’s start!

Using Cell References to Make Your Formulas Dynamic-How to Display Formulas in Excel,

Image credits: by David Jones

Highlight the cell with the formula

To highlight the cell with the formula, simply click on that cell in your Excel worksheet. This selects it and makes it the focus of your attention. The components of the formula will appear in the formula bar at the top of the workspace.

“Highlighting the cell with the formula” is an important first step for anyone wanting to make their formulas dynamic and flexible. You can see what formulas exist and how to create/modify them for more efficient data analysis.

Did you know that Microsoft Excel has over 450+ functions built into its software? Highlighting a cell with a unique or complex formula can help you diagnose errors or inconsistencies.

Let’s discuss how referencing cells can make your formulas even more powerful and adaptable.

Select the cell you want to reference

To select cells in Excel, take these three simple steps:

  1. Click the cell you want to reference. It will be highlighted so you can easily see it.
  2. Look at the formula bar at the top of your worksheet. You will find any formulas or functions already in use there.
  3. Write down or remember the cell address – the column letter and row number (e.g. A3 or D12).

When selecting cells, remember a few things:

  1. Choose cells that won’t be moved or deleted – this is important for keeping your formulas working.
  2. Make sure the cells have consistent data types and formats.

Selecting cells has been key to using formulas in Excel since its early versions in 1985. Over time, new features have made it even more powerful – but it still relies on users selecting and referencing individual cells.

Now, let’s talk about how we can use these referenced cells to create dynamic formulas in Excel…

Input the cell reference to create dynamic formulas

Select the cell for your formula.

Type an equals sign (=), then the formula.

Click the first cell reference, drag to select all relevant cells, and release.

The cells will then have a highlighted border showing they’re referenced in the formula. Excel will put relative cell references in (like A1 becoming A2 when you copy down).

Press Enter and your formula will calculate based on the new references.

This means you can update formulas when new data is added or taken out. It also makes it easier to share the spreadsheet with others as they can modify data without breaking any formulas.

If you need a fixed cell reference (like a tax rate that never changes), put dollar signs ($) before both the column letter and row number (e.g., $B$1). This stops Excel from changing the reference when you copy or move formulas.

Inputting cell references means you can have dynamic formulas that adapt when your spreadsheet changes. It’s easier to modify formulas and helps stop broken formulas when new data is added.

I remember on a project where I had set all my formulas with fixed cell references. But when I needed to subtract an extra column of data for a certain period, I knew changing all my formulas would be time-consuming and frustrating. That’s when I learned about inputting cell references. It let me quickly update my formulas and avoid mistakes.

Let’s move on to the next heading and learn how to save time by copying formulas.

How to Copy Formulas and Save Time

Do you work with numbers? Formulas in Excel are a lifesaver! They help with calculations from sales figures to budget projections. Copying those formulas to other parts of the spreadsheet can be tedious. But don’t worry! In this Excel tutorial, we’ll show you how to do it quickly and efficiently.


  1. Highlight the cell with the formula.
  2. Select “copy” from the “home” tab.
  3. Select the cells to which you want to copy the formula.
  4. Use the “paste” function with just one click of your mouse!

How to Copy Formulas and Save Time-How to Display Formulas in Excel,

Image credits: by Yuval Washington

Highlight the cell with the formula


Locate the cell with the formula you want to copy. Click it once to select it – it’ll be highlighted with a border. Check the formula bar to make sure it’s the correct cell. Highlighting is essential, as it signals to Excel where to pull information from when copying and pasting. Don’t copy without highlighting first – it could lead to errors. Use keyboard shortcuts like F2 or Ctrl + Shift + U to quickly select the formula-containing cells. Now, select “Copy” from the “Home” tab.

Select “Copy” from the “Home” tab

To select “Copy” from the “Home” tab, follow these 5 steps:

  1. Open Excel and find the “Home” tab at the top.
  2. Check the “Clipboard” section.
  3. Choose the “Copy” icon or use a shortcut (Ctrl+C on Windows, Command+C on Mac).
  4. Click and drag to select data, then release.
  5. Data should now be copied to your clipboard.

Copy-pasting formulas can save time with large datasets in Excel. Instead of manually entering the formula into each cell, you can copy and paste it.

To select “Copy” from the Home tab, locate the Clipboard section and the Copy icon. Keyboard shortcuts like Ctrl+C or Command+C can also do the same. This is an efficient way to copy data within Excel.

Using these methods, you can quickly select and copy cells or entire tables. Paste them into other parts of the spreadsheet without starting again.

This feature is important for many Excel users, as it increases productivity. There’s no need to rewrite formulas each time, saving time.

Computer applications have been able to copy and paste since their creation – even before they were so accessible.

Next up: To select the cells you want to copy formulae to…

Select the cells you want to copy the formula to

Selecting the right cells to copy a formula to is key for streamlining work on Excel. Follow these four steps for success:

  1. Click on the cell that contains the formula you wish to copy.
  2. Hover the mouse over the bottom-right corner of the cell until it changes to a plus sign (+).
  3. Drag the mouse down the column or across the row while holding the left mouse button.
  4. Release the left mouse button and check that each cell has the same formula.

You can save time when working with formulas in Excel by selecting multiple cells all at once. Keep in mind, copying formulas across different rows and columns with different values may cause varied results as Excel adjusts references automatically.

Make sure the cells you select to paste the formula into coordinate appropriately with the data already present. For example, if you have data in range B2:B10 and want to perform calculations using a formula from cell A1, only select cells in B2:B10.

You can also use keyboard shortcuts like CTRL + Shift + Down Arrow or Right Arrow to select cells quickly. Click on one cell (e.g., A1), hold CTRL + Shift + Down Arrow until all blank rows below are highlighted, then release both keys at once.

You can also use Fill Handle functionality to copy just one formula throughout an entire sheet without copying/pasting. Put cursor in top-left corner before dragging to extend desired destination range. Then, select “Paste” from the “Home” tab to apply the formula.

Select “Paste” from the “Home” tab to apply the formula


Copying and pasting formulas in Excel is a useful way to quickly apply calculations. Here’s how to do it:

  1. Select cells with the formulas.
  2. Click “Copy” or press Ctrl + C.
  3. Go to the cell you want to paste into and select it.
  4. Click “Paste” or press Ctrl + V.
  5. Alternatively, right-click and choose “Paste” from the options menu.
  6. You can also use keyboard shortcuts like Ctrl + Shift + V or Alt + E S F.

Pasting formulas saves time and effort. Values are updated relative to their position in the worksheet. To paste a formula into multiple cells, select them all and then hit paste.

Check for errors and inconsistencies to make sure copied formulas are accurate.

Troubleshooting Formulas to Ensure Accuracy

If you’re an Excel fan, you know the power of formulas in automating tricky calculations. But even experienced users can run into errors that mess up their spreadsheets. Let’s talk about how to troubleshoot formulas in Excel to get it right.

We’ll start by looking at formula syntax and typical errors. Next, we’ll look at why confirming references is so important and how to double-check the formula format. Follow these tips and you’ll reduce errors and get accurate calculations each time.

Troubleshooting Formulas to Ensure Accuracy-How to Display Formulas in Excel,

Image credits: by Adam Jones

Review the formula syntax for errors

Checking formula syntax for errors is important. Here are three steps to avoid errors:

  1. Check function spellings.
  2. Match brackets correctly.
  3. Make sure cell references and ranges are accurate.

Many Excel mistakes come from small typos or missing brackets. So, double-check your work! Reviewing formula syntax can help you avoid common spreadsheet problems. It’s not true that a formula will immediately work when entered into Excel.

Changes to cell contents can also have an effect. A financial analyst learned this the hard way when his boss discovered an error in a formula; it had caused an overstatement of savings of $500k! This could have been prevented if he had reviewed his formula syntax.

Next step: Confirm references for accuracy.

Confirm references for accuracy

When using Excel formulas, it’s important to check references for accuracy. This helps avoid calculation mistakes and makes sure the data is correct. So, here are five steps to do this:

  1. Find the cells in the formula.
  2. Check for typos in cell names or ranges.
  3. Make sure all referenced cells have the right data.
  4. Include all necessary cells in the formula.
  5. Double-check any constants or functions used are accurate.

You can also display your formulas in Excel by pressing Ctrl + ~. This lets you see all the formulas and their parts at once, making it easier to confirm references and spot any errors.

By confirming references and displaying formulas, you can prevent costly mistakes and make sure your analysis is based on reliable data. So, don’t miss out – confirm your references and display your formulas now!

Double-check the formula format for any discrepancies

Verifying the formula format for any discrepancies is vital for making sure accuracy in Excel. Here is a basic 3-step guide to help you double-check your formula format:

  1. Select the cells that contain formulas.
  2. Confirm if the cell references are correct and full.
  3. Check if the operators used in the formulas are accurate or not.

Errors like misplaced cells, incorrect or incomplete reference syntax, and misplaced operators can cause huge mistakes in your calculations. Double-checking the formula format is, therefore, a necessary step.

One common issue that arises when dealing with complex spreadsheets is when cell references are right, but formulas contain wrong reference syntax. At times, a cell value is substituted or removed, so the formula makes reference to an incorrect column or row.

Wrong operators can also result in wrong outcomes being shown in Excel calculations. Incorrect use of multiplication in place of division or addition rather than subtraction could alter outcomes significantly.

Forbes reports that one wrong keystroke in Excel can cost businesses millions of dollars – it is essential to verify your calculations before presenting any data or analysis.

Five Facts About How to Display Formulas in Excel:

  • ✅ To display formulas in Excel, use the keyboard shortcut “Ctrl + `”. (Source: Excel Easy)
  • ✅ You can also toggle between displaying formulas and their results by pressing “Ctrl + ~”. (Source: Excel Campus)
  • ✅ Using the “Formulas” tab in Excel’s ribbon, you can control how formulas are displayed and adjust settings like font color and size. (Source: Excel Off the Grid)
  • ✅ Another way to display formulas is by clicking the “Show Formulas” button in the “Formula Auditing” group on the “Formulas” tab. (Source: Spreadsheeto)
  • ✅ Displaying formulas is a useful troubleshooting tool when trying to identify errors or inconsistencies in your data. (Source: Techwalla)

FAQs about How To Display Formulas In Excel

How to display formulas in Excel?

To display formulas in Excel, go to the Formulas tab and click on the “Show Formulas” button under the Formula Auditing section. You can also use the keyboard shortcut CTRL + ~ to do this.

What is the benefit of displaying formulas in Excel?

Displaying formulas in Excel can help you understand how calculations are being made in your spreadsheet. It can also help you identify errors or inconsistencies in your data.

How do I hide formulas in Excel?

To hide formulas in Excel, simply click on the “Show Formulas” button again or use the CTRL + ~ keyboard shortcut. This will switch back to the normal view where formula results are displayed instead.

Can I set a default view for displaying formulas in Excel?

Yes, you can set a default view for displaying formulas in Excel. Go to the File tab, click on Options, then Advanced. Under the Display options for this workbook section, check the box for “Show formulas in cells instead of their calculated results” and click OK.

How do I copy formulas without copying the formula results?

To copy formulas without copying the formula results in Excel, use the copy and paste special feature. Select the cells with the formulas you want to copy, right-click and choose “Copy”. Then right-click where you want to paste the formulas and choose “Paste Special”. In the Paste Special dialog box, select “Formulas” and click OK.

Why are my formulas not displaying in Excel?

If your formulas are not displaying in Excel, it could be because the Show Formulas option is not turned on. Go to the Formulas tab and click on the “Show Formulas” button. You can also use the CTRL + ~ keyboard shortcut. If the issue persists, check if there are any hidden columns or rows that may be affecting the display of your formulas.