Top 25 Microsoft Excel Interview Questions

1)  What is Microsoft Excel?

Microsoft excel is an electronic worksheet developed by Microsoft, to be used for organizing, storing and manipulating.

2) What is ribbon?

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) How can I hide or show the ribbon?

Use the  CTRL and  F1 key to toggle &  show or hide the ribbon.

4) How can you wrap the text within a cell?

You have to 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.

5) Is it possible to prevent someone from copying the cell from your worksheet?

Yes, it is possible. 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.

6) How you can sum up the Rows and Column number quickly in the excel sheet?

By using SUM function you can get the total sum of the rows and columns, in an excel worksheet.

7) How you can add a new excel worksheet?

To add a new Excel worksheet you have to insert worksheet tab at the bottom of the screen.

8) How you can resize the column?

To resize the column you have to 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.

9) What are three report formats that are available in Excel?

• Compact
• Report
•  Tabular

10) How would you provide a Dynamic range in “Data Source” of Pivot Tables?

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.

11) Is it possible to make Pivot table using multiple sources of data?

If the multiple sources are different worksheets, from same workbook, then it is possible to make Pivot table using multiple sources of data.

12) Which event do you use to check whether the Pivot Table is modified or not?

To check whether the pivot table is modified or not we use “PivotTableUpdate” in worksheet containing the pivot table.

13) What you could do to stop the pivot table from loosing the column width upon refreshing?

Format loss in 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.

14) What is IF function in excel?

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”)

15) What does a red triangle at the top right of a cell indicates?

A red triangle in the cell indicates the comment associated with the cell.  If you place mouse on it, it will show the comment.

16) To move to the previous worksheet and to next sheet, what keys will you press?

 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.

17) What filter will you use, if you want more than two conditions or if you want to analyse the list using database function?

You will use Advanced Criteria Filter, to analyse the list or if more than two conditions should be tested.

18) What is the quick way to return to a specific area of a worksheet?

The quick way to return to a specific area of 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.

19) What is the benefit of using formula in excel sheet?

Calculating the numbers in excel sheet, not only help you to give the final ‘sum up’ of the number but, it also calculate automatically the number replaced by another number or digit.  Through excel sheet, the complex calculations becomes easy like payroll deduction or averaging the student’s result.

20) What is the “What If” condition in excel formulas?

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 is three cells A4,B4 and C4. First cell says about the amount, second cell will tell about the percentage (7.5%)  of tax and final cell will calculate the exact amount of tax.

21) How you can disable the automating sorting in pivot tables?

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”.

22) What is the AND function does in excel?

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 final result or output of more than one cells in 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.

23) How cell reference is useful in the calculation?

In order to avoid writing the data again and again forcalculating 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.

24) What is the sequence of operating mathematical operation in Excel?

The order of sequence of operating is BEDMAS

• Brackets
• Exponents
• Division
• Multiplication
• Addition
• Subtraction

25) What does a LOOK UP function searches in the MS Excel?

In Microsoft excel, the LOOKUP function returns a value from a range or from an array.

Your Excel formulas sheet: 15 tips for calculations and common tasks

Many of us fell in love with Excel as we delved into its deep and sophisticated formula features. Because there are multiple ways to get results, you can decide which method works best for you. For example, there are several ways to enter formulas and calculate numbers in Excel.

1. Manually enter Excel formulas:

Long Lists: =SUM(B4:B13)

Short Lists: =SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7). Or, place your cursor in the first empty cell at the bottom of your list (or any cell, really) and press the plus sign, then click B4; press the plus sign again and click B5; and so on to the end; then press Enter. Excel adds/totals this list you just “pointed to:” =+B4+B5+B6+B7.

Excel formulas

2. Click the Insert Function button

Use the Insert Function button under the Formulas tab to select a function from Excel’s menu list:

=COUNT(B4:B13) Counts the numbers in a range (ignores blank/empty cells).

=COUNTA(B3:B13) Counts all characters in a range (also ignores blank/empty cells).

3. Select a function from a group (Formulas tab)

Narrow your search a bit and choose a formula subset for Financial, Logical, or Date/Time, for example.

=TODAY() Inserts today’s date.

4. The Recently Used button

Click the Recently Used button to show functions you've used recently. It's a welcome timesaver, especially when wrestling with an extra-hairy spreadsheet.

=AVERAGE(B4:B13) adds the list, divides by the number of values, then provides the average.

5. Auto functions under the AutoSum button

Auto functions are my editor's personal favorite, because they're so fast. Select a cell range and a function, and your result appears with no muss or fuss. Here are a few examples:

=MAX(B4:B13) returns the highest value in the list.

=MIN(B4:B13) returns the lowest value in the list.

AutoSum in ExcelJD SARTAIN

Use the AutoSum button to calculate basic formulas such as SUM, AVERAGE, COUNT, etc.

