Intermediate Level

Run down the process of creating a drop-down list in Excel?

To create a drop-down list in Excel:
1. Enter the list of items you want in the drop-down list in a column (e.g., A1:A5).
2. Select the cell where you want the drop-down list.
3. Go to the Data tab and click Data Validation.
4. In the Allow box, select List.
5. In the Source box, select the range containing the list items (e.g., “=$A$1:$A$5”).
6. Click OK.
Now, the selected cell will have a drop-down arrow, allowing you to choose from the list.

What are the methods you adopt to fill the null values in Excel?

You can fill null (blank) values in Excel using various methods, depending on the context:
Method 1: Fill with a specific value - Select the range. - Press “Ctrl + H” to open the Find and Replace dialog. - Leave the Find what box empty, enter the value in the Replace with box, and click Replace All.
Method 2: Fill with the previous value - Select the range. - Press “Ctrl + G” to open the Go To dialog, then click Special and choose Blanks. - In the formula bar, type “=“ and press the up arrow key, then press “Ctrl + Enter”.
Method 3: Fill using interpolation - Use Excel functions like “=IFERROR(AVERAGE(A1:A10), "No Data")” or similar to handle blanks logically.

Explain correlation & covariance, also explain how to compute it in Excel?

Correlation measures the relationship between two variables, showing how one variable changes
with respect to the other. It ranges between -1 and 1.
Covariance measures the directional relationship between two variables; it tells whether the
variables increase or decrease together.
Example:
To compute:
- Correlation: Use the formula “=CORREL(array1, array2)”.
- Covariance: Use the formula “=COVARIANCE.P(array1, array2)” for the population or
“=COVARIANCE.S(array1, array2)” for the sample.