Microsoft Excel Notes

In this post, we are providing students with complete Excel notes for competitive Exams of UPSC, SSC, FSSAI, ASRB, BANKING, INSURANCE and Different State Government Exams. These Notes are highly important to crack any government exams. Students can also check our paid courses that are designed to crack any government exams with a rank.

Excel Definition

Excel is a type of software that is used for mathematical calculations, budgeting, billing, analyzing, and managing data entered into a spreadsheet.

Excel basic facts

• The First Excel version was launched in 1985
• The latest Excel version was launched in 2021
• Default Excel file name – .xlsx and .xls
• Rows are the horizontal space between horizontal lines and numbered as 1, 2, 3, 4…
• Columns are the vertical space between vertical lines and represented by letters like A, B, C…Z, AA, AB, AC…AZ, BA, BB, BC….BZ and So on.
• Total number of rows are 10,48,576
• Total number of columns are 16,384
• Last column name XFD
• Cell is the visible box in the worksheet area and actually the space between the transaction of rows and columns.
• A Cell Address is used to specified the intersection of row and column of the letter and number on worksheet.
• Last Cell Address – XFD1048576
• Clipboard contains the cut, copy and paste commands.
• Active cell: Current working cell
• Name Box – It shows the active cell address.

Shortcut Keys in Excel

🔴 Important Links 🔴
👉 Join Our Telegram Channel for All Updates
👉 Subscribe to Our YouTube Channel for Free Video
Nishant eAcademy App Free Notes || Free Batches || Free Tests
Free Download All Books and Notes for Exams
Best Current Affairs MCQs
Chat with Us

Excel terms

Workbook

The workbook refers to an Excel spreadsheet file. The workbook stores all of the data that you have entered and lets you sort or calculate the results. A workbook that is accessible to be viewed and modified by multiple users on a network is called a Shared Workbook.

Worksheet

A worksheet is a sub-component document of a workbook. It is also called spreadsheets; you can have multiple sheets nestled in a workbook. Tabs at the bottommost of the screen will indicate which of your worksheets you are presently working on; this is also known as an active sheet or active worksheet.

Cell

A cell is an intersection between a column and a row on a spreadsheet. Each cell in a spreadsheet can encompass any value that can be called using a virtual cell reference or called upon using a formula. Any data that you want to enter into your worksheet must be put in a cell. An Active Cell is one that is currently open for editing.

Column and Row Headings

These headings are lettered and numbered grey areas located just outside of columns and rows. Clicking on a heading will select the entire row or column. You can also modify the row height or column width using the headings.

Cell Range

A Cell range is a cluster of cells that have been identified as a group based on a variety of criteria. By using a colon (:) between cell references, Excel can determine the range, also called an array. A range in a row, for example, could look like A3: D3, telling the formula to look at the cells in a row between A3 and D3, while C4: F9 would tell the formula to look at all cells in a box bounded by columns C and F and rows 4 and 9. A 3-D reference refers to a range that encompasses more than one worksheet in the same workbook.

Merged Cell

When two or more cells are united, it’s become what is known as a merged cell.

Operator

Operators are signs or symbols that specify which calculation must be made in an expression. Operators do not necessarily refer to simple mathematical types; comparison, concatenation, text, or reference operators also exist.

Formula

A sequence inside a cell that is used to produce value is called a formula. It must begin with an equality sign (=). This could be a mathematical equation, functions, cell references, or operator. A formula is also known as an expression.

Function

Functions are formulas that are pre-built into Excel. They are intended to help simplify hypothetically complex formulas in a worksheet.

Error Code

Error Codes appear if Excel finds a problem with a formula provided.

Cell Formatting

This is the act of changing how a cell or its contents are displayed in the spreadsheet. When you format cells, only the visual appearance of the cells is altered; the value within the cells remains the same.

Filter

Filters are guidelines that you can employ to choose which rows in a worksheet to display. These filters can use data such as conditions or values.

AutoFill

This enables you to copy data to more than one cell easily.

AutoSum

This feature will add up the numbers you have entered in your sheet and displays the total in a cell of your choice.

AutoFormat

This is an automatic format application to cells that match pre-determined conditions. This could be as simple as size.

