Microsoft Excel Interview Questions for Data Analysts

This section presents a range of Microsoft Excel interview questions, covering various aspects of data manipulation, analysis, and presentation. These questions are tailored to assess the skills of data analysts.

Indicating Cell Comments

A red triangle in the upper-right corner of a cell indicates that a comment has been added to that cell. Hovering over the triangle will display the comment's text.

Absolute Cell Referencing

Absolute cell referencing in Excel ensures that a cell reference remains fixed when a formula is copied or filled. You achieve this by adding a dollar sign ($) before the column letter and/or row number in the cell address (e.g., `$A$1`, `$A1`, `A$1`).

Using the Dollar Sign ($) in Cell References

The dollar sign ($) in a cell reference determines whether the column, row, or both remain fixed when the formula is copied or filled.

Data Filtering Shortcut Keys

• Ctrl+Shift+L: Toggles filtering on or off for the selected range.
• Alt+Down Arrow: Opens the filter dropdown menu for a column.

Functions vs. Formulas in Excel

In Excel, functions are pre-built calculations (like SUM, AVERAGE, VLOOKUP). Formulas are user-defined expressions combining functions, cell references, operators, and constants to perform more complex calculations.

Clearing Cell Formatting Without Removing Data

1. Select the cells.
2. Go to the "Home" tab.
3. Click "Clear" (in the "Editing" group).
4. Choose "Clear Formats".

Getting Current Date and Time

Use the following methods to obtain the current date and time in Microsoft Excel:

• The `NOW()` function returns both date and time.
• The shortcut `Ctrl+;` inserts the current date.
• The shortcut `Ctrl+Shift+;` inserts the current time.

Excel Dashboards

An Excel dashboard provides a consolidated visual overview of key metrics and data. It's typically a single page or sheet displaying charts, tables, and key performance indicators (KPIs) to quickly communicate important information.

Splitting Data into Columns

Use the "Text to Columns" wizard (found in the "Data" tab) to split data in a single column into multiple columns. You can split data based on delimiters or fixed widths.

Chart Types in Excel

Excel offers various chart types for visualizing data, including:

• Line charts
• Bar charts
• Pie charts
• Scatter plots
• Area charts
• And many more...

Understanding Relative Cell References

Relative cell references (without dollar signs) change when a formula is copied or filled to a different location. The reference is relative to the formula's new position.

Dropdown Lists in Excel

Data Validation allows you to create dropdown lists in cells, limiting user input to a predefined set of options. This helps ensure data consistency and reduces errors.

LOOKUP Functions (VLOOKUP and HLOOKUP)

The VLOOKUP and HLOOKUP functions search for a value in a table and return a value from a specified column or row. These functions are valuable for extracting data based on a search criterion from larger datasets.

Using the Name Box

The Name Box (next to the formula bar) allows assigning names to cells or ranges, enhancing formula readability and maintainability.

Multiple Data Formats in Pivot Tables

PivotTables in Excel can be populated with data from various sources and formats (text files, worksheets, external data feeds).

Creating Hyperlinks in Excel

To create a hyperlink:

1. Select the cell or object.
2. Use the "Insert" tab's "Hyperlink" button, right-click and select "Hyperlink", or use the keyboard shortcut Ctrl+K.
3. Enter the URL, file path, or email address.

Access Control Lists (ACLs) on Network Interfaces

Regarding Access Control Lists (ACLs) on network interfaces:

1. You can apply as many ACLs as needed to an interface until memory is exhausted. (Incorrect)
2. Only one ACL can be applied to any interface. (Incorrect)
3. One ACL can be configured per direction (inbound or outbound) for each Layer 3 protocol on an interface. (Correct)
4. You can apply two ACLs to any interface. (Incorrect)

Applying an Access List to a Switch Interface

Which command correctly applies an access list to a switch interface? (Assuming the access-list is already defined)

1. ip access-list 101 out
2. access-list ip 101 in
3. ip access-group 101 in
4. access-group ip 101 in

Standard IP Access Lists

Which of the following is a valid example of a standard IP access list? (Standard ACLs filter based on the source IP address.)

1. access-list 110 permit host 1.1.1.1
2. access-list 1 deny 172.16.10.1 0.0.0.0
3. access-list 1 permit 172.16.10.1 255.255.0.0
4. access-list standard 1.1.1.1

Application Layer Protocols

Which of the following are TCP/IP protocols used at the Application layer of the OSI model?

1. IP, TCP
2. IP, TCP, TFTP
3. Telnet, FTP, TFTP
4. All of the above