Note: If your cursor is positioned in the empty cell just below your range of numbers, Excel determines that this is the range you want to calculate and automatically highlights the range, or enters the range cell addresses in the corresponding dialog boxes.

Bonus tip: With basic formulas, the AutoSum button is the top choice. It’s faster to click AutoSum>SUM (notice that Excel highlights the range for you) and press Enter.

Another bonus tip: The quickest way to add/total a list of numbers is to position your cursor at the bottom of the list and press Alt+ = (press the Alt key and hold, press the equal sign, release both keys), then press Enter. Excel highlights the range and totals the column.

Five handy formulas for common tasks

The five formulas below may have somewhat inscrutable names, but their functions save time and data entry on a daily basis.

Note: Some formulas require you to input the single cell or range address of the values or text you want calculated. When Excel displays the various cell/range dialog boxes, you can either manually enter the cell/range address, or cursor and point to it. Pointing means you click the field box first, then click the corresponding cell over in the worksheet. Repeat this process for formulas that calculate a range of cells (e.g., beginning date, ending date, etc.)

1. =DAYS

This is a handy formula to calculate the number of days between two dates (so there’s no worries about how many days are in each month of the range).

Example: End Date October 12, 2015 minus Start Date March 31, 2015 = 195 days

Formula: =DAYS(A30,A29)

2. =NETWORKDAYS

This similar formula calculates the number of workdays (i.e., a five-day workweek) within a specified timeframe. It also includes an option to subtract the holidays from the total, but this must be entered as a range of dates.

Example: Start Date March 31, 2015 minus End Date October 12, 2015 = 140 days

Formula: =NETWORKDAYS(A33,A34)

3. =TRIM

TRIM is a lifesaver if you’re always importing or pasting text into Excel (such as from a database, website, word processing software, or other text-based program). So often, the imported text is filled with extra spaces scattered throughout the list. TRIM removes the extra spaces in seconds. In this case, just enter the formula once, then copy it down to the end of the list.

Example: =TRIM plus the cell address inside parenthesis.

Formula: =TRIM(A39)

Excel formulas

4. =CONCATENATE

This is another keeper if you import a lot of data into Excel. This formula joins (or merges) the contents of two or more fields/cells into one. For example: In databases; dates, times, phone numbers, and other multiple data records are often entered in separate fields, which is a real inconvenience. To add spaces between words or punctuation between fields, just surround this data with quotation marks.

Example: =CONCATENATE plus (month,”space”,day,”comma space”,year) where month, day, and year are cell addresses and the info inside the quotation marks is actually a space and a comma.

Formula: For dates enter: =CONCATENATE(E33,” “,F33,”, “,G33)

Formula: For phone numbers enter: =CONCATENATE(E37,”-“,F37,”-“,G37)

5. =DATEVALUE

DATEVALUE converts the above formula into an Excel date, which is necessary if you plan to use this date for calculations. This one is easy: Select DATEVALUE from the formula list. Click the Date_Text field in the dialog box, click the corresponding cell on the spreadsheet, then click OK, and copy down. The results are Excel serial numbers, so you must choose Format>Format Cells>Number>Date, and then select a format from the list.

Formula: =DATEVALUE(H33)

Three more formula tips

As you work with formulas more, keep these bonus tips in mind to avoid confusion:

Tip 1: You don’t need another formula to convert formulas to text or numbers. Just copy the range of formulas and then paste as Special>Values. Why bother to convert the formulas to values? Because you can’t move or manipulate the data until it’s converted. Those cells may look like phone numbers, but they’re actually formulas, which cannot be edited as numbers or text.

Tip 2: If you use Copy and Paste>Special>Values for dates, the result will be text and cannot be converted to a real date. Dates require the DATEVALUE formula to function as actual dates.

Tip 3: Formulas are always displayed in uppercase; however, if you type them in lowercase, Excel converts them to uppercase. Also notice there are no spaces in formulas. If your formula fails, check for spaces and remove them.

Excel formulas

Share this article :

1

Related Articles

How do you tell if your computer has a virus

How do you tell if your Windows computer has a virus? Seriously! You have an  antivirus software installed and you go about using the computer with the feeling that it was well-protected and hence malware-free. But maybe, just maybe, there could be malware or a Trojan or a key logger operating on your computer without your knowledge and compromising your security or damaging your data.

How do you tell if your computer has a virus

Is your Windows computer infected? Learn to recognize malware infections and signs, symptoms that will tell you if your Windows PC has a computer virus. In this post we will see some of the symptoms of malware infection, that will tell you if you computer was compromised or infected with computer virus.

Symptoms of Malware Infection

The typical symptoms that your computer may have been compromised are:

