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 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.
|100 Must-Do essays topics|
|Quotes for Descriptive Essays|
|List of Government exams 2023|
|List of All Official Websites|
for Jobs, Exams & Notification
|Tab Name||Important Functions and Tools|
|Home||Clipboard, Font, Alignment, Number Format, styles, cells, sort & filter, find and Selection, Wrap text, Merge & Center, Conditional formatting, Auto sum.|
|Insert||Pivot Table, Table, illustration (Pictures, Shapes, Smart Art, Screenshot), Add-ins, Charts, 3D maps, sparklines, slicer, link, equation, symbol, Header and Footer, word art, text box, signature line|
|Page Layout||Themes, Print|
|Formula||Date & Time, Lookup & Reference, Logical, financial, auto sum|
|Data||Sort, filter, text to the column, flash fill, Data validation, (Data tools), forecast (what-if analysis), Forecast sheet, group, ungroup|
|Review||Spelling, Thesaurus, Smart Lookup, Translate, comment|
|View||Macros, Freeze panes, Switch windows|
Shortcut Keys in Excel
|F2||Edit the selected cell|
|F5||Go to a specific cell|
|F7||Spell check selected text and/ or document|
|Ctrl+Shift+;||Enter the current time|
|Ctrl+;||Enter the current date|
|Alt+Shift+F1||Insert the new worksheet|
|Shift+F3||Open the excel formula|
|Shift+F5||Bring up search box|
|Ctrl+A||Select all contents of the worksheet|
|Ctrl+B||Bold highlighted selection|
|Ctrl+I||Italic highlighted selection|
|Ctrl+U||Underline highlighted selection|
|Ctrl+5||Strikethrough highlighted selection|
|Ctrl+P||Bring up the print dialogue box for printing|
|Ctrl+Z||Undo the last action|
|Ctrl+F9||Minimize current workbook|
|Ctrl+F10||Maximize currently selected workbook|
|Ctrl+Space||Select the entire column|
|Shift+Space||Select the entire row|
|Ctrl+Shift+$||Format number in currency format|
|Ctrl+Shift+#||Format number in date format|
|Ctrl+Shift+%||Format number in a percentage format|
|Ctrl+Shift+@||Format number in time format|
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.
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.
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.
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.
When two or more cells are united, it’s become what is known as a merged cell.
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.
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.
Functions are formulas that are pre-built into Excel. They are intended to help simplify hypothetically complex formulas in a worksheet.
Error Codes appear if Excel finds a problem with a formula provided.
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.
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.
This enables you to copy data to more than one cell easily.
This feature will add up the numbers you have entered in your sheet and displays the total in a cell of your choice.
This is an automatic format application to cells that match pre-determined conditions. This could be as simple as size.
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.
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.
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.
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.
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.
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.
This enables you to find values quickly by sorting your data.
Toggles filtering on the currently selected cell.
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.
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.
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.
Drop-Down. The drop-down contains these commands: Freeze Panes, Freeze First Column, and Freeze Top Row.
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.
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.
=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:
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.
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:
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:
Summarily, COUNT processes only numbers, COUNTA outputs the total number of non-blank cells in columns, including the text value in the column header.
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.
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.
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:
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.
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:
Frequently Asked Questions
When was the first Spreadsheet launched?
It was launched in 1979.
Who is known as the father of electronic spreadsheets?
Dan Bricklin is known as the father of electronic spreadsheets.
what is the first version of Microsoft Excel?
in 1985, the first version of Microsoft Excel was launched so, MS Excel 1985 can be called the first version of MS Excel.
What is the total number of rows in MS Excel?
The total number of rows is 10,48,576
What is the total number of Columns in MS Excel?
The total number of columns is 16,384
is Excel important for FSSAI Exam?
Yes, it is one of the most important topics asked.