Category: Uncategorized
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.
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.
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”)
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(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= Strikethrough 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 + Spacebar

Select entire column = Ctrl + Spacebar

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 Gridlines

Inserting Text Automatically

Description

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

Insert the date = Ctrl + ; (semicolon)

Insert the time = Ctrl + Shift + ; (semicolon)

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=Strikethrough
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=AutoFit Col.
Alt + H + O + A=AutoFit 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 msexcel.
To become a good excel operator, very firstly you’ll have to learn about basic commands and formulas of excel.
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
8How 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.
STEPS .

CLICK ON THE HOME TAB.

CHOOSE CONDITIONAL FORMATTING

HIGHLIGHT CELL RULES

THAN CLICK ON GREATER THAN