1) Introduction: Microsoft Excel is a tabulated sheet consisting of rows and columns.
Rows are in a horizontal order and Columns are in a vertical order.
Rows are on the left side of the sheet and columns are on the top of the sheet.
Row numbers range from 1 to 1048576 and column number ranges from A to XFD i.e total of 16384 columns.
General Uses for Excel:
Budgets for household or business finances.
Invoices and receipts.
Tracking for projects, clients and customers, and health records.
Planners and calendars.
Checklists and task lists.
Financial, loan, debt, and mortgage calculations.
Inventory management.
a) Window Features: It enables you to perform routine tasks related to Microsoft applications. Here are some basic features of Microsoft Excel:
Term 
Description 
Quick Access Toolbar 
Displays quick access to commonly used commands like Save, Undo, Redo, etc. 
Title Bar 
Displays the name of the document. 
File Tab 
The File tab has replaced the Office button. It helps you manage the Microsoft application and access its options such as Open, New, Save, As Print, etc. 
Name Box 
Displays the active cell location. 
Cell 
The combination of a row and column; cells are always named with the column letter followed by the row number (e.g. A1 and AB209); cells may contain text, numbers, and formulas. 
Range 
One or more adjacent cells. A range is identified by its first and last cell address, separated by a colon. Example ranges are B6:B9, A2:B2, and A1:G245. 
Status Bar 
Displays information about the current worksheet. 
New Sheet 
Add a new sheet. 
Ribbon 
Displays groups of related commands within tabs. Each tab provides buttons for commands. 
Formula Bar 
Input formulas and perform calculations. 
Worksheet 
A grid of cells that are more than 16,000 columns wide (AZ, AAAZ, BABZ…IV) and more than 1,000,000 rows long. 
View Option 
Display worksheet view mode. 
b) Create Worksheet: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets. When you open an Excel workbook, Excel automatically selects Sheet1 for you. The name of the worksheet appears on its sheet tab at the bottom of the document window.
Steps to create a Worksheet:
Open Microsoft Excel.
Create a table of appropriate Rows and Columns.
Click on the calculation Cell, enter your formula.
Press enter and you will get your result in that cell.
In this way, you can quickly create a Worksheet.
Save the file with .xlsx.
c) Commands on Cells: To alter the layout of a sheet, sometimes some commands need to be applied to the cells of a worksheet.
i) Changing Column Width:
I. Move the mouse pointer onto the line between cells A and B, the cursor becomes a
doubleheaded arrow.
II. Hold down the mouse button and drag the column divider to the right.
III. Release the mouse button and the column is resized.
IV. Move the mouse pointer to the dividing line between the column headings as before.
V. Double click on the mouse button.
Note: AutoFit Column Width is equivalent to doubleclicking on the column border.
When using autofit, the column must first be selected or the width of the current
cell is used instead.
ii) Add Rows and Columns: You can add rows and columns to the existing rows and columns.
Procedure 
Description 
Add Row 
a) Select any cell of the row where you desire to add a new row above. b) On the Home ribbon in the Cell group, click on the Insert button, and then select Insert Sheet Rows. c) A new roll will appear above your selected cell row. 
Add Column 
a) Select any cell of the column letter where you desire to add a new column to the left. b) On the Home ribbon in the Cell group, click on the Insert button, and then select Insert Sheet Columns. c) A new column will appear to the left of your selected column. 
Delete Row or Column 
a) Select any cell where you desire to delete a row or column. b) On the Home ribbon in the Cell group, click on the Delete button, and then select Delete Sheet Rows or Delete Sheet Columns. c) The row or column where the cell was selected will be deleted. 
2nd method to add rows and columns:
Add Columns:
Select any cell in the column.
On the Home ribbon in the Cell group, click on the Insert dropdown arrow, and then select Insert Sheet Columns. A new column will appear to the left of your selected column.
Add rows:
Select any cell in a row.
On the Home ribbon in the Cell group, click on the Insert dropdown arrow, and then select Insert Sheet Rows. A new roll will appear above your selected cell row.
iii) Text and Number Alignment:
Microsoft Excel aligns the data in three ways: center, left, and right. The default text alignment is left, and the default number alignment is right. You can change the alignment by selecting the alignment icons from the Home ribbon in the Paragraph group. Firstly select the cell range on which you want to apply the alignment.
iv) Format Fonts:
Select the cell range on which you want to apply the format commands.
After Selecting the cell range from the Home ribbon you can change the font color, font size, font styles, etc.
v) Format Numbers:
Excel provides many different types of numeric formats including currency, percent, comma, scientific, fraction, time, short date, long date, etc.
On the Home ribbon, the numeric formats are located in the Number group.
Select the dropdown arrow next to General to view all format types. Select a range of cell/s before choosing the format. This range can include cell/s that do not yet contain data.
2) Adding formulas in Excel: Microsoft Excel allows you to calculate results from entered data. To perform the calculations you need to add formulas in the formula bar. Excel provides many mathematical formulas, and these can be simple arithmetical formulas or complicated formulas involving conditional statements and nested functions. Steps to add formulas in Excel:
Every formula starts with an equal to (=) sign.
Arithmetic operators to perform basic calculations.
I. Negation: A minus sign (). This operation returns the additive inverse of the number represented by the numeric constant or cell reference following the minus sign. (The additive inverse is the value added to a number to produce a value of zero; it's the same as multiplying the number by 1.)
II. Percentage: The percent sign (%). This operation returns the decimal equivalent of the percentage of the numeric constant in front of the number.
III. Exponentiation: A caret (^). This operation raises the number represented by the cell reference or constant in front of the caret to the power of the number after the caret.
IV. Multiplication: An asterisk (*). An asterisk is used for multiplication.
V. Division: A forwardslash (/) is used for division. Multiplication and division have equal precedence and are performed from left to right.
VI. Addition: A plus sign (+).
VII. Subtraction: A minus sign (). Addition and subtraction have equal precedence and are performed from left to right.
Function 
Formula 
Example 
To add up the total 
=SUM(Cell range) 
=SUM(B2+B9) 
To add individual items 
=Value1 + Value2 
=B2+C2 
Subtract 
=Value1  Value2 
=B2C2 
Multiply 
=Value1 * Value2 
=B2*C2 
Divide 
=Value1 / Value2 
=B2/C2 
Exponents 
=Value1^Value2 
=B2^C2 
Average 
=AVERAGE(cell range) 
=AVERAGE(B2:B9) 
Median 
=MEDIAN(cell range) 
=MEDIAN(B2:B9 
Max 
=MAX(cell range) 
=MAX(B2:B9) 
Min 
=MIN(cell range) 
=MIN(B2:B9) 
Comparison Operators: In most cases, the comparison operators are used with the IF function. The comparison operators are listed below:
I. Equals: An equal sign (=).
II. Is not equal to (<>).
III. Less than (<).
IV. Less than or equal to (<=).
V. Greater than (>).
VI. Greater than or equal to (>=).
Reference Operators:
Operator 
Description 
Formula 
: (Colon) 
It’s a range operator. Which gives a range of cells within start point and endpoint reference cells 
= SUM(A2:B6) #Provides all cells as a range starting from A2 to cell B6. 
, (Comma) 
Union Operator. This operator combines (as the word union suggests) two or more than two references into a single reference. 
= SUM(A2:A6, A2:B3) 
(space) 
Intersection Operator. This operator only gives reference to those cells which are common in two range arguments. 
= SUM(A2:A6 A2:B3) 
3) Microsoft Excel basic functions:
a) SUM:
Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)
It allows you to add 2 or more numbers together. You can use cell references as well in this formula.
b) AVERAGE:
Formula: AVERAGE(A1:A10)
It helps you to find the arithmetic mean of a range of numbers. You can simply select that inbuilt formula and it will provide you the results.
c) MIN & MAX:
Formula:
=MAX(B2:B6
=MIN(B2:B6)
The MAX and MIN formulas in Excel get the largest and smallest value in a set of numbers, respectively.
d) COUNT:
Formula: =COUNT(A1:A10)
The count formula counts the number of cells in a range that have numbers in them. It only counts the cells where there are numbers.
e) COUNTA
Formula: =COUNTA(A1:A10)
Counts the number of nonempty cells in a range. It will count cells that have numbers and/or any other characters in them.
f) LEN
Formula: =LEN(A1)
The LEN formula counts the number of characters in a cell. It includes spaces also.
g) TRIM
Formula: =TRIM(A1)
Gets rid of any space in a cell, except for single spaces between words. This formula is extremely useful where you pull data from a database and for some reason, extra spaces are put in behind or in front of legitimate data.
h) RIGHT, LEFT, MID
Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters).
These formulas return the specified number of characters from a text string. RIGHT gives you the number of characters from the right of the text string, LEFT gives you the number of characters from the left, and MID gives you the specified number of characters from the middle of the word.
i) VLOOKUP
Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. You define a value (the lookup_value) for the formula to look for. It looks for this value in the leftmost column of a table (the table_array).
For Example,
VLOOKUP(F2, B1:C9,2,FALSE)
lookup_value: The cell where I would like to enter my formula.
table_array: Where the lookup value is to be searched in the column.
col_index_num: the column index is the argument where the index number of the column we are looking for is given.
range_lookup: We need to give Boolean character true or false. true is for the appropriate match, and False is for the exact match. For an appropriate match, it will look only for the first word and in the exact match, all the characters of the word should be matched.
j) if statement:
Formula:
=IF(logical_statement, return this if logical statement is true, return this if logical statement is false)
if(C2>D2, “Yes it is”, “No it is Not”)
k) SUMIF, COUNTIF, AVERAGEIF:
Formulas: =SUMIF(range, criteria, sum_range), =COUNTIF(range, criteria), =AVERAGEIF(range, criteria, average_range)
l) Concatenation:
Formula:
=(A1&B1)
Ampersand (&): Use an ampersand (&) to join text strings together. Concatenation is the process to join two strings together. The ampersand is known as a text operator to join strings together in Excel formulas.
m) AND & OR:
Formula:
=IF(AND(B2>60, B2>60), "Pass", "Fail")
=IF(OR(B2>60, B2>60), "Pass", "Fail")
These are the two most popular logical functions to check multiple criteria. The difference is how they do this:
AND returns TRUE if all conditions are met, FALSE otherwise.
OR returns TRUE if any condition is met, FALSE otherwise.
n) TODAY & NOW:
Formula:
To see the current date and time whenever you open your worksheet without having to manually update it daily, use either:
=TODAY() to insert today's date in a cell.
=NOW() to insert the current date and time in a cell.
4) Charts in MS Excel: A chart is a visual representation of data in both columns and rows. Charts are usually used to analyze trends and patterns in data sets.
a) Types of Charts: Excel provides the different types of charts. Depends upon your data visualization you can select any kind of chart. The following table shows some of the most commonly used charts:
Chart Type 
When to use? 
Example 
Pie Chart 
You can use this type of graph in which a circle is divided into sectors that each represent a proportion of the whole. 

