EXCEL NOTES
MS-EXCEL
Definition: -
Microsoft Excel is a spreadsheet program used to record and analyze numerical and
statistical data. Microsoft Excel provides multiple features to perform various
operations. In this software, we can create various types of sheets like Marksheet,
Attendance Sheet, Loan Sheet, Salary Sheet, etc.
It’s Extension name: - .xlsx
Page of MS-Excel: - Spreadsheet
Zooming Level: -
Maximum: - 400%
Minimum: - 10%
Total Number of Columns: - XFD (16384)
Total Number of Rows: - 10,48,576
Total Number of Cells: - 17179869184
What is Wrap Text?
The Excel wrap text feature can help you fully display longer text in a cell without it
overflowing to other cells. "Wrapping text" means displaying the cell contents on
multiple lines, rather than one long line.
What is Merge?
Merging combines two or more cells to create a new, larger cell. This is a great way
to create a label that spans several columns
What is Conditional Formatting?
Conditional formatting is a feature in many spreadsheet applications that allows you
to apply specific formatting to cells that meet certain criteria. It is most often used as
color-based formatting to highlight, emphasize, or differentiate among data and
information stored in a spreadsheet.
What is function of Flash Fill?
Flash Fill automatically fills your data when it senses a pattern. For example, you can
use Flash Fill to separate first and last names from a single column, or combine first
and last names from two different columns. ... You can go to Data > Flash Fill to run it
manually, or press Ctrl+E.
What is function of Sort?
The Excel SORT function sorts the contents of a range or array in ascending or
descending order. Values can be sorted by one or more columns.
What is Filter?
Filter is used to filter out only a desired data from a larger set of data. It will hide all the
unwanted data and only the data specified in the filter will be shown.
What is the function of Pivot Table?
A Pivot Table is used to summarise, sort, reorganise, group, count, total or average
data stored in a table. It allows us to transform columns into rows and rows into
columns. It allows grouping by any field (column), and using advanced calculations on
them.
What is Slicer?
Slicers provide buttons that you can click to filter tables, or PivotTables. In addition to
quick filtering, slicers also indicate the current filtering state, which makes it easy to
understand what exactly is currently displayed. You can use a slicer to filter data in a
table or PivotTable with ease.
Definition of Timeline?
Timeline in Excel is a kind of SmartArt created to display the different timings of a
particular process. It is mainly used for filtering the underlying datasets by date. Such
datasets are in the form of pivot tables containing the date field.
Functions: -
1) Date & Time Functions
2) Maths Functions
3) Text Functions
4) Lookup Functions
5) Logical Functions
1)Date & Time Functions: -
2) MATHS FUNCTIONS: -
3) Text Functions: -
L
4) Lookup Functions: -
5) Logical Functions: -
What is the function of Data Validation?
Data validation is a feature that allows you to control the type of data entered into your
worksheet. For example, Excel data validation allows you to limit data entries to a
selection from a dropdown list and to restrict certain data entries, such as dates or
numbers outside of a predetermined range.
What is Consolidate data in Excel?
Data Consolidation allows you to gather together your data from separate worksheets
into a master worksheet. In other words, the Data Consolidation function takes data
from a series of worksheets or workbooks and summaries it into a single worksheet
that you can update easily.
What is the function of Scenario Manager?
Scenario Manager in Excel is used to compare data side by side and also swap
multiple sets of data within a worksheet. In simple words when you have multiple
variables and you want to see their effect on the final result, and also want to estimate
between two or more desired budgets you can use Scenario Manager.
What is Goal Seek?
Goal Seek is a process of calculating a value by performing what-if analysis on a given
set of values. For our purposes, Excel's Goal Seek feature lets you adjust a value used
in a formula to achieve a specific goal.
What is function of Data Table?
A data table is a range of cells in which you can change values in some of the cells
and come up with different answers to a problem.
What is the function of Spelling check?
Spell check is a software program that corrects spelling errors in word processing,
email and online discussions. Spell check identifies and corrects misspelled words. It
also allows you to search a document yourself for words you know you've misspelled.
What is the function of Thesaurus?
Using the thesaurus, you can look up synonyms (different words with the same
meaning) and antonyms (words with the opposite meaning).
What is function of Protect sheet?
To prevent other users from accidentally or deliberately changing, moving, or deleting
data in a worksheet, you can lock the cells on your Excel worksheet and then
protect the sheet with a password.
What is function of Protect Workbook?
To prevent other users from viewing hidden worksheets, adding, moving, deleting, or
hiding worksheets, and renaming worksheets, you can protect the structure of your
Excel workbook with a password.
What is function of Allow Edit Range?
Excel allows you to set up permissions so that particular ranges can only be edited by
specified users. The Allow Users to Edit Ranges command is on the Ribbon's Review
tab in the Changes group. If the command button is greyed out it may be because the
worksheet is protected.
What is the function of Freeze Panes?
The Excel Freeze Panes tool allows you to lock your column and/or row headings
so that, when you scroll down or over to view the rest of your sheet, the first column
and/or top row remain on the screen.
What is the function of Macros?
you can record a macro to automate those tasks. 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.
Shortcut Keys of Excel
Ctrl + A All Select
Ctrl + B Bold
Ctrl + C Copy
Ctrl + D Fill Down
Ctrl + E Flash Fill
Ctrl + F Find
Ctrl + G Go To
Ctrl + H Replace
Ctrl + I Italic
Ctrl + K Hyperlink
Ctrl + L Create Table
Ctrl + N New Page
Ctrl + O Open
Ctrl + P Print
Ctrl + R Fill Right
Ctrl + S Save
Ctrl + U Underline
Ctrl + V Paste
Ctrl + W Close the Software
Ctrl + X Cut
Ctrl + Y Redo
Ctrl + Z Undo
F11 Create chart on next sheet
Shift + F11 Insert a new sheet
Alt + Enter Wrap text
Alt + OD New rule of conditional formatting
Alt + DL Open Data Validation
Ctrl + + Insert Row/Column
Ctrl + - Delete Row/Column
Ctrl + Enter Select 1 Column
Shift + Enter Select 1 Row
Alt + HOI AutoFit Column Width
Alt + HOR Rename a sheet
Alt + EIS Open Series Option
Alt + HSS Sort (Ascending Order)
Alt + HSO Sort (Descending Order)
Alt + HBA Create Border
Alt + AT Filter
Ctrl + ~ Show Formulas
F7 Check Spelling
Shift + F7 Thesaurus
Ctrl + ; Show current date
Ctrl + Shift + : Show current time
Alt + HBN To Remove a Border
Ctrl + Shift + Spacebar To select an entire a workbook
Shift + F2 Insert a Comment
Alt + F8 View Macro