Data Validation

This feature helps to avert inappropriate data from being entered into your worksheet. Data validation promotes accuracy and consistency in the data to be entered.

Pivot Table

This is a data summarization tool most commonly used to sort, average, to sum up, data automatically. The information is heaved from one table while the results are presented in another.

Pivot Chart

This type of chart provides a visual aid for pivot tables by providing graphical illustrations of the pivot table data; the user can offer a level of interactivity with the data.

Format Painter

Copies the format from a cell (or range), allowing it to be pasted somewhere else.

Conditional Formatting – Drop-Down

It gives access to conditional formatting commands which can be used to apply cell formatting automatically. It contains these commands: Highlight Cells Rules, Top/Bottom Rules, Color Scales, Data Bars, Icon Sets, Clear Rules, New Rule, and Manage Rules.

AutoSum-

Button with Drop-Down. The same command can be found on the Formulas Tab and it contains the commands: Sum, Average, Count Numbers, Max, Min, And More Functions.

Fill – Drop-Down

This option allows you to fill the selection in any direction and into any range of adjacent cells. The drop-down contains the commands: Down, Up, Right, Left, Across Worksheets, Justify, Series and Flash Fill.

Clear – Drop-Down

This removes everything from the current selection or removes particular items. The drop-down contains the commands: Clear All, Clear Contents, Clear Formats, Clear Hyperlinks, Clear Comments, and Remove Hyperlinks.

Sort & Filter – Drop-Down

This helps you sort the currently selected data or current region into descending or ascending order and allows you to add filters to decrease the number of evident rows.

Find & Select – Drop-Down

This option permits you to find and replace an input or selected data. The drop-down contains the commands: Find, Replace, GoTo, GoTo Special, Comments, Formulas, Constants, Conditional Formatting, Select Objects, Data Validation, and Selection Pane.

Signature Line

Button with Drop-Down. The button inserts a signature line that stipulates the individual who must sign. The drop-down shows these commands: Microsoft Office Signature Line and Add Signature Services.

Equation – Drop-Down

From here, you can insert your desired equation to your workbook. The drop-down contains the commands: Area of Circle, Expansion of a Sum, Binomial Theorem, Fourier Series, and Insert New Equation.

Sort & Filter

Sort A to Z

Sorts data from lowest to highest; alphabetically from A-Z and numerically from the lowest number to the highest number.

Sort Z to A

Sorts data from highest to lowest; alphabetically from Z-A and numerically from the highest number to the lowest number.

Sort

This enables you to find values quickly by sorting your data.

Filter

Toggles filtering on the currently selected cell.

Clear

Clears the filter and sorts for the currently selected range of data.

Reapply – (Ctrl + Alt + L)

This helps you to reapply the filter and sort to the current selection so that the changes you have made are included.

Data Tools

Text to Columns – This permits you to split a single column of text into multiple columns.

Flash Fill – This automatically fills in values into the cell.

Remove Duplicates – This removes duplicate sheets from a row.

Data Validation – Drop-Down. This lets you limit the type of data that can be entered into a cell. The drop-down includes these commands: Circle Invalid Data and Clear Validation Circles.

Consolidate – This summarizes data from separate ranges then consolidates the result in a single output range.

Relationships – The relationship feature lets you create or modify relationships between tables to show linked data from separate tables on the same report.

Manage Data Model – Opens the Power Pivot window in order to add and prepare data or continue working on data on the workbook.

Forecast

What-If Analysis – Drop-Down. This function lets you try out various values for the formula in your sheet using the drop-down options. The drop-down contains these commands: Scenario Manager, Goal Seek, and Data Table.

Forecast Sheet – You can create a new worksheet to predict data trends using this feature. It will let you forecast your timeline data.

• Telegram
• YouTube
• Instagram
• Twitter
• LinkedIn
• Facebook

Freeze Panes

Drop-Down. The drop-down contains these commands: Freeze Panes, Freeze First Column, and Freeze Top Row.

Macros

It is used to define a sequence of actions to perform on a document or multiple documents that can be executed again and again.

Excel Basic Functions

Below is a list of ten simple but helpful functions that are essentially needed for your expertise skill in Excel.

SUM

