Trending February 2024 # Filter Column In Excel (Example) # Suggested March 2024 # Top 11 Popular

You are reading the article Filter Column In Excel (Example) updated in February 2024 on the website Eastwest.edu.vn. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Filter Column In Excel (Example)

Filter Column in Excel

Filter Column in Excel are used for filtering the data by selecting the data type in the filter dropdown. Using a filter, we can make out the data we want to see or on which we need to work.

To access/apply a filter in any column of excel, go to the Data menu tab; under Sort & Filter, we will find the Filter option.

How to Filter a Column in Excel?

Filtering a column in Excel is a very simple and easy task. Let’s understand how to filter a column in Excel with an example.

You can download this Column Filter Excel Template here – Column Filter Excel Template

We have some sample data tables in excel, where we will apply the filter in columns. Below is the screenshot of a data set, which has multiple columns and multiple rows with various data sets.

For applying Excel Column Filter, select the top row first, and the filter will be applied to the selected row only, as shown below. Sometimes when we work for a large data set and select the filter directly, the current look of the sheet can be applied.

As we can see in the above screenshot, row 1 is selected and ready to apply the filters.

Now for applying filters, go to the Data menu, and under Sort & Filters, select Filters.

The top row 1 now has the dropdown. This drop-down is those things by which we can filter the data as per our needs.

A drop-down menu will appear, as shown in the below screenshot.

As we can see in the above screenshot, a few filter options are provided by Microsoft.

Sort A to Z / Sort Oldest to Newest (for dates) / Sort Smallest to Largest (for numbers)

Sort Z to A / Sort Newest to Oldest (for dates) / Sort Largest to Smallest (for numbers)

Clear Filter From “Product Type” (This would entitle the name of columns where a filter is applied)

Filter by Color

Text Filters

Sort by Color

Search/Manual Filter

Once we do it, we will see the data is now filtered with Desktop. And whatever the data is in w.r.t. Desktop, the rest of the columns will also get filtered, as shown in the screenshot below.

As we can see in the above screenshot, data is now filtered with Desktop, and all the columns are also sorted with data available for Desktop. Also, the line numbers, which are circled in the above screenshot, show the random numbers. This means that the filter we applied was in a random format, so the line numbers were scattered when we applied the filter.

Now go to Text Filters.

We will find a few more options for filtering the data, as shown in the screenshot below.

The highlighted portion of Text Filters in the box has Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Contain, and Custom Filter.

Equal: With this, we can filter the data with an exact equal word available in the data.

Does Not Equal: With this, we can filter the data with a word that does not match the available words in the data.

Begins With:

This filters the data, beginning with a specific word, letter, or character.

Ends With:

This filters the data, ending with a specific word, letter, or character.

Contains:

With this, we can filter the data containing any specific word, letter, or character.

Does Not Contain: With this, we can filter the data which does not contain any specific word, letter, or character.

As we can see in the above screenshot of Custom AutoFilter, it has to two filter options at the left sides, which And and Or check-in circles separate. And the other two boxes on the left side are for filling the criteria values. This can be called a smart filter.

There are different ways of applying the Excel column filter.

By pressing Ctrl + Shift + L together.

By pressing Alt + D + F + F simultaneously.

Pros of Excel Column Filter

By applying filters, we can sort the data as per our needs.

By filters, performing the analysis or any work becomes easy.

Filters sort the data with words, numbers, cell colors, font colors, or with any range. Also, multiple criteria can use.

Cons of Excel Column Filter

Filters can be applied to all range sizes, but it is not useful if the data size increases to a certain limit. In some cases, if the data goes beyond 50,000 lines, it becomes slow, and sometimes it does not show data available in any column.

Things to Remember 

If you are using the filter and freeze panel together, apply the filter and then use the freeze panel. By doing this, data will be frozen from the middle portion of the sheet.

It will take a lot more time to apply, and sometimes the file also gets crashes. Avoid or be cautious while using a filter for huge data sets (maybe for 50000 or more).

Recommended Articles

This has been a guide to Filter columns in Excel. Here we discuss how to filter a column in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

You're reading Filter Column In Excel (Example)

Decision Tree In R: Classification Tree With Example

What are Decision Trees?

Decision Trees are versatile Machine Learning algorithm that can perform both classification and regression tasks. They are very powerful algorithms, capable of fitting complex datasets. Besides, decision trees are fundamental components of random forests, which are among the most potent Machine Learning algorithms available today.

