Author

superadmin

Browsing

How to find the average value in data ignoring Errors and Hidden rows?

MS Excel has an AVERAGE function to get the AVERAGE value. But how to compute Average ignoring errors and hidden rows as the AVERAGE function does not help when you have error(s) and hidden rows in the range. The output results in an error or a misleading answer.

To solve this and get the average ignoring errors and hidden rows, we should use the AGGREGATE

AVERAGE ignoring errors and hidden rows

Notice the AVERAGE function returns an error, this happens because the range consists of errors (non-numeric values) which can’t be processed. In case we had no errors our answers would still differ, due to hidden rows. Could AVERAGEIF be helpful as we get an opportunity to provide a condition? Not really as an IF condition would not be enough to ignore the errors.

=AGGREGATE(1,7,C11:C18)

Formula Explained

The first requirement is a function to choose, the number 1 specifies the AVERAGE function, followed by number 7 which is an option to ignore errors and hidden rows and C11: C18 is the range.
With these parameters, AGGREGATE returns the AVERAGE value, it being 9858.24. Compare the answer with that of AVERAGE, it is very different because of the criteria passed.

Thus we find that to find the AVERAGE ignoring errors and hidden rows, AGGREGATE function is the ready solution.

TIP: As soon as you type the =AGGREGATE you will see a list of all functions that you can use as arguments and their respective options in the second argument.

NOTE: If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error. If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! error value.

Click here to know and explore the power of the AGGREGATE function.

Want to Format your data in no time? Click on the Link Below
https://www.youtube.com/watch?v=WWrTOl-C_hY

How do I get in a data the maximum MAX ignoring hidden rows

MS Excel has a MAX function to get the MAX value. But this does not work if we want to find the MAX ignoring hidden rows in the range. The output results in an error or a misleading answer as the output of MAX will show the maximum value including the value in hidden rows.

We solve this by using the AGGREGATE function.

MAX ignoring hidden rows

=AGGREGATE(5,5,C11:C18)

The first requirement is a function to choose, the number 5 specifies the MAX function, followed by number 5 which is an option to ignore only hidden rows(C16) and C11:C18 is the range.
With these parameters, AGGREGATE returns the MAXIMUM value, it being 18901. Compare the answer with that of MAX, it is same because here 18901 is maximum value in the selected range and the same is in hidden row.

TIP: As soon as you type the =AGGREGATE you will see a list of all functions that you can use as arguments and their respective options in the second argument. Options are what makes AGGREGATE a powerful function.

NOTE: If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error. If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! Error value.

Click here to know and explore the power of the AGGREGATE function.

Want to Format your data in no time? Click on the Link Below
https://www.youtube.com/watch?v=WWrTOl-C_hY

How do I get in the given data the SUM ignoring hidden rows?

MS Excel has a SUM function to get the sum value, but it is not useful if we want to sum ignoring hidden rows. the output results in Error or a misleading answer as the output of SUM will show the value including the value in hidden rows. What if one wants return value only from the unhidden row but not from the hidden one?

How do we solve this?

By using the AGGREGATE function as follows:

Sum ignoring hidden rows

=AGGREGATE(9,5,C11:C18)

The first requirement is a function to choose, the number 9 specifies the SUM function, followed by number 5 which is an option to ignore only hidden rows (STORE 6 is hidden row here)and C11: C18 is the range.
With these parameters, AGGREGATE returns the sum of value, it being 52351.2. Compare the answer with that of SUM, it is very different because of the criteria passed.

TIP: As soon as you type the =AGGREGATE you will see a list of all functions that you can use as arguments and their respective options in the second argument.

NOTE: If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error. If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! Error value.

Click here to know and explore the power of the AGGREGATE function.

Want to Format your data in no time? Click on the Link Below
https://www.youtube.com/watch?v=WWrTOl-C_hY

How do I get the AVERAGE ignoring hidden rows?

MS Excel has an AVERAGE function to get the AVERAGE value. But to get the AVERAGE ignoring hidden rows is a challenge.
When you have hidden rows in the range the output results in Error or a misleading answer as the output of AVERAGE will show the AVERAGE value including the value in hidden rows.
What if one wants return value only from the unhidden row  But not from hidden one?

We solve this using the AGGREGATE function as follows:

Calculate AVERAGE Ignoring Hidden Rows - Aggregation

=AGGREGATE(1,5,C11:C18)

The first requirement is a function to choose, the number 1 specifies the AVERAGE function, followed by number 5 which is an option to ignore only hidden rows(C16) and C11: C18 is the range.
With these parameters, AGGREGATE returns the AVERAGE value, it being 7478.743. Compare the answer with that of AVERAGE, it is very different because of the criteria passed.

