Q1) Explain MS Excel in brief.
Microsoft Excel is a spreadsheet or a computer application that allows the memory of data in the form of a table. Excel was developed by Microsoft and can be used on assorted operating systems such as Windows, macOS, IOS and Android. Some of the significant features of MS Excel are :
- Availability of Graphing tools
- Built-in functions such as SUM, DATE, COUNTIF, etc
- Allows data analysis through tables, charts, filters, etc
- The availability of Visual Basic for Application (VBA)
- Flexible workbook and worksheet operations
- Allows easy data validation
Q2) What do you mean by cells in an Excel sheet?
The area which falls at the intersection of a column and a quarrel where the information is to be inserted is known as a cell. There are a total of 1,048,576 x 16,384 cells present in a one excel sheet .
Q3) Explain what is a spreadsheet?
Spreadsheets are a solicitation of cells that help you manage the datum. A individual workbook may have more than one worksheet. You can see all the sheets at the bottomland of the window, along with the names that you have given them. Take a look at the image below :
Q4) What do you mean by cell address?
The cell address of an Excel sheet refers to the cover that is obtained by the combination of the Row count and the Column rudiment. Each cell of an MS Excel tabloid will have a clear-cut cell savoir-faire .
Q5) Can you add cells?
Yes, you can insert newfangled cells into a sheet. To add a newly cell, simply select the cell where you want to insert it and then select the Insert choice. you will see the following window : Select the desire option and then click on OK .
Q6) Can you format MS Excel cells? If yes, then how?
Yes, MS Excel cells can be formatted. In order to format these cells, you can use the commands deliver in the Font group of the Home tab. When you open the Font window, you will see the play along options :
|Number||Allows formatting cells to be of any character such as currency, accountancy, date, share, etc|
|alliance||Allows text control, conjunction and setting its guidance|
|baptismal font||Enables respective fonts, styles, sizes, colors, etc|
|frame||Allows cell borders to be changed, removed, colored, etc|
|fill||Enables you to choose unlike colors and styles to fill up the cell|
|protective covering||Allows you to lock or hide cells|
Q7) Can you add comments to a cell?
Yes, comments can be added. To add comments to a cell, select the cell, right-click on it and then select the New Comment choice. These comments will be visible to all those people who have access to the Excel sail .
Q8) Can you add new rows and columns to an Excel sheet?
Yes, you can add rows and column to an Excel sheet. To add new rows and columns select the position where you intend to add them and right-click on it. then select the Insert option from where you can choose to select an stallion quarrel or column .
Q9) What is Ribbon and where does it appear?
The Ribbon is basically your key interface with Excel and it appears at the top of the Excel window. It allows users to access many of the most significant commands directly. It consists of many tabs such as File, Home, View, Insert, etc. You can besides customize the ribbon to suit your preferences. To customize the Ribbon, right-click on it and select the “ Customize the Ribbon ” choice. You will see the following window : You can select or unselect any option of your choice from here .
Q10) How do you freeze panes in Excel?
MS Excel allows you to freeze panes that will help you see the headings of the rows and the column even if scroll down a long room on the plane. To Freeze Panes in Excel, follow the given steps :
- First, select the Rows and Columns you wish to freeze
- Then, select Freeze Pane present in the View tab
- Here, you will see the following three options to selectively freeze the rows and columns as shown in the image below:
Q11) How do you add a Note to a cell?
To add a note, select the cell and right-click on the same. then select the New Note option and type in any note that you wish to. In case you want to delete the Note, follow the same routine and select the Delete Note option. Notes are indicated by a loss triangle at the top-right corner of the cell .
Q12) Can you protect workbooks in Excel?
Yes, workbooks can be protected. Excel provides three options for this :
- Passwords can be set to open Workbooks
- You can protect sheets from being added, deleted, hidden or unhidden
- Protecting window sizes or positions from being changed
Q13) How do you apply a single format to all the sheets present in a workbook?
To apply the same format to all the sheets of a workbook, follow the given steps :
- Right-click on any sheet present in that workbook
- Then, click on the Select All Sheets option
- Format any of the sheets and you will see that the format has been applied to all the other sheets as well
Q14) What do you understand by Relative Cell Addresses?
Whenever you copy formulas in Excel, the addresses of the reference cells get modified automatically in arrange to match the position where the recipe is copied. This is done by a system that is called Relative Cell Addresses. EXAMPLE: Take a look at the visualize below where I have written the formula in C9 and copying the like formula to C10. As you can see, C10 shows the summarize of A10 and B10, unlike A9 and B9.
Q15) In case you don’t want to modify the cell addresses when they are copied, what should you do?
If you do not want Excel to change the addresses when you copy formulas, you must make use of Absolute Cell Addresses. When you use absolute Cell References, the row and the column addresses do not get modified and remain the same. Example: For absolute reference, you will need to use the $ sign before column and quarrel number. Take a search at the model shown in the given image :
Q16) What will you do if you want to change either the column letter or the row number but not both?
To do this, you must make use of Mixed Cell Addresses where either the row or column is relative while the other is absolute. EXAMPLE: Take a expression at the double below where the column hold relative reference while the rows are absolute. consequently, the values that are to be added in C9 are 5 and 5 since the column letter is the same as in the original formula and therefore the resultant role.
Q17) Can you protect cells of a sheet from being copied?
Yes, you can do it by protecting the necessitate cells or the accomplished sheet. In regulate to do this, follow the given steps :
- Select the cells that you want to protect
- Open up the Font window from the Home tab
- From the Protection pane, select Protection and then check the Hidden box
- Click on Review tab present in the Ribbon, and then select Protect sheet option (Excel will not hide the required cells unless you do this)
- Specify a password (This will help you in unprotecting the sheet later)
Q18) How do you create Named Ranges?
To create named ranges, follow the given steps :
- Select the area to which you intend to give a name
- From Ribbon, select Formulas
- Click on Define Name from Defined Names group
- Give any name of your choice
Q19) What are macros?
Excel allows you to automate the tasks you do regularly by recording them into macro. therefore, a macro is an action or a typeset of them that you can perform newton phone number of times. For case, if you have to record the sales of each detail at the end of the sidereal day, you can create a macro that will automatically calculate the sales, profits, loss, etc and use the like for the future alternatively of manually calculating it every day .
Q20) How do you create dropdown lists in Excel?
To create dropdown lists, follow the given steps :
- Click on Data tab present in the ribbon
- Then, from the Data Tools group, click on Data Validation
- Navigate to Settings>Allow>List
- Select the source list array
Pivot Tables and Pivot charts:
Q21) Explain Pivot tables along with their features?
Pivot Tables are statistical tables that condense data of those tables that have extensive information. The summary can be based on any playing field such as sales, averages, sums, etc that the pivot table represents in a simple and healthy manner .
Some of the features of Excel Pivot Tables are as follows :
- Allow the display of exact data you have to analyze
- Provide various angles to view the data
- Allow you to focus on important details
- Comparison of data is very handy
- Pivot tables can detect different patterns, relationships, data trends, etc
- They can create instant data
- Accurate reports
- Serve the base for Pivot charts
Q22) How do you create Pivot Tables?
In ordering to create a Pivot table, you will first need to prepare the data in a tabular format. Keep the follow points in thinker while preparing the data :
- Arrange the data intorows and columns
- Thefirst rowshould contain unique headingfor each of the columns
- The columnsshould have only one type of data
- Rowsmust have data for asingle recording only
- No blank rows
- Columnsshould not becompletely blank
- The data for creating Pivot table should be separate from other data present in the sheet
For example, let ’ s create a Pivot chart for the table shown in the image below : To create a Pivot table, select the mesa and pawl on the Insert yellow journalism. then choose Pivot Table instruction and you will see the following window : Specify where you intend to create the table and then click on OK. Once this is done you will see that an empty pivot mesa has been created. besides, PivotTables Fields paneling will open that will help you configure the Pivot table. Take a look at the effigy below where I have created a Pivot table :
Q23) What are Pivot charts in MS Excel?
MS Excel charts are data visual image tools that help you visualize data in assorted ways. These charts can be of any type such as Bar, Pie, Area, Line, Doughnut, etc. For exemplar, take a look at the Pivot board in the image below : now, if you wish to create a pivot graph for this table, select any cell from the table and then from the Insert check, choose the Pivot Chart option. You will see the comply options : Choose any chart of your preference and pawl on OK. You can besides format these charts respectively .
Q24) Can you create Pivot tables using multiple tables?
Yes, you can create Pivot tables using more than one nucleotide table. To do this, follow the given steps :
- Press Alt+D and then press P to open up the PivotTable Wizard
- Then select Multiple consolidation ranges option and click on Next and you will see another dialog box as shown below:
- Select I will create the page fields option and click on Next
- In the next window, you will need to add all the required ranges as shown below:
- Once that is done, click on Next
- Specify the region where you want to create the table and then click on Finish
- You will see the pivot table has been created by merging both the tables as shown in the image below:
Q25) What happens when you check the Defer Layout Update option present in the PivotTable Fields window?
In case you check this option, you will not see dynamic changes while interchanging the mesa fields. By default, this option is off or unbridled. All the changes will appear only after you click on the Update button when you check this box .
Q26) Can you create a pivot table using tables from different worksheets?
If both the sheets are from the like workbook, you can create a pivot mesa for tables from different sheets ampere well. To create a pivot table from two different sheets, follow the like steps as shown in Q24 and when you specify the tables, go to the respective sheet and select the tables you intend to merge .
Q27) Is it possible to see the details of the results displayed in a pivot table?
Yes, it is possible to see the details of the results shown by the pivot tables in Excel. In order to see the details for any resultant role, double-click on the value and you will see that a new tabloid has been created with a new postpone having details about the factors that have led to that particular result. For example, if I double-click on one of the city values for Brad in the pivot table shown in Q15, I will see the following board that displays the details regarding the lapp :
Q28) How are PivotTables used to filter data?
Excel PivotTables allow you to filter data according to your requirements. To do this, place the field based on which you wish to filter out the data. then from the pivot table, open the dropdown list confront for the field you have placed in the Filter sphere and select the section of your option. For example, in the table shown in Q22, if you wish to filter the data for different cities, you can do it well as shown below : As you can see, I have filtered the data for Chicago .
Q29) How do you change the value field to show some other result other than the Sum?
In ordain to change the value field to show results other than the Sum, right-click on the Sum of Amount values and then click on Value Field Settings. here is the dialogue corner that you will see : From here, you can select any respect of your choice and then click on OK .
Q30) How to stop automatic sorting in PivotTables?
Excel automatically sorts the data present in the Pivot Tables. In case you do not want Excel to do this, open the dropdown menu fro the Row Labels or the Column Labels, and then click on More classify Options. You will see the Sort dialogue box opening. Click on More Options and unselect the Sort automatically option.
Formulas and Functions:
Q31) What do you understand by Excel functions?
Functions, in Excel, are used to perform specific tasks. Excel has many built-in functions that are used to calculate results of respective formulas thereby helping in time conservation. besides, these functions make it very easy to execute formulas which would have been unmanageable to manually write down .
Q32) What are the various categories of functions available in Excel?
Functions in Excel are categorized as follows :
|Date & Time||DAY, DATE, MONTH, etc|
|fiscal||ACCINTM, DOLLARDE, ACCINT, etc|
|Math & Trig||SUM, SUMIF, PRODUCT, SIN, COS, etc|
|statistical||average, COUNT, COUNTIF, MAX, MIN, etc|
|Lookup & Reference||COLUMN, HLOOKUP, ROW, VLOOKUP, CHOOSE, etc|
|database||DAVERAGE, DCOUNT, DMIN, DMAX, etc|
|text||BAHTTEXT, DOLLAR, LOWER, UPPER, etc|
|legitimate||AND, OR, NOT, IF, TRUE, FALSE, etc|
|data||INFO, ERROR.TYPE, TYPE, ISERROR, etc|
|Engineering||COMPLEX, CONVERT, DELTA, OCT2BIN, etc|
|cube||CUBESET, CUBENUMBER, CUBEVALUE, etc|
|compatibility||PERCENTILE, RANK, VAR, MODE, etc|
|web||ENCODEURL, FILTERXML, WEBSERVICE|
Q33) What is the operator precedence of formulas in Excel?
Formulas in Excel are executed according to the BODMAS rules. BODMAS, as many of us know, stands for Brackets Order Division Multiplication Addition and Subtraction. That means, in every recipe, brackets are executed foremost ( if they are present ) followed by multiplication, division, etc. An example of the like is shown in the persona below : As you can see, the output is 27 i.e obtained by inaugural adding 4+5 and then multiplying it by 3. In case you do not specify the brackets, you will get the result by foremost multiplying 3×4 and then adding 5 to it i.e 12+5 resulting in 17 .
Q34) Explain SUM and SUMIF functions.
SUM: The SUM function is used to calculate the summarize of all the values that are specified as a argument to it. The syntax of this serve is as follows : SUM ( number1, number2, … ) EXAMPLE:
As you can see in the persona, the SUM affair is calculating the total price for all the vegetables. SUMIF: This function is used to calculate the sum of values that comply with a given discipline. SYNTAX: SUMIF ( range, criteria, [ sum_range ] ) where ,
- range specifies the range of cells to be evaluated
- criteria provides the condition to be met
- sum_range is optional and provides the actual cells to be summed up
EXAMPLE : As you can see, the SUMIF serve is calculating the union of goals scored merely by Dybala .
Q35) What are the different types of COUNT functions available in Excel?
Excel provides five types of COUNT functions i.e COUNT, COUNTA, COUNTBLANK, COUNTIF, and COUNTIFS. The COUNT returns the total number of cells that have numbers in the range that is specified to it as a parameter. SYNTAX: COUNT ( value1, value2, … ) EXAMPLE: In the above prototype, you can see that the COUNT function is used to calculate the number of cells having numeral data in them. COUNTA: Counts the number of cells in a given range that are not empty. SYNTAX: COUNT ( value1, [ value2 ], … ) EXAMPLE: The above effigy shows the functionality of the COUNTA routine that returns the total of cells that are not between A4 and B10. COUNTIF: This function counts the act of cells that comply to a given condition. SYNTAX: COUNTIF ( range, criteria ) EXAMPLE: Take a look at the persona below, where the COUNTIF function is used to calculate the phone number of cells that have the list, Dybala. COUNTBLANK: Counts all the blank cells in a given range. SYNTAX: COUNTBLANK ( range ) EXAMPLE:
COUNTIFS: This is a particular function that allows you to specify a set of conditions in ordering to count them. SYNTAX: COUNTIFS ( criteria_range1, range1, [ criteria_range2, criteria2 ], … ) EXAMPLE:
Q36) How do you calculate the percentage in Excel?
Percentages, as we all know, are ratios that are calculated as a fraction of 100. mathematically, the percentage can be defined as follows : percentage = ( Part/ Whole ) x 100 In Excel, the share can be calculated in a similar manner. Take a attend at the image below where the percentage has been calculated for the values present in A1 and A2. here are the steps followed in order to obtain the result :
- Select the cell destination cell to display the percentage
- Then, type a “=” sign
- Type in A1/ A2 then hit the Enter key
- Click on Home tab, select % symbol from the numbers group
Q37) Explain how to calculate compound interest in Excel?
To calculate compound matter to in Excel, you can use the FV function. FV returns the future rate of an investment based on the periodic, constant interest pace and payments. SYNTAX: FV ( rate, nper, pmt, pv, type ) To find the pace, the numeral of periods are used to divide the annual rate ( annual rate/ periods ). nper is obtained by multiplying the no. of years ( term ) with the periods ( term * periods ). periodic requital ( pmt ) can be any value ( including zero ). EXAMPLE:
The investing sum is $ 500, rate is 10 % for 5 years. There are no periodic payments hence the respect for pmt is 0. -B1 means that $ 500 has been taken from you. consequently, the FV for this is $ 822.65 .
Q38) How do you find averages in MS Excel?
median can be calculated using the average officiate. SYNTAX: average ( number1, number2, … ) EXAMPLE:
To calculate the average marks scored by Dave and Ava, I have used the AVERAGE routine .
Q39) What is VLOOKUP in Excel?
VLOOKUP is a function present in Excel used to lookup and bring forth data from a given range. V in VLOOKUP stands for Vertical and to use this affair, data should be arranged vertically. VLOOKUP is very utilitarian when you have to find some piece of data from a huge total of data .
Q40) How does the VLOOKUP function work?
The VLOOKUP function, in Excel, a search value and begins to look for the lapp in the leftmost column. When it finds the first happening of the given search value, VLOOKUP starts to move right i.e in the row where the value was found. It goes on until the column number specified by the user and returns the desire rate. This officiate is used to match demand and approximate search values. however, the nonpayment match is an approximate match .
VLOOKUP ( lookup_value, table_array, col_index_num, [ range_lookup ] ) here ,
- lookup_value gives the value to be looked out for
- table_index is the range from where the data is to be taken
- col_index_num specifiesthe column from which you want to fetch the value
- range_lookup is a logical value i.e TRUE or FALSE( truewill find the closest match;FALSE checks for exact match)
Q41) Explain the exact match with an example.
For an demand match, set the range_lookup value as FALSE. EXAMPLE: In case you want to look for the appellation of an employee, follow the given steps :
- Select the destination cell and type “=”
- Use VLOOKUP
- Specify thelookup_value (Here, it is the ID) along with the other parameters
- Set range_lookup value to FALSE
- The function will be: =VLOOKUP(104, A1: D8, 3, FALSE)
As you can see, VLOOKUP has returned the appellation of the employee having 104 as his ID .
Q42) Explain the approximate match with an example.
For an approximate catch, VLOOKUP will fetch values when there are no exact matches of the given loopup_value. For an approximate equal, set the range_lookup value to TRUE. Remember that the mesa must be sorted in ascending decree for VLOOKUP to do an approximate match. so here, VLOOKUP basically starts to look for an approximate match of the given search value and the, stops at a value that is future largest of the given search rate. It then moves into that quarrel to return the value from the column that has been specified. The be visualize shows an model of an approximate match by VLOOKUP :
- Follow the same steps specified for exact match
- For the range_lookup value, use TRUE
- The function will be: =VLOOKUP(55, A12: C15, 3, TRUE)
The search value is 55 and the adjacent largest of the search value show in the beginning column is 40. therefore, the output is ‘ second gear Class ’ .
Q43) Can you use VLOOKUP for multiple tables?
Yes, you can use VLOOKUP for multiple tables american samoa well. In lawsuit you have two search tables, make named ranges for each table, and then use the IF function to select between each table based on some given condition. Click here to know more about this .
Q44) How do you perform a horizontal lookup in Excel?
To perform a horizontal search, you will have to make manipulation of the HLOOKUP affair. SYNTAX: HLOOKUP ( lookup_value, table_array, row_index_num, [ range_lookup ] ) here ,
- lookup_value gives the value to be looked out for
- table_index is the range from where the data is to be taken
- row_index_num specifiesthe row from which you want to fetch the value
- range_lookup is a logical value i.e TRUE or FALSE( genuinewill find the closest match;FALSE checks for exact match)
Q45) How will you fetch the current date in Excel?
You can make use of the TODAY affair. This function will return the current date in the MS Excel date format. SYNTAX: today ( ) EXAMPLE:
Q46) How does the AND function work?
The AND serve in Excel is used to whether a given condition or a hardened of conditions is TRUE or not. In encase all the conditions are satisfied, this function will return a boolean TRUE. SYNTAX: AND ( logical1, [ logical2 ], … ) where, logical1, logical2, … are conditions from 1-255 that you want to check EXAMPLE:
Q47) What is the What If Analysis?
What If analysis is the technique of performing changes to one or more rule confront in the cells in order to see how it affects the result of those formulas in the worksheet. Excel provides three types of What If psychoanalysis tools :
- Goal Seek
- Data Tables
Scenarios and Data Tables take a hardening of inputs to check for the electric potential results. Scenarios can work with many variables but stimulation values can be at the soap 32. Data tables, on the other hand, sour with equitable one or two variables but can accept many clear-cut values for each of those variables. Goal Seek, in contrast to Scenarios and Data Tables, takes the outputs and determines the possible inputs for the same .
Q48) Can you create shortcuts for most frequently used formulas?
Yes, you can do it by customizing the Quick Access Toolbar. To customize it, right-click anywhere on the Quick Access Toolbar and select the Customize Quick Access Toolbar option. You will see the following window : From here, blue-ribbon Formulas and then choose any formula that you wish to create a shortcut for .
Q49) What is the difference between formulas and functions in Excel?
Formulas are that are defined by the user that is used to calculate some results. Formulas either be elementary or complex and they can consist of values, functions, defined names, etc. A officiate, on the other hand, is a built-in patch of code that is used to perform some detail action. Excel provides a huge number of built-in functions such as SUM, PRODUCT, IF, SUMIF, COUNT, etc .
Q50) How do you use wildcards with VLOOKUP?
Wildcards can be used when you are not sure of the demand search value. In order to use wildcards in Excel, you should make consumption of the “ * ” symbol. For exemplar, in the table that you see in the persona below, if you enter “ erg ” and then use wildcards with it, VLOOKUP will fetch the output that corresponds to “ Sergio ”.
Read more: Top 4 for interview questions in 2022
This brings us to the end of this article on Excel Interview Questions. I hope you are clear with all that has been shared with you. Make sure you practice as much as possible and revert your experience. Got a interview for us ? Please citation it in the comments section of this “ Excel Interview Questions ” blog and we will get back to you vitamin a soon as possible. To get in-depth cognition on any trending technologies along with its diverse applications, you can enroll for exist Edureka MS Excel Online training with 24/7 patronize and life access .