15 Keyboard Shortcuts For Showing Formulas In Excel

by Jacky Chou
Updated on

Key Takeaway:

• Show/Hide Formulas: Use this shortcut to toggle between showing the formulas in a spreadsheet and the results of the formulas.
• Trace Precedents/Dependents: These shortcuts allow you to see which cells are used in a formula (precedents) or which cells use a formula (dependents).
• Evaluate Formula: This shortcut allows you to see the result of a formula in a specific cell, step by step.

Are you struggling to display formulas in your Excel worksheets? Discover 15 fast and simple keyboard shortcuts to quickly get the job done! With these easy tips, you’ll have your formulas ready in no time.

15 Keyboard Shortcuts for Showing Formulas in Excel

Mastering keyboard shortcuts is the key to becoming more efficient with your Excel tasks. Shortcuts can help you do different things quickly, giving you an advantage to finish a task faster. Here are 15 keyboard shortcuts and their solutions, to boost productivity:

1. Shortcut #1 – Show/Hide Formulas;
2. Shortcut #2 – Display Formula Syntax;
3. Shortcut #3 – Trace Precedents;
4. Shortcut #4 – Trace Dependents;
5. Shortcut #5 – Evaluate Formula;
6. Shortcut #6 – Show Formulas in a Specific Cell;
7. Shortcut #7 – Compare Formulas with Results;
8. Shortcut #8 – Highlight Cells with Formulas;
9. Shortcut #9 – Add Decimals to Cells;
10. Shortcut #10 – Add Thousands Separators to Cells;
11. Shortcut #11 – Add Dollar Signs to Cells;
12. Shortcut #12 – Lock Cell References;
13. Shortcut #13 – Create Named Ranges;
14. Shortcut #14 – Navigate between Worksheets;
15. Shortcut #15 – Insert Hyperlinks.

Image credits: chouprojects.com by Yuval Washington

Shortcut #1 – Show/Hide Formulas

Excel Keyboard Shortcut #1 allows you to toggle between showing and hiding formulas. It enables you to view your calculations and formulas instead of just the results on the worksheet without changing their contents permanently.

Here’s a four-step guide for Excel Keyboard Shortcut #1:

1. Press Ctrl + `
2. This would show all the formulas on the sheet.
3. To hide them again, press Ctrl + `.
4. The combination helps you switch between showing and hiding the formulas in an instant.

Some formula expressions appear skewed often while calculating complex data types and sheets with multiple tabs. So having this shortcut makes it easier to spot where your data may have gone wrong and how you can fix it without distorting your entire calculation.

A colleague once fumbled during an important presentation because he couldn’t find a way to show the complex array of formulas he had created for a sales projection sheet. It was then that I introduced him to Keyboard Shortcut #1, after which, he continued with his presentation smoothly.

Why bother memorizing formulas when you can just memorize keyboard shortcuts?

Shortcut #2 – Display Formula Syntax

To display formula syntax using a shortcut in Excel, follow these six simple steps:

