HOW TO USE COUNTIF IN EXCEL

HOW TO USE COUNTIF IN EXCEL

Excel COUNTIF function is best & so simple to use for situations when you want to count cells, based on a single condition. COUNTIF Function can be described as a formula that can be used for counting the number of cells that fulfill a particular condition, within a predefined range.

How to use COUNTIF in excel. Microsoft Excel defines COUNTIF as a formula that, “Counts the number of cells within a range that meet the given condition”.

This definition clearly explains that: COUNTIF Function is a better and sophisticated type of COUNT formula that gives you the control over, which cells you wish to count.

Syntax

=COUNTIF(range, criteria)

Arguments :-

  • range –  the range of cells which you want to count.

  • criteria –the criteria that must be evaluated against the range of cells for a cell to be counted.

countif1

This formula counts those cell which values are greater then 100.

The formula is  =COUNTIF(D2:D11,”>100″)

The result is 4. Because D2 to D11 four cells meet the condition which values are greater then 100. Hence this formula shows 4 in D12.

 

COUNT CHARACTER VALUE WITH COUNTIF FUNCTION


It’s possible and so easy to count character values in excel with COUNTIF function. The COUNTIF function is not case sensitive.

countif2

Here a simple COUNTIF formula which calculate the specific word or character in given range.

The formula is =COUNTIF(D3:D11,”MANAGER”)

 

The result is 2. Because D3 to D11 two cells meet the condition which values are manager. Hence this formula shows 2 in D11.

HOW TO USE MULTIPLE COUNTIF 


If you want to count multiple words from a column or row then you will have to use multiple COUNTIF function with different conditions.

The syntax is same :-

=COUNTIF(range,”criteria”)+COUNTIF(range,”criteria”)+COUNTIF(range,”criteria”)

=COUNTIF(C4:C12,”H.R”)+COUNTIF(C4:C12,”I.T”)+COUNTIF(C4:C12,”QUALITY”)

 

multicountif

The formula shows the result is 7. Because cell C4 to C12 the word “H.R” comes three times, “I.T” also comes three times and one time is “QUALITY”. Therefore 3+3+1=7

 

HOW TO USE COUNTIFS IN EXCEL


The COUNTIFS function Count cells that match multiple criteria.If you want to count based on multiple criteria, use COUNTIFS function.

Syntax

=COUNTIFS(range1, criteria1, [range2], [criteria2], …..)

Arguments :-

  • range –  the range of cells which you want to count.

  • criteria –the criteria that must be evaluated against the range of cells for a cell to be counted.

    countifs

=COUNTIFS(B3:B11,”ANKIT”,C3:C11,”I.T”)

The result is 2. Because the condition of formula met two times.

SHORTCUT KEYS IN EXCEL

SHORTCUT KEYS IN EXCEL

Shortcut keys excel is most useful for a user because shortcut keys save your working time and make your task much efficient. This tutorial tells you about some important shortcut keys of excel .

Formatting Text In Excel

Description

  • Ctrl + B= Bold toggle for selection

  • Ctrl + I= Italic toggle for selection

  • Ctrl + U= Underline toggle for selection

  • Ctrl + 5= Strike-through for selection

  • Ctrl + Shift + F= Change the font

  • Ctrl + Shift + P= Change the font size

  • Ctrl + Shift + 7= Apply outline borders

  • Alt + Enter= Wrap text in same cell

 

