Tuesday, January 21, 2020

HIGHLIGHT DUPLICATE VALUE IN EXCEL


In this lesson, we will teach you how to find and highlight duplicate data in excel worksheet.
May be duplicate data can be useful in sometime but often duplicate data makes harder to understand and sometimes create confusion to the readers of those data and bring unclear meaning.
But, in MS-Excel, you can easily find and highlight duplicate data of your worksheet with some simple steps.
By using, conditional formatting (one of Excels most useful function) to find and highlight duplicate data. That’s the way, you can review the duplicates and decide if you want to remove them.

The task is very simple. Just follow the below steps and at last, we use some demo data and screenshots so that you can clearly understand the thing. Have a look at those images and practice with those sample data or your own data to master the topic.

Without furthermore delay, let’s proceed, 

At first, select the data you assigned.

select your data in the worksheet



Go to the Home tab, click on the Conditional Formatting option.






Highlight Cell Rules Go to this option (as shown in the image) and click on the 'Duplicate Values' option from here.



The default screen will come infront of you, 



but, you can change styles from the below drop-down box.




and, finally you can get the below image by selecting 'Red Text' with Duplicate data 

exist in the worksheet.




Isn’t it very much easy to find and highlight duplicate data in your Excel worksheet! So, for more easy MS-Excel tutorial stay with tutorialabc.com and best of luck to those enthusiasts who are keen to learn easy and useful things from us.

Saturday, January 4, 2020

How to use COUNT Function in MS-Excel


Nowadays, every business relies on some spreadsheet program and MS-Excel is one of the major spreadsheets program.

If you are following our tutorial and instructions, then no need to introduce you to MS-Excel again. Today, we are going to learn a simple yet very useful topic in Excel. Though it’s very easy to learn but need some practice over and over to get used to it. We will instruct you in this tutorial, how to use Count Function and some variants of Count Function.

For this tutorial, we will use some functions in a writing form and some own created excel sheet and sample data and also some screenshot for learning purposes.

We will perform the Operations in a very simple manner because we generally consider that our audience and learners are the beginners in MS-Excel. So, we give them thorough instructions but in a short way.

Without furthermore delay, let’s jump to our today’s topic, i.e. – “HOW TO USE COUNT FUNCTION IN MS-EXCEL”.

By definition, Count Function counts the number of cells containing numbers in the cells. A question arises in your mind, what sort of things you would like to count? Well, the answer will be, you can count any numeric value including the number of cells by selecting an item, using cell reference or defining the range of cells as arguments passed in the Function.

One important thing to remember, Count Function can count all numeric values including negative values, dates, time and so on but only empty cells and text values cannot be counted by Count Function.

But, there is also built-in CountA Function available in excel which counts the number of cells containing numbers as well as text.

The main difference between them is that Count Function only counts cells containing numbers but CountA counts all cells that aren’t empty. That means CountA generally count anything.

Again, keep in mind that, Count Function only count numeric values while CountA can count numeric values as well as texts and characters.

Now, you may think, in which situation, you need to use Count Function! Maybe, you are working with an excel sheet with a list of invited people for a meeting and you need to count the number of people who attend the meeting. Or, suppose you need to find out the total number of employee in an office. By using a Count Function in Excel you can very easily & precisely count the number of employee of that office.

Basic syntax of COUNT & COUNTA FUNCTION

=COUNT(value1,[value2],…)
                   and
=COUNTA(value1,[value2],…)

Now, let’s take a look on the below screenshot of our excel sheet which consists of very few amounts of sample data so that you can easily cope with the basics of Count Function.



In the image, you can see that, you want to count the number of employee by EMP_ID and by looking at the presented data which is ID’s starting from Cell A2 (211) to Cell A10 (121) we can say that, there is only 9 employee available. 

But, in Excel we will write a simple Count Function and pass some arguments and it will automatically count the number of employee. In the next image, you will see the Count Function using for our excel sheet to count the number of employee by using EMP_ID. (Follow the blue marked arrows for the clarification)