Training and Visualizing a decision trees in R

To build your first decision tree in R example, we will proceed as follow in this Decision Tree tutorial:

Step 1) Import the data

set.seed(678) titanic <-read.csv(path) head(titanic)

Output:

## X pclass survived name sex ## 1 1 1 1 Allen, Miss. Elisabeth Walton female ## 2 2 1 1 Allison, Master. Hudson Trevor male ## 3 3 1 0 Allison, Miss. Helen Loraine female ## 4 4 1 0 Allison, Mr. Hudson Joshua Creighton male ## 5 5 1 0 Allison, Mrs. Hudson J C (Bessie Waldo Daniels) female ## 6 6 1 1 Anderson, Mr. Harry male ## age sibsp parch ticket fare cabin embarked ## 1 29.0000 0 0 24160 211.3375 B5 S ## 2 0.9167 1 2 113781 151.5500 C22 C26 S ## 3 2.0000 1 2 113781 151.5500 C22 C26 S ## 4 30.0000 1 2 113781 151.5500 C22 C26 S ## 5 25.0000 1 2 113781 151.5500 C22 C26 S ## 6 48.0000 0 0 19952 26.5500 E12 S ## home.dest ## 1 St Louis, MO ## 2 Montreal, PQ / Chesterville, ON ## 3 Montreal, PQ / Chesterville, ON ## 4 Montreal, PQ / Chesterville, ON ## 5 Montreal, PQ / Chesterville, ON ## 6 New York, NY tail(titanic)

Output:

## X pclass survived name sex age sibsp ## 1304 1304 3 0 Yousseff, Mr. Gerious male NA 0 ## 1305 1305 3 0 Zabour, Miss. Hileni female 14.5 1 ## 1306 1306 3 0 Zabour, Miss. Thamine female NA 1 ## 1307 1307 3 0 Zakarian, Mr. Mapriededer male 26.5 0 ## 1308 1308 3 0 Zakarian, Mr. Ortin male 27.0 0 ## 1309 1309 3 0 Zimmerman, Mr. Leo male 29.0 0 ## parch ticket fare cabin embarked home.dest ## 1304 0 2627 14.4583 C ## 1305 0 2665 14.4542 C ## 1306 0 2665 14.4542 C ## 1307 0 2656 7.2250 C ## 1308 0 2670 7.2250 C ## 1309 0 315082 7.8750 S

To overcome this issue, you can use the function sample().

shuffle_index <- sample(1:nrow(titanic)) head(shuffle_index)

To overcome this issue, you can use the function sample().

Decision tree R code Explanation

sample(1:nrow(titanic)): Generate a random list of index from 1 to 1309 (i.e. the maximum number of rows).

Output:

## [1] 288 874 1078 633 887 992

You will use this index to shuffle the titanic dataset.

titanic <- titanic[shuffle_index, ] head(titanic)

Output:

## X pclass survived ## 288 288 1 0 ## 874 874 3 0 ## 1078 1078 3 1 ## 633 633 3 0 ## 887 887 3 1 ## 992 992 3 1 ## name sex age ## 288 Sutton, Mr. Frederick male 61 ## 874 Humblen, Mr. Adolf Mathias Nicolai Olsen male 42 ## 1078 O'Driscoll, Miss. Bridget female NA ## 633 Andersson, Mrs. Anders Johan (Alfrida Konstantia Brogren) female 39 ## 887 Jermyn, Miss. Annie female NA ## 992 Mamee, Mr. Hanna male NA ## sibsp parch ticket fare cabin embarked home.dest## 288 0 0 36963 32.3208 D50 S Haddenfield, NJ ## 874 0 0 348121 7.6500 F G63 S ## 1078 0 0 14311 7.7500 Q ## 633 1 5 347082 31.2750 S Sweden Winnipeg, MN ## 887 0 0 14313 7.7500 Q ## 992 0 0 2677 7.2292 C Step 2) Clean the dataset

The structure of the data shows some variables have NA’s. Data clean up to be done as follows

Drop variables home.dest,cabin, name, X and ticket

Create factor variables for pclass and survived

Drop the NA

library(dplyr) # Drop variables #Convert to factor level mutate(pclass = factor(pclass, levels = c(1, 2, 3), labels = c('Upper', 'Middle', 'Lower')), na.omit() glimpse(clean_titanic)

