Microsoft Excel ROYAL INFOTECH
Microsoft Excel
Microsoft Excel 2013
Title Bar
Address Formula Bar
Quick Access Column
Bar Toolbar Headings
Scroll Bar
Row
Headings
Worksheet
Ribbon
Cell Pointer
INTRODUCTION
1. Microsoft Excel 2013 is an Electronic Spread sheet Package.
2. Excel can be used for the following:
i. Calculations of Numeric Data
ii. Charts (Graphs)
iii. Reports
iv. Salary sheets
v. Balance sheets
vi. Attendance sheets etc.
3. File of excel is known as Workbook.
4. Extension of workbook is .XLSX
5. Workbook is made up of no. of worksheets.
6. Each worksheet is identified by name which can be renamed.
7. Worksheet is made up of Rows and Columns.
8. Each Row is identified by a Number like 1..2..3..4..5...
9. Each Column is identified by a Letter like A...B..C..D....
10.Maximum Rows in a worksheet are1048576
11.Maximum Columns in a worksheet are16384 (XFD)
12.Intersection of Row & Column is called CELL.
13.Each cell has a unique address.
1 9773620583 / 9821728337
Microsoft Excel ROYAL INFOTECH
To move Cell Pointer (movement)
CELL saola
Enter : Downwards
Tab : Towards Right
Last Column (XFD) : End + Right Arrow Key
First Column (A) : Home
LastRow (1048576) : End + Down Arrow Key
First Cell (A1) : Ctrl + Home
Mouse Pointers Fill Handle
In a cell, we can enter: Cell Pointer
Move
1. Characters (Labels)
2. Numbers (Values)
+ While
dragging
3. Date & Time
4. Formulas
5. Functions
Formulas or Functions always starts with (=) equal sign.
2 9773620583 / 9821728337
Microsoft Excel ROYAL INFOTECH
SORT & SORT - To arrange data in Ascending or descending order.
Ascending : A to Z 1,2,3,..... Descending : Z to A 100,99,98
FILTER
FILTER - To filter records according to the selection.
Chart
This option is used to make Chart (Graph) from the selected
range of data.
After selecting a Data Select the Chart Type and its Subtype.
Pivot Table A PivotTable is an interactive way to quickly summarize large
amounts of data. You can use a PivotTable to analyze numerical
data in detail, and answer unanticipated questions about your
data.
SUBTOTAL The SUBTOTAL function is designed for columns of data, or
vertical ranges. It is not designed for rows of data, or horizontal
ranges.
Macro A macro is an action or a set of actions that you can run as many
times as you want. When you create a macro, you are recording
your mouse clicks and keystrokes. After you create a macro, you
can edit it to make minor changes to the way it works
HLOOKUP Use HLOOKUP when your comparison values are located in a row
across the top of a table of data, and you want to look down a
VLOOKUP specified number of rows. Use VLOOKUP when your comparison
values are located in a column to the left of the data you want to
find. The H in HLOOKUP stands for "Horizontal."
MATCH The MATCH function searches for a specified item in a range of
cells, and then returns the relative position of that item in the
range.
INDEX The INDEX function returns a value or the reference to a value
from within a table or range.
3 9773620583 / 9821728337
Microsoft Excel ROYAL INFOTECH
Functions in EXCEL
Functions are special pre-defined (system defined) formulae that take value or
values to perform a specific task (operation) & returns a value or values.
( : Colon ) Range : Series of Cells e.g. A1 to A10 (A1:A10), B1 to E5 (B1:E5)
=SUM(Range) Shows total of given no or range
=AVERAGE(Range) Shows average values of given range
=MIN(Range) Displays minimum value from given No / range
=MAX(Range) Displays maximum value from given No / range
=COUNT(Range) Counts no. of numeric entries within range
=PRODUCT(RANGE) Multiplies all the numbers of the given range
=TODAY() Displays current (system) Date (MM/DD/YYYY)
=NOW() Displays current (system) Date & Time (MM/DD/YYYY HH:MM)
=MOD(No,Divisible No.) Displays remainder value
=LOWER(“TEXT”) To convert into lower case
=LOWER(Cell Address)
=UPPER(“TEXT”) To convert into upper case
=UPPER(“Cell Address”)
=LEN(“TEXT”) To calculate the length of the text
=LEN(Cell Address)
=IF(Condition, ANS1 if Will check given condition, and if it is satisfying then will display
True, ANS2 if false) first answer otherwise second answer
Condition : Cell Address </>/= value to check
Important Shortcuts in Excel
1. To create a new workbook. Ctrl + N.
2. To open an existing workbook. Ctrl + O.
3. To save a workbook/spreadsheet. Ctrl + S.
4. To close the current workbook. Ctrl + W.
5. To close Excel. Ctrl + F4.
6. To move to the next sheet. Ctrl + PageDown.
7. To move to the previous sheet. Ctrl + PageUp.
8. To go to the Data tab. Alt + A.
9. To go to the View tab Alt + W
10. To go the Formula tab Alt + M
11. To edit a cell F2
12. To copy cells Ctrl + C,
13. To paste cell (which is cut or copied) Ctrl + V
14. To make Font/Text Bold Ctrl + B
15. To make Font/Text Italicize (Italic) Ctrl + I
16. To move to the next cell Tab
4 9773620583 / 9821728337
