Microsoft Excel

 How do you make a dropdown list in MS Excel? First, click on the Data tab that is present in the ribbon. Under
the Data Tools group, select Data Validation. Then navigate to Settings > Allow > List. Select the source you
want to provide as a list array.
 The order of operations used in MS Excel while evaluating formulas. MS Excel follows a standard math
protocol to evaluate a formula. This protocol is called “order of operations” – PEMDAS – ~Parentheses,
~Exponents, ~Multiplication, ~Division, ~Addition, ~Subtraction. MS Excel also applies some customization to
handle the formula syntax. The order in which MS Excel performs calculations can affect the return value of
the formula. First of all, Excel evaluates any expressions in parentheses. As we have seen in mathematical
formulae too, parentheses essentially override the normal order of operations. It prioritizes certain operations.
Next, Excel resolves cell references like A1 (cell address). It evaluates range references like A1:A10, making
them arrays of values. It also performs range operations like a union (comma) and an intersection (space).
Next, Excel performs –: -Exponentiation, -Negation, -% conversions, -Multiplication and division, -Addition and
subtraction, - Concatenation, - Logical operators.
 How can you split a column into 2 or more columns? You can split a column into 2 or more columns by
following the below steps: 1. Select the cell that you want to split. Then, navigate to the Data tab, after that,
select Text to Columns. 2. Select the delimiter. 3. Choose the column data format and select the destination
you want to display the split. 4. The final output will look like below where the text is split into multiple
columns.
 What is the difference between a function and a formula in Excel? A formula is a user-defined expression that
calculates a value. A function is pre-defined built-in operation that can take the specified number of
arguments. A user can create formulas that can be complex and can have multiple functions in it. For example,
=A1+A2 is a formula and =SUM(A1:A10) is a function.
 What is Power Pivot & Power Query? Power Pivot is an add-on provided by Microsoft for Excel since 2010.
Power Pivot was designed to extend the analytical capabilities and services of Microsoft Excel. Power Query is
a business intelligence tool designed by Microsoft for Excel. Power Query allows you to import data from
various data sources and will enable you to clean, transform and reshape your data as per the requirements.
Power Query allows you to write your query once and then run it with a simple refresh.
 What is macro in excel? Macro refers to an algorithm or a set of actions that help automate a task in Excel by
recording and playing back the steps taken to complete that task. Once the steps are stored, you create a
Macro, and it can be edited and played back as many times as the user wants. Macro is great for repetitive
tasks and also eliminates errors. For example, suppose an account manager has to share reports regarding the
company employees for non-payment of dues. In that case, it can be automated using a Macro and doing
minor changes every month, as needed.
 What is the difference between count, counta, and countblank in Excel?
The count function is very often used in Excel. Let’s look at the difference between count and its variants.
Count: it counts the number of cells that contain numeric values only. Cells that have string values, special
characters, and blank cells will not be counted
Counta: it counts the number of cells that contain any form of content. Cells that have string values, special
characters, and numeric values will be counted. However, a blank cell will not be counted.
CountBlank: as the name suggest, it counts the number of blank cells only. Cells that have content will not be
taken into consideration.
 How can you remove duplicate values in a range of cells? 1. To delete duplicate values in a column, select the
highlighted cells, and press the delete button. After deleting the values, go to the ‘Conditional Formatting’
option present in the Home tab. Choose ‘Clear Rules’ to remove the rules from the sheet. 2. You can also
delete duplicate values by selecting the ‘Remove Duplicates’ option under Data Tools present in the Data tab.
 What is the shortcut to add a filter to a table in EXCEL? The filter mechanism is used when you want to display
only specific data from the entire dataset. By doing so, there is no change being made to the data. The
shortcut to add a filter to a table is Ctrl+Shift+L.
 How are nested IF statements used in Excel? The function IF() can be nested when we have multiple conditions
to meet. The FALSE value in the first IF function is replaced by another IF function to make a further test.
 How to prevent someone from copying the cell from your worksheet in excel? If you want to protect your
worksheet from being copied, go into Menu bar > Review > Protect sheet > Password. By entering password
you can prevent your worksheet from getting copied.
 How will you write the formula for the following in Excel? - Multiply the value in cell A1 by 10, add the result