Code Explanation

select(-c(home.dest, cabin, name, X, ticket)): Drop unnecessary variables

pclass = factor(pclass, levels = c(1,2,3), labels= c(‘Upper’, ‘Middle’, ‘Lower’)): Add label to the variable pclass. 1 becomes Upper, 2 becomes MIddle and 3 becomes lower

factor(survived, levels = c(0,1), labels = c(‘No’, ‘Yes’)): Add label to the variable survived. 1 Becomes No and 2 becomes Yes

na.omit(): Remove the NA observations

Output:

## Observations: 1,045 ## Variables: 8 Step 3) Create train/test set

Before you train your model, you need to perform two steps:

Create a train and test set: You train the model on the train set and test the prediction on the test set (i.e. unseen data)

Install chúng tôi from the console

The common practice is to split the data 80/20, 80 percent of the data serves to train the model, and 20 percent to make predictions. You need to create two separate data frames. You don’t want to touch the test set until you finish building your model. You can create a function name create_train_test() that takes three arguments.

create_train_test(df, size = 0.8, train = TRUE) arguments: -df: Dataset used to train the model. -size: Size of the split. By default, 0.8. Numerical value -train: If set to `TRUE`, the function creates the train set, otherwise the test set. Default value sets to `TRUE`. Boolean chúng tôi need to add a Boolean parameter because R does not allow to return two data frames simultaneously. create_train_test <- function(data, size = 0.8, train = TRUE) { n_row = nrow(data) total_row = size * n_row train_sample < - 1: total_row if (train == TRUE) { return (data[train_sample, ]) } else { return (data[-train_sample, ]) } }

Code Explanation

function(data, size=0.8, train = TRUE): Add the arguments in the function

n_row = nrow(data): Count number of rows in the dataset

total_row = size*n_row: Return the nth row to construct the train set

train_sample <- 1:total_row: Select the first row to the nth rows

if (train ==TRUE){ } else { }: If condition sets to true, return the train set, else the test set.

You can test your function and check the dimension.

data_train <- create_train_test(clean_titanic, 0.8, train = TRUE) data_test <- create_train_test(clean_titanic, 0.8, train = FALSE) dim(data_train)

Output:

## [1] 836 8 dim(data_test)

Output:

## [1] 209 8

The train dataset has 1046 rows while the test dataset has 262 rows.

You use the function prop.table() combined with table() to verify if the randomization process is correct.

prop.table(table(data_train$survived))

Output:

## ## No Yes ## 0.5944976 0.4055024 prop.table(table(data_test$survived))

Output:

## ## No Yes ## 0.5789474 0.4210526

In both dataset, the amount of survivors is the same, about 40 percent.

Install rpart.plot

rpart.plot is not available from conda libraries. You can install it from the console:

install.packages("rpart.plot") Step 4) Build the model

You are ready to build the model. The syntax for Rpart decision tree function is:

rpart(formula, data=, method='') arguments: - formula: The function to predict - data: Specifies the data frame- method: - "class" for a classification tree - "anova" for a regression tree

You use the class method because you predict a class.

library(rpart) library(rpart.plot) fit <- rpart(survived~., data = data_train, method = 'class') rpart.plot(fit, extra = 106

Code Explanation

rpart(): Function to fit the model. The arguments are:

survived ~.: Formula of the Decision Trees

data = data_train: Dataset

method = ‘class’: Fit a binary model

rpart.plot(fit, extra= 106): Plot the tree. The extra features are set to 101 to display the probability of the 2nd class (useful for binary responses). You can refer to the vignette for more information about the other choices.

Output:

You start at the root node (depth 0 over 3, the top of the graph):

At the top, it is the overall probability of survival. It shows the proportion of passenger that survived the crash. 41 percent of passenger survived.

This node asks whether the gender of the passenger is male. If yes, then you go down to the root’s left child node (depth 2). 63 percent are males with a survival probability of 21 percent.

In the second node, you ask if the male passenger is above 3.5 years old. If yes, then the chance of survival is 19 percent.

You keep on going like that to understand what features impact the likelihood of survival.

Note that, one of the many qualities of Decision Trees is that they require very little data preparation. In particular, they don’t require feature scaling or centering.

By default, rpart() function uses the Gini impurity measure to split the note. The higher the Gini coefficient, the more different instances within the node.

Step 5) Make a prediction