Description

  • Ctrl + N New file

  • Open file Ctrl + O

  • Ctrl + S Save file

  • Move between open workbooks Ctrl + F 6

  • Ctrl +  F 4 Close file

  • F 12 Save as

  • Ctrl + P Display the print menu

  • Ctrl + A Select whole spreadsheet

  • Ctrl + Space Select column

  • Shift + Space Select row

  • Ctrl + Z Undo last action

  • Ctrl + Y Redo last action

  • Alt + F 4 Exit Excel

    Navigating

  • Description

  • Tab= Move to next cell in row

  • Shift + Tab= Move to previous cell in row

  • Page Up= Up one screen

  • Page Down= Down one screen

  • Ctrl + Page Down= Move to next worksheet

  • Ctrl + Page Up= Move to previous worksheet

  • Ctrl + Home= Go to first cell in data region

  • Ctrl + End= Go to last cell in data region

    Formatting Cells

  • Description 

  • Ctrl + 1= Format cells

  • Ctrl + Shift + F= Select font

  • Ctrl + Shift + P= Select point size

  • Ctrl + Shift + 4= Format as currency

  • Ctrl + Shift + 5= Format as percentage

  • Ctrl + Shift + 1= Format as number

    Editing/Deleting Text

  • Description 

  • Delete one character to right = Delete

  • Delete one character to left = Backspace

  • Edit active cell = F 2

  • Cancel cell entry = Escape Key

  • Highlighting Cells

  • Description

  • Select entire worksheet = Ctrl + A

  • Select entire row = Shift + Space-bar

  • Select entire column = Ctrl + Space-bar

  • Manual select = Hold Shift + with Left, Right, Up, Down Arrow

Key  Display & Printing

Discription

Alt + W + F + F=Freeze Panes

Alt + W + Q=Zoom

Ctrl + Mouse Scroll Wheel=Zoom

Alt + P + S + P=Page Setup

Alt + P + R + S=Set Print Range to Selected Area

Ctrl + F 2=Print Preview

Alt + W + I=Page Break View

Alt + W + L=Normal View

Alt + W + VG=Toggle Grid-lines

  • Inserting Text Automatically

  • Description 

  • Auto sum a range of cells = Alt + “=” Equals Sign

  • Insert the date = Ctrl + ; (semi-colon)

  • Insert the time = Ctrl + Shift + ; (semi-colon)

  • Insert columns/rows = Ctrl + Shift + + (plus sign)

  • Insert a new worksheet = Shift + F 11

  • Misc

  • Description 

  • SHORTCUT KEYS IN EXCEL

  • Find text = Ctrl + F

  • Replace text dialog = Ctrl + H

  • Create a chart automatically on new sheet = F 11

  • Edit a cell comment = Shift + F 2

  • Move to Next Sheet = Ctrl + Page Down

  • Move to Prior Sheet = Ctrl + Page Up

  • Go to a cell command = Ctrl + G

  • Auto Sum= Alt + Equals(=)

  • Formula Mode = Equals Sign

  • Insert Date In Current Cell = Ctrl + ; (semicolon)

  • Insert Time In Current Cell = Ctrl + Shift + ; (semicolon)

  • Spell Check = F 7

  • Tab Backwards = Shift + Tab

  • Auto Filter Ctrl + Shift + A

  • Cell Hyperlink Alt + Shift + H

Filtering, Sorting & Validation

Shortcut keys in excel

Alt + A + SS=Sort Data

Alt + A + SA=Sort Ascending

Alt + A + SD=Sort Descending

Ctrl + Shift + L=Filter Data

Alt + A + Q=Advanced Data Filter

Right Mouse Button + E + V=Filter by Cell’s Properties

Alt + A + M=Remove Duplicates

Format Menu

SHORTCUT KEYS IN EXCEL

Ctrl + 1=Format Dialog

Ctrl + Alt + V=Paste Special

Ctrl + Alt + V + T=Paste Formats

Ctrl + Alt + V + V=Paste Values

Ctrl + Alt + V + F=Paste Formulas

Alt + H + FC=Font Color

Alt + H + H=Fill Color

Alt + H + B=Border Options

Alt + H + A + L / C / R=Align Left / Center/ Right

Alt + H + 6=Increase Indent

Alt + H + 5=Decrease Indent

Alt + H + 0=Increase Decimal Places

Alt + H + 9=Decrease Decimal Places

Ctrl + B=Bold

Ctrl + I=Italics

Ctrl + U=Underline

Ctrl + 5=Strike-through

Ctrl + Shift + &=Add Borders

Ctrl + Shift + –=Delete Borders

Shift + Ctrl + ~=General

Shift + Ctrl + !=Number

Shift + Ctrl + @=Time

Shift + Ctrl + #=Date

Shift + Ctrl + $=Currency

Shift + Ctrl + %=Percentage

Shift + Ctrl + ^=Scientific

=TEXT(Cell, Format)=Displays cell using custom format

