Welcome to interviewquestions.guru. Your long-awaited Business Analyst interview is approaching? Not sure what questions to expect and how to prepare? No worries, this article will help you with both!
In this article, you will find the 25 most common Excel Interview Questions for Business Analyst 2022. We have also prepared for you a special article about how to prepare for a job interview – Full Guide & Checklist and Business Analyst Excel shortcuts .pdf which you can download now totally free (links in the article).
Table of Contents
Excel Interview Questions for Business Analyst
What is Ribbon in Excel?
This is one of the first and basic Excel interview questions to expect. If you have ever worked in Excel, you must have certainly seen and use the Excel Ribbon hundreds of times. But do you really know what it is? Would you be able to provide its definition during a job interview? Let us help you with that.
All MS Office suite programs have a so-called “Ribbon”. “Ribbon” is a toolbar or a command bar, available at the top of a window and it consists of several tabs. Each tab gathers a set of specific features. “Ribbon” and tabs usage is very intuitive. For example in the “Insert” tab, you can find the options to insert different kinds of objects (tables, charts, add-ins, etc.).
How to remove formatting in Excel without deleting cell content?
To clear the cell format without removing its content (text, formulas, etc.), you need to go to the “Home” tab, click on “Clear” and “Clear Formats”.
How to automatically adjust column width in Excel (AutoFit)?
You have 3 ways to automatically adjust the column width in Excel:
- Double-clicking. Place the cursor over the right edge of the column header and double-click.
- From a Ribbon, using the “AutoFit” option. Go to “Ribbon”, select the “Home” tab, click on “Format” and “AutoFit Column Width”.
- Using a key combination: ALT + H + O + I
To learn more about the Excel shortcuts, check our other articles:
80+ Must Know Excel shortcuts for Windows
80+ Must Know Excel shortcuts for Mac
What number formats are available in Excel?
MS Excel offers over 10 different number formats. Depending on the type of data which you work on, you can choose to form the formats below:
To learn more about each of the above number formats, visit support.microsoft.com.
What types of cell references do you know?
Excel offers two (or three*) types of cell references:
What is different about them, is the way they behave when being copied – check the table below. In Excel, all cell references are, by default, relative. To switch the reference type and make it absolute, block a cell by adding a dollar sign ($).
|Reference type||Cell name||Description|
|Relative||A1||Both column and row will adjust when copying|
|Absolute||$A$1||The column and the row will not adjust when copying – they will always remain A1|
|Mixed||$A1||Only the column will not adjust when copying|
|A$1||Only the row will not adjust when copying|
What is the order of operations in Excel?
This is one of the most commonly asked Business Analyst Excel interview questions.
When it comes to the order of operations, Excel follows the famous PEMDAS rule. Ever heard of PEMDAS? It’s an acronym which stands for:
- Multiplication and Division (from left to right)
- Addition and Subtraction (from left to right)
What is VLOOKUP and how to use it?
Another great example of the most popular Excel interview questions. No matter what position you apply for, Business Analyst, Data analyst, accountant, etc., VLOOKUP is something that you simply must know.
VLOOKUP (Vertical Lookup) is one of the most helpful and one of the most used Excel functions. VLOOKUP allows to automatically search for a certain value in one column, to get a value from another column in the same row. Not sure if you got it? Check our example:
What types of Charts are available in Excel?
When it comes to the charts, Excel offers a whole variety of choices. Depending on the purpose of your chart and the type of data, you can choose from over 10 different chart types! What is more, each chart type contains several sub-types and an almost unlimited number of customization options.
The most popular chart types are:
- X Y (Scatter);
What are Pivot Tables in Excel and how to use them?
Pivot Tables are one of the most popular Excel features. When a regular table that just stores the data is not enough, the pivot tables come with help. There are two ways to insert a pivot table:
- From a Ribbon. Go to the “Insert” tab, click on “PivotChart”, “PivotChart” and click on “OK”.
- Using a key combination: Alt + D + P
It is also worth to mention, that Pivot Tables are not created once for all. They will not refresh automatically, but there is are several easy manual ways to do it:
- Click anywhere on the pivot table area to display “PivotTable Tools” on the Ribbon.
- Click on the “Analyze” section, click on “Refresh” and select “Refresh” You can also choose the “Refresh All” option and refresh the pivot tables in the whole workbook.
Alternatively, you can also use the keyboard shortcut: Alt + F5.
What is Dashboard in Excel?
This is another example of guaranteed Excel Interview Questions for Business Analysts. Dashboards are one of the Business Analyst Excel skills which you must be familiar with.
The dashboards provide a simplified, one-window overview of the large volumes of data. They are very useful, for example, for status follow-ups, KPI tracking, etc. They are usually built of several elements, like tables, numbers, or charts.
Other Excel Interview Questions for Business Analyst
What are Freeze Panes in Excel?
What is Conditional Formatting in Excel?
What is a comment in Excel?
What is the difference between a comment and a note?
What are Functions in Excel?
What are VLOOKUP limitations?
What is macro in Excel?
What are the two macro languages in Excel?
What are macro limitations in Excel?
How to speed up a macro in Excel?
How would your sensitive data in Excel?
How would you handle missing or suspected data?
How to reduce a size of an Excel file?
What are the most popular error messages in Excel?
It was the last of our 25 most common Excel Interview Questions for Business Analyst 2022.
More from interviewquestions.guru:
- Tell me about yourself in an interview
- 100 Funny interview questions
- Illegal interview questions
- 50 Questions to ask the interviewer
- Top 75 Excel Interview Questions 2022
Thank you for reading this article till the end. Share with us your experience with Excel Interview Questions. Don’t forget to give us a thumb up!