Try to practice with your own data and CountA Function also work in a same manner. You can also see a Function named COUNTIF and this function also work as like as other two functions but for COUNTIF Function we need to pass the arguments – the range and some comparable reference as a criteria. We will elaborately give you a lesson of COUNTIF in some other tutorial as soon as possible. We will not extend our today’s topic anymore and hope you guys got a little idea about how a Count and CountA Function work in MS-Excel. So, we suggest, keep practicing with more and more data and find differences between your results & observe closely. Soon you will understand the functions completely and encourage others to learn these basic things.

If you have doubt or you do not understand a thing of this tutorial, then ask us via comment or contact us via our CONTACT page, we will get back to you with your answers. Thank you for your support and stay with tutorialabc for better content. 


Feel free to share with your friends and family and give your valuable suggestions so that we can improve our service.


Friday, November 22, 2019

How to create a sample student report card in MS-Excel


Well, today we are going to learn how someone can create a sample student report card in MS-Excel. We will use some screenshot of our own generated demo report card and its looks very simple yet easy to make one.
So, without wasting valuable time of everyone’s, let’s proceed to the point right now.

Prerequisite

To create a student report card, you should have a clear understanding of different types of mathematical expressions (conditions……if-else) and should know how to perform some basic level mathematical operations, like- Addition, Average and so on. That’s set, isn’t it looks simple! Well, actually it is. So, let’s start,

At first, we are giving a screenshot of our completed Student Report Card & here it is,



So, we hope, you get the idea how your student report card will look like. (Take a closer look at the above screenshot please)

Create the basic layout of the report card like the title of your desired school name, the number of field required for the card, input values including Student_ID, Student_Name, Subjects & Marks etc.

For the demo, create this below layout first,



Here, you can see we have already input some demo values for our first six columns (from left to right) and now we have to calculate Total marks of those subjects entitled as English, Science, Math an ICT. So, how we are going to do that? Well, it’s just a simple addition operation.

In the Total column, we will take our mouse cursor and click the G6 cell first. Then, start typing “ = ” in that cell and start clicking from left side cell of subject values, for our case English is most left sided subject and has a value of 87 which is in the cell C6 then use the addition symbol (+) and select the next cell of the subject i.e. for our case Science and has a value of 77 which is in cell number D6 and similarly just select other cells of the subject values and just press the Enter button of your keyboard. 

You will get the Total marks of those subjects based on your input values. Or, you could just type the below formula in the G6 cell.



Now, just put your mouse cursor below in the G6 cell and you will notice a black + symbol there, just drag your mouse from the next cell G7 to the last cell of that table G11 and you will get Total marks of all other students in the table.

Now, it time to calculate the Average of marks of those individual students. We definitely know how to calculate average from some values, right!

Well, in mathematics, the formula of average,

Average =Addition of all values/ number of inputs

For our case, we can see, there are four (4) subjects and we already calculated the Total marks by performing addition of subject-wise values and we put the value in a new column titled as Total. So, it will save our time and effort to calculate the Average marks of students.


So, from above screenshot, it is easy to calculate Average marks of Students. For example, Average Marks of Student A = Total/4 (Here is only four subject enlisted) and same formula will work on other Students Average Marks Calculation.

From our report card, we will use this simple formula ( =G6/4 ) for calculating the Average marks of Student Name, A and for other Students we will just drag the ‘+’ symbol from the below of the H6 cell to the following cells.

Now, give your full attention, because in this part, we will learn how to calculate the Grades of those Students and for that we will use a little bit difficult formula.

For calculating Grades of the Students, at first simply we will use this formula, =IF(H6>=80,"A+",IF(H6>=70,"A",IF(H6>=60,"B",IF(H6>=50,"C",IF(H6>=40,"D",IF(H6<40,"F")))))) to find out the Grade of Student A.

Then, for other Students Grades we will just drag the ‘+’ symbol from the below of the I6 cell to the following cells.


And, for the last field of our report card, we will Comment out the Grades (it could be Outstanding, Good, Satisfactory based on performance) and we will do it in a manual way. That’s all for our demo Student Report Card for Beginners and hopefully, everyone will clearly understand the full process of generating a simple Student Report Card in MS-Excel in any version.

The final look of our created Student Report Card,


Thanks for your support. Have a good day & stay with tutorialabc.com for more lessons and learning contents.