TIP: As soon as you type the =AGGREGATE you will see a list of all functions that you can use as arguments and their respective options in the second argument.

NOTE: If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error. If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! Error value.

Click here to know and explore the power of the AGGREGATE function.

Want to Format your data in no time? Click on the Link Below
https://www.youtube.com/watch?v=WWrTOl-C_hY

How do I get the MIN ignoring hidden rows in the data?

MS Excel has a MIN function to get the minimum value but when you have hidden rows in the range, to get the MIN ignoring hidden rows is not possible. The output results in an error or a misleading answer, as the output of MIN will show the minimum value including the value in hidden rows. What if one wants return value only from the unhidden row but not from the hidden one?

We can solve this using the AGGREGATE function as follows:

Calculate MIN Ignoring Hidden Rows - Aggregation

=AGGREGATE(5,5,C11:C18)

The first requirement is a function to choose, the number 5 specifies the MIN function, followed by number 5 which is an option to ignore only hidden rows and C11: C18 is the range.
With these parameters, AGGREGATE returns the Minimum value, it being 1515. Compare the answer with that of MIN, it is very different because of the criteria passed.

TIP: As soon as you type the =AGGREGATE you will see a list of all functions that you can use as arguments and their respective options in the second argument.

NOTE: If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error. If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! Error value.

Click here to know and explore the power of the AGGREGATE function.

Want to Format your data in no time? Click on the Link Below
https://www.youtube.com/watch?v=WWrTOl-C_hY

LARGE value in a data containing hidden rows

MS Excel has a LARGE function to get the LARGE value but when you have hidden rows in the range, the calculation of LARGE ignoring hidden rows results in an error or a misleading answer. This is because the output of LARGE will show the value including the value in hidden rows. What if one wants return value only from the unhidden row but not from the hidden one?

How to get Large ignoring hidden rows?

By using the AGGREGATE function as follows:

Large ignoring hidden rows

=AGGREGATE(14,5,C11:C18)

The first requirement is a function to choose, the number 14 specifies the LARGE function, followed by number 5 which is an option to ignore only hidden rows viz C16 and C11: C18 is the range and 1 is the Kth largest value.
With these parameters, AGGREGATE returns the LARGE value, it being 17901. Here the answer will be the same because whether it is hidden or unhidden the large value is 17901

TIP: As soon as you type the =AGGREGATE you will see a list of all functions that you can use as arguments and their respective options in the second argument.

NOTE: If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error. If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! Error value.

Click here to know and explore the power of the AGGREGATE function.

Want to Format your data in no time? Click on the Link Below
https://www.youtube.com/watch?v=WWrTOl-C_hY

Median Value

MS Excel has a MEDIAN function to get the median value but when you have to find the median ignoring hidden rows in the range, the output results in Error or a misleading answer as the output of MEDIAN will show the MEDIAN value including the value in hidden rows. What if one wants to return value only from the unhidden row but not from the hidden one?

How do we get the median ignoring hidden rows?

Using the AGGREGATE function as follows :

Median Ignoring Hidden Rows

=AGGREGATE(12,5,C11:C18)

The first requirement is a function to choose, the number 12 specifies the MEDIAN function, followed by number 5 which is an option to ignore only hidden rows (STORE6) and C11: C18 is the range.
With these parameters, AGGREGATE returns the MEDIAN of the range, it being 7781.2 Compare the answer with that of MEDIAN, it is very different because of the criteria passed.

TIP: As soon as you type the =AGGREGATE you will see a list of all functions that you can use as arguments and their respective options in the second argument.

NOTE: If a second ref argument is required but not provided, AGGREGATE returns a #VALUE! error. If one or more of the references are 3-D references, AGGREGATE returns the #VALUE! Error value.

Click here to know and explore the power of the AGGREGATE function.

Want to Format your data in no time? Click on the Link Below
https://www.youtube.com/watch?v=WWrTOl-C_hY

Conditional formatting helps to use a formula to determine the cells to be formatted. Conditional formatting is a tool useful in presenting data to the users in an Excel spreadsheet. Conditional formatting highlighting the data using colors and icons to cell or range of cells. The important function is to grab the attention of the users to the most important data in that spreadsheet.

One can apply the following formats using conditional formatting
⦁ Number format
⦁ Font, color, font style, and font but conditional formatting do not have the option of changing the size of the font.
⦁ Color filling and pattern
⦁ Border color and border style (but not border thickness)
⦁ The formatting will be applied if the condition or rules that we supplied are met.