This is the first Excel function I will be familiarizing you with. It is the one that performs the basic arithmetic operation of addition. Your Sum formula in Excel should include at least 1 number, referenced to a cell or a range of cells.

For example:

=SUM(A2:C6) – this adds up values in cells A2 through C6.

=SUM(A2, C6) – this adds up values in cells A2 and C6.

There are times when you will need to perform multiple functions such as addition and division, you can perform this within a single formula, for example, add up values in cells A2 through C6, and then divide the sum by 5:

=SUM(A2:C6)/5

Also, you can sum with conditions. To sum with conditions, use the SUMIF function:

• In the 1st argument, you input the range of cells to be verified against the criteria (A1:A4)
• In the 2nd argument, input the criteria itself (C2)
• In the last argument, input the cells to sum (B1:B4)

=SUMIF(A1:A4, C2, B1:B4)

Note: The easiest and fastest way to sum a row or column of numbers is to select a cell following the numbers you want to sum (the cell directly below the last value in the column), and click the AutoSum button on the Home tab, in the Formats group. Excel will insert a SUM formula for you automatically and add up the corresponding values.

AVERAGE

The second function we will be looking at is the average. Excel AVERAGE function does exactly what its name implies, that is; it finds an average, or arithmetic mean, of numbers.

Having a closer look at the formula from the previous example (=SUM(A2:C6)/5), what this does is that it sums values in cells A2 through C6, and then divides the result by 3, what it does here is called average; dividing the total by the frequency.

To perform an average, you can simply input this formula in a cell:

=AVERAGE(A2:C6)

To calculate average cells based on condition, use the AVERAGEIF formula, where A2:C6 is the criteria range, D3 is the criteria, and E2:E6 are the cells to average:

=AVERAGEIF(A2:C6, D3, E2:E6)

MAXIMUM & MINIMUM

The MAX and MIN formulas in Excel get the highest and lowest value in a set of numbers, respectively. The formulas for maximum and minimum is as simple as:

=MAX(A2:C6) – identify the maximum value from cell A2 through C6.

=MIN(A2:C6) – identify the minimum value from cell A2 through C6.

COUNT & COUNTA

Another important function in Excel is the COUNT & COUNTA function. If you need to know how many are cells in a given range contain numeric values (numbers or dates), you don’t need to waste time by counting them by hand, the Excel COUNT function will do the trick.

The COUNT function deals only with those cells that comprise numbers while the COUNTA function counts all cells that are not blank, whether they contain numbers, dates, text, times, or errors.

For example, to find out how many cells in column C contain numbers, use this formula: =COUNT(C:C)

To count all non-empty cells in column A, use:

=COUNTA(C:C)

Summarily, COUNT processes only numbers, COUNTA outputs the total number of non-blank cells in columns, including the text value in the column header.

IF

This seems to be the most popular function in Excel. In simple terms, you use an IF formula to ask Excel to verify a certain condition and return one value or perform one calculation if the condition is met, and another value or calculation if the condition is not met. For example:

IF(logical_test, [value_if_true], [value_if_false]). This will test the validity or otherwise of the input data.

TRIM

There are times when your correct Excel formulas return just a bunch of errors, one of the first things to check is extra spaces in the referenced cells. These extra spaces need to be trimmed. There are quite a few ways to get rid of unwanted spaces in Excel, with the TRIM function being the easiest one:

For example, to trim extra spaces in column B, enter the following formula in cell B1, and then copy it down the column:

=TRIM(B1). This will eliminate all extra spaces in cells.

LEN

The LEN function is used to determine the number of characters in a certain cell.

For example, if you wish to find out how many characters are in cell C2, just type the formula below into another cell:

=LEN(C2)

Note: Excel LEN function counts absolutely all characters including spaces.

AND & OR

These are the two most popular logical functions to verify multiple criteria.

• AND returns TRUE if all conditions are met; FALSE if otherwise.
• OR returns TRUE if any condition is met; FALSE if otherwise.

CONCATENATE

This function is applicable when you want to take values from two or more cells and combine them into one cell. For example, to combine the values from cells A3 and B3, just enter this formula in a different cell:

=CONCATENATE(A3, B3)

Frequently Asked Questions