Alt + H + O + I=Auto-Fit Col.

Alt + H + O + A=Auto-Fit Row

Alt + H + O + W=Column Width

Alt + H + O + H=Row Height

Alt + H + L + R=Conditional Formatting

Alt + H + T=Format as Table

 

 

HOW TO LEARN EXCEL

HOW TO LEARN EXCEL

Excel is a most powerful spreadsheet and data analysis application
These days, every job requires advance Excel skills. An organization wants these Excel skills –creating tables, format data, manipulate basic essential operations like(sum, subtraction, multiplication, division, average & summaries with filters and formulas, highlight data with conditional formatting, validation, charting , advance formulas and much more another thing which you will learn from here.

HOW TO BECOME AN EXCEL EXPERT
Excel is a huge application in itself with thousand of features and formulas.Formulas is back bone the ms-excel.
To become a good excel operator, very firstly you’ll have to learn about basic commands and formulas of excel.

http://www.dreamstime.com/royalty-free-stock-photo-3d-learn-here-crossword-image21000275

BASICS OF EXCEL :-


What is a workbook
what is a sheet
what is a cell
what is cell address

Create a new blank workbook

Explore the Excel interface
Move within worksheet cells
Enter data
Select and format data
Copy and paste formatting and data
Edit data
Insert and resize columns
Save a workbook in a new folder
How to cut,copy & paste commands works
How to find the specific name/text

How to protect file or sheet: Encrypt an Excel file with a password so that it requires a password to open it.
How to hide or unhide columns/rows

 

Keyboard Shortcuts: Keyboard shortcuts allow you to do things with your keyboard instead of your mouse to increase your speed.
shortcut commands of excel like ( ctrl +c, ctrl+v, ctrl+x, ctrl+z, ctrl+y, ctrl+s etc)

Other shortcut keys in excel

The basic formulas of excel is here :-
1- How to insert or delete a column/row
2- How to add two numbers in excel
3- How to subtraction/ division/multiplication in excel

4- How to apply conditional formatting 

5- How to use filters

6- How to use VLOOKUP function 

7- How to use multiple IF statement

8-How to use AND

 

EXCEL CONDITIONAL FORMATTING

Excel conditional formatting


Excel conditional formatting is a really powerful feature when it comes to applying different formats to data that meets certain conditions. It can help you highlight the most important information in your spreadsheets and identify variances of cells’ values with a quick glance.

At the same time, Conditional Formatting is often deemed as one of the most intricate and obscure Excel functions, especially by beginners. If you feel intimidated by this feature too, please don’t! In fact, conditional formatting in Excel is very straightforward and easy to use, and you will make sure of this in just 5 minutes when you have finished reading this short tutorial.

The basics of conditional formatting

Cells, rows, or columns can be  formatted to change text, icon, font color or background color if they meet certain conditions. For example, if they contain a certain word, number or a value.

Open an excel spreadsheet.
Select the cells you want to apply format rules.
Click Format and then Conditional formatting. A toolbar will open to the right.
Create a rule.
Single color: Under “Format cells if,” choose the condition that you want to trigger the rule. Under “Formatting color, choose what the cell color or font color look like if condition is true.
Color scale: Under “Preview,” select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow Down Arrow.
Click Done.

The same as usual cell formats, you use conditional formatting in Excel to format your data in different ways by changing cells’ fill color, font color and border styles. The difference is that conditional formatting is more flexible, it allows you to format only the data that meets certain conditions.

You can apply excel conditional formatting to one or several cells, rows, columns or the entire table based on the cell contents or based on another cell’s value. You do this by creating rules (conditions) where you define when and how the selected cells should be formatted.

greaterthan-cndi

grtrtthan

STEPS .

  1. CLICK ON THE HOME TAB.

  2. CHOOSE CONDITIONAL FORMATTING

  3. HIGHLIGHT CELL RULES

  4. THAN CLICK ON GREATER THAN

grtrtthan

5. CLICK ON THE DROPDOWN LIST.

6. CLICK ON CUSTOM FORMAT.

7.CHOOSE A CUSTOM COLOR.

grtr3

Excel conditional formatting