SQL Constraints

Which SQL structure is used to constrain column values in a table?

1. LIMIT constraint
2. CHECK constraint
3. VALUE constraint
4. None of the above

Types of SQL CHECK Constraints

Which of the following is NOT a common type of SQL CHECK constraint?

1. System date checks
2. Range checks
3. Value list checks
4. Checks comparing values between two columns within the same table

XSLT Template Termination

In XSLT processing, what signals the end of a template?

1. SELECT ... FROM ... WHERE ... clause
2. {item, action} element
3. {for-each select} element
4. </html> tag

Standard Messaging Protocol

Which standard protocol is designed for sending messages of any type using any underlying transport protocol?

1. SOAP (Simple Object Access Protocol)
2. SGML (Standard Generalized Markup Language)
3. SQL (Structured Query Language)
4. ADO (ActiveX Data Objects)

XML Schemas

Which statement is NOT true about XML schemas?

1. They define data structure and content.
2. They define elements and attributes and their relationships.
3. They are XML documents themselves.
4. They have their own unique syntax and grammar.

Memory Allocation in C

How can you combine the following two C statements into one?

char *p;
p = (char*) malloc(100);

1. char p = *malloc(100);
2. char *p = (void*)malloc(100);
3. char *p = (char*)malloc(100);
4. char *p = (void*)(malloc(100));

Pointer Sizes in DOS

How many bytes are used by near, far, and huge pointers under DOS?

1. near=2, far=4, huge=4
2. near=4, far=8, huge=8
3. near=2, far=4, huge=8
4. near=4, far=4, huge=8

Pointer Arithmetic for Multi-dimensional Arrays

What is the correct pointer expression to access the element a[i][j][k][l] in a four-dimensional array a (assuming row-major order)?

1. ((((a+i)+j)+k)+l)
2. *(*(*(*(a+i)+j)+k)+l)
3. (((a+i)+j)+k+l)
4. ((a+i)+j+k+l)

Microsoft Interview: Sample Answers to Common Questions

This section provides example answers to frequently asked questions in a Microsoft interview, covering self-introduction, reasons for applying, strengths and weaknesses, and career goals. These are sample answers; tailor your own responses to your specific experiences and the job description.

Self-Introduction

Hello, my name is Ginni Bhatia, and I'm from Kurukshetra. I have an MCA in Computer Science (70% aggregate) from Panipat Institute of Engineering and Technology (affiliated with Kurukshetra University), a B.Com (VOC) from Dayanand Mahila College (65% aggregate, Kurukshetra University), and a PGDCA (6.9 CGPA) from Lovely Professional University. My hobbies include singing, dancing, and watching movies. I'm passionate about web design. My strengths are honesty, sincerity, responsibility, and a positive attitude. My weakness is that I can sometimes be lazy. I have over six years of experience at Accenture, where I contributed to the development of an award-winning trading platform. I'm seeking a challenging role in an innovative company like yours where I can leverage my skills and creative thinking.

Why Should We Hire You?

This role aligns perfectly with my career goals. I'm eager to contribute my skills and experience to a successful company. I am confident that my experience and skills make me well-suited for this opportunity, and I am excited to contribute to your team's success.

Do You Consider Yourself Successful?

I see success as continuous growth and learning. I thrive when I'm challenged to implement new ideas and see them come to fruition. My success is measured by what I have learned and how effectively I apply that knowledge to overcome challenges and achieve results.

Are You Willing to Travel?

Yes, I enjoy traveling. Exploring new places and meeting new people would be a great experience.

Greatest Strengths and Weaknesses

I excel in teamwork and possess strong organizational and planning skills honed over years of experience, allowing me to consistently deliver projects on time. While I'm comfortable working independently, I also thrive in collaborative environments. One area I've focused on improving is public speaking; I've actively sought opportunities to present my work and build confidence in this area.

Dream Job

This position is my ideal role; it perfectly matches my aspirations to work for a reputable multinational company like yours.

Reason for Leaving Previous Job

I'm seeking a new challenge and an opportunity to utilize my skills in a company with a strong reputation and innovative environment, such as yours.

Teamwork

Yes, absolutely. While I'm capable of independent work, I value collaboration and consider myself a strong team player who actively contributes and supports my colleagues.

Working Nights and Weekends

I'm flexible and willing to work nights and weekends if needed to meet the company's objectives.

Working Under Pressure

I thrive under pressure; it motivates me to work more efficiently and effectively.