You can predict your test dataset. To make a prediction, you can use the predict() function. The basic syntax of predict for R decision tree is:

predict(fitted_model, df, type = 'class') arguments: - fitted_model: This is the object stored after model estimation. - df: Data frame used to make the prediction - type: Type of prediction - 'class': for classification - 'prob': to compute the probability of each class - 'vector': Predict the mean response at the node level

You want to predict which passengers are more likely to survive after the collision from the test set. It means, you will know among those 209 passengers, which one will survive or not.

predict_unseen <-predict(fit, data_test, type = 'class')

Code Explanation

predict(fit, data_test, type = ‘class’): Predict the class (0/1) of the test set

Testing the passenger who didn’t make it and those who did.

table_mat <- table(data_test$survived, predict_unseen) table_mat

Code Explanation

table(data_test$survived, predict_unseen): Create a table to count how many passengers are classified as survivors and passed away compare to the correct decision tree classification in R

Output:

## predict_unseen ## No Yes ## No 106 15 ## Yes 30 58

The model correctly predicted 106 dead passengers but classified 15 survivors as dead. By analogy, the model misclassified 30 passengers as survivors while they turned out to be dead.

Step 6) Measure performance

You can compute an accuracy measure for classification task with the confusion matrix:

The confusion matrix is a better choice to evaluate the classification performance. The general idea is to count the number of times True instances are classified are False.

Each row in a confusion matrix represents an actual target, while each column represents a predicted target. The first row of this matrix considers dead passengers (the False class): 106 were correctly classified as dead (True negative), while the remaining one was wrongly classified as a survivor (False positive). The second row considers the survivors, the positive class were 58 (True positive), while the True negative was 30.

You can compute the accuracy test from the confusion matrix:

It is the proportion of true positive and true negative over the sum of the matrix. With R, you can code as follow:

accuracy_Test <- sum(diag(table_mat)) / sum(table_mat)

Code Explanation

sum(diag(table_mat)): Sum of the diagonal

sum(table_mat): Sum of the matrix.

You can print the accuracy of the test set:

print(paste('Accuracy for test', accuracy_Test))

Output:

## [1] "Accuracy for test 0.784688995215311"

You have a score of 78 percent for the test set. You can replicate the same exercise with the training dataset.

Step 7) Tune the hyper-parameters

Decision tree in R has various parameters that control aspects of the fit. In rpart decision tree library, you can control the parameters using the rpart.control() function. In the following code, you introduce the parameters you will tune. You can refer to the vignette for other parameters.

rpart.control(minsplit = 20, minbucket = round(minsplit/3), maxdepth = 30) Arguments: -minsplit: Set the minimum number of observations in the node before the algorithm perform a split -minbucket: Set the minimum number of observations in the final note i.e. the leaf -maxdepth: Set the maximum depth of any node of the final tree. The root node is treated a depth 0

We will proceed as follow:

Construct function to return accuracy

Tune the maximum depth

Tune the minimum number of sample a node must have before it can split

Tune the minimum number of sample a leaf node must have

You can write a function to display the accuracy. You simply wrap the code you used before:

predict: predict_unseen <- predict(fit, data_test, type = ‘class’)

Produce table: table_mat <- table(data_test$survived, predict_unseen)

Compute accuracy: accuracy_Test <- sum(diag(table_mat))/sum(table_mat)

accuracy_tune <- function(fit) { predict_unseen <- predict(fit, data_test, type = 'class') table_mat <- table(data_test$survived, predict_unseen) accuracy_Test <- sum(diag(table_mat)) / sum(table_mat) accuracy_Test }

You can try to tune the parameters and see if you can improve the model over the default value. As a reminder, you need to get an accuracy higher than 0.78

control <- rpart.control(minsplit = 4, minbucket = round(5 / 3), maxdepth = 3, cp = 0) tune_fit <- rpart(survived~., data = data_train, method = 'class', control = control) accuracy_tune(tune_fit)

Output:

## [1] 0.7990431

With the following parameter:

minsplit = 4 minbucket= round(5/3) maxdepth = 3cp=0

You get a higher performance than the previous model. Congratulation!

Summary

We can summarize the functions to train a decision tree algorithm in R

Library Objective Function Class Parameters Details

rpart Train classification tree in R rpart() class formula, df, method