Bar Chart 
Numerical values of variables can be represented by the height or length of lines or rectangles of equal width. 

Column Chart 
When you want to display the comparisons among subjects on an overall chart. They are often used to show data comparisons in a visual way. 

Line Chart 
You will use a line chart when you want to emphasize changes in values for one variable for continuous values of a second variable 

Combo Chart 
Combination Charts allow the reader to study data to determine if there is a relationship between the two data sets. 
i) Steps to create a Pie Chart: A pie chart is a circular graph that shows the proportions of categories based on the size of each slice. The entire pie represents 100 percent, and each slice represents a portion of a whole.
Select the range (You can select only one row with its labels).
On the Insert tab, in the Charts group, click the Pie symbol.
Click Pie.
You can perform some changes in the chart:
Select the pie chart.
Click the + button on the right side of the chart and click the check box next to Data Labels.
Click the paintbrush icon on the right side of the chart and change the color scheme of the pie chart.
Rightclick the pie chart and click Format Data Labels.
Check Category Name, uncheck Value, check Percentage and click Center.
Rightclick the data labels and click Font to change the font size and font color of the data labels.
Bear 
Dolphin 
Whale 

2018 
54 
77 
54 
ii) Steps to create a Bar Chart:
Select the range (Example, A1:D6)
On the Insert tab, in the Charts group, click the Column symbol.
Click clustered bar.
Bear 
Dolphin 
Whale 

