# formulae that is used in a cell to calculate a value is visible in

### Mohammed

Guys, does anyone know the answer?

get formulae that is used in a cell to calculate a value is visible in from screen.

## How to show formulas in Excel

The tutorial demonstrate an easy way to show formulas in Excel 2016, 2013, 2010 and older versions. Also, you will learn why Excel is showing formula, not result, in a cell and how to fix this.

## How to show formulas in Excel

by Svetlana Cheusheva, updated on November 4, 2022

In this short tutorial, you will learn an easy way to display formulas in Excel 2016, 2013, 2010 and older versions. Also, you will learn how to print formulas and why sometimes Excel shows a formula, not result, in a cell.

If you are working on a spreadsheet with a lot of formulas in it, it may become challenging to comprehend how all those formulas relate to each other. Showing formulas in Excel instead of their results can help you track the data used in each calculation and quickly check your formulas for errors.

Microsoft Excel provides a really simple and quick way to show formulas in cells, and in a moment, you will make sure of this.

How to display formulas in Excel

Print formulas in Excel

Why Excel shows formula not result, and how to fix this

## How to show formulas in Excel

Usually, when you enter a formula in a cell and press the Enter key, Excel immediately displays the calculated result. To show all formulas in the cells containing them, use one of the following methods.

### 1. Show Formulas option on the Excel ribbon

In your Excel worksheet, go to the Formulas tab > Formula Auditing group and click the **Show Formulas** button.

Microsoft Excel displays formulas in cells instead of their results right away. To get the calculated values back, click the Show Formulas button again to toggle it off.

### 2. Show formulas in cells instead of their results in Excel options

In Excel 2010 and higher, go to File > Options. In Excel 2007, click Office Button > Excel Options.

Select Advanced on the left pane, scroll down to the Display options for this worksheet section and select the option **Show formulas in cells instead of their calculated results**.

At first sight, this seems to be a longer way, but you may find it useful when you want to display formulas in a number of Excel sheets, within the currently open workbooks. In this case, you just select the sheet name from the dropdown list and check the Show formulas in cells… option for each sheet.

### 3. Excel shortcut to show formulas

The fastest way to see every formula in your Excel spreadsheet is pressing the following shortcut: Ctrl + `

The grave accent key (`) is the furthest key to the left on the row with the number keys (next to the number 1 key).

The Show Formulas shortcut toggles between displaying cell values and cell formulas. To get the formula results back, simply hit the shortcut again.

Note. Whichever of the above methods you use, Microsoft Excel will show all formulas of the **current worksheet**. To display formulas in other sheets and workbooks, you will need to repeat the process for each sheet individually.

If you want to view the data used in a formula's calculations, use any of the above methods to show formulas in cells, then select the cell containing the formula in question, and you will see a result similar to this:

Tip. If you click a cell with a formula, but the formula does not show up in the formula bar, then most likely that formula is **hidden** and the worksheet is protected. Here are the steps to unhide formulas and remove the worksheet protection.

## How to print formulas in Excel

If you want to print formulas in your Excel spreadsheet instead of printing the calculated results of those formulas, just use any of the 3 methods to show formulas in cells, and then print the worksheet as you normally print your Excel files (File > Print). That's it!

## Why is Excel showing formula, not result?

Did it ever happen to you that you type a formula in a cell, press the Enter key… and Excel still shows the formula instead of the result? Don't worry, your Excel is all right, and we will have that mishap fixed in a moment.

In general, Microsoft Excel can display formulas instead of calculated values for the following reasons:

You may have inadvertently activated the Show Formulas mode by clicking the corresponding button on the ribbon, or pressing the CTRL+` shortcut. To get the calculated results back, just toggle off the Show Formulas button or press CTRL+` again.