rpart Train regression tree rpart() anova formula, df, method

rpart Plot the trees rpart.plot()

fitted model

base predict predict() class fitted model, type

base predict predict() prob fitted model, type

base predict predict() vector fitted model, type

rpart Control parameters rpart.control()

minsplit Set the minimum number of observations in the node before the algorithm perform a split

minbucket Set the minimum number of observations in the final note i.e. the leaf

maxdepth Set the maximum depth of any node of the final tree. The root node is treated a depth 0

rpart Train model with control parameter rpart()

formula, df, method, control

Note : Train the model on a training data and test the performance on an unseen dataset, i.e. test set.

Aggregate In Excel (Formula, Examples)

AGGREGATE in Excel (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

AGGREGATE in Excel

Aggregate function comprises multiple mathematical functions such as average, sum, max, min, mean, etc., along with the conditions which are customized for each of these sub-functions available under aggregate function. We can even ignore the different cells or rows, such as a blank row, error value, etc., and get the desired output. We have 19 sub-functions available in the Aggregate function with different Options.

Definition:

“Returns the AGGREGATE in a database of values or list”

It means it performs several calculations (19 Excel statistical functions)

AGGREGATE Formula in Excel

An AGGREGATE function has two types of formula

It can be noticed when you type the AGGREGATE function in an Excel cell

1) AGGREGATE in Reference form

2) AGGREGATE in Array Formula

Function_num: It is a number; it can be from 1 to 19. It depends on which specific function you want to use in the below-mentioned list

Here each number represents a function; it is a compulsory argument

1 to 13 are Reference forms, and 14 to 19 are array form

1: AVERAGE

2: COUNT

3: COUNTA

4: MAX

5: MIN

6: PRODUCT

7: STDEV.S

8: STDEV.P

9: SUM

10: VAR.S

11: VAR.P

12: MEDIAN

13: MODE.SNGL

14: LARGE

15: SMALL

16: PERCENTILE.INC

17: QUARTILE.INC

18: PERCENTILE.EXC

19: QUARTILE.EXC

Options: is the number 0 to 7 that specifies which values to ignore for the aggregate function Note: If the options parameter is omitted, by default, options are set to 0

0: Ignore nested SUBTOTAL and AGGREGATE functions

1: Ignore nested SUBTOTAL, AGGREGATE functions, and hidden rows

2: Ignore nested SUBTOTAL, AGGREGATE functions, and error values

3: Ignore nested SUBTOTAL, AGGREGATE functions, hidden rows & error values

4: Ignore nothing

6: Ignore error values

7: Ignore hidden rows and error values

ref1, ref2, ref[3]: It is the first numeric argument for the function when using the REFERENCE syntax. It is values or numeric values on which we want to perform the computation. You must provide at least two arguments, and you can include additional arguments if needed; or the second reference, Numeric arguments should be between 2 to 253 for which you want the aggregate value

Array: An array refers to a range of cells when using the ARRAY syntax

[k]: The last 6 functions (under 1 to 19 function list): k value as a fourth argument

It is an optional argument used if we need to find out LARGE, SMALL, chúng tôi chúng tôi chúng tôi or chúng tôi when using the ARRAY syntax

How to Use the AGGREGATE Function in Excel?

This AGGREGATE function is very simple and easy to use. Let us now see how to use the AGGREGATE function with the help of some examples.

You can download this AGGREGATE Function Excel Template here – AGGREGATE Function Excel Template

Example #1

The following table contains yearly sales data (2024)

If you run the =SUM (B8:B16) function directly in cell B17,

It gives the correct value because that column does not contain hidden rows, errors & nested subtotals

Here will calculate the SUM using an AGGREGATE function in cell B19.

=AGGREGATE(9,4,B8:B16),

The result will be 487.

Function_ num: For the SUM function, the function_ num is 9

Option: In Column B, i.e. For 2024-year sales data, all values are given, and we won’t have to ignore any values; it does not contain hidden rows, errors & nested subtotals. so we will select Option 4 (ignore nothing)

Array: is a range for which you want to calculate aggregate functions. Here reference range of values is B8:B16. It is selected as an array of numeric values

‘k’ is an optional argument, and is used only for a function like LARGE, SMALL, chúng tôi chúng tôi chúng tôi or chúng tôi We are calculating the SUM here to omit the value of k. 

Example #2