1. Change in your browsers home page or default search
2. Your web browser hangs or becomes sluggish
3. Your computer starts behaving sluggishly or hangs often
4. You are unable to open security-related sites or Microsoft.com domains.
5. You get re-directed to web pages you did not intend to visit
6. Unexpected toolbars in your browser
7. Security software or Firewall disabled
8. Your security software throws up warnings or its icon turns red or something.
9. Pop-ups while browsing, unknown or excessive
10. Balloon Notifications from your system tray
11. Excessive CPU or Memory utilization
12. Internet or data transfer activity – Modem appears to be working overtime
13. You start seeing unexpected Windows error messages
14. Some programs start automatically
15. Some important Windows functions or features disabled. Eg, Task Manager, Registry Editor, Control Panel, Command Prompt, etc.
16. New unknown unwanted software installed, etc.
17. You see new icons on your desktop suddenly.

If you see any of these signs, if is quite possible that your computer may have become infected with a virus or malware.

Starting with Windows Vista, Microsoft introduced many security features in the operating system which were further improved upon in Windows 7 and Windows 8. Nevertheless, should your computer get infected with malware.

Share this article :

Connect to another computer using Remote Desktop Connection

With Remote Desktop Connection, you can connect to a computer running Windows from another computer running Windows that's connected to the same network or to the Internet. For example, you can use all of your work computer's programs, files, and network resources from your home computer, and it's just like you're sitting in front of your computer at work.

To connect to a remote computer, that computer must be turned on, it must have a network connection, Remote Desktop must be enabled, you must have network access to the remote computer (this could be through the Internet), and you must have permission to connect. For permission to connect, you must be on the list of users. Before you start a connection, it's a good idea to look up the name of the computer you're connecting to and to make sure Remote Desktop connections are allowed through its firewall.

If you're user account doesn't require a password to sign in, you'll need to add a password before you're allowed to start a connection with a remote computer.

Watch this video to learn how to connect to another computer using Remote Desktop Connection (1:13)

To allow remote connections on the computer you want to connect to

1.       Open System by clicking the Start button  , right-clicking Computer, and then clicking Properties.

2.       Click Remote settings.   If you're prompted for an administrator password or confirmation, type the password or provide confirmation.

The Remote tab in the System Properties dialog box

3.       Under Remote Desktop, select one of the three options.

4.       Click Select Users.

If you're an administrator on the computer, your current user account will automatically be added to the list of remote users and you can skip the next two steps.

5.       In the Remote Desktop Users dialog box, click Add.

6.       In the Select Users or Groups dialog box, do the following:

·         To specify the search location, click Locations, and then select the location you want to search.

·         In Enter the object names to select, type the name of the user that you want to add, and then click OK.

The name will be displayed in the list of users in the Remote Desktop Users dialog box. Click OK, and then click OK again.

Note

·         You can’t connect to a computer that's asleep or hibernating, so make sure the settings for sleep and hibernation on the remote computer are set to Never. (Hibernation is not available on all computers.) For information about making those changes, see Change, create, or delete a power plan (scheme).   

      To look up the computer name of the remote computer

1.       On the remote computer, open System by clicking the Start button  , right-clicking Computer, and then clickingProperties.

2.       Under Computer name, domain, and workgroup settings, you can find your computer name, and its full computer name if your computer is on a domain.

Your network administrator might also be able to give you the name of the computer.

To allow Remote Desktop connections through a Windows Firewall

If you're having trouble connecting, Remote Desktop connections might be getting blocked by the firewall. Here's how to change that setting on a Windows PC. If you're using another firewall, make sure the port for Remote Desktop (usually 3389) is open.

1.       On the remote computer, click Start and select Control Panel.

2.       Click System and Security.

3.       Click Allow a program through Windows Firewall under Windows Firewall.

4.       Click Change settings and then check the box next to Remote Desktop.

5.       Click OK to save the changes.

To set a password for your user account

Your user account must have a password before you can use Remote Desktop to connect to another computer.

1.       Click Start and select Control Panel.

2.       Double-click User Accounts.

3.       Select Change your Windows Password under User Accounts.

4.       Click Create a password for your account and follow the instructions on the screen.

To start Remote Desktop on the computer you want to work from

1.       Open Remote Desktop Connection by clicking the Start button  . In the search box, type Remote Desktop Connection, and then, in the list of results, click Remote Desktop Connection.

2.       In the Computer box, type the name of the computer that you want to connect to, and then click Connect. (You can also type the IP address instead of the computer name.)

Notes

·         To find the Remote Desktop Connection shortcut, click the Start button  , click All Programs, and then click Accessories. To quickly open Remote Desktop Connection, click the Start button  , type mstsc in the search box, and then press Enter.

·         Redirecting a device on your computer makes it available for use in a Remote Desktop session. If a Remote Desktop Connection dialog box appears after you click Connect and enter your credentials, you can redirect local devices and resources, such as your local drives or Clipboard. Click Details, and then select the check box for each item you want to redirect.

For more information about Remote Desktop, see Remote Desktop Connection: frequently asked questions.

Source : microsoft.com/