In conditional formatting, you have to select a cell or range of cells to apply the format. Conditional formatting will not return the condition if the cell is blank. Here you can create a new rule/condition for the cell. The conditions can be, based on the selected cell’s contents, or based on the contents of another cell.

As we know that conditional formatting provides many options to format the data but If you don’t see the exact options you need when you create your own conditional formatting rule, you can use a logical formula to specify the formatting criteria. 

Below is an example of Conditional formatting to use a formula to format cells.

We have data containing product id,code and selling price given in figure 1.0. Let us see how to “use a formula to determine which cell to format” in conditional formatting with the given data.

Use a formula to determine which cell to format - Conditional formatting 1
FIGURE 1.0

Step 1

The first step is to select the cell or required range of cells in a spreadsheet for which we want to apply the conditional formatting. Without selecting a cell or range of data the return value will not be highlighted. Here we have selected the cell range D8: D17 see figure 1.1

Use a formula to determine which cell to format - Conditional formatting 2

STEP 2:

After selecting the range of cell, select conditional formatting, a dropdown box will appear here-select “New Rule” option as shown in figure 1.2

Conditional Formatting > NewRule > Use A Formula To Determine Which Cell To Format

Use a formula to determine which cell to format - Conditional formatting 3
figure 1.2

Step 3:

After selecting  “use a formula to determine which cells to format”, an edit the rule description box will be shown as shown in the below figure.

In that type the formula. In our example, we have both odd and even numbers so, here we will highlight the odd numbers only using the “ISODD” formula in selling price. Refer figure 1.3

The supplied formula is =ISODD(D8).

You have to start the formula with an equal sign (=), and the formula must return a logical value of TRUE (1) or FALSE (0).

Use a formula to determine which cell to format - Conditional formatting 4
figure 1.3

STEP 4 :

After clicking format, a “Format Cells” dialog box will appear in screen, where you select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK as shown in figure 1.4

use a formula
figure 1.4

STEP 5 :

Thus Excel will return the result of supplied data highlighted with odd numbers in selling price column.

Use a formula to determine which cell to format - Conditional formatting 5

 If any cells contain a formula that returns an error, conditional formatting is not applied to those cells. To address this, use IS functions or an IFERROR function in your formula to return a value that you specify (such as 0, or “N/A”) instead of an error value.

Another example

In the data given below, we have selected the product id column, and we are going to use the conditional formatting by giving reference to another column that is selling price.

Use a formula to determine which cell to format - Conditional formatting 6

if you refer to the below-given figure, you see that the formula we applied is D8 greater than 400 =D8>400.

Use a formula to determine which cell to format - Conditional formatting 7

And we will get the return result in column product id, ie in cell Row B where it will highlight those product ids whose selling price is more than 400.

Use a formula to determine which cell to format - Conditional formatting 8

NOTE

You can refer to another one of the many uses of Conditional formatting here.

You can view our videos available on our Youtube channel to enhance and refresh your Excel knowledge.

MANAGING RULES

Managing rules is helpful to view all conditional format applied in the workbook. One can edit, insert new cell rule and can delete the applied format in a worksheet.

For example, the given image is data where some conditional formatting is applied.

managing rules

Let us discuss how to use managing rules in excel.

First select the range of cell and click Conditional formatting > Manage rules..

conditional formatting

Once you clicked the manage rule option a dialog box will pop up showing all the conditional formatting that was used in the worksheet. In the given example we have applied two formatting option as shown in figure 1.3

Managing Rules - Conditional Formatting 9

NEW RULE:

The New rule can be added using the manage rules option.

Click the new rule box (circled in the below figure 1.4)

Managing Rules - Conditional Formatting 10

After clicking a dialog box of rule type will pop up. here we can select the rule that we want to apply.

In this example we will select the option of “Format only top or bottom ranked values”

Once it is clicked, select top/bottom; and number. Here we have given Top 5 range. After selecting all the required formatting, click OK

Managing Rules - Conditional Formatting 11
figure 1.5

After adding new cell rule in the sheet the managing rule dialog box will appear like the image given below. If you note the image you will find that the newly added rule of Top 5 is appearing in the list.

Managing Rules - Conditional Formatting 12

EDIT RULE:

One can edit the already applied rule using the Edit rule option in the manage rule option. In our example, we have applied the formula =D8>400

By selecting the formula, click edit rule option as shown in figure 1.6

Managing Rules - Conditional Formatting 13
figure 1.6

Once you clicked the edit rule option a dialog box of edit formatting rule will appear and here we have edited the formula by changing the value 400 to 200 i.e =D8>200. And select OK. Refer figure 1.7

