Top 100 MS Excel Interview Questions for Fresher and Experienced
Microsoft Excel is one of the greatest, most powerful, most important software applications of all time.Many in the industry will no doubt object. But it provides enormous
capacity to do quantitative analysis, letting you do anything from statistical analyses of databases with hundreds of thousands of records to complex estimation tools with user
-friendly front ends. And unlike traditional statistical programs, it provides an intuitive interface that lets you see what happens to the data as you manipulate them.
So there is huge requirement of MS-excel masters who are able to handle any problem related to excel .If you want to become a MS excel expert or want to apply for excel job then
you must have necessary skills.These skills are:
Here's the top 50 Excel interview questions and answers:
1)What is Microsoft Excel?
Ans.Microsoft Excel is an Electronic Spreadsheet Program or application developed and manufactured by Microsoft Corporation that allows users to organize, format, and calculate
data with formulas using a spreadsheet system broken up by rows and columns.It features calculation, graphing tools, pivot tables, and a macro programming language called
Visual Basic for Applications.Microsoft Excel usually comes bundled with Microsoft Office and is compatible with other applications offered in the suite of products.It has
replaced Lotus 1-2-3 as the industry standard for spreadsheets. The first software program similar to Excel was released in 1982 and was called Multiplan.
With that short answer you can also describe your computer experience in layman's terms.You might be an expert user with high proficiency with servers, operating systems,
applications and Internet use.On the other hand,be able to speak in tech-savvy terminology so the interviewer knows you understand computer technology effectively.
2)What are dashboard and why do organisations use it?
Ans.Dashboards often provide at-a-glance views of KPIs (key performance indicators) relevant to a particular objective or business process (e.g. sales, marketing, human
resources, or production).The term dashboard originates from the automobile dashboard where drivers monitor the major functions at a glance via the instrument cluster.
Dashboards give signs about a business letting the user know something is wrong or something is right.Dashboards typically are limited to show summaries, key trends,
comparisons, and exceptions. There are four Key elements to a good dashboard which help organisations to work effectively:
*Simple, communicates easily
*Minimum distractions.
*Supports organized business with meaning and useful data
*Applies human visual perception to visual presentation of information
Smart organizations are doing everything they can to find every opportunity to drive their business and helpthem stay ahead of their competition. To uncover these opportunities
,decision-makers need easy access to a very wide range of information and they need advanced visualizations to help them analyze and understand the data.One way decision-maker
get easy access to data is through dashboards. Modern dashboard technology enables dozens of reports to be combined into a single, easily-consumable, visual representation.This
ability to consolidate many reports has been the biggest breakthrough in the last five years and has redefined what we think of as a “dashboard".Dashboards are highly
interactive, self-contained environments that hold the answers to many standard questions of business operations.
3)What is your process for creating a dashboard??
Ans.Excel Dashboard reports are the new buzz word employers are looking for, and for good reason. Dozens of dashboard building solutions have sprung up to meet the demand. Yet
in the ?urry of technology and enthusiasm, little attention has been paid to how to design focused, thoughtful, and user-friendly dashboards.
Smart and effective answers will your your interviewer more.So you may say that you:
1.Will first ?nd the speci?c reasons why your dashboard will be useful to your organization.This section offers exercises to de?ne and re?ne the purpose for your dashboard.
2.Decide that what type of dashboard you are creating?
3.Information discrimination ( select important data from raw data)
4.Choose tha perfect Metric
5.Framing the dashboard
*Form
*Structure
*Design principles
*Functionality
6.Information design
*Interface design
1. Organize the dashboard page like a web design expert
2. Choose the appropriate use of color to enhance your dashboard
3. Make the right typography decisions to ensure attractive, readable
*Information display
1. Pick the chart type that best ?ts your data
2. Style charts to be attractive and effective
3. Learn about advanced visualization and features for your dashboard
And before you fire up Excel grab a scrap of paper and mock up your dashboard layout also.
4)Do you use VBA to automate your work in Excel.If yes, how often do you use it?
Ans.You may say that yes, you use Visual Basic for Applications(VBA) to automate your tasks in Excel by writing so called macros.And then share the process of using VBA with
your interviewer smartly that you use VBA by step by step i.e
5)How to create a Data list in Excel?
Ans.The process of creating datalist is :
*Select any one cell in the table.
*Choose List > Create List from the menu to open the Create List dialog box.
*The dialog box shows the range of cells to be included in the list. If the table was created properly, Excel will usually select the correct range.
*If the range selection is correct, click OK.
FINAL STEP- The list (A blank row containing an asterisk is added to the bottom of the list. Additional data can be added to the list using this new row)
6)What is the most unusual thing you have used Excel for?
Ans.Here you need to mention those things which helped you for exploration and learning new things.You may some points in your answer like :
7)Can you tell me about some of the newer enhancements to Excel recently?
Ans.Some of the new enhancements you can mentions in your answer are as follows are:
With the names, you also need to add some details of particular enhancements
8)How do you provide Dynamic Range in 'Data Source' of Pivot Tables?
Ans.A Dynamic Range of data perhaps should use a Dynamic Rangename as the source for the pivot tables.Thus, when you add new rows of data, the dynamic range reference
automatically includes the added rows.
Suppose the data extends from say column [A] to column [P], and starts in row 1 to row whatever.
Suppose that column [B] always contains an entry for a data row (for example, col [B] might be say, ItemRef, which is never blank or empty.
Then you could create a rangename say, sourceBlock, and in the name "refers to" box use the formula
="a1" & counta("b1:b16536")
9)Is it possible to display the text in the data area of Pivot Table?
Ans.No its not possible to dislplay the text in data area of pivot table.You could display the text fields in the Row and Column areas, and show a count of the records in the
data area or we can combine Custom Number Formats with the Pivot Table to produce Text based answers.
10)How do you make two data fields appear side-by-side, instead of in separate rows?
Ans.After adding the two fields to the pivot table's data area, drag the Data field button to the right, onto the cell that contains the word 'Total'. There's a picture here
that shows the 'Total' cell.
11)How to debug a VBA code?
Ans.To start to debug a Visual Studio project, attach to a process, break into code, step through code, run to the cursor, run to a function on the call stack, set the next
statement,step through Just My Code, stop debugging, restart debugging, or detach from a debugged process.
12)Which are the 2 macro languages and which do you use ?
Ans.First is XLM and second is VBA(visual basic applications).So for answering effectivel you may say that early versions of Excel used a macro language called XLM.The VBA
language was introduced in Excel 5, and is vastly superior in every way.So for new macro development you should use VBA.You can also add the more benefits of using VBA like:
13)How can you increase the number of rows and columns in a worksheet?
Ans.The amount of rows in a worksheet is fixed i.e 1,048,576 , so you cannot increase the amount of rows. What you can do is insert rows into part of a spreadsheet. It will
have the effect of pushing rows down, but it will not increase the amount of rows in the worksheet.And same with columns they are also fixed(16384) its not possible to increase
,you can start a new Excel workbook and transfer your data.
14)What is Excel Dependency tree?
Ans.Excel Dependency Tree is an internal structure which is used to build calculation change and informs Excel which cells should be recalculated when something is changed in
the spreadsheet.Recalculation may be triggered in such cases:
*User entered new data into cell
*Excel commands are used which instructs Excel to recalculate all workbook or only a specific range
*When workbook is saved, if Recalculate Before Save option is turned on.
Dependency Tree is not shown by Excel, you can one use Formula Auditing commands to work with it. Show Precedents and Show Dependents commands point you to list of dependent
cells in the spreadsheet.Dependency Auditor adds its own set of formula auditing commands to work with Dependency Tree and research the calculations. It provides you set of
very helpful graphical and navigation tools to go through dependency list.
15)What are keyboard shortcuts and their equivalent VBA methods for Formulae calculation and building Excel dependency trees?
Ans.Keyboard Shortcut keys with their equivalent VBA methods are:
16)What is the ribbon and when would you use it?
Ans.The Ribbon is the strip of buttons and icons located above the work area beginning with Excel 2007.The Ribbon replaces the menus and toolbars found in earlier versions of
Excel.Above the Ribbon are a number of tabs, such as Home, Insert, and Page Layout. Clicking on a tab displays the options located in this section of the ribbon.
17)How can you hide or show the ribbon?
Ans.To Hide and show the ribbon you can press the CTRL+F1 key or Double click on one of the ribbon tabs-such as Home, Insert, or Page Layout.
18)What is IF function in excel?
Ans.The IF statement is a simple function in Excel that is one of the building blocks you need when you are working with large spreadsheets. You may not know you need it yet,
but once you know how to use it, you won't want to live without it.
IF() Function Syntax
The IF() function has the following syntax:
=IF(logical test, value if true, value if false)
The IF function works by performing a logical test that can only have one of two outcomes - TRUE or FALSE. It then outputs a result based on the outcome of that test.
19)Why does excel have two macro languages?
Ans.Early version of Execl used a macro language called XLM.The VBA language was introduced in Excel 5 and its vastly superior in every way.XLM has been phased out , so you
should use VBA for new macro development.
20)What types of workbook protection are available?
Ans.Excel provides three ways to protect a workbook:
To protect protect a workbook you can activate the workbook to be protected, then choose Tools - Protection - Protect Sheet. You will be asked to provide a password (optional)
.If you do provide a password, that password will be required to unprotect the workbook.
21)What are worksheets ?
Ans.A worksheet is a collection of cells where you keep and manipulate the data. A worksheet begins with row number one and column A.Each cell can contain a number, text or
formula. A cell can also reference another cell in the same worksheet, the same workbook or a different workbook. By default, each Excel workbook contains three worksheets.When
you open Excel, Excel automatically selects Sheet1 for you.The name of the worksheet appears on its sheet tab at the bottom of the document window.To select one of the other
two worksheets,simply click on the sheet tab of Sheet2 or Sheet3.
22)How can you add, rename or remove worksheets?
Ans.To add a worksheet: Touch + next to the last worksheet tab
To remove a workshhet: Touch the sheet's tab, then touch x to delete the worksheet
To rename a worksheet: Touch twice on the name of a worksheet
23)What are comments and how do you add comments to a cell?
Ans.Commenst are a verbal or written remark expressing an opinion or reaction.Using comments can help you make a worksheet easier to understand by providing additional context
for the data it contains. For example, you can use a comment as a note that provides information about data in an individual cell. You can also add a comment to a column
heading to provide guidance on data that a user should enter.
To add comments ta a cell:
24)What does a red triangle at the top right of a cell indicates?
Ans.Thew red triangle at the top of right of a cell indicates that:
25)How can you prevent someone from copying your worksheets?
Ans.In order to protect your worksheet from getting copied, you need to go into Menu bar >Review > Protect sheet > Password. By entering password,you can secure your worksheet
from getting copied by others.
26)What does a look up function searches in in the MS Excel?
Ans.The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the LOOKUP function:
*LOOKUP FUNCTION (SYNTAX #1)
-In Syntax #1, the LOOKUP function searches for value in the lookup_range and returns the value in the result_range that is in the same position.
-The syntax for the Microsoft Excel LOOKUP function is:
LOOKUP( value, lookup_range, [result_range] )
*LOOKUP FUNTION (SYNTAX #2)
-In Syntax #2, the LOOKUP function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array.
-The syntax for the Microsoft Excel LOOKUP function is:
LOOKUP( value, array )
27)How can we sum up the rows and column number quickly in the excel sheet?
Ans.We can sum up the rows and column number quickly in the excel sheet by the Excel SUM Function.The SUM function provides a quick and easy way to carry out this task in an
Excel worksheet.The syntax for the SUM function is:
=SUM ( Number1, Number2, ... Number255 )
28)What is Macro in Excel ?
Ans.A macro can be defined as the recording of a series of tasks. It’s the simplest form of automation – show a software program the steps you follow to get something done, and
the software will follow along. When used right, macros can save you hours by automating simple, repetitive tasks.Marcos in Excel are written in Excel VBA (Visual Basic for
Applications). This is a version of Visual Basic (a prominent Microsoft programming language) developed specifically for use in Office-like applications.
29)How can we create an Excel Macro?
Ans.To create Excel Macro :
30)How can you resize the column and rows?
Ans.The width of columns and height of rows can be adjusted to better fit your data in a few different ways.
31)What is the quick way to return to a specific area of spreadsheet?
Ans.By typing in Name Box you can return to a specific area of spreadsheet in quick way.
32)Tell me about the benefit of using formula in Excel?
Ans.Formulas in spreadsheet programs such as Excel are used to perform calculations on values entered and stored in the program.Excel's main function is to interlink cells,
through the use of formulas, so that the formula cell changes when data is inputted into linked cells. The best way to store information in spreadsheets is through the use of
formulas.Formulas can range from basic mathematical operations-such as addition and subtraction-to complex engineering and statistical calculations.
33)How can you format data in Excel?
Ans.To format data in Excel you van:
34)What is the function of sumif in Microsoft excel?
Ans.You would use it when you only want to total some values from a list. So say you had values in A1 to A15 and you only wanted to total up the ones that were over 100, you
could use a formula like this:
=SUMIF(A1:A15,">100")
Say you had the names of people in A1 to A15, and some of the names were there more than once, and you had values in B1 to B15. Then say you want to add values that are beside
only one particular name that occurs a few times in the list and that name was John. You could do that with this formula:
=SUMIF(A1:A15,"John",B1:B15)
So anywhere John is found in the cells from A1 to A15, it will total the corresponding values in B1 to B15.
35)How can you combine different chart types into your Excel spreadsheet?
Ans.Excel offers a wide range of chart types: Line Charts, Column Charts, Area Charts, Bar Charts, Scatter Charts, and Pie Charts, to name but a few. You can even mix different
types on a single chart by assigning different chart types to different series on the chart. These mixtures are called Combination Charts, and Excel provides a small number of
these on the Custom Types tab of the Chart Type dialog box.To combine different charts:
Step-1 Enter or copy/paste your data into an Excel worksheet.
Step-2 Select the data that you want included in the chart. Open the Insert tab on the Excel ribbon, and click on the arrow under Column in the Charts section to expand the
selection panel. Choose the column chart type that you want to use.
Step-3 Next, right-click on the column representing the data that you want to convert to a line.
Step-4 In the Change Chart Type window that appears, select what type of chart you want to use for this variable.Click OK to continue.
36)How can you change the colour and font of the Worksheet tabs?
Ans.To change colour of the worksheets:
To change the font :
37)How to protect the Macros you create so people cannot see or alter them?
Ans.To protect the Macros:
In the Visual Basic Editor, go to Tools > VBAProject properties, lock the project for viewing, and enter a password.
38)How to perform lock operation in an cell?
Ans.To lock the cells:
39)How can convert existing Excel worksheet data and charts to HTML document?
Ans.You can convert existing Excek worksheet data and charts to HTML document by using the Internet Assistant Wizard.With the Internet Assistant Wizard,you can leverage
existing Microsoft Excel spreadsheet data to create and distribute documents that can be viewed online with popular browsers. By clicking a series of buttons in the wizard, you
can convert spreadsheet data to HTML format either as a separate Web document or into an existing Web document.
40)How are array formulas useful?
Ans.Array formulas may be Excel's most powerful feature for summarizing data.You can use array formulas to do complex
tasks, such as:
41)What is the difference between SUBSTITUTE and REPLACE in Excel?
Ans.In simple words the Replace function replaces part of a text string, based on the number of characters you specify, with a different text string. The Substitute function
substitutes new text for old text in a text string. You use Substitute when you want to replace specific text in a text string and you use Replace when you want to replace any
text that occurs in a specific location in a text string.
42)What is the difference between in COUNT and COUNTA in Excel?
Ans.The COUNT function is generally used to count a range of cells containing numbers or dates excluding blanks.COUNTA, on the other hand will count everything numbers, dates,
text or a range containing a mixture of these items, but does not count blank cells. COUNTA stands for count all.Although you can use COUNTA to generate the number of clients
or employees in a list, it counts each entry, not unique entries. To count unique entries you would have to use the SUM function together with the COUNTIF function.
43)How do i find a feature that i used in Excel 2003 or Excel 2007?
Ans.Microsoft has developed an interactive referencing guide to help you find your favorite Excel 2003 or Excel 2007 commands in Excel 2010. With this guide, you can point your
mouse to an Excel 2003 or Excel 2007 menu or button and see where that feature is now located. You can also use the HELP feature to find out how to do a specific task in Excel
2010.
44)How can you protect formulas for being deletd or changed?
Ans.To protect formulas :
45)The process of identifying specific rows and columns so that certain columns and rows are always visible on the screen is called?
Ans.Answer is Freezing panes .When you freeze panes, you keep specific rows or columns visible when you scroll in the worksheet.For example, you might want to keep row and
column labels visible as you scroll. A solid line indicates that row 1 is frozen to keep column labels in place when you scroll.
46)How to use VLook up?
Ans.VLOOKUP is a great tool for pulling data from tables, but it has a handicap: it can only work with one criteria for matching information. If there are multiple rows in your
sheet with the same information, you’ll only get the first one. If you need to use two or more conditions to match a specific piece of data, you’re out of luck. Fortunately,
Excel has a pair of functions called INDEX and MATCH that can help produce the same results as VLOOKUP with multiple criteria.
VLOOKUP() Function Syntax-The VLOOKUP() function has the following syntax:
=VLOOKUP(lookup_value, lookup_table_range, column, exact)
VLOOKUP() works by taking the value you are looking up (lookup_value) and looking for it in the first column of the table you are searching in (lookup_table_range).
Once the function finds the matching value in the lookup_table_range, it then reads across that row the table to the column you chose and returns the value it finds there.
47)How to apply the same formatting and data to every sheet in a workbook in excel?
Ans.To apply the same formatting to every sheet in a workbook click CTRL+ click the tabs of the sheets that you want to group together,and the grouped tabs will all turn white.
(Normally, only the current sheet has a white tab, while the others are gray.) While sheets are grouped, anything you enter in one sheet also gets entered into the others.After
formatting grouped sheets, and perhaps entering the same headings in each, remember to click on the tabs to ungroup them, so that you don't accidentally insert or remove data
in multiple sheets when you insert or remove data in one.
48)Is there any way to create shortcuts to Excel functions?
Ans.Yes.Just above the HOME button is the QUICK ACCESS TOOLBAR. You can customize this toolbar to display the shortcuts that you most frequently use from the list provided.
If you click on the small down arrow on the edge of the toolbar, a list of the most common shortcuts are displayed. You can choose shortcuts from this list or if you want a
more in-depth selection, click on the MORE COMMANDS option. You can then add more shortcuts from the window that pops up.
49)Is cell reference is useful in the calculation? If yes, then how?
Ans.Yes cell reference is very useful in the calculation.Most of the time in Excel,you will be using a cell's address in the formula.This is called using a cell reference.The
advantage of using cell references is that you can change a value in a referenced cell and the formula will automatically recalculate.Using cell references in your formulas
will make sure the values in your formulas are accurate.
50)What is the role of AND function in excel?
Ans.One common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then
returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the AND function as the logical_test argument of the IF function, you
can test many different conditions instead of just one.
The AND function syntax has the following arguments:
*logical1 Required. The first condition that you want to test that can evaluate to either TRUE or FALSE.
*logical2 Optional. Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.
All The Best!!
Think Big, Think Differently :-)
capacity to do quantitative analysis, letting you do anything from statistical analyses of databases with hundreds of thousands of records to complex estimation tools with user
-friendly front ends. And unlike traditional statistical programs, it provides an intuitive interface that lets you see what happens to the data as you manipulate them.
So there is huge requirement of MS-excel masters who are able to handle any problem related to excel .If you want to become a MS excel expert or want to apply for excel job then
you must have necessary skills.These skills are:
- Good knowledge of all aspects of excel and more .
- Integrating excel with other tools and optimizing excel
- Using excel productively
- Ability to set up dynamic & interactive charts
- Ability to combine various charts in to one
- Manage Data Easily With The Pivot-table Tool
- Familiar with VBA & can write macros to automate their day to day work, thus saving countless hours of time & money
- Ability to combine formulas with conditional formatting to highlight data that meets almost any condition
- Able to seamlessly write & combine formulas like SUMIFS, SUMPRODUCT, INDEX, MATCH, LOOKUP formulas
- Knowing how to debug the formulas , audit them and how to use which formula for which occasion
Here's the top 50 Excel interview questions and answers:
1)What is Microsoft Excel?
Ans.Microsoft Excel is an Electronic Spreadsheet Program or application developed and manufactured by Microsoft Corporation that allows users to organize, format, and calculate
data with formulas using a spreadsheet system broken up by rows and columns.It features calculation, graphing tools, pivot tables, and a macro programming language called
Visual Basic for Applications.Microsoft Excel usually comes bundled with Microsoft Office and is compatible with other applications offered in the suite of products.It has
replaced Lotus 1-2-3 as the industry standard for spreadsheets. The first software program similar to Excel was released in 1982 and was called Multiplan.
With that short answer you can also describe your computer experience in layman's terms.You might be an expert user with high proficiency with servers, operating systems,
applications and Internet use.On the other hand,be able to speak in tech-savvy terminology so the interviewer knows you understand computer technology effectively.
2)What are dashboard and why do organisations use it?
Ans.Dashboards often provide at-a-glance views of KPIs (key performance indicators) relevant to a particular objective or business process (e.g. sales, marketing, human
resources, or production).The term dashboard originates from the automobile dashboard where drivers monitor the major functions at a glance via the instrument cluster.
Dashboards give signs about a business letting the user know something is wrong or something is right.Dashboards typically are limited to show summaries, key trends,
comparisons, and exceptions. There are four Key elements to a good dashboard which help organisations to work effectively:
*Simple, communicates easily
*Minimum distractions.
*Supports organized business with meaning and useful data
*Applies human visual perception to visual presentation of information
Smart organizations are doing everything they can to find every opportunity to drive their business and helpthem stay ahead of their competition. To uncover these opportunities
,decision-makers need easy access to a very wide range of information and they need advanced visualizations to help them analyze and understand the data.One way decision-maker
get easy access to data is through dashboards. Modern dashboard technology enables dozens of reports to be combined into a single, easily-consumable, visual representation.This
ability to consolidate many reports has been the biggest breakthrough in the last five years and has redefined what we think of as a “dashboard".Dashboards are highly
interactive, self-contained environments that hold the answers to many standard questions of business operations.
3)What is your process for creating a dashboard??
Ans.Excel Dashboard reports are the new buzz word employers are looking for, and for good reason. Dozens of dashboard building solutions have sprung up to meet the demand. Yet
in the ?urry of technology and enthusiasm, little attention has been paid to how to design focused, thoughtful, and user-friendly dashboards.
Smart and effective answers will your your interviewer more.So you may say that you:
1.Will first ?nd the speci?c reasons why your dashboard will be useful to your organization.This section offers exercises to de?ne and re?ne the purpose for your dashboard.
2.Decide that what type of dashboard you are creating?
3.Information discrimination ( select important data from raw data)
4.Choose tha perfect Metric
5.Framing the dashboard
*Form
*Structure
*Design principles
*Functionality
6.Information design
*Interface design
1. Organize the dashboard page like a web design expert
2. Choose the appropriate use of color to enhance your dashboard
3. Make the right typography decisions to ensure attractive, readable
*Information display
1. Pick the chart type that best ?ts your data
2. Style charts to be attractive and effective
3. Learn about advanced visualization and features for your dashboard
And before you fire up Excel grab a scrap of paper and mock up your dashboard layout also.
4)Do you use VBA to automate your work in Excel.If yes, how often do you use it?
Ans.You may say that yes, you use Visual Basic for Applications(VBA) to automate your tasks in Excel by writing so called macros.And then share the process of using VBA with
your interviewer smartly that you use VBA by step by step i.e
- STEP-1 Enabling VBA(To enable VBA you have to use the File, Options and select Customise the Ribbon.You then have tick the Developer box to allow the Developer icon to appear).
- STEP-2 Insert a module(To insert a module you use the command Insert,Macro,Module).
- STEP-3 Entering text.
- STEP-4 Each module you create is given a default name(Module1, Module2 and so on - but you can rename it quite easily by typing a new name into the Properties box at the
- bottom left)
- STEP-5 The beginning and the end(All programs in VBA have to start and end in a particular way. The first line has to read Sub name() where name is the name you want to give
- your program.Sub is short for Subroutine but more of this later).
- STEP-6 Getting the message.
- STEP-7 Running the program(To run the program you can simply click on the green “play” arrow in the Visual Basic toolbar).
- STEP-8 Input(to find out how to get information back from the user - i.e. let them input something.This makes use of another VBA command, InputBox, but pay attention because it is a little more tricky than MsgBox).
5)How to create a Data list in Excel?
Ans.The process of creating datalist is :
- STEP-1 Keeping track of data (Data can be entered easily into the spreadsheet, and, with just a few clicks of the mouse you can sort through your data and find what you want).
- STEP-2 Tables of data (The basic format for storing data in Excel is a table. In a table, data is entered in rows.Each row is known as a record and the easiest way of doing
- so is, to create what's known as a list from the data in a table).
- STEP-3 Entering data correctly (The first step in creating a table is to enter the data. When doing so, it is important to ensure that it is entered correctly.Incorrect data
- can cause many serious problems).
- STEP-4 Rows are records (to create a list, a table of data needs to contain at least two data records).
- STEP-5 Columns are fields (Each column needs a heading to identify the data it contains. These headings are called field names).
- STEP-6 Create the list
*Select any one cell in the table.
*Choose List > Create List from the menu to open the Create List dialog box.
*The dialog box shows the range of cells to be included in the list. If the table was created properly, Excel will usually select the correct range.
*If the range selection is correct, click OK.
FINAL STEP- The list (A blank row containing an asterisk is added to the bottom of the list. Additional data can be added to the list using this new row)
6)What is the most unusual thing you have used Excel for?
Ans.Here you need to mention those things which helped you for exploration and learning new things.You may some points in your answer like :
- I used excel to create stunning screen mockups and wowed my clients.
- I have used excel to generate and clean a list of tags for the blog i write(if).
- I wrote a small macro to send updates to twitter.
7)Can you tell me about some of the newer enhancements to Excel recently?
Ans.Some of the new enhancements you can mentions in your answer are as follows are:
- Powerpivot
- Tables
- Slicers
- Sparklines
- IFERROR
With the names, you also need to add some details of particular enhancements
8)How do you provide Dynamic Range in 'Data Source' of Pivot Tables?
Ans.A Dynamic Range of data perhaps should use a Dynamic Rangename as the source for the pivot tables.Thus, when you add new rows of data, the dynamic range reference
automatically includes the added rows.
Suppose the data extends from say column [A] to column [P], and starts in row 1 to row whatever.
Suppose that column [B] always contains an entry for a data row (for example, col [B] might be say, ItemRef, which is never blank or empty.
Then you could create a rangename say, sourceBlock, and in the name "refers to" box use the formula
="a1" & counta("b1:b16536")
9)Is it possible to display the text in the data area of Pivot Table?
Ans.No its not possible to dislplay the text in data area of pivot table.You could display the text fields in the Row and Column areas, and show a count of the records in the
data area or we can combine Custom Number Formats with the Pivot Table to produce Text based answers.
10)How do you make two data fields appear side-by-side, instead of in separate rows?
Ans.After adding the two fields to the pivot table's data area, drag the Data field button to the right, onto the cell that contains the word 'Total'. There's a picture here
that shows the 'Total' cell.
- Change pivot table data to horizontal layout.And to keep related data from spreading horizontally off of the screen and to help minimize scrolling,click Show in Compact Form.
- In compact form, fields are contained in one column and indented to show the nested column relationship.
- To outline the data in the classic PivotTable style, click Show in Outline Form.
- To see all data in a traditional table format and to easily copy cells to another worksheet, click Show in Tabular Form.
11)How to debug a VBA code?
Ans.To start to debug a Visual Studio project, attach to a process, break into code, step through code, run to the cursor, run to a function on the call stack, set the next
statement,step through Just My Code, stop debugging, restart debugging, or detach from a debugged process.
- Specify the configurations for the debug and release versions of a program.
- Set start options (command-line arguments, working directory, remote machine).
- Debug at design time.
- Enable just-in-time debugging, which launches the Visual Studio debugger when a program running outside Visual Studio encounters a fatal error.
- Set breakpoints for source lines, assembly instructions, and call stack function. Specify conditions, hit counts, and execution location.
12)Which are the 2 macro languages and which do you use ?
Ans.First is XLM and second is VBA(visual basic applications).So for answering effectivel you may say that early versions of Excel used a macro language called XLM.The VBA
language was introduced in Excel 5, and is vastly superior in every way.So for new macro development you should use VBA.You can also add the more benefits of using VBA like:
- The Visual Basic Application is the user friendly program that you will use to talk with Excel.
- To develop analysis and reporting programs downstream from large central databases such as Sybase, SQL Server, and accounting, financial and production programs such as
- Oracle, SAP, and others.
- Probide the ability to do what you normally do in Excel, but a thousand times faster. ETC
13)How can you increase the number of rows and columns in a worksheet?
Ans.The amount of rows in a worksheet is fixed i.e 1,048,576 , so you cannot increase the amount of rows. What you can do is insert rows into part of a spreadsheet. It will
have the effect of pushing rows down, but it will not increase the amount of rows in the worksheet.And same with columns they are also fixed(16384) its not possible to increase
,you can start a new Excel workbook and transfer your data.
14)What is Excel Dependency tree?
Ans.Excel Dependency Tree is an internal structure which is used to build calculation change and informs Excel which cells should be recalculated when something is changed in
the spreadsheet.Recalculation may be triggered in such cases:
*User entered new data into cell
*Excel commands are used which instructs Excel to recalculate all workbook or only a specific range
*When workbook is saved, if Recalculate Before Save option is turned on.
Dependency Tree is not shown by Excel, you can one use Formula Auditing commands to work with it. Show Precedents and Show Dependents commands point you to list of dependent
cells in the spreadsheet.Dependency Auditor adds its own set of formula auditing commands to work with Dependency Tree and research the calculations. It provides you set of
very helpful graphical and navigation tools to go through dependency list.
15)What are keyboard shortcuts and their equivalent VBA methods for Formulae calculation and building Excel dependency trees?
Ans.Keyboard Shortcut keys with their equivalent VBA methods are:
- F9 - Recalculates all worksheets in all open workbooks.
- Shift+F9 - Recalculates the active worksheet.
- Ctrl+Alt+F9 - Recalculates all worksheets in all open workbooks (Full recalculation).
- Shift+Ctrl+Alt+F9 - Rebuilds the dependency tree and does a full recalculation
16)What is the ribbon and when would you use it?
Ans.The Ribbon is the strip of buttons and icons located above the work area beginning with Excel 2007.The Ribbon replaces the menus and toolbars found in earlier versions of
Excel.Above the Ribbon are a number of tabs, such as Home, Insert, and Page Layout. Clicking on a tab displays the options located in this section of the ribbon.
17)How can you hide or show the ribbon?
Ans.To Hide and show the ribbon you can press the CTRL+F1 key or Double click on one of the ribbon tabs-such as Home, Insert, or Page Layout.
18)What is IF function in excel?
Ans.The IF statement is a simple function in Excel that is one of the building blocks you need when you are working with large spreadsheets. You may not know you need it yet,
but once you know how to use it, you won't want to live without it.
IF() Function Syntax
The IF() function has the following syntax:
=IF(logical test, value if true, value if false)
The IF function works by performing a logical test that can only have one of two outcomes - TRUE or FALSE. It then outputs a result based on the outcome of that test.
19)Why does excel have two macro languages?
Ans.Early version of Execl used a macro language called XLM.The VBA language was introduced in Excel 5 and its vastly superior in every way.XLM has been phased out , so you
should use VBA for new macro development.
20)What types of workbook protection are available?
Ans.Excel provides three ways to protect a workbook:
- Require a password to open the workbook.
- Prevent users from adding sheets, deleting sheets, hiding sheets, and unhidingsheets.
- Prevent users from changing the size or position of windows.
To protect protect a workbook you can activate the workbook to be protected, then choose Tools - Protection - Protect Sheet. You will be asked to provide a password (optional)
.If you do provide a password, that password will be required to unprotect the workbook.
21)What are worksheets ?
Ans.A worksheet is a collection of cells where you keep and manipulate the data. A worksheet begins with row number one and column A.Each cell can contain a number, text or
formula. A cell can also reference another cell in the same worksheet, the same workbook or a different workbook. By default, each Excel workbook contains three worksheets.When
you open Excel, Excel automatically selects Sheet1 for you.The name of the worksheet appears on its sheet tab at the bottom of the document window.To select one of the other
two worksheets,simply click on the sheet tab of Sheet2 or Sheet3.
22)How can you add, rename or remove worksheets?
Ans.To add a worksheet: Touch + next to the last worksheet tab
To remove a workshhet: Touch the sheet's tab, then touch x to delete the worksheet
To rename a worksheet: Touch twice on the name of a worksheet
23)What are comments and how do you add comments to a cell?
Ans.Commenst are a verbal or written remark expressing an opinion or reaction.Using comments can help you make a worksheet easier to understand by providing additional context
for the data it contains. For example, you can use a comment as a note that provides information about data in an individual cell. You can also add a comment to a column
heading to provide guidance on data that a user should enter.
To add comments ta a cell:
- Step-1 Select the cell that you want to add a comment to.
- Step-2 On the Review tab, in the Comments group, click New Comment.
- Step-3 In the body of the comment, type the comment text.
- Step-4 Click outside the comment box.The comment box disappears, but the comment indicator remains.
24)What does a red triangle at the top right of a cell indicates?
Ans.Thew red triangle at the top of right of a cell indicates that:
- There is an error in the cell
- There is a comment associated with the cell
- The font color of the text in cell is red
- The cell can't accet formula
25)How can you prevent someone from copying your worksheets?
Ans.In order to protect your worksheet from getting copied, you need to go into Menu bar >Review > Protect sheet > Password. By entering password,you can secure your worksheet
from getting copied by others.
26)What does a look up function searches in in the MS Excel?
Ans.The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the LOOKUP function:
*LOOKUP FUNCTION (SYNTAX #1)
-In Syntax #1, the LOOKUP function searches for value in the lookup_range and returns the value in the result_range that is in the same position.
-The syntax for the Microsoft Excel LOOKUP function is:
LOOKUP( value, lookup_range, [result_range] )
*LOOKUP FUNTION (SYNTAX #2)
-In Syntax #2, the LOOKUP function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array.
-The syntax for the Microsoft Excel LOOKUP function is:
LOOKUP( value, array )
27)How can we sum up the rows and column number quickly in the excel sheet?
Ans.We can sum up the rows and column number quickly in the excel sheet by the Excel SUM Function.The SUM function provides a quick and easy way to carry out this task in an
Excel worksheet.The syntax for the SUM function is:
=SUM ( Number1, Number2, ... Number255 )
28)What is Macro in Excel ?
Ans.A macro can be defined as the recording of a series of tasks. It’s the simplest form of automation – show a software program the steps you follow to get something done, and
the software will follow along. When used right, macros can save you hours by automating simple, repetitive tasks.Marcos in Excel are written in Excel VBA (Visual Basic for
Applications). This is a version of Visual Basic (a prominent Microsoft programming language) developed specifically for use in Office-like applications.
29)How can we create an Excel Macro?
Ans.To create Excel Macro :
- Step-1 Turn on the Developer tab.
- Step-2 Right click anywhere on the ribbon, and then click Customize the Ribbon.
- Step-3 Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary).
- Step-4 Check the Developer check box.Click OK.
- Step-5 On the Developer tab, in the Code group, click Visual Basic.
- Step-6 If needed, in the Visual Basic Editor, on the Insert menu, click Module.
- Step-7 In the code window of the module, type or copy the macro code that you want to use.
- Step-8 To run the macro from the module window, press F5.
30)How can you resize the column and rows?
Ans.The width of columns and height of rows can be adjusted to better fit your data in a few different ways.
- Use the format button- The Format button in the cells group under the Home tab can also be used to adjust widths and heights. Simply select the cells that need to be adjusted,then click on the Format button and choose either Row Height or Column Width. Type in your desired number, and the cells will adjust.
- Drag the border- Hover your arrow over the border between the column or row labels. Then click and drag the border to widen or narrow the size of each cell.The columns to theright or rows below will adjust to make room for your changes.
- You can make several columns or rows the same width or height. Select your desired columns or rows by clicking on the labels while holding down either the shift key or control key. Then use any of the methods described above, and all of the columns will be made the same width or rows the same height.
31)What is the quick way to return to a specific area of spreadsheet?
Ans.By typing in Name Box you can return to a specific area of spreadsheet in quick way.
32)Tell me about the benefit of using formula in Excel?
Ans.Formulas in spreadsheet programs such as Excel are used to perform calculations on values entered and stored in the program.Excel's main function is to interlink cells,
through the use of formulas, so that the formula cell changes when data is inputted into linked cells. The best way to store information in spreadsheets is through the use of
formulas.Formulas can range from basic mathematical operations-such as addition and subtraction-to complex engineering and statistical calculations.
33)How can you format data in Excel?
Ans.To format data in Excel you van:
- Use column labels to identify data
- Use cell borders to distinguish data
- Extend data formats and formulas
- Avoid leading or trailing spaces to avoid errors
- Use an Excel table format to work with related data
34)What is the function of sumif in Microsoft excel?
Ans.You would use it when you only want to total some values from a list. So say you had values in A1 to A15 and you only wanted to total up the ones that were over 100, you
could use a formula like this:
=SUMIF(A1:A15,">100")
Say you had the names of people in A1 to A15, and some of the names were there more than once, and you had values in B1 to B15. Then say you want to add values that are beside
only one particular name that occurs a few times in the list and that name was John. You could do that with this formula:
=SUMIF(A1:A15,"John",B1:B15)
So anywhere John is found in the cells from A1 to A15, it will total the corresponding values in B1 to B15.
35)How can you combine different chart types into your Excel spreadsheet?
Ans.Excel offers a wide range of chart types: Line Charts, Column Charts, Area Charts, Bar Charts, Scatter Charts, and Pie Charts, to name but a few. You can even mix different
types on a single chart by assigning different chart types to different series on the chart. These mixtures are called Combination Charts, and Excel provides a small number of
these on the Custom Types tab of the Chart Type dialog box.To combine different charts:
Step-1 Enter or copy/paste your data into an Excel worksheet.
Step-2 Select the data that you want included in the chart. Open the Insert tab on the Excel ribbon, and click on the arrow under Column in the Charts section to expand the
selection panel. Choose the column chart type that you want to use.
Step-3 Next, right-click on the column representing the data that you want to convert to a line.
Step-4 In the Change Chart Type window that appears, select what type of chart you want to use for this variable.Click OK to continue.
36)How can you change the colour and font of the Worksheet tabs?
Ans.To change colour of the worksheets:
- Step-1 Right-click on the worksheet tab you want to change. Excel displays a Context menu.
- Step-2 Choose Tab Color from the Context menu. Excel displays the Format Tab Color dialog box.
- Step-3 Select a color to use for the tab.
- Step-4 Click on OK.
To change the font :
- Step-1 Minimize all open applications by pressing Windows button + M, and right-click the desktop.
- Step-2 Select Properties, and then select the Appearance tab.
- Step-3 Click Advanced (skip this step if using Windows 98).
- Step-4 From the Item dropdown list, choose Scrollbar.
- Step-5 Change the Size and click OK twice.
37)How to protect the Macros you create so people cannot see or alter them?
Ans.To protect the Macros:
In the Visual Basic Editor, go to Tools > VBAProject properties, lock the project for viewing, and enter a password.
38)How to perform lock operation in an cell?
Ans.To lock the cells:
- Step-1 Select all cells.
- Step-2 Right click, and then click Format Cells.
- Step-3 On the Protection tab, uncheck the Locked check box and click OK.
- Step-4 Right click cell(you want to lock), and then click Format Cells.
- Step-5 On the Protection tab, check the Locked check box and click Ok.Now your cell will be is locked .
39)How can convert existing Excel worksheet data and charts to HTML document?
Ans.You can convert existing Excek worksheet data and charts to HTML document by using the Internet Assistant Wizard.With the Internet Assistant Wizard,you can leverage
existing Microsoft Excel spreadsheet data to create and distribute documents that can be viewed online with popular browsers. By clicking a series of buttons in the wizard, you
can convert spreadsheet data to HTML format either as a separate Web document or into an existing Web document.
40)How are array formulas useful?
Ans.Array formulas may be Excel's most powerful feature for summarizing data.You can use array formulas to do complex
tasks, such as:
- Count the number of characters that are contained in a range of cells.
- Sum only numbers that meet certain conditions, such as the lowest values in a range or numbers that fall between an upper and lower boundary.
- Sum every nth value in a range of values.
- Counting Values Based on Multiple Criteria.
- Multiple Sums Simultaneously
- Counting Unique Values
41)What is the difference between SUBSTITUTE and REPLACE in Excel?
Ans.In simple words the Replace function replaces part of a text string, based on the number of characters you specify, with a different text string. The Substitute function
substitutes new text for old text in a text string. You use Substitute when you want to replace specific text in a text string and you use Replace when you want to replace any
text that occurs in a specific location in a text string.
42)What is the difference between in COUNT and COUNTA in Excel?
Ans.The COUNT function is generally used to count a range of cells containing numbers or dates excluding blanks.COUNTA, on the other hand will count everything numbers, dates,
text or a range containing a mixture of these items, but does not count blank cells. COUNTA stands for count all.Although you can use COUNTA to generate the number of clients
or employees in a list, it counts each entry, not unique entries. To count unique entries you would have to use the SUM function together with the COUNTIF function.
43)How do i find a feature that i used in Excel 2003 or Excel 2007?
Ans.Microsoft has developed an interactive referencing guide to help you find your favorite Excel 2003 or Excel 2007 commands in Excel 2010. With this guide, you can point your
mouse to an Excel 2003 or Excel 2007 menu or button and see where that feature is now located. You can also use the HELP feature to find out how to do a specific task in Excel
2010.
44)How can you protect formulas for being deletd or changed?
Ans.To protect formulas :
- Step-1 Select the whole worksheet with Ctrl + A, and right click, choose Format Cells from the context menu.
- Step-2 And a Format Cells dialog box will pop out. Click Protection, and unchecked the Locked option. Click OK. The whole worksheet has been unlocked.
- Step-3 Then click Home > Find & Select > Go To Special, and a Go To Special dialog box will appear. Check Formulas from Select option, and then click OK.
- Step-4 All of the cells which contain formulas will be selected.
- Step-5 Then go to lock the select cells.Right click on the selected cells,and choose Format Cells from the context menu,and a Format Cells dialog box will display.Click Protection,
- check the Locked checkbox.
- Step-6 And then click Review > Protect Sheet, and a Protect Sheet dialog box will pop out, you can enter the password in the Passwordto unprotect sheet box .
45)The process of identifying specific rows and columns so that certain columns and rows are always visible on the screen is called?
Ans.Answer is Freezing panes .When you freeze panes, you keep specific rows or columns visible when you scroll in the worksheet.For example, you might want to keep row and
column labels visible as you scroll. A solid line indicates that row 1 is frozen to keep column labels in place when you scroll.
46)How to use VLook up?
Ans.VLOOKUP is a great tool for pulling data from tables, but it has a handicap: it can only work with one criteria for matching information. If there are multiple rows in your
sheet with the same information, you’ll only get the first one. If you need to use two or more conditions to match a specific piece of data, you’re out of luck. Fortunately,
Excel has a pair of functions called INDEX and MATCH that can help produce the same results as VLOOKUP with multiple criteria.
VLOOKUP() Function Syntax-The VLOOKUP() function has the following syntax:
=VLOOKUP(lookup_value, lookup_table_range, column, exact)
VLOOKUP() works by taking the value you are looking up (lookup_value) and looking for it in the first column of the table you are searching in (lookup_table_range).
Once the function finds the matching value in the lookup_table_range, it then reads across that row the table to the column you chose and returns the value it finds there.
47)How to apply the same formatting and data to every sheet in a workbook in excel?
Ans.To apply the same formatting to every sheet in a workbook click CTRL+ click the tabs of the sheets that you want to group together,and the grouped tabs will all turn white.
(Normally, only the current sheet has a white tab, while the others are gray.) While sheets are grouped, anything you enter in one sheet also gets entered into the others.After
formatting grouped sheets, and perhaps entering the same headings in each, remember to click on the tabs to ungroup them, so that you don't accidentally insert or remove data
in multiple sheets when you insert or remove data in one.
48)Is there any way to create shortcuts to Excel functions?
Ans.Yes.Just above the HOME button is the QUICK ACCESS TOOLBAR. You can customize this toolbar to display the shortcuts that you most frequently use from the list provided.
If you click on the small down arrow on the edge of the toolbar, a list of the most common shortcuts are displayed. You can choose shortcuts from this list or if you want a
more in-depth selection, click on the MORE COMMANDS option. You can then add more shortcuts from the window that pops up.
49)Is cell reference is useful in the calculation? If yes, then how?
Ans.Yes cell reference is very useful in the calculation.Most of the time in Excel,you will be using a cell's address in the formula.This is called using a cell reference.The
advantage of using cell references is that you can change a value in a referenced cell and the formula will automatically recalculate.Using cell references in your formulas
will make sure the values in your formulas are accurate.
50)What is the role of AND function in excel?
Ans.One common use for the AND function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and then
returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. By using the AND function as the logical_test argument of the IF function, you
can test many different conditions instead of just one.
The AND function syntax has the following arguments:
*logical1 Required. The first condition that you want to test that can evaluate to either TRUE or FALSE.
*logical2 Optional. Additional conditions that you want to test that can evaluate to either TRUE or FALSE, up to a maximum of 255 conditions.
All The Best!!
Think Big, Think Differently :-)
Comments