MS Excel
Excel Interview Question
Part 1
1. Explain MS Excel in brief.
Microsoft Excel is a tablet or a computer program that stores data in a table. Excel is compatible with different operating systems, including Windows, macOS, IOS, and Android.
MS Excel has some of the main features:
Graphing tools
Integrated features (SUM, DATE, COUNTIF)
Permit Data Analysis via tables, charts, filters
Visual Simple Application Accessibility (VBA)
Worksheet process and versatile workbook
Enables fast validation of data
2. What do you mean by cells in an Excel sheet?
The junction of a column with a row to insert the information is called a cell. In a single sheet of excel, there are a total of 1,048,576 x 16,384 cells.
3. Explain what a spreadsheet is.
Spreadsheets are cell compositions that assist you in data management. There may be more than one worksheet in one workbook. You can see all the sheets and the names you gave them at the bottom of the window. Look at the following picture:
4. What do you mean by cell address?
The cell address of an Excel sheet is the address obtained by combining the row number and the column alphabet. A different cell address is given for every cell in an MS Excel sheet.
5. Can you format MS Excel cells? If yes, then how?
Yes, you can format MS Excel cells. You may use the commands in the Font category of the Home tab to format these cells. You can see the following options when you open the Font window:
6. Can you add comments to a cell?
You can add notes, yes. Select the cell, right-click on it, and then select the New Comment option to add comments to the cell. All those people with access to the Excel sheet can see these remarks.
Comments for many reasons are used:
To explain the function of cells
To clarify a cell formula
To leave notes about a cell for other users
7. What is Ribbon, and where does it appear?
The Ribbon is essentially your main Excel interface, and you can find it at the top of the Excel window. It provides users with direct access to many of the main commands. It includes several tabs, including Paper, House, Display, Insert, etc. You can also modify the ribbon according to your needs. Right-click on the Ribbon to change and choose the option “Customize Ribbon.”
8. How do you freeze panes in Excel?
MS Excel will freeze panels that allow you to see rows and column headings even while scrolling long distances on the document. Follow the steps given to freeze panes in Excel:
Choose the columns and rows you would like to freeze
Then, in the View tab, pick Freeze Pane,Here you can see three options for freezing rows and columns selectively, as shown in the following picture:
9. How do you add a Note to a cell?
To add a Note, right-click on the cell and pick it. Then select New Note and type in any note you like. If you want to delete the Note, follow the same process and choose the option Delete Note. The Notes appear in the top right corner of the cell with a red triangle.
10. Can you protect workbooks in Excel?
Yes, you can safeguard workbooks. For this, Excel offers three options:
You can access workbooks with passwords.
You may prevent adding, removing, hiding, or unhiding sheets.
Excel offers a defense against changing window sizes or positions.
11. How do you create dropdown lists in Excel?
Follow the steps given to build drop-down lists:
Click on the Ribbon Data tab.
Click on Data Validation from the Data Tools Community.
Setup>Allow>List to navigate
Pick the array of the source list.
12. Explain Pivot tables along with their features.
Pivot tables are statistical tables containing tables with detailed data. The summary can depend on any field like revenue, averages, amounts, etc., which gets efficiently and intelligently described in the pivot table.
Features:
The following are some of the attributes of Excel Pivot Tables:
Enable exact data to be displayed for analysis
Offer different viewing angles
Let you concentrate on important information
Data comparison is very realistic
Various trends, link, data trend, etc., may be recognized in pivot tables
You should create immediate data
Reports correct
Serve the Pivot charts basis
13. How do you generate Pivot Tables?
You need first to prepare the data in a tabular format to construct a pivot table. Bear in mind during data preparation the following points:
Set the data into columns and rows.
For each column, the first row should have a single heading.
Columns should contain only one data form.
For a single recording, only Rows must have data.
No rows should be empty.
Columns should not be exclusively empty.
Data should be separated from other data present in the sheet to establish a pivot table.
14. What are Pivot charts in MS Excel?
MS Excel charts are tools to display the data in different ways. These diagrams may include Bar, Pie, Field, Line, Doughnut, etc.
15. Can you create a pivot table using tables from different worksheets?
You could also build a pivot table for the table from separate sheets if the two sheets are from the same workbook.
16. Is it possible to see the details of the results displayed in a pivot table?
Yes, you can get a detailed view of the results displayed by the pivot tables in Excel. To see the results, double-click on the value, and you will see that a new sheet with a new table has come up with details of the factors which led to this specific outcome.
17. How are Pivot tables used to filter data?
You can filter data according to your requirements with Excel Pivot tables. Place the field on which you want the data to be filtered. Then open the drop-down list of the field you put in the Filter area from the pivot table and choose your line.
18. How to stop automatic sorting in Pivot tables?
The data in Pivot Tables get automatically sorted by Excel. If you don’t want Excel, open the Row Labels or Column Labels drop-down menu and select More Sort Options. You can see the opening of the Type dialogue box. Click Additional Options and automatically unselect the Sort option.
19. What do you understand by Excel functions?
In Excel, functions are used to carry out those tasks. Excel has several integrated features that are used to measure the outcomes of different formulas, helping to save time. Furthermore, these functions allow the execution of formulas that were difficult to write down manually quite simple.
20. What is the operator precedence of formulas in Excel?
Excel formulas are carried out under the BODMAS law. As many of us know, BODMAS reflects the addition and subtraction of the Brackets Order Division. That implies, the brackets are performed first in every formula (if present) and then multiply, divide, etc. The following picture provides an example of this:
As you can see, the performance is 36, which means that 5+7 gets added first and three times more. If the brackets are not defined, the result will first be multiplied by 3 to 5 and added 5, i.e., 15 + 7, to 22.
21. What are the various categories of functions available in Excel?
The Excel function categories are as follows:
22. What is VLOOKUP in Excel?
VLOOKUP is an Excel function used for researching and generating data from a particular set. V is Vertical in VLOOKUP, and we should organize data vertically to use this feature. VLOOKUP is incredibly useful if we need to seek a certain amount of data from a large number of data.
23. How does the VLOOKUP function work?
In Excel, the VLOOKUP function has a lookup value, and in the most left column begins to look for it. If the value of that search first occurs, VLOOKUP will step right, i.e., in the search value row. It continues until the user’s stated column number is returned. This function matches correct and estimated search values. The default match, however, is approximate.
Here,
lookup_value: The value you want to check for is the lookup value
table_index: Table index is the set of data to be taken from
col_index_num: col index num defines the column you want to extract the value from
range_lookup: Logical value, i.e., TRUE or FALSE, is the range lookup (TRUE will find the closest match; FALSE checks for exact match)
24. Can you use VLOOKUP for multiple tables?
Yes, for many tables, you can also use VLOOKUP. If you have two search tables, build named fields for each table and use the IF function to choose from each table based on a specific condition.
25. How will you obtain the current date in Excel?
The TODAY function obtains the current date. The current date gets returned in the date format of MS Excel.
26. What is the What If Analysis?
What if Analysis is the method used to alter one or more of the cellular formulas to see how it impacts the worksheet outcome of such formulas? Excel offers three kinds of What if methods for Analysis:
Scenarios: Scenarios and data tables provide a collection of inputs for future outcomes. Many variables can function with scenarios, but the maximum input values are 32.
Goal Seek: Goal Seek takes outputs and decides potential inputs to the same, contrary to Scenarios and Data Tables.Data Tables: Data tables operate for just one or two variables, but they can accept several different values for all of these variables.
27. What is the difference between formulas and functions in Excel?
Formulas are specified by the consumer who uses some results to measure them; either simple or complex formulas can consist of values, functions, names, etc.
On the other hand, a Function is a built-in code that gets employed in some actions. Excel has many integrated functions like SUM, PRODUCT, IF, SUMIF, COUNT, and so forth.
28. What is the benefit of using formulas in Excel sheets?
The Excel sheet calculation not only lets you track the final “sum up” of the number but automatically calculates the number replaced by a new digit or number. The complex calculations are made simple with Excel sheets, such as the payroll deduction or the student averaging results.
29. How can you disable the automating sorting in Pivot tables?
Automated sorting of pivot tables to be disabled by:
30. How is cell reference useful for calculation?
For calculation, the cell reference prevents writing the data repeatedly. You must direct Excel to that data’s specific position when you write any formula for a particular function. The cell reference point is called this spot. Each time the cell adds a new value, the cell is determined based on the reference cell’s formulation.
31. What does the function “AND” in Excel do?
In Excel, the AND Function is a logical function that tests/evaluate multiple conditions and returns “true” or “false” depending on whether or not they are met. The AND function is defined as “=AND(logical1,[logical2]…),” where “logical1” is the first condition to be evaluated.
If all of the logical values or conditions are true, then it returns “true.” If any of the logical values or conditions are evaluated as false, it returns “false.” Depending on the circumstances and the requirement, it may have more logical values. Non-zero values are treated as “true” when evaluating numbers; zero is considered false.
32. What is the use of a Macro in Excel?
The use of macros allows you to perform a series of tasks repeatedly, and Macros can be used to automate repetitive tasks and instructions.
33. What are the two macro languages in Microsoft Excel?
VBA and XLM (Visual Basic Applications). XLM was used in earlier versions of Excel. VBA was introduced in Excel 5 and is now widely used.
34. In MS-Excel, how do you apply the same formatting to every sheet in a workbook?
Right-click the ‘Worksheet tab’ and select ‘Select All Sheets.’ Any formatting you do now will be applied to the entire workbook, and select only the sheets that require formatting to apply to a specific group of sheets.
Advanced Excel Interview Questions
Part 2
1. How do you select multiple cells in Excel?
There are a few notable methods to select multiple cells in Excel, and they are discussed below:
Click and drag: To choose a specific group of cells, click on a cell, hold down the left mouse button, and drag the cursor to cover those cells. To choose the range, let go of the mouse button.
Shift key: Choose the first cell in the range, then choose the last cell in the range while holding down the Shift key. The initial and last cells themselves are also included in the selection of cells between them.
Ctrl key: To select a specific cell, click it while depressing the Ctrl key. You can choose non-contiguous cells or ranges by doing this.
2. How do you insert a new row or column in Excel?
These procedures should be followed in Excel to add a new row or column:
Adding a Row:
Where you wish to place the new row, choose the row below. Select row 4, for instance, if you wish to add a row between row 3 and row 4.
To access the context menu, perform right-click on the chosen row.
Go to the context menu and select ‘Insert’ or ‘Insert Cells.’ This will add a new row above the currently chosen row.
3. How do you delete a row or column in Excel?
The following techniques can be used in Excel to delete a row or column:
Remove a Row:
You can delete a row by selecting the entire row. To select the complete row, click on the row number on the left side of the Excel window.
To access the context menu, perform right-click on the chosen row.
Make your selection from the context menu for ‘Delete’ or ‘Delete Cells.’ The specified row will be deleted after this action.
4. What is the SUM function used for?
Excel’s SUM function is used to determine the total or sum of a set of numbers. It enables you to easily add up several values that are contained in a given range or a collection of distinct cells.
5. What is the difference between relative and absolute cell references?
Relative Cell Reference: Excel’s default type of reference is a relative cell reference. A relative reference in a formula moves the formula’s location in relation to the cell into which it is copied or filled.
In contrast, an absolute cell reference does not change when duplicated or filled and remains constant. Dollar signs ($) are used in the cell reference before the column letter and row number to indicate it.
6. What is the difference between the COUNT and COUNTA functions?
The difference between the COUNT and COUNTA functions is defined further:
COUNT COUNTA
Count cells with numeric values only. Count Non empty cells,including text and other data types.
Ignores text and non numeric values. Includes both Numeric and Non numeric values.
7. What is conditional formatting in Excel?
Excel’s conditional formatting tool enables you to format cells according to particular requirements or specifications. It gives you a tool to graphically emphasize or highlight data that satisfies predetermined criteria, making it simpler to see patterns, trends, or outliers in your spreadsheet.
9. How do you remove duplicates in Excel?
There are some processes to remove duplicates in Excel, and they are discussed further. Use Excel’s built-in Remove Duplicates function to eliminate duplicates and retain only unique data. Here is how to go about it:
Choose the cell range containing the data you wish to eliminate duplicates from. This range may consist of one or more columns.
Open the Excel ribbon and choose the ‘Data’ tab. Select ‘Remove Duplicates’ from the list of ‘Data Tools’ options. There will be a dialogue box. All columns in your specified range will be automatically selected in the dialogue box. Make sure the ‘My data has headers’ option is selected if your data has headers. Review the columns that you chose to remove duplicates from. You can uncheck any columns that you don’t want to be used for duplication checks if required.
10. How do you use the VLOOKUP function in Excel?
Excel’s VLOOKUP function may be used to find a value in a table’s leftmost column and return a similar value from a different column. It is very beneficial for carrying out search operations and getting data from huge data sources.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Where,
lookup_value: The value you want to look up. Select the cell whose value you want to match.
table_array: The table or range where Excel should search for the lookup_value. Here, select the range in which you want to match the lookup value to what you want as return- second-column respective values or third-column respective values, etc.
col_index_num: This represents what value you want in return, keep in mind that for the second column, it will be 2, and for the third column it will be 3, and so on.
range_lookup: [Optional] This parameter can be either TRUE or FALSE (or 1 or 0). It specifies whether you want an exact match (FALSE/0) or an approximate match (TRUE/1). If omitted, Excel assumes TRUE/1 by default, which means it will find the closest match if an exact match is not found.
11. What Charts are available in Excel?
Excel has many types of charts, including Bar charts display data with horizontal bars while column charts display data with vertical bars. Both are used to compare values across categories. Pie charts, on the other hand, are utilized to demonstrate the distribution of categories in percentage or proportion. Pie charts are most effective when there are only a few categories, and they are easy to read at a glance.
12. What are Comments, and How Do You Use Them?
Comments in Excel are annotations that provide additional information about a cell’s content. To use them, right-click the target cell, select “Insert Comment,” and enter your note. The comment is represented by a small red triangle in the corner of the cell. Hover over it to view the comment’s content.
13. Elaborate on the IF function in Excel?
Excel’s IF function enables you to run logical tests and return various values depending on the outcome. You can use your spreadsheet to execute conditional computations and make judgments thanks to it.
Syntax: IF(logical_test, value_if_true, value_if_false)
14. What is a pivot table in Excel?
A pivot table in Excel is an effective tool for data summary that enables structured, interactive analysis and summarization of enormous volumes of data. It offers a mechanism to glean insightful information, identify trends, and reach data-driven conclusions. An input dataset is organized into a structured table with rows, columns, and values using a pivot table. Without changing the original dataset, it enables you to dynamically group, filter, and change the data.
15. How do you add calculated fields in a pivot table?
To add calculated fields in a pivot table:
1.Select Pivot Table: Start by selecting the pivot table.
2.Go to PivotTable Tools: Navigate to the “PivotTable Tools” or “Analyze” tab.
3.Click “Fields, Items, & Sets”: Choose this option from the toolbar.
4.Select “Calculated Field”: Click on “Calculated Field.”
5.Create Calculated Field: Name your calculated field, define the formula, and click “Add.”
6.Field Appears: The calculated field appears in your pivot table for analysis.
Adding calculated fields enhances data analysis in pivot tables.
17. What is data validation in Excel?
The ability to limit the kind of data that may be entered into a cell or a range of cells in Excel is known as data validation. By specifying particular criteria or standards for data entry, it helps to assure data correctness, consistency, and integrity. You can enforce data rules, avoid mistakes, and enhance data quality in your Excel spreadsheets by using data validation.
18. How do you create a drop-down list in Excel?
There are a few steps mentioned to create a drop-down list in Excel:
Choose the cell or cells in which the drop-down menu will be shown.
Open the Excel ribbon and choose the ‘Data’ tab.
Now, simply left-click on the ‘Data Validation’ icon located in the ‘Data Tools’ category. This action will prompt a dialogue box to appear.
Navigate to the ‘Settings’ tab in the dialogue box.
Choose ‘List’ from the ‘Allow’ dropdown menu.
Enter the values you wish to see in the drop-down list in the ‘Source’ column. These are your two choices:
Directly type the values, separating them with commas (for example, Option 1, Option 2, Option 3).
As an alternative, you may choose from the drop-down list a group of cells that contain the values you want to utilize. For instance, you would put ‘=$A$1:$A$3’ in the ‘Source’ box if your choices were located in cells A1 to A3.
If you want the drop-down arrow to show directly in the cell, make sure the ‘In-cell dropdown’ selection is selected. Users will still be able to access the drop-down list by pressing Alt + Down Arrow when the cell is chosen if you uncheck this option.
For the data validation to take effect and the drop-down list to be created, click ‘OK’.
21. What is the Analysis ToolPak in Excel?
A Microsoft Excel add-in that offers more data analysis and statistical tools is called The Analysis ToolPak. It is intended to support users in carrying out challenging data analysis tasks and drawing well-informed conclusions from the outcomes. You must enable the Analysis ToolPak in Excel in order to utilize it. Here is how to go about it: Activate Excel and select the ‘File’ tab. From the menu, choose ‘Options’. Select ‘Add-Ins’ in the Excel Options dialogue box’s left-hand menu. Select ‘Excel Add-ins’ from the ‘Manage’ drop-down option at the bottom of the dialogue box, then click the ‘Go’ button. Check the box next to ‘Analysis ToolPak’ in the Add-Ins dialogue box, then click ‘OK’.
22. What is the IFERROR function used for?
Excel uses the IFERROR function to address formula mistakes that might happen. If a formula produces an error, such as a #DIV/0! error (division by zero) or a #VALUE! error (invalid value), you can provide a value or an action to do. The syntax IFERROR function is the following:
IFERROR(value, value_if_error)
23. What is the difference between the lookup and index function?
Lookup: The LOOKUP function requires that the data be sorted and operates on a one-column or one-row range. Based on the position inside the range, it delivers the value that exactly matches the lookup value whereas The INDEX function lets you specify the row and column numbers to obtain a value from a two-dimensional range (table or array). It gives back the value found where the specified row and column meet.
24. How is VLOOKUP different from LOOKUP?
Users have the option to utilize VLOOKUP for searching a value within the leftmost column of a table. Users have the option to utilize VLOOKUP for searching a value within the leftmost column of a table. The value is then returned from left to right, whereas, in the meantime, the user can search for data in a row or column using the LOOKUP function. It brings up the value in a different row or column.
25. How many report formats are available in Excel?
There are three formats:
Compact Form
Outline Form
Tabular Form
26. What is the DATEDIF function in Excel?
The Excel Date/Time functions section includes the DATEDIF Function. When working as a financial analyst, we might need to know how many days, months, or years separate two dates. We can compute the difference with the aid of the DATEDIF function.
27. What is a Slicer and how does it work?
Slicers are visual filtering tools in Excel that let you filter and work with data in pivot tables and pivot charts. By choosing particular values or categories, it offers a user-friendly approach to slice and dice data, making it simpler to analyze and examine the data. Slicers allow you to easily filter and analyze data in a pivot table or pivot chart without having to use complicated calculations or filtering tools. Slicers provide you with a simple, visual approach to examine your data, zero in on certain subsets, and discover new patterns by filtering and enhancing the view of your data in real-time.
28. What is the What-If analysis?
Change the input values in a formula or collection of formulas to explore various possibilities using Excel’s What-If Analysis function. It enables you to comprehend the effects of various inputs on the outcomes and aids in the development of sound judgments based on such circumstances. These flexible What-If Analysis tools let you experiment with various scenarios by altering the inputs and evaluating the results. They are very helpful in financial modeling, forecasting, budgeting, making decisions, and any other situation where you need to understand how your calculations respond to input data.
29. Write the VBA function to calculate the area of a circle?
Sub area()
Dim a as Float
Dim pi as Float
Dim r as Float
a = r*r*pi
MsgBox ‘The Area of The Circle is: ‘ &a
End Sub
30. What are named ranges?
In Excel, named ranges are cell ranges, formulae, or constants that have been given user-defined labels or names. It is simpler to comprehend and deal with the data if you give a range of cells or a formula a meaningful name rather than just using the cell references (such as A1, B2, etc.).
The following steps can be taken to create a Named Range:
Select the range that you intend to give a name.
From Ribbon, select Formulas
Click on ‘Define Name’ from the ‘Defined Names group’.
Give any name of your choice
31. What is the operator precedence of formulas in Excel?
To establish the order in which operations are carried out, Excel formulas adhere to a specified order of precedence, also known as operator precedence. From highest to lowest, the Excel operator precedence is as follows:
Expressions that are surrounded by brackets are evaluated first. The innermost brackets are examined first when they are nested.
Exponentiation (): Operations involving exponentiation are carried out next. For instance, the exponentiation operation is carried out first in the expression ‘=23,’ resulting in 8.
Division (/) and multiplication (): Operations involving multiplication and division are analyzed from left to right. For instance, the multiplication (6*3) is done first in the formula ‘=63/2’, then the division (/2), which yields the result 9 as the result.
Operations for addition (+) and subtraction (-) are assessed from left to right. For instance, the subtraction (10-4) comes first in the formula ‘=10-4+2’, then the addition (+2), and the result is 8 as a consequence.
String concatenation (&): The ampersand (&) operator is used when string concatenation is desired. It is assessed from left to right. For instance, the concatenation operator (&) is used to combine the two strings in the formula ‘= ‘Hello’ & ‘World,’ producing ‘Hello World.’
32. What are the exact match and approximate match?
Aspect Exact Match Approximate Match
Definition Finds a value that exactly matches the lookup value. Finds the closest match less than or equal to the lookup value.
Use Case Ideal for finding precise data points, like exact numbers or names. Used when searching for values within a range or category.
Result Returns the corresponding data exactly matching the lookup value. Returns the closest matching value and associated data.
Notation Often represented as “FALSE” or “0” in the “range_lookup” argument. Represented as “TRUE” or “1” in the “range_lookup” argument.
Example Finding a specific customer’s account balance. Categorizing test scores into grade ranges.
33. What is the difference between formulas and functions in Excel?
Formulas are defined by the user and used to calculate certain results. Formulas are either simple or complex and can consist of values, functions, defined names, etc. A function, on the other hand, is a built-in piece of code that is used to perform a specific action. Excel provides a huge number of built-in functions like SUM, PRODUCT, IF, SUMIF, COUNT, etc.
34. What are Wildcards in Excel?
Wildcards in Excel are special characters that represent unknown or variable values when searching or filtering data. They are used as placeholders to match patterns or partial strings within a larger text or value. Wildcards are often used in functions such as COUNTIF, SUMIF, VLOOKUP, and others.
Here are the two main wildcards used in Excel:
Asterisk (*) wildcard: An asterisk (*) represents any number of characters (including the null character) in a string.
For instance, when you utilize the formula ‘=COUNTIF(A1:A5, ‘app*’), it will tally all cells within the range A1 to A5 that start with the word ‘app’ and are followed by any additional characters.
Question mark (?) Joker: A question mark (?) represents a single character in a string.
For example, if you use the formula ‘=COUNTIF(A1:A5, ‘ap?le’)), it will count all cells in the range A1-A5 that contain ‘ap’ and any single character, then ‘le’. ‘.
35. Explain the difference between SUBSTITUTE and REPLACE functions?
The SUBSTITUTE function is used to replace an occurrence of the given text in a string with new text.
Syntax: SUBSTITUTE (text, old_text, new_text, [copy_number])
=SUBSTITUTE (‘Hello, world!’, ‘world’, ‘Excel’) will return ‘Hello, Excel!’.
The REPLACE function is used to replace certain text in a string with new text.
Syntax: REPLACE (old_text, start_number, number_of_characters, new_text)
Example: =REPLACE (‘Hello, world!’, 8, 5, ‘Excel’) will return ‘Hello, Excel!’.
36. Write the function to calculate the compound interest?
Excel’s FV function calculates the future value of an investment or loan based on periodic payments, a fixed interest rate, and a certain number of periods. It is often used in financial planning, investment analysis, and loan calculations.
The syntax of the FV function is as follows: FV (rate, nper, pmt, [pv], [type])
interest: period-based interest. This should be consistent with the specified number of cycles. nper: total number of payment periods. pmt: payment made in each period. It can be a constant value or a cell reference. [dv]: (optional) present value or capital of the investment or loan. If omitted, it is assumed to be 0. [type]: (Optional) Specifies whether payments are made at the beginning (1) or end (0) of each period. If omitted, it is assumed to be 0 (end-of-period payments).
37. Describe the volatile functions in Excel?
A volatile function in Excel is a function that recalculates when a change occurs in a workbook, regardless of whether the input values have actually changed. These operations are called volatile because they can significantly affect the performance and responsiveness of the workbook, especially when dealing with large amounts of data or complex formulas.
Here are a few examples of the volatile function:
NOW(), RAND(), TODAY(), OFFSET(), etc.
38. Write a function to extract the domain name from your Gmail?
=SUBSTITUTE(MID(A1, FIND(‘@’, A1) + 1, LEN(A1)), ‘.’, ”)
39. Write the formula to extract the first name from a name?
=LEFT(A1, FIND(‘ ‘, A1) – 1)
40. When do we get a reference error?
A Reference Error typically occurs in spreadsheet programs like Excel when a formula or function references a cell that doesn’t contain valid data or a valid cell address. This error happens when the referenced cell is blank or contains an error itself, making it impossible for the formula to calculate or return a result.
41. What is ABS function in Excel?
Excel’s ABS function is used to return the absolute value of a number. The absolute value of a number is its distance from zero, whether the number is positive or negative. The ABS function ensures that the result is always positive or zero.
42. Write a function to get the last date of the month?
Use the EOMONTH function to display the last date of the month in Excel. EOMONTH means ‘end of month’ and is specifically designed to return the last day of the corresponding month.
The syntax of the EOMONATO function is as follows:
EOMONTH(start_date, months)
43. Can we use VBA to edit the recorded macro?
Yes, you can use VBA (Visual Basic for Applications) to edit a macro saved in Excel. When you record a macro in Excel, it creates VBA code that represents the actions performed during recording. You can then use and edit this VBA code to make changes or improve the functionality of the recorded macro.
44. Use conditional formatting to highlight the top 10 selling products in each region steps followed?
Select the sales data range, including the product and region columns.
Click Home > Conditional Formatting > New Rule.
In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
True Box Formula.
Click Format and select the desired formatting for the top 10 selling products. For example, you can choose to highlight them in green.
Click OK twice to close the dialog boxes.
45. In the Format values where this formula is true box, enter the following formula?
To achieve returning a specific value based on a condition, Excel’s IF function can be used. There is no built-in “True Box” formula. The IF function allows you to specify a condition and return one result if it’s true, and another result if it’s false. For example, you could use IF function to return a value of “Yes” if a cell is greater than 10, and “No” if it is not. This can be a useful tool in data analysis and decision making.
Example: True Box Formula : =RANK($C2,$C:$C,1)<=10
where C2 is the cell containing the product sales for a specific region.
46. To design a user-friendly Excel dashboard, you should consider the following?
To create a user-friendly Excel dashboard, keep the layout simple and organized with clear titles and labels. Group related information together and use consistent formatting to avoid confusion. Use charts and graphs to visually represent the data and make it easier to understand. Limit the amount of information displayed on one page to avoid overwhelming the user, and consider adding interactive features for further user engagement. Regularly update the dashboard to ensure it remains relevant and useful to its intended audience.
47. Why Would You ‘'Freeze Panes’” in Microsoft Excel?
Freezing a pane in a spreadsheet keeps a designated section of data stationary as you move through other information. This feature is often employed to ensure a particular element remains in place, such as an index or key data. By using freeze panes, users can navigate through a sheet without losing track of that important information, improving overall productivity and organization.
48. What Is a Surface Chart in Excel?
A surface chart is a 3D map used to display topological data that requires accurate coordinates. It provides a visual representation of the terrain, highlighting peaks, valleys, and slopes. The chart can be used to facilitate analysis and visualization of the topography of an area. It is especially useful for geology, geography, and earth science professionals who want to study and interpret landforms and identify patterns. The chart’s 3D nature allows for more precise analysis of data, providing insights that might not be evident from 2D visualizations.
49. What is a Bubble Chart in Excel?
A bubble chart is a type of scatter chart that represents a third variable through the size of the scatter dots. This chart allows for the tracking of three variables: X, Y, and the third variable indicated by the size of the bubbles. The larger the bubble, the larger the value of the third variable. This chart is useful for displaying multiple sets of data and comparing them visually.
50. What Is a Donut Chart in Excel?
A donut chart is a type of chart that uses concentric rings, similar to a pie chart but without a center. It can be a powerful visualization tool for presenting data in Excel.
51. How Can You Import Data From Another Workbook?
Microsoft Excel allows easy importing of data from another workbook through external references. By typing the source workbook name and the sheet from where data is to be pulled, one can create an external reference between workbooks. The command is simply written as follows: “data from: =[SourceWorkbook.xlsx]Sheet1!$A$1”. It is important to note that the file extension must be included in the source workbook name.
52. What Is a Logical Test in Excel?
The IF() function in Excel can be used to create logical tests where an output is produced based on a specified scenario. For example, the code =IF(A1>0,”Yes”,”No”) tests whether a number in cell A1 is greater than 0 or not. If it is, the output will be “Yes” and if it is equal to or less than 0, the output will be “No”. This function can be useful in a variety of situations where conditional outputs are required.
53. What Is a Timeline Used for in Excel?
A timeline in Excel is used to represent a chronological sequence of events. It can visually display important dates and milestones on a horizontal or vertical axis, allowing users to quickly understand the sequence of events and their durations. Timelines can be applied to many industries and projects, such as project management, historical research, and marketing campaigns. Excel provides various tools and templates to create different types of timelines, including Gantt charts, milestone charts, and chronology charts.
55. What are the Major Types of Errors You Can Encounter in Excel?
Some of the major types of errors you can encounter in Excel are #DIV/0! (attempting to divide by zero), #REF! (a cell reference error), #NAME? (a formula error), #VALUE! (an error in a calculation due to an incorrect data type), and #N/A (a value is missing or cannot be found). It is important to identify and fix these errors in order to ensure the accuracy and integrity of your spreadsheet and its calculations.
56. What Does 'Merge' Do in Excel?
The “Merge” function in Excel helps to combine two or more adjacent cells into one cell. This is particularly helpful when dealing with lengthy texts or titles that are too long to fit in a single cell. The merged cell’s size automatically adjusts to accommodate the combined text. When you use the Merge function, the content from the upper-leftmost cell in the selected range will be preserved, and all other contents from the remaining cells will be removed. This function is located in the “Home” tab under the “Alignment” section in the ribbon.
57. What Does It Mean to ‘“Hide’” a Sheet?
Hiding a sheet in a workbook means that it is no longer visible in the sheet navigation, but can still be accessed and edited unless additional protection is put in place. This is useful for archiving old information while keeping it accessible if needed.
58. What Is the Order of Operations in Excel?
The order of operations in Excel is as follows: first, any calculations inside parentheses are performed; second, any exponentiations are completed; third, multiplication and division are carried out from left to right; and finally, addition and subtraction are executed from left to right.
Remembering this order of operations is critical for getting accurate results in Excel. Invalid results can arise if the user does not follow the correct order.
59. How do you edit a formula?
To change a formula in a cell, double-click the cell to open the Formula Editor. You can then make your desired changes to the formula. The Formula Editor will automatically provide suggestions as you make changes, which can be helpful in remembering the correct parameters for the formula.
60. What is a radar chart?
A radar chart, also called a spider or web chart in Excel, is designed to compare different values for a single item. It consists of axes that emanate from a central point, each representing a specific metric. The axis’s length indicates the value of that metric, and all the axes meet at the center of the chart. This chart type is useful for displaying multiple data points and comparing them quickly. It provides a clear visual representation of different values and can help in identifying the strengths and weaknesses of the item being compared.
61. What is stock chart?
A stock chart in Excel depicts the price of a stock over time, helping investors and traders track their investments and make informed decisions. These charts can display various data points, such as opening/closing prices, high/low prices, and trading volume. They are a crucial tool for analyzing market trends and forecasting future stock behavior. With Excel’s customizable features, users can create a personalized and visually appealing stock chart to analyze their portfolio’s performance.