Managing Rules - Conditional Formatting 14
figure 1.7

Once all the edit rule and adding new rule is done, the dialog box will look similar to the figure below. These are the results of the conditional formatting we have applied in our worksheet.

Managing Rules - Conditional Formatting 15
figure 1.8

You can also delete any rule applied in a selected worksheet in the same manner.
You can compare the figure 1.3 with this figure 1.8
Click Apply and Ok.

Finally the worksheet will highlight all changes that we have done by editing and adding a new rule.

See figure 1.9

Managing Rules - Conditional Formatting 16
figure 1.9
Here in selling price 492 falls under both rule of duplicate and value above 200 i.e D8>200, but you notice the letters of 492 are highlighted in red and cell is highlighted in magenta color denoting both rules in one.

NOTE

You can refer to another one of the many uses of Conditional formatting here.

You can view our videos available on our Youtube
channel to enhance and refresh your Excel knowledge.

What is Conditional Formatting ?

Conditional formatting is a tool useful in presenting data to the users in an Excel spreadsheet in a unique way.
The foremost function of conditional formatting is to present huge data in an understandable manner. Conditional formatting highlighting the data using colors and icons to cell or range of cells. The important function is to grab the attention of the users to the most important data in that spreadsheet.

One can apply the following formats using conditional formatting
⦁ Number format
⦁ The font color, font style, and font. But conditional formatting does not have the option of changing the size of the font.
⦁ Color filling and pattern
⦁ Border color and border style (but not border thickness)
⦁ The formatting will be applied if the condition or rules that we supplied are met.

In conditional formatting, you have to select a cell or range of cells to apply the format. Conditional formatting will not return the condition if the cell is blank. Here you can create a new rule/condition for the cell. The conditions can be, based on the selected cell’s contents, or based on the contents of another cell.

AVAILABILITY

Conditional formatting was introduced in the year 2007.

UNIQUE OR DUPLICATE VALUES

While working on big data we come across many duplicates in the working data which may create confusion.
In order to find duplicates and unique value, Excel has special function of format only unique or duplicates values under conditional formatting.
In this article we will learn how to use this “Format Only Unique Or Duplicates Values”.

STEP 1
We have data of various product id, product name, and its selling price. From this data we will find duplicates in this available data. Refer figure 1.1

Format Only Unique or Duplicate Values - Conditional Formatting 17
figure1.1

STEP 2 :
The next step is to select the cell or required range of cells in a spreadsheet for which we want to apply the conditional formatting. Without selecting a cell or range of data the return value will not be highlighted. In this example, we have selected the product id column as shown in figure 1.2

Format Only Unique or Duplicate Values - Conditional Formatting 18
figure 1.2

STEP 3:

After selecting the range of cell, select conditional formatting, a dropdown box will appear here select “New Rule” option as shown in figure 1.3
Conditional Formatting > NewRule > Format only unique or duplicates only

Format Only Unique or Duplicate Values - Conditional Formatting 19
figure 1.3

STEP 4 :

After selecting the new rule option, a dialog box with the selected rule type will pop up. Here select “Format only unique or duplicate values”.
Under the Edit Rule Description option select duplicate or unique. The duplicate will highlight those cells which appear more than one time.

The cells which are unique i.e, those data which appear only one time in the selected range of cells will be highlighted when “unique” is selected.

In our example figure 1.4 we have selected duplicate to format.

Format Only Unique or Duplicate Values - Conditional Formatting 20
figure 1.4

STEP 5 :

After clicking format a “Format Cells” dialog box will appear in screen, where you select the number, font, border, or fill format you want to apply when the cell value meets the condition, and then click OK as shown in figure 1.5

Format Only Unique or Duplicate Values - Conditional Formatting 21
figure 1.5

STEP 6 :

You can see the font style and color in the preview box that appears in a new formatting rule dialog box as shown in figure 1.6 . You can choose more than one format also.

Format Only Unique or Duplicate Values - Conditional Formatting 22
figure 1.6

STEP 7 :

The final result will be the cells with highlighted duplicates value.

If we select unique as the required result in step 4, the outcome will be as shown in case 2, highlighting only the unique values.

Format Only Unique or Duplicate Values - Conditional Formatting 23

The same steps are applicable to get unique value highlighted.

Make sure that a cell or range of cells is selected while applying conditional formatting.

To remove conditional formatting in given data you can select Clear formatting option.

NOTE

You can refer to another one of the many uses of Conditional formatting here.

You can view our videos available on our Youtube
channel to enhance and refresh your Excel knowledge.