2017 
68 
70 
72 
2018 
84 
89 
90 
2019 
67 
70 
73 
2020 
76 
78 
79 
iii) Steps to create a Column Chart:
Select the range.
On the Insert tab, in the Charts group, click the Column symbol.
Click clustered bar.
Bear 
Dolphin 
Whale 

2017 
68 
70 
72 
2018 
84 
89 
90 
2019 
67 
70 
73 
2020 
76 
78 
79 
Difference between Bar Chart and Column Chart: Both the Bar and the Column charts display data using rectangular bars where the length of the bar is proportional to the data value. Both the charts are used to compare two or more values. However, the difference lies in their orientation. A bar chart is oriented horizontally whereas the column chart is oriented vertically.
iii) Steps to create a Line Chart:
Select the range.
On the Insert tab, in the Charts group, click the Line symbol.
Click Line with Markers.
Bear 
Dolphin 
Whale 

2017 
68 
70 
72 
2018 
84 
89 
90 
2019 
67 
70 
73 
2020 
76 
78 
79 
.
iii) Steps to create a Combo Chart:
Select the range.
On the Insert tab, in the Charts group, click the Combo symbol.
Click Create Custom Combo Chart.
Bear 
Dolphin 
Whale 

2017 
68 
70 
72 
2018 
84 
89 
90 
2019 
67 
70 
73 
2020 
76 
78 
79 
5) Microsoft Excel Keyboard shortcuts:
General Shortcuts: 

