Excel 2007, 2010, 2013 Questions and Answers
Excel 2007, 2010, 2013 Questions and Answers
Excel is one of the best application from Microsoft office. Following are the top most frequently asked questions and answers about excel. This questions are for Excel 2007 , Excel 2010 and Excel 2013.
If you want to download Latest Excel Application please visit Microsoft Office official site.
1) What are the total no of cells in 2007 & 2010 Excel ?
Answer: 17,179,869,184 cells (16,384 columns X 1,048,576 rows)
2) what is the file extension of 2007 & 2010 excel ?
Answer: .xlsx format
3) What are the total no of rows and columns 2007 & 2010 Excel ?
Answer: 16,384 columns and 1,048,576 rows
4) What is the short cut key to go from 1st to the last cell?
Answer: CTRL+ARROW KEY (To Go from 1st cell of the row/column to last cell of the row/column)
5) How to write formula in Excel?
- Click the cell in which you want to enter the formula.
- In the formula bar , type = (equal sign).
- Do one of the following: To create a reference, select a cell, a range of cells, a location in another worksheet, or a location in another workbook.
6) Where is the address bar located in Excel?
7) Where is the formula bar located in Excel?
The formula bar in Excel is located above the work area of the spreadsheet. ( If not active then go to View tab and tick formula bar)
8) What is transpose in excel?
If data is entered in columns or rows, but you want to rearrange that data into rows or columns instead, you can quickly transpose the data from one to the other
1. On the Home tab, in the Clipboard group, click Copy.
2. Go to the cell that you want to transpose it.
3. On the Home tab, in the Clipboard group, click the arrow below Paste, and then click Transpose.
9) What is wrap text in excel?
Microsoft Excel can wrap text so it appears on multiple lines in a cell. You can format the cell so the text wraps automatically, or enter a manual line break.
- In a worksheet, select the cells that you want to format.
- On the Home tab, in the Alignment group, click Wrap Text .
10) What is merge & Center in excel?
When you merge two or more adjacent horizontal or vertical cells, the cells become one larger cell that is displayed across multiple columns or rows.
1. Select two or more adjacent cells that you want to merge.
2. On the Home tab, in the Alignment group, click Merge and Center.
11 How to Insert symbols in Excel?
- On the worksheet, click the cell in which you want to insert the symbol.
- On the Insert tab, in the Text group, click Symbol.
- Click the Symbols tab.
12 ) what is Auto Sum in excel?
If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you’re done.
13) What is excel sort and filter?
When you enter data into your worksheet it is often unorganized making it difficult to examine. When analyzing the information in your spreadsheet, you may need to rearrange the data in different ways to answer different questions. Excel’s sorting feature can help your rearrange your data so you can use it more efficiently.
Note: If your spreadsheet contains formulas, be careful when using the sort feature. Formulas rely on cell references to perform their calculations and moving the data with the sort feature may destroy these references.
To sort a list of data:
Select a single cell in the column containing the data you want to sort.
Select the Home tab.
Under the Editing group, press the Sort and Filtering button and select the order you want your data to be sorted.
Note: If you select an entire column, Excel will sort only that column and will mismatch the data contained in the other columns.
Filtering is a way that you can use Excel to quickly extract certain data from your spreadsheet. Unlike sorting, filtering doesn’t just reorder the list. It actually hides the rows or columns containing data that do not meet the filter criteria you define. Excel has an Auto Filter feature that makes it very easy to extract data from your spreadsheet.
To use the Auto Filter:
Click on any cell in your spreadsheet.
Select the Home tab.
Under the Editing group, press the Sort and Filtering button and select the Filter button.
Drop-down menus will appear next to each cell heading.
Clicking on any drop-down menu will provide you with options for sorting or filtering.
13) What is excel short cut key for current date and time?
To insert the current date, press CTRL+; (semi-colon).
To insert the current time, press CTRL+SHIFT+; (semi-colon).
14) How to protect Excel workbook ?
To encrypt your workbook and set a password to open it:
- Click the Microsoft Office Button , point to Prepare, and then click Encrypt Document.
- In the Password box, type a password, and then click OK.You can type up to 255 characters. By default, this feature uses AES 128-bit advanced encryption. Encryption is a standard method used to help make your file more secure.
- In the Reenter password box, type the password again, and then click OK.
- To save the password, save the file.
Password protect worksheet or workbook elements
- Select the worksheet that you want to protect.
- To unlock any cells or ranges that you want other users to be able to change, do the following:
- Select each cell or range that you want to unlock.
- On the Home tab, in the Cells group, click Format, and then click Format Cells.
- On the Protection tab, clear the Locked check box, and then click OK.
- To hide any formulas that you do not want to be visible, do the following:
- In the worksheet, select the cells that contain the formulas that you want to hide.
- On the Home tab, in the Cells group, click Format, and then click Format Cells.
- On the Protection tab, select the Hidden check box, and then click OK.
- To unlock any graphic objects (such as pictures, clip art, shapes, or Smart Art graphics) that you want users to be able to change, do the following:
- Hold down CTRL and then click each graphic object that you want to unlock.This displays the Picture Tools or Drawing Tools, adding the Format tab.TIP You can also use the Go To command to quickly select all the graphic objects in a worksheet. On theHome tab, in the Editing group, click Find & Select, and then click Go To. Click Special, and then clickObjects.
- On the Format tab, in the Size group, click the Dialog Box Launcher next to Size.
- On the Properties tab, clear the Locked check box, and if present, clear the Lock text check box.NOTE You do not need to unlock buttons or controls for users to be able to click and use them. You can unlock embedded charts, text boxes, and other objects created with the drawing tools that you want users to be able to modify.
- On the Review tab, in the Changes group, click Protect Sheet.
- In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.More information about the elements that you can select
15) How to Freeze Panes in Excel?
To keep an area of a worksheet visible while you scroll to another area of the worksheet, you can lock specific rows or columns in one area by freezing or splitting 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.
On the worksheet, do one of the following:
- To lock rows, select the row below the row or rows that you want to keep visible when you scroll.
- To lock columns, select the column to the right of the column or columns that you want to keep visible when you scroll.
- To lock both rows and columns, click the cell below and to the right of the rows and columns that you want to keep visible when you scroll.
- On the View tab, in the Window group, click the arrow below Freeze Panes.
- Do one of the following:
- To lock one row only, click Freeze Top Row.
- To lock one column only, click Freeze First Column.
- Do one of the following:
16) What is Excel status bar?
The status bar at the bottom of Microsoft Office programs displays status on options that are selected to appear on the status bar. Many options are selected by default. If you want to customize the status bar, right-click it, and then click the options that you want.
In Microsoft Office Excel 2007, the following options are available on the status bar.
|This option||Displays the following on the status bar|
|Cell Mode||Selected by default, this option displays the current cell editing mode on the left side of the status bar. One of the following modes is displayed.
|Signatures||Selected by default, this option indicates that the active workbook has been digitally signed.|
|Information Management Policy||Selected by default, this option indicates that Information Rights Management (IRM) has been used to restrict permission to content in the active workbook.|
|Permissions||Selected by default, this option displays an icon next to the Cell Mode indicator that you can click to view the current read and edit document permissions. This icon displays only when access to the document has been restricted (Microsoft Office Button, Prepare, Restrict Permission,Restricted Access).|
|Caps Lock||When selected, this option displays Caps Lock to indicate that CAPS LOCK is turned on to allow typing in uppercase characters. This option is not selected by default.|
|Num Lock||When selected, this option displays Num Lock to indicate that NUM LOCK is turned on to allow using the keys on the numeric keypad to enter numbers in the worksheet. This option is not selected by default.|
|Scroll Lock||Selected by default, this option displays Scroll Lock to indicate that SCROLL LOCK is turned on to allow scrolling in the worksheet by using the arrow keys.|
|Fixed Decimal||Selected by default, this option displays Fixed Decimal to indicate that all numerical values that you enter on the worksheet will be displayed with fixed decimals. This option is turned on when you select the Automatically insert a decimal point check box under Editing options on theAdvanced tab of the Excel Options dialog box (Microsoft Office Button , Excel Options,Advanced).|
|Overtype Mode||When selected, this option displays Overtype to indicate that INSERT was pressed to activate overtype mode while editing cell contents in cell editing mode (double-click a cell or press F2). This option is not selected by default.|
|End Mode||Selected by default, this option displays End Mode to indicate that END was pressed to activate end mode. Pressing END followed by an arrow key moves the selection in the direction of the arrow key, stopping at the start and end of data, and then the start or end of the worksheet.|
|Macro Recording||Selected by default, this option displays a button next to the Cell Mode indicator that you can click to start recording a macro.|
|Selection Mode||Selected by default, this option displays one of the following cell selection modes.
|Page Number||Selected by default, this option displays the page number of the selected worksheet page and the number of pages in the worksheet when you are working in Page Layout view or Print Preview view.|
|Average||Selected by default, this option displays the average that is calculated from selected cells that contain numerical values.|
|Count||Selected by default, this option displays the number of selected cells.|
|Numerical Count||When selected, this option displays the number of selected cells that contain numerical values. This option is not selected by default.|
|Minimum||When selected, this option displays the minimum numerical value in selected cells. This option is not selected by default.|
|Maximum||When selected, this option displays the maximum numerical value in selected cells. This option is not selected by default.|
|Sum||Selected by default, this option displays the sum of numerical values in selected cells.|
|View Shortcuts||Selected by default, this option displays the Normal view, Page Layout view, and Page Break Preview buttons. You can click these buttons to change the current view.|
|Zoom||Selected by default, this option displays the Zoom level. You can click Zoom to open the Zoomdialog box, where you can specify the percentage of magnification that you want to use.|
|Zoom Slider||Selected by default, this option displays the Zoom slider with the Zoom out and Zoom inbuttons. You can then drag the slider or click the Zoom out and Zoom in buttons to magnify the content of the worksheet to have a closer look, or to reduce the size of the content on the worksheet so that you can view more content.|
16) Construct formulas?
|Example formula||What it does|
|=SUM(A:A)||Adds all numbers in column A|
|=AVERAGE(A1:B4)||Averages all numbers in the range|
|+ (plus sign)||Addition||3+3|
|– (minus sign)||Subtraction
|/ (forward slash)||Division||3/3|
|% (percent sign)||Percent||20%|
|= (equal sign)||Equal to||A1=B1|
|> (greater than sign)||Greater than||A1>B1|
|< (less than sign)||Less than||A1<B1|
|>= (greater than or equal to sign)||Greater than or equal to||A1>=B1|
|<= (less than or equal to sign)||Less than or equal to||A1<=B1|
|<> (not equal to sign)||Not equal to||A1<>B1|
17) Excel Short cut key to switch from one sheet to another?
|CTRL+PgUp||Switches between worksheet tabs, from left-to-right.|
|CTRL+PgDn||Switches between worksheet tabs, from right-to-left.|
18) How many sheets are there in Office 2007 and 2013 as default?
Answer: 3 (Sheet 1, Sheet, Sheet 3)
18) What is the maximum number of worksheets can insert in Excel workbook?
Answer : 255 worksheet
19) In address what is A and 1 ?
A1 Optional. A logical value that specifies the A1 or R1C1 reference style. In A1 style, columns are labeled alphabetically, and rows are labeled numerically.
Extra: In R1C1 reference style, both columns and rows are labeled numerically. If the A1 argument is TRUE or omitted, the ADDRESS function returns an A1-style reference; if FALSE, the ADDRESS function returns an R1C1-style reference.
To change the reference style that Excel uses, click the Microsoft Office Button , click Excel Options, and then click Formulas. Under Working with formulas, select or clear the R1C1 reference stylecheck box.
20) How to change the default colors that Excel?
Every workbook uses a palette of 56 colors, but you can change palette for the current workbook or even change the default colors for new workbooks.
To change colors of the current workbook:
1. On the Page Layout tab, click Colors, and pick the color set you want
2. To create your own set of colors, click Customize Colors.
3. Click the button next to the theme color you want to change (for example, Accent 1 or Hyperlink), and then pick a color under Theme Colors.
4. To create your own color, click More Colors, and then pick a color on the Standard tab or enter numbers on the Custom tab.
5. TIP Under Sample (shown above), you can see a preview of the changes that you made.
6. Repeat this for all the colors you want to change.
7.In the Name box, type a name for the new theme colors, and click Save.
Excel Formulas You Should Definitely Know
Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)
The SUM formula does exactly what you would expect. It allows you to add 2 or more numbers together. You can use cell references as well in this formula
The count formula counts the number of cells in a range that have numbers in them.
Counts the number of non-empty cells in a range. It will count cells that have numbers and/or any other characters in them.
The COUNTA Formula works with all data types.
The LEN formula counts the number of characters in a cell. Be careful though! This includes spaces
Gets rid of any space in a cell, except for single spaces between words. I’ve found this formula to be extremely useful because I’ve often run into situations where you pull data from a database and for some reason extra spaces are put in behind or in front of legitimate data. This can wreak havoc if you are trying to compare using IF statements or VLOOKUP’s.
6. RIGHT, LEFT, MID
Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of characters).
(Note: In all of these formulas, wherever it says “text” you can use a cell reference as well)
These formulas return the specified number of characters from a text string.RIGHT gives you the number of characters from the right of the text string, LEFT gives you the number of characters from the left, and MID gives you the specified number of characters from the middle of the word. You tell the MID formula where to start with the start_number and then it grabs the specified number of characters to the right of the start_number.
Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
By far my most used formula. The official description of what it does: “Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify…”. (See the full explanation of VLOOKUP) Basically, you define a value (the lookup_value) for the formula to look for. It looks for this value in the leftmost column of a table (the table_array).
8. IF Statements
Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)
When you’re doing an analysis of a lot of data in Excel there are a lot of scenarios you could be trying to discover and the data has to react differently based on a different situation.
9. SUMIF, COUNTIF, AVERAGEIF
Formulas: =SUMIF(range, criteria, sum_range), =COUNTIF(range, criteria), =AVERAGEIF(range, criteria, average_range)
These formulas all do their respective functions (SUM, COUNT, AVERAGE) IF the criteria are met. There are also the formulas: SUMIFS, COUNTIFS, AVERAGEIFS where they will do their respective functions based on multiple criteria you give the formula.
A fancy word for combining data in 2 (or more) different cells into one cell. This can be done with the Concatenate excel formula or it can be done by simply putting the & symbol in between the two cells. If I have “Steve” in cell A1 and “Quatrani” in cell B1 I could put this formula: =A1&” “&B1 and it would give me “Steve Quatrani”. (The “ “ puts a space in between what you are combining with the &). I can use =concatenate(A1, “ “, B1) and it will give me the same thing: “Steve Quatrani”