by 5, and divide it by 2. To write a formula for the above-stated question, we have to follow the PEDMAS
Precedence. The correct answer is ((A1*10)+5)/2. Answers such as =A1*10+5/2 and =(A1*10)+5/2 are not
correct. We must put parentheses brackets after a particular operation.
 How can you remove duplicate values in a range of cells? 1. To delete duplicate values in a column, select the
highlighted cells, and press the delete button. After deleting the values, go to the ‘Conditional Formatting’
option present in the Home tab. Choose ‘Clear Rules’ to remove the rules from the sheet. 2. You can also
delete duplicate values by selecting the ‘Remove Duplicates’ option under Data Tools present in the Data tab.
 How are nested IF statements used in Excel? The function IF() can be nested when we have multiple conditions
to meet. The FALSE value in the first IF function is replaced by another IF function to make a further test.
 Top 10 Excel functions for data analysis
SUMIF/SUMIFS: Sum values based on specified conditions, allowing you to aggregate data selectively.
AVERAGE: Calculate the average of a range of numbers, useful for finding central tendencies.
COUNT/COUNTIF/COUNTIFS: Count the number of cells that meet specific criteria, helping with data profiling.
MAX/MIN: Find the maximum or minimum value in a dataset, useful for identifying extremes.
IF/IFERROR: Perform conditional calculations and handle errors in data gracefully.
VLOOKUP/HLOOKUP: Search for a value in a table and return related information, aiding data retrieval.
PivotTables: Dynamically summarize and analyze data, making it easier to draw insights.
INDEX/MATCH: Retrieve data based on criteria, providing more flexible lookup capabilities than VLOOKUP.
TEXT and DATE Functions: Manipulate text strings and work with date values effectively.
Statistical Functions (e.g., AVERAGEIFS, STDEV, CORREL): Perform advanced statistical analysis on your data.
 Question: What is the difference between CONCATENATE and "&" in Excel? Answer: CONCATENATE and "&"
both combine text, but "&" is more concise. For example, =A1&B1 achieves the same result as
=CONCATENATE(A1, B1).
 Question: How can you freeze rows and columns simultaneously in Excel? Answer: Use the "Freeze Panes"
option under the "View" tab. Select the cell below and to the right of the rows and columns you want to
freeze, and then click on "Freeze Panes."
 Question: Explain the VLOOKUP function and when would you use it? Answer: VLOOKUP searches for a value
in the first column of a range and returns a corresponding value in the same row from another column. It's
useful for looking up information in a table based on a specific criteria.
 Question: What is the purpose of the IFERROR function? Answer: IFERROR is used to handle errors in Excel
formulas. It returns a specified value if a formula results in an error, and the actual result if there's no error.
 Question: How do you create a PivotTable, and what is its purpose? Answer: To create a PivotTable, select your
data, go to the "Insert" tab, and choose "PivotTable." It summarizes and analyzes data in a spreadsheet,
allowing you to make sense of large datasets.
 Question: Explain the difference between relative and absolute cell references. Answer: Relative references
change when you copy a formula to another cell, while absolute references stay fixed. Use a $ symbol to make
a reference absolute (e.g., $A$1).
 Question: What is the purpose of the INDEX and MATCH functions? Answer: INDEX returns a value in a
specified range based on the row and column number, while MATCH searches for a value in a range and
returns its relative position. Combined, they provide a flexible way to look up data.
 Question: How can you find and remove duplicate values in Excel? Answer: Use the "Remove Duplicates"
feature under the "Data" tab. Select the range containing duplicates, go to "Data" -> "Remove Duplicates," and
choose the columns to check for duplicates.
 Question: Explain the difference between a workbook and a worksheet. Answer: A workbook is the entire
Excel file, while a worksheet is a single sheet within that file. Workbooks can contain multiple worksheets.
 Question: What is the purpose of the COUNTIF function? Answer: COUNTIF counts the number of cells within
a range that meet a specified condition. For example, =COUNTIF(A1:A10, ">50") counts the cells in A1 to A10
that are greater than 50.
 MS-Excel (Google sheets knowledge is a plus)
👉 Lookups (vlookup, xlookup, hlookup and its use cases)
👉 Pivot tables, Pivot charts
👉 Power Query, Power Pivot
 👉 Conditional formatting
👉 Various charts and its formatting
👉 Basic VBA/Macro
👉 Major Excel functions/formulas (text, numeric, logical functions)