Ctrl+N 
Create a new workbook 
Ctrl+O 
Open an existing workbook 
Ctrl+S 
Save a workbook 
Ctrl+S 
Save a workbook 
F12 
Open the Save As dialog box 
Ctrl+W 
Close a workbook 
Ctrl+F4 
Close Excel 
F4 
Repeat the last command or action. For example, if the last thing you typed in a cell is “hello,” or if you change the font color, clicking another cell and pressing F4 repeats that action in the new cell. 
Shift+F1 
Insert a new worksheet 
Ctrl+Z 
Undo an action 
Ctrl+Y 
Redo an action 
Ctrl+F2 
Switch to Print Preview 
F1 
Open the Help pane 
F7 
Check Spelling 
Shift+F9 
Calculate active worksheets 
Ctrl+F1 
Show or hide the ribbon 
Ctrl+Shift+U 
Expand or collapse the formula bar 
Ctrl+F9 
Minimize the workbook window 
F11 
Create a bar chart based on selected data (on a separate sheet) 
Alt+F1 
Create an embedded bar chart based on select data (same sheet) 
Ctrl+F 
Search in a spreadsheet, or use Find and Replace 
Alt+F 
Open the File tab menu 
Alt+H 
Go to the Home tab 
Alt+N 
Open the Insert tab 
Alt+P 
Go to the Page Layout tab 
Alt+M 
Go to the Formulas tab 
Alt+A 
Go to the Data tab 
Alt+R 
Go to the Review tab 
Alt+W 
Go to the View tab 
Alt+X 
Go to the Addins tab 
Alt+Y 
Go to the Help tab 
Ctrl+Tab 
Switch between open workbooks 
Shift+F3 
Insert a function 
Alt+F8 
Create, run, edit, or delete a macro 
Alt+F11 
Open the Microsoft Visual Basic For Applications Editor 
Moving around in a Worksheet 

Left/Right Arrow 
Move one cell to the left or right 
Up/Down Arrow 
Move one cell up or down 
Ctrl+Up/Down Arrow 
Move to the top or bottom cell in the column 
Tab 
Go to the next cell 
Shift+Tab 
Go to the previous cell 
Ctrl+Home 
Move to the beginning of a worksheet 
Page Up/Down 
Move one screen up or down in a worksheet 
Alt+Page Up/Down 
Move one screen to the right or left in a worksheet 
Ctrl+Page Up/Down 
Move to the previous or next worksheet 
Selecting Cells 

Shift+Left/Right Arrow 
Extend the cell selection to the left or right 
Shift+Space 
Select the entire row 
Ctrl+Space 
Select the entire column 
Ctrl+Shift+Space 
Select the entire worksheet 
Editing Cells 

F2 
Edit a cell 
Shift+F2 
Add or edit a cell comment 
Ctrl+X 
Cut contents of a cell, selected data, or selected cell range 
Ctrl+C or Ctrl+Insert 
Copy contents of a cell, selected data, or selected cell range 
Ctrl+V or Shift+Insert 
Paste contents of a cell, selected data, or selected cell range 
Ctrl+Alt+V 
Open the Paste Special dialog box 
Delete 
Remove the contents of a cell, selected data, or selected cell range 
Esc 
Cancel an entry in a cell or the formula bar 
Enter 
Complete an entry in a cell or the formula bar 
Formatting Cells 

Ctrl+B 
Add or remove bold to the contents of a cell, selected data, or selected cell range 
Ctrl+I 
Add or remove italics to the contents of a cell, selected data, or selected cell range 
Ctrl+U 
Add or remove underline to the contents of a cell, selected data, or selected cell range 
Ctrl+Shift+& 
Apply outline border 
Ctrl+Shift+_ (Underline) 
Remove outline border 
Ctrl+9 
Hide the selected rows 
Ctrl+0 
Hide the selected columns 
Ctrl+1 
Open the Format Cells dialog box 
Ctrl+5 
Apply or remove strikethrough 
Ctrl+Shift+$ 
Apply currency format 
Ctrl+Shift+% 
Apply percent format 