Here we have mentioned most frequently asked MS Excel Interview Questions and Answers specially for freshers and experienced.
1. What is Microsoft Excel?
Ans:
Microsoft Excel is an electronic spreadsheet application that enables users to store, organize, calculate and manipulate the data with formulas using a spreadsheet system broken up by rows and columns. It also provides the flexibility to use an external database to do analysis, make reports, etc. thus saving lots of time.
2. What is ribbon?
Ans:
Ribbon refers to the topmost area of the application that contains menu items and toolbars available in MS-Excel. Ribbon can be shown/hidden using CTRL+F1. The ribbon runs on the top of the application and is the replacement for the toolbars and menus. The ribbons have various tabs on the top, and each tab has its own group of commands.
3. Explain Spreadsheet and its Basics.
Ans:
Spreadsheet can be compared to a paper ledger sheet. It consists or rows and columns and their intersection called cells.
4. How many data formats are available in Excel? Name some of them.
Ans:
Eleven data formats are available in Microsoft Excel for data Storage. Example:
Number – Stores data as a number
Currency – Stores data in the form of currency
Date – Data is stored as dates
Percentage – Stores numbers as a percentage
Text Formats – Stores data as string of texts
5. Specify the order of operations used for evaluating formulas in Excel.
Ans:
The order of operations in Microsoft Excel is same as in standard mathematics. It’s defined by the term “PEMDAS” or “BEDMAS”.
Parentheses or Brackets
Exponent
Multiplication
Division
Addition
Subtraction
6. How can you wrap the text within a cell?
Ans:
You must select the text you want to wrap, and then click wrap text from the home tab and you can wrap the text within a cell.
7. Explain Macro in MS-Excel.
Ans:
Macros are used for iterating over a group of tasks. Users can create macros for their customized repetitive functions and instructions. Macros can be either written or recorded depending on the user.
8. Which are the two macro languages in MS-Excel?
Ans:
XLM and VBA (Visual Basic Applications). Earlier versions of Excel used XLM. VBA was introduced in Excel 5 and mostly used now.
9. Is it possible to prevent someone from copying the cell from your worksheet?
Ans:
Yes, it is possible. To protect your worksheet from getting copied, you need to go into Menu bar >Review > Protect Sheet > Password. By entering a password, you can secure your sheet from getting copied by others.
10. What are charts in MS-Excel?
Ans:
To enable graphical representation of the data in Excel, charts are provided. A user can use any chart type, including column, bar, line, pie, scatter, etc. by selecting an option from Insert tab’s Chart group.
11. How can you sum up the Rows and Column number quickly in the Excel sheet?
Ans:
By using SUM function, you can get the total sum of the rows and columns, in an Excel worksheet.
12. Explain few useful functions in Excel.
Ans:
Following are the functions available in Excel for manipulating the data:
Math and Financial Functions – SQRT, DEGREE, RAND(), GCD
Logical Functions – IF, AND, FALSE, TRUE
Date and Time functions – NOW(), DATEVALUE(), WEEKDAY(NOW())
Index Match – VLOOKUP and INDEX MATCH
Pivot tables
13. What does a red triangle at the top right of a cell indicate?
Ans:
The red triangle indicates that some comment is associated with the cell. Hover the mouse over it, and you can read the full comment.
14. How can you add a new Excel worksheet?
Ans:
To add a new Excel worksheet, you should insert worksheet tab at the bottom of the screen.
15. What is the use of NameBox in MS-Excel?
Ans:
Name Box is used to return to a particular area of the worksheet by typing the range name or cell address in the name box.
16. How can you resize the column?
Ans:
To resize the column, you should change the width of one column and then drag the boundary on the right side of the column heading till the width you want. The other way of doing it is to select the Format from the home tab, and in Format you have to select AUTOFIT COLUMN WIDTH under cell section. On clicking on this, the cell size will get formatted.
17. Explain pivot tables and its uses.
Ans:
A pivot table is a tool that allows for quick summarization of large data. It automatically performs a sort, count, total or average of the data stored in the spreadsheet and displays result in another spreadsheet. It saves a lot of time. Allows to link external data sources to our Excel.
18. What are three report formats that are available in Excel?
Ans:
Following are the types of report formats
Compact
Report
Tabular
19. How would you provide a Dynamic range in “Data Source” of Pivot Tables?
Ans:
To provide a dynamic range in “Data Source” of Pivot tables, first, create a named range using offset function and base the pivot table using a named range created in the first step.
20. Is it possible to make Pivot table using multiple sources of data?
Ans:
If the multiple sources are different worksheets, from the same workbook, then it is possible to make Pivot table using multiple sources of data.
21. Which event do you use to check whether the Pivot Table is modified or not?
Ans:
To check whether the pivot table is modified or not we use “PivotTableUpdate” in worksheet containing the pivot table.
22. How can you disable automatic sorting in pivot tables?
Ans:
To disable automatic sorting in pivot tables:
Go To > More Sort Options > Right Click ‘Pivot tables’ > Select ‘sort menu’ > select ‘More Options’ > deselect ‘Sort automatically’.
23. What is Freeze Panes in MS-Excel?
Ans:
To lock any row or column, freeze panes is used. The locked row or column will be visible on the screen even after we scroll the sheet vertically or horizontally.
24. What could you do to stop the pivot table from loosing the column width upon refreshing?
Ans:
Format loss in a pivot table can be stopped simply by changing the pivot table options. Under the “Pivot Table Options” turn on the “Enable Preserve Formatting” and disable “Auto Format” option.
25. Explain workbook protection types in Excel.
Ans:
Excel provides three ways to protect a workbook:
Password protection for opening a workbook
Protection for adding, deleting, hiding and unhiding sheets
Protection from changing size or position of windows.
26. Explain the difference between SUBSTITUTE and REPLACE function in MS-Excel?
Ans:
The SUBSTITUTE function substitutes one or more instances of old text with the new text in a string.
Syntax: SUBSTITUTE(text, oldText, newText, [instanceNumber])
Example: Let text at A2 be ex99,ex99
SUBSTITUTE(A2,”9?,”8?,1) =>ex89,ex99
SUBSTITUTE(A2,”9?,”8?,2) =>ex88,ex99
SUBSTITUTE(A2,”9?,”9?) =>ex88,ex88
The REPLACE function swaps part of the text string with another set of text.
Syntax: REPLACE(oldText, startNumber, NumberCharacters, newText)
Example: Let text at A2 be ex99
REPLACE(A2,5,1,”00?) =>ex009
27. Difference between COUNT, COUNTA, COUNTIF and COUNTBLANK in Ms-Excel.
Ans:
COUNT is used to count cells containing numbers, dates, etc. any value stored as number excluding blanks.
COUNTA or Count All is used to count any cell value containing numbers, text, logical values, etc. any type of value excluding blanks.
COUNTBLANK count blank cells or cells with an empty string.
COUNTIF and COUNTIFS count cells matching a certain criteria.
28. What is IF function in Excel?
Ans:
To perform the logic test IF function is performed. It checks whether certain conditions is true or false. If the condition is true, then it will give result accordingly if the condition is false then the result or out-put will be different.
Example: For example, you select the cell, and you want to display that cell as “Greater than five,” when value is true (=5 or 5) and “less than five” when value is false (<5). For that by using IF condition you can display result.
=IF (Logical test, value if true, value if false)
=IF (A1>5, “Greater than five, “Less than five”)
29. Can we create shortcuts to Excel functions?
Ans:
Yes. ‘Quick Access Toolbar’ above the home button can be customized to display most frequently used shortcuts.
30. What is the use of LOOKUP function in Excel?
Ans:
In Microsoft Excel, the LOOKUP function returns a value from a range or an array.
31. How can you apply the same formatting to every sheet in a workbook in MS-Excel?
Ans:
Right Click ‘Worksheet tab’ > Choose ‘Select All Sheets’. Now any formatting done will be applied to the whole workbook. To apply to a particular group of sheets, select only those sheets that need formatting.
32. What are left, right, fill and distributed alignments?
Ans:
Left /Right alignment align the text to left and right most of the cell.
Fill as the name suggests, fill the cell with same text repetitively.
Distributed, spread the text across the width of the cell.
LEFT | FILL | DISTRIBUTED |
Ab | abababababababab | a b |
A | aaaaaaaaaaaaaaaa | A |
33. To move to the previous worksheet and next sheet, what keys will you press?
Ans:
To move to the previous worksheet, you will use the keys Ctrl + PgUp, and to move to the next sheet you will use keys Ctrl + PgDown.
34. What filter will you use, if you want more than two conditions or if you want to analyze the list using database function?
Ans:
You will use Advanced Criteria Filter, to analyze the list or if more than two conditions should be tested.
35. What is the quick way to return to a particular area of a worksheet?
Ans:
The quick way to return to a specific area of the worksheet is by using name box. You can type the cell address or range name in name box to return to a specific area of a worksheet.
36. Which function is used to determine the day of the week for a date?
Ans:
WEEKDAY () returns the day of the week for a particular date counting from Sunday.
Example: Let date at A1 be 12/30/2016
WEEKDAY(A1,1) =>6
37. What is the benefit of using formula in Excel sheet?
Ans:
Calculating the numbers in Excel sheet, not only help you to give the final ‘sum up’ of the number but, it also calculates automatically the number replaced by another number or digit. Through Excel sheet, the complex calculations become easy like payroll deduction or averaging the student’s result.
38. What is the “What If” condition in Excel formulas?
Ans:
The “What If” condition is used to change the data in Microsoft Excel formulas to give different answers.
Example: You are buying a new car and want to calculate the exact amount of tax that will be levied on it then you can use the “What If” function. For instance, there are three cells A4,B4, and C4. First cell says about the amount, the second cell will tell about the percentage (7.5%) of tax and the final cell will calculate the exact amount of tax.
39. How can you disable the automating sorting in pivot tables?
Ans:
To disable the automating sorting in pivot tables,
Go to > “More Sort Options”> Right Click “Pivot table” > Select “Sort” menu > Select “More Options” > Deselect the “Sort automatically when the report is created.”
40. What is the AND function does in Excel?
Ans:
Like IF function, AND function also does the logical function. To check whether the output will be true or false the AND function will evaluate at least one mathematical expression located in another cell in the spreadsheet. If you want to see the output of more than one cells in a single cell, it is possible by using AND function.
Example: If you have two cells, A1 and A2, and the value you put in those two cells are >5 and you want result should display as ‘TRUE’ in cell B1 if value>5, and ‘False’ if any of those values<5. You can use AND function to do that.
41. How cell reference is useful in the calculation?
Ans:
In order to avoid writing the data again and again for calculating purpose, cell reference is used. When you write any formula, for specific function, you need to direct Excel the specific location of that data. This location is referred as, cell reference. So, every time a new value added to the cell, the cell will calculate according to the reference cell formula.
42. What are the most important data formats seen in Excel, and how are they used?
Ans:
Your Excel interview may start out with an easier question like this one. If you’ve spent any time in Excel, you’ve almost certainly had a chance to experiment with different data types, and will likely be familiar with some of the most frequently-used:
Numbers. Numbers are one of the most frequently-seen data types in Excel. They can be formatted with a customized number of decimal places, and appear with or without commas separating the thousands digits. Numbers can be added, subtracted, divided, multiplied, or included in formulas and functions that accept numerical inputs.
Dates. Excel can display dates in any number of ways, including the classic US-style MM/DD/YYYY format. Dates can be added or subtracted using standard addition and subtraction, and can also be manipulated using a slew of date-based functions. Interestingly, dates in Excel are technically also stores as numbers, with each date represented as the number of days elapsed since January 1, 1900. For example, the date May 6, 2019 is stored in Excel as the number 42,129, because there are 42,129 days between January 1, 1900, and May 6, 2019.
Percentages. Numbers can also be formatted as percentages, which multiplies the given number by 100 and adds a percentage sign at the end. For example, the number 0.08 is equivalent to the percentage 8%.
Strings. Text is stored in Excel in a format called a string. Strings of text can contain standard characters such as letters, numbers, and punctuation; strings can be manipulated via text-manipulation functions like MID and RIGHT.
43. What is the order of operations used when evaluating formulas in Excel?
Ans:
Just like in standard mathematics, Excel uses an order of operations when evaluating different operators within the formula bar. You’ll almost surely recognize the acronym PEMDAS — it’s the order of operations taught in mathematics classes worldwide, and it’s also the order that Excel uses. PEMDAS stands for:
- Parentheses
- Exponents
- Multiplication
- Division
- Addition
- Subtraction
When evaluating formulas, Excel always processes operators in this order. If you find yourself receiving an unexpected result from your mathematical formulas, double-check to make sure that parentheses are used properly to achieve the results you want.
44. What is a function in Excel?
Ans:
If you’re a frequent Excel user, functions are probably second nature to you. You’ve used SUM, AVERAGE, and even VLOOKUP so many times that you don’t even think about what a function actually is when you’re creating spreadsheets.
As such, this question may come as a surprise to you — especially because describing what a function is can be a bit difficult if you never think about it.
Here’s an easy way to do it: think of a function like a recipe. It’s used to combine a bunch of ingredients — which may not taste particularlly good individually — into something much more useful.
The name of the function is like the title of the recipe. It describes what the function does, like take a SUM or an AVERAGE.
The arguments of the function describe what ingredients go into it. Individual functions can take any number of arguments, from one to an infinite number. It all depends on the function.
Finally, the output of the function is what comes out the other side: a useful quantity that can show you important data, or be used as an input to other functions in your spreadsheet.
45. In your opinion, what are a few of the most useful functions in Excel? How do you use them?
Ans:
This is a tricky question, because it asks you to use your subjective judgement rather than answering objectively. As such, you’ll have a wide range of latitude in your response — and you should have a well thought-out reply prepared that demonstrates both your proficiency with Excel and your wide range of past experience using spreadsheets.
Here are a couple of our top recommendations for features, formulas, and functions to discuss:
INDEX MATCH. VLOOKUP and INDEX MATCH are two of Excel’s most important and commonly-used functions. As veteran Excel users know, they’re used to look up values from an external table, and are important parts of automating your work with dynamic spreadsheets. One of the two is bound to come up in any Excel interview, but if you get this question, we recommend bringing up INDEX MATCH. It’s a slightly more useful function, and also lacks many of the disadvantages of VLOOKUP, like the inability to insert new rows and columns into your sheets. If you go with this function, outline how you’ve used it in the past to dynamically lookup values and populate columns of data that would otherwise need to be manually copied and pasted.
IF statements. IF statements are another staple of any Excel veteran’s arsenal. Bring them up to let your interviewer know that you’ve created advanced spreadsheets that make decisions based on criteria calculated in real-time.
PivotTables. PivotTables are an extraordinarily useful tool, and if you’re applying for a job that requires intermediate or advanced Excel knowledge, they’re sure to be an important criteria used by your interviewer. You may have used PivotTables for any number of things in the past, but be sure to emphasize how useful they are when quick, accurate calculations are necessary based on large sets of data with hundreds or thousands of rows.
46. What is the syntax of the VLOOKUP function? Are there any disadvantages to using this function?
Ans:
VLOOKUP is one of the most commonly-used functions in the business world, and if you’ve got an Excel interview, you’ll almost certainly be asked about it. Be sure you’re properly prepared for your interview by memorizing the syntax of VLOOKUP by heart:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
VLOOKUP’s arguments can be described as follows:
- lookup_value defines a value to look up within an external table of data.
- table_array defines a table of data in which to look up the lookup_value. The lookup_value must appear within the first column of this table.
- col_index_num tells the function how many columns into the table_array to look for a value to return.
- range_lookup is a TRUE / FALSE switch that tells the function whether to look for an exact match, or an approximate match, to the specified lookup_value.
As for disadvantages — VLOOKUP does have one crucial one: since the col_index_num is usually entered manually into the function, VLOOKUP can break if columns of data are inserted into the table_array after the function is written. This may not seem like a major disadvantage, but can cause hours of frustration over broken formulas if you’re not careful.
To fix the problems that sometimes occur when columns of data are inserted, consider replacing your VLOOKUP formulas with INDEX MATCH, which contains similar functionality with none of the disadvantages.
47. What is the difference between absolute and relative cell references? In which situations would you use each?
Ans:
As many Excel users know, one of the most magical features of Excel is the cell reference. Cell references allow users to include the values of external cells in formulas dynamically — rather than hard-coding particular values manually.
However, cell references can be confusing when copied and pasted to different locations. By default, Excel uses relative cell references, which change dynamically as they are copied and pasted around a sheet. For example, if a reference to cell A1 is copied and pasted one row down and one column to the right, the new reference will point to cell B2. This allows users to perform similar calculations on different ranges of cells quickly and easily.
In contrast, absolute cell references do not change when they are copied and pasted to other locations within a sheet. Absolute cell references can be used on either rows, columns, or both at the same time, and are indicated using the $sign. For example, if a reference to cell $A$1 is copied and pasted one row down and one column to the right, the new reference will point to cell A1 — it won’t change at all, because both the row and column are locked. If a reference to cell $A1 is copied and pasted one row down and one column to the right, the new reference will point to cell A2 — only the row number will change, because the column letter is locked.
Here’s a handy table that will show you what the $ sign means depending on where you see it in a cell reference:
Format | Meaning | Explanation |
$A$1 | Row and column locked | Cell reference will not change at all as cell is copied and pasted. |
$A1 | Column locked | Only row reference will change as cell is copied and pasted. |
A$1 | Row locked | Only column reference will change as cell is copied and pasted. |
A1 | Nothing locked | Both row and column will change as cell is copied and pasted. |
48. What is a PivotTable, and when would you use one? What are the key PivotTable ‘sections’ into which users can drag columns?
Ans:
As one of the most-used Excel features in business settings, PivotTables are sure to come up during any in-depth Excel interview. Be sure you’re prepared in advance with a firm grasp of what exactly PivotTables are, and why they’re useful in practice.
Simply put, a PivotTable is a tool used to summarize large quantities of data quickly and easily. It can help you analyze a data set of tens, hundreds, or even thousands of rows with minimal effort using a number of pre-defined functions — like SUM, COUNT, and AVERAGE.
There are many use cases for PivotTables, but they’re most handy when you need to analyze a large data set quickly. If you’ve got high-level, one-off questions on a massive data set — for example, “how many cookies did we sell in February of last year”, or “which salesperson closed the most deals this March”, chances are a PivotTable is the perfect way to answer them.
Each PivotTable is composed of a number of key sections, into which the columns of a target data set can be bucketed:
Report filter. This section allows us to filter our table by one or more criteria. For example, we can only show data in our Pivot Table for the month of January.
Column labels. This section allows us to summarize data across columns, placing data labels along the top of the screen.
Row labels. This section allows us to summarize data across rows, placing data labels along the side of the screen.
Values. This section allows us to specify what we’re summarizing — for example, total sales or number of items ordered.
49. Do PivotTables have any drawbacks? How can they be solved?
Ans:
Of course, no Excel feature is without its drawbacks, and there’s a chance your interviewer will dive deeper into your PivotTable knowledge by asking you to explore some of their weaknesses. This will help the recruiter assess your in-depth experience on one of Excel’s most important features — after all, PivotTables can’t be used for everything!
If asked about the drawbacks of PivotTables, consider the following:
- Input data needs to be formatted properly. PivotTables can only be used in specific situations in which the input data set appears in flat file format— meaning that it’s broken down to it’s most granular level. If data is already summarized on a table, PivotTables may not be the best way to analyze it.
- PivotTables need to be refreshed if input data changes. This can lead to confusing and errors when using PivotTables as part of larger dashboards.
- PivotTables are easily modified, so it can be difficult to recreate your calculations. There are many times during which you’ll arrive at an answer using PivotTables, then have a difficult time recreating that answer if a supervisor asks to see your work in more detail. The flexibility of PivotTables can be a double-edged sword!
As an alternative to PivotTables, consider using conditional summary functions like SUMIFS and COUNTIFS, particularly when constructing dashboards. They can produce similar results, but are less ‘fluid’ — making your results more predictable and easier to track.
50. What are some best practices when creating complex models in Excel?
Ans:
Excel can be used for simple calculations, but it’s most effective when constructing complex mathematical models that help predict outcomes, project financial results, or track data over time. If you’re interviewing for a highly analytical role, there’s a good chance your recruiter will ask about how you can use spreadsheets to accomplish these more difficult tasks.
When talking through your answer, be sure to mention the following modeling best practice, which help keep your spreadsheets clean, organized, and flexible:
Create multiple tabs. Keeping different pieces of your model (for example, inputs, outputs, and calculations) on separate tabs can help with model organization, particularly if you’re planning to hand your spreadsheet off to someone who has never seen it before.
Use dynamic inputs. When constructing a model in Excel, values should never be hard-coded into cells — especially if they are flexible assumptions that may change down the line. Always keep assumptions and inputs on their own tab, and use cell references rather than hard-coded values to pull them into your formulas.
Add a table of contents. Large models can be extraordinarily complex, and adding a table of contents to the beginning can help keep things organized and easy to use for yourself and your supervisor.
Comment aggressively. You are the person who understands your models the best, but other people in your organization will doubtless be using them, too. So, be sure to over-comment and explain your calculations line-by-line so that they are as easy to follow as possible for other users.
51. Talk about some of the spreadsheets you’ve made that you’re most proud of.
Ans:
This is also a softer, more subjective question. It doesn’t have to do with the features of Excel itself — rather, your interviewer may ask it to get a sense for your past experiences with spreadsheets and your enthusiasm for quantitative analysis.
Before walking into your interview, be sure that you have 2-3 examples of your prior spreadsheet use prepared so that you can answer this question. The more excited you are about these examples, the better; it’s likely that your interviewer is also trying to get a sense for the excitement and passion that you’d bring to the job if hired.
Here are some examples of applications of Excel that you might want to talk about, if applicable:
- Constructing dashboards in Excel to measure and track business metrics;
- Putting together cash flow or revenue projections over time;
- Using Excel as a project management dashboard to track progress across multiple workstreams;
- Automating day-to-day tasks using spreadsheets with IF statements and other conditional logic; or
- Performing back-of-the-envelope calculations to estimate sales volume in various business scenarios.
52. What is Microsoft Excel?
Ans:
Microsoft Excel is an electronic spreadsheet program, created by multiple highly skilled engineers from Microsoft. It enables users to organize, format, and calculate data with formulas using a spreadsheet system broken up by rows and column.
We also use this tool for storing, organizing and manipulating the data. In addition, it also offers programming that supports VBA, and we can use external database to make dynamic reports, analysis etc. Smart use of this program saves a lot of time and helps in creating our own applications too.
53. What is the easiest solution to reduce the file size?
Ans:
Below are the steps to reduce the file size:
- Find the last cell that contains data in the sheet. Delete all rows and columns after this cell
- To delete the rows, press the key Shift+Space then press Ctrl+Shift+Down on your keyboard
- Rows will get selected till the last row. Press Ctrl+- on the keyboard to delete the blank rows
- To delete the column, Press the key Ctrl+Space then press Ctrl+Shift+Right Arrow key on your keyboard
- Columns will get selected till the last row
- Press Ctrl+- on the keyboard to delete the blank columns
54. How many rows and columns are there in Microsoft Excel 2003 and later versions?
Ans:
Refer to the table below for the number of rows, columns and cells for Microsoft Excel 2003 & later version:-
Excel Versions | Rows | Columns | Total Cells |
MS Excel 2003 | 65536 | 256 | 16777216 |
MS Excel 2007 | 1048576 | 16384 | 17179869184 |
MS Excel 2010 | 1048576 | 16384 | 17179869184 |
MS Excel 2013 | 1048576 | 16384 | 17179869184 |
55. Below are the few Excel 2010, Excel 2013 shortcuts keys
Ans:
Tab | Moves one cell to the right in a worksheet |
SHIFT+TAB | Moves to the previous cell in a worksheet. |
CTRL+TAB | Switches to the next tab in dialog box. |
CTRL+SHIFT+TAB | Switches to the previous tab in a dialog box. |
CTRL+1 | Displays the Format Cells dialog box. |
CTRL+SHIFT+L | This is Excel filter keyboard shortcuts, use for filtering a column. E.g. If you have to set a couple of filters, and you already have filters placed in your data, instead of clearing each filter which is time-consuming, just turn-off and turn-on the filters, by pressing CTRL+SHIFT+L twice and your work is done. |
CTRL+Arrow keys | To select a group of cells. Eg. it is difficult when you have to deal with large data and use the mouse to navigate the database. So to make your work easier, just rely on CTRL + arrow key combinations all the time to select a group of cells. |
ALT+ES – Paste Special > Values | Pasting the value.E.g. When we apply any formula and want only the values to be shown in the cells, we need to use copy and then paste special values. If we need to get data from other workbooks or need to format existing tables, we can use ALT+ES (press E then leave the key and press S) to paste special value. |
F4 | Repeat the last command or action. E.g. If the color of text in a particular cell is changed, you can repeat the command by just pressing F4. This key can be used to repeat any action that was last performed provided the file is still open. |
F2 | Edit a selected cell. You can use F2 to edit a cell or formula in a cell. |
Ctrl + F3 | Add name for any range. E.g. If you are making a dashboard or Excel model, just press Shift + F3, no matter where you are (formula editing, conditional formatting, data validation etc.) and you get a list of all the defined names. Pick one and press ok to add it to the formula you are typing. |
CTRL+K | Add Hyperlink. E.g. If you have more than a one sheet, Hyperlinks make your work book accessible and save your time. You can directly link the pages to one another using so that the sheets are accessible on a single click of the hyperlink. |
CTRL+T – | Insert Table. E.g. If you have selected any cell in a range of related data just press CTRL+T to make it a table. |
CTRL+S – | Save the sheet. |
Ctrl + R | Fills the range to the right of the data |
Ctrl + D | Fills range down word |
Ctrl+W | Close a particular work book |
F9 | Refresh the sheets’ formula |
Ctrl+PageDown/PageUp | Navigate sheets within the workbook |
Ctrl+shift+1 | Applies the Number format for two decimal places, thousands separator, and minus sign (-) for negative values. |
Alt F11 | Opens VBA editor |
Ctrl F6 | Switches to the next workbook window. |
Ctrl ~ | Change the cell value as general |
Ctrl + spacebar | Select a particular column |
Shift + spacebar | Select a particular row |
Ctrl + Shift + spacebar | Select entire sheet |
ALT + = | Auto sum for a column. E.g. When you have entered some data in a column and at the end of the column you need the summation of the column you can simply use ALT + = to save your time. |
Ctrl + B | Applies and removes bold format. |
F5 | A Go-to window will open which allows you to go to a particular cell based on the reference provided. |
56. What is Ribbon in MS-Excel?
Ans:
The ribbon in Excel consists of the tabs at the top. These tabs are split into groups which categorize related command buttons into sub tasks.
Each group has its respective command button and the dialog box launcher, which are present in the lower right corner in some of the groups.
This opens a dialog box containing a bunch of additional options we can choose from.
As per Excel’s default settings, we have 8 tabs. Which are:
- File
- Home
- Insert
- Page Layout
- Formulas
- Data
- Review
- View
57. Which option do we use to adjust the text within a cell and what is the procedure to do it?
Ans:
To adjust text in a cell, we use Wrap text option. It can be used in two ways:
Option 1: In the Home tab > Alignment > Wrap Text.
Option 2:
Press Ctrl+1 on your keyboard
Format cells dialog box will appear
In the Alignment Tab
Click on Wrap text
And then click on OK
58. How to select all the objects in the sheet?
Ans:
To select the object, we use Go to Special option.
Follow the below steps to select the objects:
- Press the shortcut key F5 to open the Go to Special dialog box
- Click on Special > Click on object > Click on OK
- All objects will get selected
59. What is Microsoft Excel?
Ans:
Microsoft Excel is an electronic worksheet or spreadsheet application which is used for organizing, storing, and manipulating and analyzing data. It is developed by Microsoft.
60. What are cells?
Ans:
The area where data is stored is known as cell.
61. Does each cell have unique address?
Ans:
Yes, each cell has a unique address depends on the row and column value of the cell.
62. How can you add cells, rows or columns in Excel?
Ans:
If you want to add a cell, row or column in Excel, right click the cell you want to add to and after that select insert from the cell menu. The insert menu makes you able to add a cell, a column or a row and to shift the cells affected by the additional cell right or down.
63. How would you format a cell? What are the options?
Ans:
A cell can be formatted by using the format cells options. There are 6 format cells options:
Number
Alignment
Font
Border
Fill
Protection
64. What is the use of comment? How to add comments to a cell?
Ans:
Comments are used for a lot of reasons:
Comments are used to clarify the purpose of the cells.
Comments are used to clarify a formula used in the cell.
Comments are used to leave notes for others users about a cell.
To add a comment: Right click the cell and choose insert comment from the cell menu. Type your comment.
65. What does the red triangle indicate at the top right hand corner of the cell?
Ans:
The red triangle at the top right hand corner of a cell indicates that there is a comment linked to the particular cell. If you put your cursor on it, it will show the comment.
66. How would you add comments to a cell?
Ans:
To add a comment to a cell, you right click the cell and choose insert comment from the cell menu. Type your comment in the comment area provided. A red triangle at the top right hand corner of a cell indicates that there is a comment linked to that particular cell. To remove a comment from a cell, right lick the cell and then select delete comment from the cell menu.
67. What are charts in MS Excel?
Ans:
Charts are used to enable graphical representation of the data in Excel. A user can use any chart type, including column, bar, line, pie, scatter, etc. by selecting an option from Insert tab?s Chart group.
68. What is Freeze Panes in MS-Excel?
Ans:
Freeze Panes are used to lock any row or column. The locked row or column will be visible on the screen even after we scroll the sheet vertically or horizontally.
69. Which are the different workbook protection types in Excel?
Ans:
There are three ways to protect a workbook in Excel:
Password protection for opening a workbook
Protection for adding, deleting, hiding and unhiding sheets
Protection from changing size or position of windows.
70. What is the difference among COUNT, COUNTA, COUNTIF and COUNTBLANK in Ms-Excel?
Ans:
COUNT is used to count cells containing numbers, dates, etc. any value stored as number excluding blanks.
COUNTA or Count All is used to count any cell value containing numbers, text, logical values, etc. any type of value excluding blanks.
COUNTBLANK count blank cells or cells with an empty string.
COUNTIF and COUNTIFS count cells matching a certain criteria.
71. What is Ribbon?
Ans:
The ribbon specifies an area which runs along the top of the application and contains menu items and toolbars available in Excel. The ribbon has various tabs that contain groups of commands for use in the application.
72. Is it possible to hide or show the ribbon?
Ans:
You can hide or show (minimize or maximize) the ribbon by pressing CNTRL F1.
73. How to prevent someone from copying the cell from your worksheet?
Ans:
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.
74. What are the several report formats in Excel?
Ans:
There are three report formats in Excel:
Compact
Report
Tabular
75. How to sum up the rows and column number quickly in the Excel sheet?
Ans:
The SUM function is used to get the total sum of the rows and columns, in an excel worksheet.
76. How can you resize the column?
Ans:
There are two ways to resize a column:
To change the width of one column, drag the boundary on the right side of the column until you find your desirable width.
Select the Format from the home tab, and in Format, select the AUTOFIT COLUMN WIDTH under cell section. Click on this to change the cell size.
77. Is it possible to make Pivot table using multiple sources of data?
Ans:
If the multiple sources are different worksheets from the same workbook, then you can use these multiple sources of data to make Pivot table.
78. How can you check whether the Pivot table is modified or not?
Ans:
To check whether the Pivot table is modified or not, you should use the “PivotTableUpdate” in worksheet containing the pivot table.
79. What does the IF function in Excel?
Ans:
IF function is used in Excel to check whether certain conditions are true or false. If the condition is true then it will give the result accordingly and if the condition is false the result or output will be different.
80. What filter should we use, if you want more than two conditions or if you want to analyze the list using database function?
Ans:
You should use “Advanced Criteria Filter” to analyze the list or test more than two conditions.
81. What are the advantages of using formula in Excel sheet?
Ans:
Formula makes it easy to calculate the numbers in Excel sheet. It also calculates automatically the number replaced by another number or digit. It is used to make complex calculations easy.
82. What is the order of sequence of operating mathematical operation in Excel?
Ans:
The order of sequence is written as BEDMAS:
Brackets
Exponents
Division
Multiplication
Addition
Subtraction
83. What is the use of LOOK UP function in MS Excel?
Ans:
The LOOK UP function is used to return a value from an array.
84. What is a Macro in Excel? How to create an Excel Macro?
Ans:
Excel Macro is the set of instructions that is recorded by users for repetition purposes. It is created by the users for repetitive instructions and functions they perform on a regular basis.
How would you reduce the file size? What is the easiest way to reduce the file size?
You can use the following steps to reduce the file size:
- Find the last cell that contains data in the sheet. Delete all rows and columns after this cell.
- To delete the rows, press the key Shift+Space then press Ctrl+Shift+Down on your keyboard.
- Rows will get selected till the last row. Press Ctrl+- on the keyboard to delete the blank rows.
- To delete the column, Press the key Ctrl+Space then press Ctrl+Shift+Right Arrow key on your keyboard.
- Columns will get selected till the last row.
- Press Ctrl+- on the keyboard to delete the blank columns.
85. How many rows and columns are there in Microsoft Excel 2003 and later versions?
Ans:
You can see the number of columns, rows, cells for Microsoft Excel version 2003 and later versions in the following table:
Excel Versions | Rows | Columns | Total Cells |
MS Excel 2003 | 65536 | 256 | 16777216 |
MS Excel 2007 | 1048576 | 16384 | 17179869184 |
MS Excel 2010 | 1048576 | 16384 | 17179869184 |
MS Excel 2013 | 1048576 | 16384 | 17179869184 |
86. What is the syntax of Vlookup?
Ans:
Vlookup Syntax:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
87. Is it possible to make pivot table using multiple sources of data?
Ans:
Yes. It is possible by using data modeling technique.
Interesting, will come back here once in a while.