The Microsoft Excel is 1 of the best tools to analyze information too I recollect every programmer should locomote familiar amongst this tool. There are a yoke of genuine reasons for why I am bespeak you lot to larn too original Excel. Since most of us piece of job inwards Windows too Excel is almost ever available, knowing how you lot tin exercise it for your information analysis too reporting tin actually brand a difference. Managers, Directors, Clients, too People at a higher score simply honey reports too 1 time your sense grows, your responsibleness also grows too you lot demand to exercise many reports or piece of job on Excel canvass generated past times others. It automatically becomes portion of your life, particularly if you lot are progressing on projection management space.
To locomote honest amongst you lot guys, fifty-fifty though I accept to a greater extent than than 12 years of sense too I showtime u used Excel to a greater extent than than xv years ago, my Excel skills are even therefore wretched for my experience. Barring simply about essential concepts e.g. editing, filtering, sorting, removing duplicates, too simply about essential functions e.g. SUM, VLOOKUP etc, I don't know much.
One of the principal argue of that is I never used Excel similar many others exercise too never realize its importance. I was simply happy that I know Excel but I hardly knew whatsoever advanced concepts, but things accept changed inwards concluding twelvemonth when I took a yoke of courses to improve my Microsoft Skill e.g. Excel 2013 Fundamental Course from PluralSight.
I accept been doing simply about information analysis piece of job lately too I industrial plant life Excel actually useful for analyzing data. For example, suppose you lot got a listing of books sold from your log file too instantly you lot desire to know which majority has sold how many times?
Earlier I used to shop that information inwards a temp tabular array inwards SQL Server too therefore run the GROUP BY query, a lot of piece of job for a uncomplicated thing.
But, every bit I said, things got improved inwards concluding 1 year, I come upwards to know most many Excel functions, particularly afterwards joining another Advanced Excel Formulas too Functions course of study at Udemy, which tin exercise a lot for you lot too 1 of them is COUNTIF.
As the elevate suggests, this business office is a combination of COUNT too IF, which way it tin count based upon a given condition.
As per Microsoft Excel documentation, COUNTIF(range, criterion) counts the publish of cells inwards a given make which matches the given condition. There is also a COUNTIFS() business office which is similar to COUNTIF() but allows you lot to specify multiple conditions.
List of books
Effective Java
Head First Java
Cracking the Coding Interview
Algorithms
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Head First Design Pattern
Head First Design Pattern
Effective Java
Head First Java
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Algorithms
Head First Design Pattern
Head First Design Pattern
Cracking the Coding Interview
Cracking the Coding Interview
This volition withdraw all the duplicates on that column too you lot volition accept a listing of unique values every bit shown below:
This is the easiest way to withdraw duplicate values from a listing using Microsoft Excel but this functionality is exclusively available from Microsoft Excel 2013. This is also 1 of my favorite Excel tip which I learned from Advanced Excel: Top Excel Tips too Formulas course of study from Udemy.
=COUNTIF(A1:A35,D1)
This business office tells Microsoft Excel that search inwards the make from A1 to A35 for the value acquaint inwards D. I accept given D1 because my unique values are inwards column D too they start from the showtime row because at that spot is no header.
If your Excel canvass has a header too therefore your formula should tell D2 because that's your showtime value.
Btw, You don't demand to type the make simply type the business office too and therefore conduct the starting jail mobile telephone too drag till the concluding jail mobile telephone inwards the column you lot accept pasted the listing of values.
For populating criterion, simply select the value inwards the listing without duplicate. Once it is done for 1 jail mobile telephone you lot tin simply drag it to apply the same formula for all the cells every bit shown below.
Btw, if you lot were to exercise this on simply about other canvass too therefore you lot demand to include the canvass elevate every bit good inwards your formula.
If you lot await at it closely, this is what GROUP BY does inwards SQL. You give a listing of values too and therefore it divides them into the grouping too tells you lot the count of each value.
That's all most how to grouping a listing of values inwards Excel too industrial plant life their count. It's rattling useful tips land generating reports too I accept industrial plant life it using quite often. For example, you lot tin exercise this fob to generate a sales study to discovery how many times each particular is sold. You tin too therefore discovery the best seller or make 10 courses books etc. Btw, I even therefore think, I don't know much of Excel but afterwards realizing its importance inwards my career, I am also taking Excel fundamentals course to improve my Excel skills. you lot tin bring together too.
Other Useful Tips too Resources for Programmers
To locomote honest amongst you lot guys, fifty-fifty though I accept to a greater extent than than 12 years of sense too I showtime u used Excel to a greater extent than than xv years ago, my Excel skills are even therefore wretched for my experience. Barring simply about essential concepts e.g. editing, filtering, sorting, removing duplicates, too simply about essential functions e.g. SUM, VLOOKUP etc, I don't know much.
One of the principal argue of that is I never used Excel similar many others exercise too never realize its importance. I was simply happy that I know Excel but I hardly knew whatsoever advanced concepts, but things accept changed inwards concluding twelvemonth when I took a yoke of courses to improve my Microsoft Skill e.g. Excel 2013 Fundamental Course from PluralSight.
I accept been doing simply about information analysis piece of job lately too I industrial plant life Excel actually useful for analyzing data. For example, suppose you lot got a listing of books sold from your log file too instantly you lot desire to know which majority has sold how many times?
Earlier I used to shop that information inwards a temp tabular array inwards SQL Server too therefore run the GROUP BY query, a lot of piece of job for a uncomplicated thing.
But, every bit I said, things got improved inwards concluding 1 year, I come upwards to know most many Excel functions, particularly afterwards joining another Advanced Excel Formulas too Functions course of study at Udemy, which tin exercise a lot for you lot too 1 of them is COUNTIF.
As the elevate suggests, this business office is a combination of COUNT too IF, which way it tin count based upon a given condition.
As per Microsoft Excel documentation, COUNTIF(range, criterion) counts the publish of cells inwards a given make which matches the given condition. There is also a COUNTIFS() business office which is similar to COUNTIF() but allows you lot to specify multiple conditions.
Microsoft Excel - COINTIF Function Example
In this article, I volition percentage a tip which I accept been using regularly. I accept a listing of values amongst me e.g. customer name, books names, or particular names too I demand to discovery out the count of each particular inwards the list. It's similar GROUP BY inwards SQL too I'll present you lot how to exercise that inwards Excel using COUNTIF function. That's why I accept also seat the title, how to exercise a grouping past times inwards Excel.Step 1: Copy List of values into Excel
Let's glue the listing of books nosotros industrial plant life inwards a column inwards our Excel canvass e.g. re-create them into the showtime column every bit shown below.List of books
Effective Java
Head First Java
Cracking the Coding Interview
Algorithms
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Algorithms
Head First Design Pattern
Head First Design Pattern
Effective Java
Head First Java
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Head First Design Pattern
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Cracking the Coding Interview
Algorithms
Head First Design Pattern
Head First Design Pattern
Cracking the Coding Interview
Cracking the Coding Interview
Step 2: Remove Duplicates
In club to display the majority too their count, nosotros showtime demand to withdraw duplicates. For that simply re-create the listing of books into simply about other column too click on withdraw duplicates every bit shown inwards the next screenshot.
This volition withdraw all the duplicates on that column too you lot volition accept a listing of unique values every bit shown below:
This is the easiest way to withdraw duplicate values from a listing using Microsoft Excel but this functionality is exclusively available from Microsoft Excel 2013. This is also 1 of my favorite Excel tip which I learned from Advanced Excel: Top Excel Tips too Formulas course of study from Udemy.
Step 3: Create Formula using COUNTIF function
Once you lot did that, simply add together a formula inwards the adjacent column to present their count using COUNTIF business office every bit shown below:=COUNTIF(A1:A35,D1)
This business office tells Microsoft Excel that search inwards the make from A1 to A35 for the value acquaint inwards D. I accept given D1 because my unique values are inwards column D too they start from the showtime row because at that spot is no header.
If your Excel canvass has a header too therefore your formula should tell D2 because that's your showtime value.
Btw, You don't demand to type the make simply type the business office too and therefore conduct the starting jail mobile telephone too drag till the concluding jail mobile telephone inwards the column you lot accept pasted the listing of values.
For populating criterion, simply select the value inwards the listing without duplicate. Once it is done for 1 jail mobile telephone you lot tin simply drag it to apply the same formula for all the cells every bit shown below.
Btw, if you lot were to exercise this on simply about other canvass too therefore you lot demand to include the canvass elevate every bit good inwards your formula.
If you lot await at it closely, this is what GROUP BY does inwards SQL. You give a listing of values too and therefore it divides them into the grouping too tells you lot the count of each value.
That's all most how to grouping a listing of values inwards Excel too industrial plant life their count. It's rattling useful tips land generating reports too I accept industrial plant life it using quite often. For example, you lot tin exercise this fob to generate a sales study to discovery how many times each particular is sold. You tin too therefore discovery the best seller or make 10 courses books etc. Btw, I even therefore think, I don't know much of Excel but afterwards realizing its importance inwards my career, I am also taking Excel fundamentals course to improve my Excel skills. you lot tin bring together too.
Other Useful Tips too Resources for Programmers
- Microsoft Excel - Advanced Excel Formulas too Functions (see)
- How to compare 2 lists of values inwards Excel? (tip)
- How to read/write Excel files inwards Java program? (see)
- How to enclose a listing of values into unmarried quotes inwards Excel? (tip)
- How to charge information from a CSV file inwards Java? (program)
- Microsoft Excel 2016 Bible past times John Walkenbach (book)
- 10 tips to acquire a ameliorate programmer (tips)
- Microsoft Excel - Excel from Beginner to Advanced (see)