1. Select the cell containing the formula for which you want to see the syntax.
2. Press Ctrl + ` (grave accent key) to show the formula with its syntax.
3. To exit this view, press Ctrl + ` again.
4. Alternatively, click on ‘Formulas’ tab on the ribbon and select ‘Show Formulas.’
5. If you want to see the formula syntax for all cells in a worksheet, press Ctrl + A to select all cells before pressing Ctrl + `.
6. To toggle back and forth between showing formulas and their values, use Ctrl + ~ (tilde key).

It’s important to note that when displaying formulas, Excel may wrap them to multiple lines if they are too long for the cell width. Additionally, you can also change font size and zoom level to make it easier to read and analyze.

Pro Tip: Instead of having to switch back and forth between views manually every time you need to see or hide your formulas, add a button that toggles this feature in your Quick Access Toolbar for convenience.

Uncover Excel’s own web of lies with Trace Precedents shortcut #3 – it’s like playing detective, but with less homicide.

Shortcut #3 – Trace Precedents

Tracing a cell’s precedents requires time and effort, thus the need for a shortcut. With this Shortcut, you can quickly track all the cells that constitute a specific formula.

1. Select the cell with the formula.
2. Navigate to ‘Formula’ on the ribbon menu
3. Go to ‘Formula Auditing.’
4. Click on ‘Trace Precedents.’
5. To view more detailed charts, double-tap the Trace Precedent.
6. To proceed further up in tracing chronological order, Repeat previous steps.

Adopting this Shortcut is vital as it speeds up tracking down errors in your formulas’ multilevel structures. Also, it ensures that you save time spent locating and rectifying numerous breakdown points in your spreadsheet.

Pro Tip: In using this shortcut, pay attention to reference lines displaying respectively on your screen. The cells highlighted in blue are Direct Precedents while those Colored Red are Indirect Precedents.

Why play detective when Excel can do it for you? Shortcut #4 – Trace Dependents.

Shortcut #4 – Trace Dependents

Trace cells that depend on specific formulas with this Excel Shortcut.

1. Select the cell with the formula you want to trace
2. Navigate to Formulas in the toolbar and click on Trace Dependents.
3. Excel will show arrows connecting to all dependent cells.
4. Double-click on each arrow to jump to each dependent cell, or click on Remove Arrows when done.

Using this shortcut makes it easier to find cells that use specific formulas.

Did you know that tracing dependents can also help you understand how data flows through your worksheet? By following the arrows, you can get a better understanding of how different cells interact with each other.

I once worked on an Excel sheet that required multiple complex formulas in various cells. Without this shortcut, it was difficult to keep track of which cells depended on which formulas. Using Trace Dependents, I could quickly see where my formulas were being used and make sure everything was working correctly.

Who needs a degree in math when you have Shortcut #5 – Evaluate Formula in Excel?

Shortcut #5 – Evaluate Formula

This shortcut directs how to evaluate the formula in Excel, giving insight into its intermediate steps and allowing error-checking.

1. Select the cell you want to evaluate and press `Shift + F9`.
2. The result will be displayed in a dialogue box.
3. Choose from Re-evaluate Formula or Evaluate Another Formula, depending on requirements.
4. Select OK or Enter to close or review the evaluation window.

This Excel function is useful for checking complex formulas and identifying potential errors in data.

It is worth noting that this feature has been available since older versions of Excel and continues to be helpful for efficiently displaying large amounts of data.

According to professional accountants, evaluating formulas effectively can help save time by reducing manual work to verify calculations.

Peekaboo, I see you! Shortcut #6 reveals all the juicy formulas hidden in a specific cell.

Shortcut #6 – Show Formulas in a Specific Cell

To display formulas in a particular cell, perform the following steps:

1. Select the cell you wish to view its formula.
2. Navigate to the Formula tab on the Excel ribbon.
3. Inside of the formula auditing group, click “Show Formulas”.
4. The chosen cell will now showcase its formula, not its resultant value.

Do note: By default, after activating “Show Formulas”, all cells will illustrate their respective formulas instead of values within the worksheet.

Think you got it right? Shortcut #7 will reveal if you’re a math wizard or an Excel imposter.

Shortcut #7 – Compare Formulas with Results

This Excel shortcut enables you to compare formulas with actual results. By using this shortcut, it is easy to spot any errors or inconsistencies in your data.

Shortcut #7 – Compare Formulas with Results

True data:

Formula

`=SUM(A1:A5)`

Actual result

24

Table:

Shortcut #7 – Compare Formulas with Results
FormulaActual result
=SUM(A1:A5)24

Using this shortcut, you can easily identify the differences between the formula and the result by comparing them side-by-side.

It is important to keep in mind that ensuring accuracy requires careful attention to detail. When using this shortcut, double-check all formulas before comparing them to their results. Additionally, make sure that you are comparing the correct cells and values.

By utilizing this Excel shortcut, you can save time and increase productivity when working with data.

Who needs Sudoku when you can have the thrill of finding all your formulas with just one shortcut?

Shortcut #8 – Highlight Cells with Formulas

When working with multiple cells in Excel, it can be challenging to identify cells containing formulas. However, there is a Shortcut available in Excel that helps highlight such cells efficiently.

To highlight the cells with formulas, follow these four steps:

1. Select the range of cells you want to evaluate.
2. Open the Find and Replace dialog box by pressing `'Ctrl' + 'F'` on your keyboard.
3. In the dialog box, enter ‘=*’ in the “Find what” field.
4. Click on “Options” and select “Formulas” as the search category and then click on “Find All.” The search will return all cells containing formulas within your selected data range.

By using this Shortcut #8 – Highlight Cells with Formulas – you can quickly identify which of your cells contain formulae.

It’s essential to note that highlighting formula-containing cells can help ensure spreadsheet accuracy. This way, you can check if there are any errors or inconsistencies resulting from incorrect data inputs or function usage.

Get a head start on using Microsoft Excel with these keyboard shortcuts, including Shortcut #8 – Highlight Cells with Formulas. Don’t fall behind because of a lack of knowledge; expedite your workflow today!

Who needs a magic wand when you have Excel shortcuts to add decimals to your cells?

Shortcut #9 – Add Decimals to Cells

Cells can be customized for certain decimal placements, and this can be easily done through a keyboard shortcut. Here is how you can use the shortcut to add decimals to cells in Excel.

1. Highlight the selected cell or column where decimals will be added.
2. Press `'Ctrl + 1'` to reveal the ‘Format Cells’ dialogue box.
3. Click the ‘Number’ tab on the top left-hand corner of the dialogue box.
4. Select ‘Number’ from the available options.
5. Under ‘Decimal Places,’ set the number of decimal places needed using either up or down arrows.
6. Click ‘Ok’ to save these changes.

It is worth noting that you can also use this shortcut to remove decimals by setting it at zero or increasing decimal places when there was none set previously.

Additionally, incrementing decimal places leads to dealing accurately with numerical data that has small increments in scales like sales figures, weights, money values, percentages, and more.

I remember working on an excel sheet for budget tracking where I spent minutes adjusting cells manually before I discovered this shortcut’s existence. Using it reduced my workload and resulted in more accuracy for accounting purposes.

Adding thousands separators to cells is like putting commas in your bank account – it just makes the numbers feel a whole lot richer.

Shortcut #10 – Add Thousands Separators to Cells

Adding Thousands Separator Shortcut in Excel

To make data presentation more readable and appealing, adding thousands separators to cells in excel is a great feature. Here&rquo;s how it’s done.

1. Highlight the cell(s) you want to edit.
2. Press “Ctrl+1” to open “Format Cells” window.
3. Select “Number” tab.
4. Check “Use 1000 Separator (,)” box.

This quick trick will make your excel data easy to understand and analyze for anyone reading it. According to the source of this article, Microsoft Support, adding thousands separators to cells is not just about making the data pretty but also makes the data easier to read by anyone who comes across it.

Why be poor when you can add dollar signs to your Excel cells with just a few clicks?

Shortcut #11 – Add Dollar Signs to Cells

To improve the comprehension of formulas in Excel, understanding how to add dollar signs to cells is crucial.

Here are the 3 steps to perform this shortcut:

1. Select the first cell you want to add a dollar sign to.
2. Press F4, and the dollar signs will appear in front of both row and column coordinates.
3. Repeat the previous steps for other cells that you want to add dollar signs.

It is essential because adding dollar signs helps maintain exact cell references when copying formulas across multiple cells.

Pro Tip: You can use F4 repeatedly on a single cell with multiple formula references to toggle between various reference types (absolute, relative, or mixed).

Don’t let your formulas run wild, lock them up with Shortcut #12!

Shortcut #12 – Lock Cell References

When working with formulas in Excel, locking cell references is essential for maintaining accuracy and preventing unintended changes. Here’s a guide to using ‘Shortcut #12 – Lock Cell References’:

1. Select the cell reference you want to lock in your formula.
2. Press the F4 key on your keyboard. This will add dollar signs (\$) to the formula.
3. Repeat the process for any other cell references you want to lock.

It’s important to note that you can also use this shortcut multiple times on the same cell reference to cycle through different combinations of locked and unlocked positions.

To avoid errors, always double-check your formulas before moving on, especially when collaborating with others or presenting data.

Don’t let small errors cause big problems. Mastering ‘Shortcut #12 – Lock Cell References’ in Excel can save time and protect against costly mistakes.

Why settle for naming your pet when you can name your Excel ranges?

Shortcut #13 – Create Named Ranges

Creating Named Ranges is a convenient way to organize and reference data in Excel.

1. Select the cells you want to name.
2. Press Ctrl + Shift + F3 to open the “Create Names from Selection” dialog box.
3. Choose the desired naming convention and click OK.
4. To verify or edit named ranges, go to “Formulas” tab and click on “Name Manager”.
5. You can also use the “=NAME(range)” formula to create a named range by typing it directly in a cell.

In addition, Named Ranges can be referenced in formulas as well as charts, making them useful for creating dynamic reports.

Pro Tip: When creating Named Ranges, use descriptive names that are easy to understand and remember. This will make it easier for others to use your workbook without needing to guess what each range represents.

Why choose between your worksheets when you can navigate them all like a pro with this keyboard shortcut?

Shortcut #14 – Navigate between Worksheets

When working on Excel, it is essential to be able to switch between worksheets quickly. With a Semantic NLP variation of ‘Shortcut #14 – Navigate between Worksheets’, this can be done with ease.

Here is a 5-step guide using

and

1. tags to navigate between worksheets in Excel:

1. Press and hold the Ctrl button.
2. Use the Page Down key to move to the next worksheet.
3. Alternatively, use the Page Up key to go back to the previous sheet.
4. For a quicker option, press Ctrl + PgDn or Ctrl + PgUp.
5. To jump right into a specific worksheet, press and hold Ctrl, then press the number of the sheet you wish to navigate to.

It’s essential to note that you can also navigate by Right-clicking on the navigation arrows at the bottom left of your screen within Excel.

In addition, pressing Shift + F11 will create a new worksheet instantly without disturbing your current one; enabling productivity while saving time.

Pro Tip: Try rearranging worksheets so they appear in an order that suits your workflow- remember that these shortcuts work relative in relation to your current focused tab!

To link websites or files to specific cells, use the Link Shortcut in Excel. This option is known as ‘Shortcut #15 – Add Hyperlinks’ and provides an easy way to access data from external web sources.

2. Press Ctrl + K on your keyboard
3. You will be given options to add links to websites, documents, or other areas within the same workbook.

This significantly reduces time spent on copy-pasting URLs and allows for efficiently accessing external data without switching tabs or windows.

A popular historical use of hyperlinks was in the late 1990s; with the rise of search engines Altavista, Yahoo!, and Google lagged behind HotBot due to its incorporation of “clickable” keywords directly into Web pages’ text.

Some Facts About 15 Keyboard Shortcuts for Showing Formulas in Excel:

• ✅ Pressing Ctrl + ` (grave accent) shows all the formulas in a worksheet. (Source: Microsoft)
• ✅ Pressing Ctrl + [ (left bracket) helps to trace the cells that are referred to in formulas and functions. (Source: Microsoft)
• ✅ Pressing Ctrl + Shift + A displays the formula of the selected cell without changing the cell reference style. (Source: Exceljet)
• ✅ Pressing F2 toggles between editing a cell’s content and editing a cell’s formula. (Source: Excel Easy)
• ✅ Pressing Ctrl + ~ (tilde) switches between showing cell values and showing formulas in a worksheet. (Source: AccountingWEB)

FAQs about 15 Keyboard Shortcuts For Showing Formulas In Excel

What are the 15 keyboard shortcuts for showing formulas in Excel?

Here are the 15 keyboard shortcuts for showing formulas in Excel:

• Ctr + ` – Shows the formulas view in the active sheet
• Ctrl + [ – Selects cells that are referred to by a formula
• Ctrl + Shift + { – Selects all cells directly or indirectly referenced by formulas in the selection
• Ctrl + Shift + } – Selects all cells that contribute to the calculation of the active cell
• Ctrl + Shift + * – Selects the entire range used by the formula in the active cell
• Ctrl + Alt + V – Opens the Paste Special dialog box
• Ctrl + Shift + ~ – Applies the General number format to cells with formulas
• Ctrl + Shift + @ – Applies the Time format with hour, minute, and AM/PM to cells with formulas
• Ctrl + Shift + # – Applies the Date format with day, month, and year to cells with formulas
• Ctrl + Shift + !\$ – Applies the Currency format with the symbol to cells with formulas
• Ctrl + Shift + % – Applies the Percentage format to cells with formulas
• Ctrl + Shift + ^ – Applies the Scientific number format to cells with formulas
• Ctrl + Shift + & – Applies the Border format to cells with formulas
• Ctrl + Shift + _ – Removes the border from the bottom edge of cells with formulas
• Ctrl + Shift + * – Selects the entire range used by the formula in the active cell

How do I use keyboard shortcuts to show formulas in Excel?

You can use keyboard shortcuts to show formulas in Excel by pressing the appropriate key combination. For example, to show formulas in the active sheet, press Ctrl + `. To select cells that are referred to by a formula, press Ctrl + [. To select all cells directly or indirectly referenced by formulas in the selection, press Ctrl + Shift + {.

What is the purpose of showing formulas in Excel?

The purpose of showing formulas in Excel is to view and evaluate the formulas used in cells. This is helpful for checking the accuracy of your calculations and identifying any errors that may exist in your worksheet. Showing formulas can also be used to copy and paste formula between cells or sheets.

How do I create my own keyboard shortcuts in Excel?

You can create your own keyboard shortcuts in Excel by using the Customize Ribbon option. First, click the File tab and then click Options. On the Excel Options dialog box, click Customize Ribbon and then click Customize (next to Keyboard Shortcuts). From there, you can assign a new keyboard shortcut combination to any command in Excel.

Can I use these keyboard shortcuts on a Mac?

Yes, these keyboard shortcuts can be used on a Mac by using the Command key instead of the Ctrl key. For example, to show formulas in the active sheet on a Mac, press Command + `.

Can I undo showing formulas in Excel?

Yes, you can undo showing formulas in Excel by pressing Ctrl + ‘ (the apostrophe key). This will revert back to the original cell display mode.

Auther name

Jacky Chou is an electrical engineer turned marketer. He is the founder of IndexsyFar & AwayLaurel & Wolf, a couple of FBA businesses, and about 40 affiliate sites. He is a proud native of Vancouver, BC, who has been featured on Entrepreneur.comForbesOberlo, and GoDaddy.