The following table contains yearly sales data (2024)

In column C, for the 2024 yearly sales data. In the range C8:C16, a cell C11 & C12 contains an error value (#DIV/0! & #N/A); in the AGGREGATE formula, when an appropriate option is used, the AGGREGATE in Excel gives the correct SUM value, neglecting the error value.

It returns an error value due to an error in that range. Because that column contains #DIV/0! & #N/A errors.

To ignore the error values, we have to use option 6 in an AGGREGATE function

=AGGREGATE(9,6,C8:C16).

The result or output will be 334.

Function_ num: For the SUM function, the function_ num is 9

Option: In Column C, i.e. For 2024-year sales data, In the range C8:C16, a cell C11 & C12 contains an error value (#DIV/0! & #N/A). To ignore these errors, we will select Option 6 (Ignore error values)

Array: It is a range for which you want to calculate aggregate functions. Here reference range of values is C8:C16. The system has chosen it as an array of numeric values.

‘k’ is an optional argument used only for functions like LARGE, SMALL, chúng tôi chúng tôi chúng tôi or chúng tôi We are calculating the SUM here to omit the value of k.

When an appropriate option is used in the AGGREGATE function, the AGGREGATE in Excel returns or gives the SUM of the remaining values neglecting the error value in cells C11 & C12. i.e. 334

Example #3

The following table contains yearly sales data (2024)

In column D, for the 2023 yearly sales data. In the range D8:D16, cell D9 is a blank cell or hidden row & D12 contains an error value (#N/A). In the AGGREGATE formula, when an appropriate option is used, the AGGREGATE in Excel gives the correct SUM value, neglecting the hidden row & error value.

If you run the =SUM (D8:D16) function directly in cell D17,

It returns an #N/A error value due to an error in that range. Because that column contains #N/A error & hidden row or blank value.

=AGGREGATE(9,7,D8:D16),

The result or output will be 262

Function_ num: For the SUM function, the function_ num is 9

Option: In column D, for the 2023 yearly sales data. In the range D8:D16, cell D9 is a blank cell or hidden row & D12 contains an error value (#N/A). To ignore these errors, we will select Option 7 (Ignore hidden rows and error values)

Array: It is a range for which you want to calculate aggregate functions. Here reference range of values is D8:D16. It is selected as an array of numeric values

‘k’ is an optional argument and is used only for a function like LARGE, SMALL, chúng tôi chúng tôi chúng tôi or chúng tôi We are calculating the SUM here to omit the value of k.

when an appropriate option is used in an AGGREGATE function, the AGGREGATE in Excel returns or gives the SUM of the remaining values neglecting the error value in cells D9 & D12. i.e. 262

Things to Remember

The AGGREGATE function is applicable only for vertical ranges or columns of data. It is not designed for horizontal range or rows of data.

It has a limitation; it only ignores the hidden rows; it does not ignore the hidden columns.

An AGGREGATE function is applicable only for the numeric value

Function _ num argument value should not exceed 19 or less than 1. Similarly, for option argument should not be greater than 7; otherwise, it will give #VALUE! error

In the AGGREGATE function, if in function number argument, if you are using 14 to 19 (LARGE, SMALL, chúng tôi chúng tôi chúng tôi or QUARTILE.EXC), the “K” argument should be used. g. =AGGREGATE(15, 6, A1:A9, 3). If the “K” value or second reference argument is ignored, it will result in a #VALUE! error

Recommended Articles

This has been a guide to AGGREGATE Function. Here we discuss the AGGREGATE Formula and how to use the AGGREGATE Function, practical examples, and downloadable Excel templates. You can also go through our other suggested articles –

Highlight Duplicates In Excel (Examples)

Highlight Duplicates in Excel

We can highlight the duplicate values in the selected dataset, whether a column or row of a table, from the Highlight Cells Rule, available in Conditional Formatting under the Home menu tab. To highlight the duplicates, select the data from where we need to highlight the duplicates, then select the Duplicate Values option, which is there under Conditional Formatting. From the box of Duplicate Values, choose Duplicate with the type of color formatting we want. Mainly Red text is selected by default to highlight duplicates. In this article, we will learn about Highlight Duplicates in Excel

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Conditional Formatting – Using Duplicate Values Rule

Conditional Formatting – Using Excel Function or Custom Formula (COUNTIF)

How to Highlight Duplicate Values in Excel?

Highlighting Duplicate Values in Excel is very simple and easy. Let’s understand how to find and highlight duplicate values in Excel using two methods.

You can download this Highlight Duplicates Excel Template here – Highlight Duplicates Excel Template

Conditional Formatting – Duplicate Values Rule

Here we will find the duplicate values in Excel using the conditional formatting feature and highlight those values. Let’s take an example to understand this process.

Example #1

We have given the below dataset.

To highlight the duplicate values in the above dataset, follow the below steps:

Select the entire dataset.

Go to the HOME tab.

It will open a drop-down list of formatting options, as shown below.

It will open a dialog box of Duplicate Values, as shown in the below screenshot.

Select the color from the color palette to highlight the cells.

It will highlight all the duplicate values in the given data set. The result is shown below.

With the highlighted duplicate values, we can take action accordingly.

Conditional Formatting – Using Excel Function or Custom Formula (COUNTIF)

We will use here COUNTIF function. Let’s take an example to understand this method.

Example #2

Let’s again take the same dataset values to find the duplicate values in Excel.

For highlighting the duplicate values here, we will use the COUNTIF function that returns TRUE if a value appears more than once in the list.

The COUNTIF function we will use like shown below:

Follow the below steps to do this.

Select the whole dataset.

It will open a drop-down list of formatting options, as shown below.

It will open a dialog box for creating a new custom rule, as shown below.

Select the last option, “Use a formula determining which cells to format,” under the Select a Rule Type section.

It will display a formula window, as shown below.

This will highlight all the cells having duplicate values in the dataset. The result is shown below:

Things to Remember about Excel Highlight Duplicate Values

Finding and highlighting duplicate values in Excel often comes into use while managing attendance sheets, address directories, or other related documents.

After highlighting duplicate values, if you delete those records, be extra cautious about impacting your entire dataset.

Recommended Articles

This has been a guide to Highlight Duplicates in Excel. Here we discuss how to highlight duplicate values in Excel using practical examples and a downloadable Excel template. You can also go through our other suggested articles –

Price In Excel (Formula, Examples)

PRICE in Excel

The price function in excel is used to calculating what would be the price need to pay off a bond per 100 units (mostly in Dollars) which also pays the periodic interest. The price function is financial in Excel. This is mostly used when an investor borrows money by selling bonds instead of stocks. It requires a few more attributes, usually more than other financial functions such as settlement, maturity, yield, redemption, and frequency as mandatory attributes.

Start Your Free Excel Course

PRICE Formula in Excel:

Below is the PRICE Formula:

Explanation of Price Function in Excel

The PRICE Formula in Excel has 7 segments:

Settlement: This refers to the calendar day on which the deal is settled. The argument passed to this bracket is the date following the date of issue when the security or bond is traded on the market to the entity that is the buyer of said security bond.

Maturity: This bracket accepts a date as a valid argument. It is the calendar day on which the security or bond reaches its expiration, and the principal amount is paid back to a person or entity holding the bond.

Rate: This bracket refers to the annual interest rate of the security or bond at which coupon payments are processed or made.

Redemption: This section refers to the security value; every $100 face value is reimbursed to the bond owner on the redemption date.

Frequency: This bracket refers to the rate of occurrence of coupon payments made every year.

Let us understand the Frequency segment a little more:

The payments can be made Monthly, Annually, Semi-annually, or Quarterly. In these cases, the Frequency would be as follows:

Basis: This bracket is optional and refers to any integer argument which specifies the financial day counting basis.

We shall see the possible values for “Basis” in the table below:

How to Use the PRICE Function in Excel?

You can download this PRICE Function Excel Template here – PRICE Function Excel Template

Example #1

Suppose we are given the following data to calculate the price function in Excel.

The following screenshot shows us how the PRICE Excel function prices a bond.

So the Final Result will be :

Things to Remember

For computation, Excel’s Date format is linear or sequential. That means the default value 1 refers to 1st January 1900, so 2 would ideally be the following day, i.e. 2nd January 1900.

All the variables passed as Settlement, frequency, maturity, and basis value should be valid integers, i.e., floating-point numbers.

If the value passed as maturity or the day of settlement is not a rational date, in that case, the formula of PRICE will result in the #VALUE! error.

If rate < 0 or if Yld < 0 or redemption ≤ 0, then PRICE would return a #NUM! error.

If the value passed as a frequency in the formula of the PRICE function is anything other than 4, 2, or 1, then the PRICE function would return the #NUM! error as a result.

If the basis is greater than 4 or If the basis is less than 0, then the PRICE function will return the #NUM! error.

If maturity value ≤ settlement value, in that case, a #NUM! the PRICE function would return the error.

Thus, it might also be wise to enclose the PRICE function with an IFERROR function, i.e., use the PRICE function inside an IFERROR function to handle the various error cases that might arise in the Frequency, Basis, Settlement Value, etc.

So the result will be :

Recommended Articles

This has been a guide to PRICE in Excel. Here we discuss the Formula in Excel and how to use a PRICE Function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –

Excel Files Opening In Notepad

If you notice that your Excel files are opening in Notepad, then read this post to learn how to fix the issue. The default behavior of a document or file is to open in the application with which it has been created. However, a few users have been experiencing issues opening Excel files on their Windows 11/10 PC. When they try to open such a file (.csv, .xlsx, .xlx, etc.), it opens in Notepad instead of opening in Microsoft Excel.

This happens when the file association has been corrupted or the user does not have Microsoft Excel installed on his computer. This also happens when Excel is not set as the default program for these file types. Under these circumstances, Windows tries to open the file with its own built-in alternatives. These alternatives are not able to read or display the content of the file, and hence leave the users with some gibberish text, which they don’t understand.

Fix Excel files opening in Notepad

If Excel files are opening in Notepad on your Windows 11/10 PC, use these solutions to fix the issue:

Check the file extension.

Use the Open With option.

Choose a default application for Excel file type.

Let us see these in detail.

1] Check the file extension

Check the file extension to make sure the file you’re trying to open is a type of Excel file. Sometimes while renaming files we accidentally mess up with the file name extensions. Taking this into account, if you’ve mistakenly changed csv to css, Windows may use Notepad to read the file.

When you try to open an Excel file, make sure the file’s icon matches the file type. If it doesn’t, enable the File name extension option in File Explorer and check the extension of the file. It should belong to the Excel family and should be something like .xls, .xlsx, .csv, etc.

Press the Win+E keys to open File Explorer.

Navigate to the folder where the file is located.

Check if the extension of the file is spelled correctly and is preceded by a ‘dot’ symbol. If not, rename the file and correct the file extension.

Read: How to Set or Change File Associations & Extensions in Windows 11

2] Use the Open With option

Another attempt you can make to open the file in Excel when Notepad is trying to read it is to use the Open With option. This option allows you to open a file using a desired application.

Read: How to change File Type on Windows 11

3] Choose a default application for Excel file type

Windows allows us to choose a default application to open specific file types. For example, we may choose to open a PDF file in Adobe Acrobat or in Microsoft Edge – it’s up to us. The same applies to Excel file types. If Excel is not set as the default application to open the Excel file types (.xls, .xlm, .cvs, etc.), the file may open in another application.

Press Win+I key combinations to open Windows Settings.

On the Default apps screens, type the extension name of the problematic file in the search bar on top and press the Enter key. The application associated with the file type will show on top.

Select Excel from the list. If you can’t find Excel in the list, add it from the program’s installation directory, as explained above.

Now close the Settings window and try opening the file again. The issue should have been resolved.

Read: How to reset all Apps and File Associations to default in Windows

NOTE: The following file extensions are normally set as the default for Excel in Windows:

csv, dqy, iqy, odc, ods, oqy, rqy, slk, xla, xlam, xlk, xll, xlm, xls, xlsb, xlshtml, xlsm, xlsx, xlt, hlthtml, xltm, xltx, xlw.

TIP: If you find that you cannot open a particular file type, then our File Association Fixer may be able to easily help you fix, repair and restore the broken file associations.

Why are my Excel files opening in Notepad?

Excel comes as a part of the Microsoft Office suite and needs to be installed externally on a Windows PC. If a user has not installed Office or Excel on his computer or has uninstalled it for any reason – or if the file extensions have become corrupted, Windows will read the Excel files using Notepad. This is because all Excel files are essentially text documents and Notepad is Windows built-in text viewer app.

Read: Virus has changed all file extensions

How do I change the default from Notepad to Excel?

Read Next: Excel not opening on Windows computer.

Update the detailed information about Filter Column In Excel (Example) on the Eastwest.edu.vn website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!