You may have accidentally typed a **space** or **single quote** (') before the equal sign in the formula:

When a space or single quote precedes the equal sign, Excel treats the cell contents as text and does not evaluate any formula within that cell. To fix this, just remove the leading space or single quote.

Before entering a formula in a cell, you may have set the cell's formatting to **Text**. In this case, Excel also perceives the formula as a usual text string and does not calculate it.

To fix this error, select the cell, go to the Home tab > Number group, and set the cell's formatting to **General**, and while in the cell, press F2 and ENTER.

## Excel: Calculations Based on Visible Rows

The SUBTOTAL and AGGREGATE functions let you get a unique count of customers in a data set that has a filter applied.

## Technology |

Technology | Excel: Calculations Based on Visible Rows

BY BILL JELEN June 1, 2021

AddThis Sharing Buttons

Share to Facebook Share to LinkedIn Share to Reddit Share to Twitter Share to More 15

A reader recently asked how to get a unique count of customers in a data set that has a filter applied. For example, if a spreadsheet of fruit sales was filtered to show just sales of apples, could you have a formula that would calculate the number of unique customers from the visible rows?

Ever since Dynamic Arrays debuted in Microsoft 365, getting a unique list of values is easy using the =UNIQUE(A2:A999) formula. To get a count of the unique values, you would use =COUNTA(UNIQUE(A2:A999)). But these formulas count all the values in A2:A999, even those hidden by a filter.

### EXCLUDING HIDDEN ROWS

Of the more than 400 functions in Excel, only two are able to optionally exclude hidden rows: SUBTOTAL and AGGREGATE. The solution to counting the number of unique customers in the visible rows involves adding a helper column to identify if each row is currently visible.

The SUBTOTAL function includes an unexpected feature when it’s used in the last row of a filtered data set. Because the AutoSum command uses SUBTOTAL to total the visible rows in a filtered data set, Excel will never hide the last row in a filtered data set if it contains a SUBTOTAL formula. This “feature” potentially breaks your ability to filter, so SUBTOTAL won’t work to solve this problem.

The AGGREGATE function was introduced in 2010 as an improvement to the SUBTOTAL function. It offers 19 calculation options, such as SUM, COUNTA, MEDIAN, LARGE, SMALL, PERCENTILE, and QUARTILE. To solve this problem, you will be using a 3 to count text entries as the first argument of the function.

The second argument allows you to specify what types of values should be excluded. There are eight options for this argument (values of 0 to 7). For example, using 0 tells the function to ignore cells with SUBTOTAL and AGGREGATE functions; a 3 ignores hidden rows as well as cells with error values or SUBTOTAL and AGGREGATE functions; a 4 ignores nothing; and a 6 ignores cells with error values. We’ll use a 5, which excludes only hidden rows.

### COUNTING UNIQUE VISIBLE VALUES

Figure 1 contains a small data set in cells A1:C11. Add a helper column to the right of the data with a heading of “Visible?” The formula in D2 is =AGGREGATE(3,5,A2). The 3 in this formula says to use the COUNTA function to count cells that contain text or numbers. The 5 says to ignore hidden rows. The A2 reference says to count how many cells there are in column A of this row. When a row is visible, column D will contain a 1. You can see the SUM function in D13 returns a total of 10.

When you apply a filter to the data, the AGGREGATE function on the hidden rows will return a 0 instead of a 1. While you can’t see the zeros in Figure 2, you can tell from the SUM function in D13 that the values in rows 2, 4-6, 9, and 11 must have changed to a zero.

The AGGREGATE function in the Visible? column provides a way to operate on just the visible rows in the data set. Now we can apply the solution to get the list of unique customers displayed.

A temporary formula in cell A16 uses =FILTER(B2:B11,D2:D11=1) to return a list of customer names from the visible rows. This formula spills to A16:A19 and gives you the list: Barb, Andy, Andy, Barb.

In cell C16, a temporary formula of =UNIQUE(FILTER(B2:B11,D2:D11=1)) returns one occurrence of each customer: Barb, Andy.

Finally, the formula in A22 counts the number of text entries returned by the UNIQUE function: =COUNTA(UNIQUE(FILTER(A2:A11,D2:D11=1))).

### SINGLE-CELL AGGREGATE

Note that it’s somewhat unusual to specify a single cell in the AGGREGATE function. Normally, the AGGREGATE function would be pointing at your entire data set. If you wanted to calculate the median value of the visible rows, you could simply use =AGGREGATE(12,5,C2:C11). In this formula, 12 indicates MEDIAN, 5 indicates to ignore the hidden rows, and C2:C11 is all of the sales figures in the data set. In this case, you wouldn’t need the helper column to identify which rows are hidden.

As Microsoft continues to add new functions to Excel, it’s more likely that you might want to use a new calculation that isn’t one of the 19 calculations supported by AGGREGATE. In these cases, you can use the single-cell AGGREGATE in the helper column to handle detecting if a row is currently visible or not.

**Bill Jelen**is the host of MrExcel.com and the author of 67 books about Excel. He helped create IMA’s Excel courses on data analytics and the IMA Excel 365: Tips in Ten series of microlearning courses. Send questions for future articles to [email protected]

AddThis Sharing Buttons

Share to Facebook Share to LinkedIn Share to Reddit Share to Twitter Share to More 15 0 No Comments YOU MAY ALSO LIKE

## Calculating Cell Values with Formulas in Excel

This guide will walk you through some of the most common Excel formulas like SUM, PRODUCT, AVERAGE, and more.

Chhaya Wagmi

## Calculating Cell Values with Formulas

### Chhaya Wagmi

Sep 14, 202011 Min read22,984 Views

Business Professional

Microsoft Excel

## Introduction

In this guide, you will learn how to calculate cell values using various functions in Microsoft Excel 2019.

Excel formulas allow users to perform calculations on large amounts of data in a very short time and simplified manner. For an example, what if a teacher wants to calculate the average grade scored by thousands of students in a subject? It would take him hours to calculate the average using a calculator, but through Excel, a simple drag of cells containing grades and a simple formula can do the work in a few seconds. Excel formulas play an important role in the lives of students, auditors, business professionals, an many others.

There are various functions present in the Excel 2019 version to calculate cell values:

SUM PRODUCT AVERAGE COUNT & COUNTA IF MAX & MIN TRIM DEC2BIN

This guide will walk through each of these functions.

## SUM Function

The SUM function is used to add values from multiple cells. Here's the syntax:

1=SUM(n1, n2, n3, ...)

Consider the example given below:

A B C D E F G

**Sr. Number**

**Value1**

**Value2**

**Value3**

**Value4**

**Formula**

**Result**

1 103 53 21 3423 =SUM(B1:E1) ?

2 122 25 51 321 =SUM(B2, C2, D2, E2) ?

3 88 50 15 12 =SUM(88, 50, 15, 12) ?

4 62 57 17 343 =SUM(B4, D4) ?

5 15 51 11 87 =SUM(B2:E2, B5:E5) ?

After applying the formula, the result column will be updated as given below:

A B C D E F G

**Sr. Number**

**Value1**

**Value2**

**Value3**

**Value4**

**Formula**

**Result**

1 103 53 21 3423 =SUM(B1:E1) 3600

2 122 25 51 321 =SUM(B2, C2, D2, E2) 519

3 88 50 15 12 =SUM(88, 50, 15, 12) 165

4 62 57 17 343 =SUM(B4, D4) 79

5 15 51 11 87 =SUM(B2:E2, B5:E5) 683

## PRODUCT Function

As the name suggests, all the numbers passed inside the PRODUCT function gets multiplied. Here's the syntax:

1=PRODUCT(n1, n2, n3, ...)

Consider the example given below:

A B C D E F G

**Sr. Number**

**Value1**

**Value2**

**Value3**

**Value4**

**Formula**

**Result**

1 13 3 21 3 =PRODUCT(B1:E1) ?

2 22 2 5 31 =PRODUCT(B2:E2, 3) ?

3 8 50 5 2 =8 50 5 * 2 ?

4 6 7 17 3 =PRODUCT(C4, E4) ?

5 15 5 11 8 =PRODUCT(B2:E2, B5:E5) ?

After applying the formula, the result column will be updated as given below:

A B C D E F G

**Sr. Number**

**Value1**

**Value2**

**Value3**

**Value4**

**Formula**

**Result**

1 13 3 21 3 =PRODUCT(B1:E1) 2457

2 22 2 5 31 =PRODUCT(B2:E2, 3) 20460

3 8 50 5 2 =8 50 5 * 2 4000

4 6 7 17 3 =PRODUCT(C4, E4) 21

5 15 5 11 8 =PRODUCT(B2:E2, B5:E5) 45012000

## AVERAGE Function

If you want to calculate the arithmetic mean of given numbers, use the AVERAGE function. Here's the syntax:

1=AVERAGE(n1, n2, n3, ...)

Consider the example given below:

A B C D E F G

**Sr. Number**

**Value1**

**Value2**

**Value3**

**Value4**

**Formula**

**Result**

1 13 3 21 3 =AVERAGE(B1:E1) ?

2 22 2 5 31 =AVERAGE(B2:E2, 3) ?

3 6 7 17 3 =PRODUCT(C3:E3) ?

4 15 5 11 8 =PRODUCT(B2:B4) ?

After applying the formula, the result column will be updated as given below:

A B C D E F G

**Sr. Number**

**Value1**

**Value2**

**Value3**

**Value4**

**Formula**

**Result**

1 13 3 21 3 =AVERAGE(B1:E1) 10

2 22 2 5 31 =AVERAGE(B2:E2, 3) 12.6

3 6 7 17 3 =PRODUCT(C3:E3) 9

4 15 5 11 8 =PRODUCT(B2:B4) 14

## COUNT & COUNTA Function

The COUNT function is used "to get the number of entries in a number field that is in a range or array of numbers," whereas the COUNTA function "counts the number of cells that are not empty in a range," according to Excel's documentation.

The COUNT and COUNTA functions have the following syntax:

1=COUNT(n1, n2, n3, ...)

2=COUNTA(n1, n2, n3, ...)

Consider the example given below to understand the use of COUNT and COUNTA functions better:

A B C D E F G

**Sr. Number**

**Value1**

**Value2**

**Value3**

**Value4**

**Formula**

**Result**

1 13 3 21 3 =COUNT(B1:E1) ?

2 22 2 5 31 =COUNTA(B2:E2) ?

3 6 7 NAME =COUNT(B3:E3) ?

4 TOM 5 11 =PRODUCT(B4:E4) ?

After applying the formula, the result column will be updated as given below:

A B C D E F G

**Sr. Number**

**Value1**

**Value2**

**Value3**

**Value4**

**Formula**

**Result**

1 13 3 21 3 =COUNT(B1:E1) 4

2 22 2 5 31 =COUNTA(B2:E2) 4

3 6 7 NAME =COUNT(B3:E3) 2

4 TOM 5 11 =COUNTA(B4:E4) 3

## IF Function

When you have a logical condition that can either be true or false, use the IF function. The IF function has the following syntax:

Guys, does anyone know the answer?