General Shortcuts:
Select whole spreadsheet Ctrl + A
Go to
data tab Alt + A
Bold Ctrl + B
Copy Ctrl+ C
Fill the above cell data Ctrl
+ D
Open find and replace options Ctrl + F
Find Ctrl + F
Open go-to options Ctrl + G
Open find and replace options Ctrl + H
Go to home tab Alt + H
Italic Ctrl + I
Insert link Ctrl
+ K
Go to formula tab Alt + M
New file Ctrl + N
insert tab Alt + N
Open file Ctrl + O
Display the print menu Ctrl + P
Go to page layout tab Alt +
P
Save file Ctrl + S
Underline Ctrl + U
Paste Ctrl
+ V
Close an excel sheet Ctrl + W
Go to View tab Alt + W
Cut Ctrl + X
Redo last action Ctrl + Y
Undo last action Ctrl + Z
Select column Ctrl + Space
Select row Shift + Space
Shortcuts :
Add data from link copy link, go to insert And paste link in website.
Special Shortcuts:
Autofit data Ctrl-A, Alt H O A(rows), Alt H O I(columns)
Delete blank rows Ctrl G, select ( Special, Blanks, Ctrl –, shift cells up )
Delete blank columns Ctrl G, select ( Special, Blanks, Ctrl –, shift cells left )
Select particular row/column to add or remove and follow below :
i) Add rows Ctrl + +
ii) Remove rows Ctrl + –
iii) Add columns Ctrl + +
iv) Remove columns Ctrl + –
Combine 2
columns =concatenate(B2,” “,C2)
Unhide
rows Ctrl-A , Ctrl + Shift + 9
Sequence Sequence(100)
Remove cell contents
Delete
Fill a color Alt + H, H
Centre align cell
contents Alt
+ H , A , C
Add borders Alt + H, B, A
Delete column Alt + H, D,
C
Hide the selected rows Ctrl
+ (9
Hide the selected columns Ctrl
+ )0
Enter the current time Ctrl + Shift + ;
Enter the current date Ctrl + ;
Save as F12
Create a chart automatically on new
sheet F11
Insert a new worksheet Shift + F11
Create chart F11
Maximize currently selected window Ctrl + F10
Minimize current window Ctrl + F9
Resize workbook window Ctrl + F8
Move workbook window Ctrl + F7
Spell check selected text and/or document F7
Switch between open workbooks/windows Ctrl + F6
Restore
down workbook window Ctrl + F5
Bring up the search box Shift + F5
Go to a specific cell F5
Strikethrough highlighted selection Ctrl + 5
Close file Ctrl + F4
Find previous match Ctrl +
Shift + F4
Find next match Shift F4
Repeat the last action F4
Open the Excel formula window Shift + F3
Edit a cell comment Shift + F2
Edit the selected cell F2
Insert new worksheet Alt + Shift + F1
Move between Excel worksheets in the same document Ctrl + Page up &
Page Down
Move between two or more open Excel files Ctrl + Tab
Create the formula to sum all of the above cells Alt + =
Insert the value of the above cell into the current cell Ctrl +
Format number in comma format Ctrl + Shift +!
Format number in currency format Ctrl + Shift + $
Format number in date format Ctrl + Shift + #
Format number in percentage format Ctrl + Shift + %
Format number in scientific format Ctrl + Shift + ^
Format number in time format Ctrl + Shift + @
Move to next section of text Ctrl + (Right arrow)
Select entire column Ctrl + Space
Select entire row Shift
+ Space
Separate
combined letter and number Enter 1st row in seperated forms and Ctrl E
Create naming lit TEXTJOIN(“,”,,1st cell)
Navigating
Shortcuts:
Move to next cell in row Tab
Move to the previous cell in row Shift + Tab
Up to one screen Page Up
Down one screen Page Down
Move to next worksheet Ctrl + Page Down
Move to previous worksheet Ctrl + Page Up
Go to the first cell in data region Ctrl + Home
Go to the last cell in data region Ctrl + End
Text formatting Shortcuts:
Strikethrough Ctrl + 5
Change the font Ctrl + Shift + F
Change the font size Ctrl + Shift + P
Apply outline borders Ctrl + Shift + 7
Remove all borders Ctrl + Shift +
Underline
Wrap text in same cell Alt + Enter
Cell formatting Shortcuts:
Format cells Ctrl + 1
Select font Ctrl + Shift + F
Select point size Ctrl + Shift + P
Format as Number Ctrl + Shift + 1
Format as Time Ctrl + Shift + 2
Format as Date Ctrl + Shift + 3
Format as currency Ctrl + Shift + 4
Format as percentage Ctrl + Shift + 5
Highlighting Cells Shortcuts:
Select entire row Shift + Spacebar
Select entire column Ctrl + Spacebar
Manual select Hold Shift + with Left,
Right, Up, Down Arrow Key
Inserting Text Automatically
Shortcuts:
Autosum 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)
Formulas:
SUM :
=SUM(CELL1,CELL2,......)
EXAMPLE :
=SUM(B2:G2) –> A simple selection that sums the values of a row.
=SUM(A2:A9) – >A simple selection that sums the values of a column.
=SUM(A2:A6, A8, A11:A14) –> It sums the values from range A2 to A6, skips A7, adds A8, skips A9 and A10, then finally adds from A11 to A14.
AVERAGE :
=AVERAGE(CELL1, CELL2,......)
EXAMPLE :
=AVERAGE(B2:B12) –> Shows a simple average, also similar to (SUM(B2:B12)/10)
COUNT :
=COUNT(CELL1,CELL2,......)
EXAMPLE :
=COUNT(A:A) –> Counts all values that are numerical in A column. However, you must adjust the range inside the formula to count rows.
=COUNT(A1:C1) –> Now it can count rows.
COUNTA:
=COUNTA(CELL1,CELL2,......)
EXAMPLE :
COUNTA(C2:C13) –> Counts rows 2 to 13 in column C regardless of type. However, like COUNT, you can’t use the same formula to count rows. You must make an adjustment to the selection inside the brackets – for example, COUNTA(C2:H2) will count columns C to H.
IF:
=(logical_test, [value_if_true], [value_if_false])
EXAMPLE :
=IF(C3<D4, ‘TRUE’, ‘FALSE’) –> Checks if the value at C3 is less than the value at D4. If the logic is true, let the cell value be TRUE, else, FALSE
=IF(SUM(C2:C8) > SUM(D2:D8), SUM(C2:C8), SUM(D2:D8)) –> An example of a complex IF logic. First, it sums C2 to C8 and D2 to D8, then it compares the sum. If the sum of C2 to C8 is greater than the sum of D2 to D8, then it makes the value of a cell equal to the sum of C2 to C8. Otherwise, it makes it the SUM of C2 to C8.
AVERAGEIF: This formula returns the average of all numbers in a range of cells, on the base of given criteria in the parameter.
COUNTIF: This formula returns the number of cells in a range on the base of given criteria in the parameter.
COUNTIFS: This formula returns the number of cells in a range based on multiple criteria in the parameter.
SUMIF: This formula returns the total numbers in a range of cells on the base of given criteria in the parameter.
SUMIFS: This formula returns the total numbers in a range of cells based on multiple criteria in the parameter.
SUMPRODUCT: This formula sum multiplies the corresponding items in the arrays.
TRIM:
=TRIM(CELL)
EXAMPLE :
=TRIM(A3) –> Removes empty spaces in the value in cell A3.
MAX & MIN:
=MIN(CELL1, CELL2,......)
=MAX(CELL1, CELL2,......)
EXAMPLE :
=MIN(B2:B10) –> Finds the minimum number between B2 and B10.
=MAX(B2:B10) –> Finds the maximum number between B2 and B10.
VLOOKUP: VLOOKUP is used to get the
data for a lookup value in the leftmost column of a section of your spreadsheet
called the table array on the base index_number (column number).
HLOOKUP: HLOOKUP is used to get the
data for a lookup value in the topmost row of a section of your spreadsheet
called the table array on the base index_number (row number).
MATCH: This formula searches for a
value in an array and returns the relative position of that item
INDEX: This formula gets a value from
a table on the base of a given row and column parameter.
INDIRECT: This formula returns the
reference to a cell or range on the base of its string parameter.
OFFSET: This formula returns a
reference to a range that is offset by the number of rows and columns.
CHOOSE: This formula gets a value
from an array list based on a given parameter.
ADDRESS: This formula returns a cell
address in text format.
String/Text Functions
FIND: This formula returns a position
of text or character in a text. This is case-sensitive.
LEN: This formula returns the number
of characters (including space) of a text.
MID: This function returns a part of a text or a character from a text on the base of the parameter.
SUBSTITUTE: This formula replaces the
fully or partially text with another text on the base parameter
TEXT: This formula returns a value
converted to text with a specified format on the base of the parameter.
TEXTJOIN: This formula is used to combine 2 or more texts together separated by a delimiter as given in the parameter.
Date/Time Functions
DATE: This formula returns the create
a date from the given parameter of year, month, and day.
DATEVALUE: This formula converts the
text (should be in form of date) into a date.
EOMONTH: This formula calculates the
last day of the month after adding a specified number of months to date as
given in the parameter.
NETWORKDAYS: This formula returns the
number of workdays between 2 dates, excluding Saturday and Sunday and
holidays.
NOW: This formula returns the current
system date and time
TODAY: This formula returns the current system date only.
Basic Charts:
Charts are very useful to convey information. Analyzing the data points by charts are easy in comparison of tables.
Below is the list of some basic
charts available in Microsoft Excel.
COLUMN CHART:
Column Charts are used to compare
values by categories.
PIE CHART:
A pie chart is used to display each
category’s contribution parts of a whole number.
STACKED COLUMN CHART:
A stacked column chart is used to
display the bifurcation of multiple categories in columns.
LINE CHART:
A-Line Chart is used to display the
trend line of the data.
DOUGHNUT CHART:
Doughnut Chart is also used to
display each category’s contribution parts of a whole number.
STACKED BAR CHART:
A stacked Bart chart is also used to
display the bifurcation of multiple categories in Bars. we use this chart when our
category names are big.
BAR CHART:
A bar chart is like a horizontal column
chart, It is used when category names are big.
AREA CHART:
Area Chart is also used to show
trends like a Line chart.
COMBO CHART:
Combo Chart (Column + Line) chart can
be used to show two different/same type of data points for multiple
categories
PIVOT TABLE:
Pivot tables are used to summarize and analyze the data. This is a very useful and powerful feature available in Microsoft Excel.
Data Validation:
The data validation feature available in Microsoft Excel is used to prevent invalid entry by the user. Data validation is used in the Excel cells.