The 50 most common Excel skills in your job

1, Excel to determine whether the score grades pass, how to do?

A: Excel can judge whether the score score is passed or not.

=if (a1>60, "pass", "fail")

2, Excel frequency statistics with what function?

A: Frequency returns the frequency distribution of the data in a range in a column of vertical arrays, using a reply frequency or frequency View sample.

3. When will excel use the $ symbol?

A. When you copy a formula, when the reference position of the cell does not want to change, add $ to the line number or column before you know the details reply to the "absolute reference" view Tutorial

4, merged cells after the data or text I want to retain how to deal with?

A: Multiple cells contain content, and if you want to keep the contents of all cells after merging, you can use the following method.

1. Select the range of cells and pull the column widths to the width of all the cells that can be merged.

2. Start tab-edit-justify. Combine the contents of multiple cells into a single cell

3. Press the Alt+enter key at the separated space to add a forced line break, wrapping.

4. Merge Cells again

5, after inserting the table found that the number of rows is not enough, how to quickly insert more lines?

A: How many rows to select if you need to insert them, and then how many empty rows to insert at a time

6, merged cells after the data or text I want to retain how to deal with

A: Multiple cells contain content, and if you want to keep the contents of all cells after merging, you can use the following method.

1. Select the range of cells and pull the column widths to the width of all the cells that can be merged.

2. Start tab-edit-justify. Combine the contents of multiple cells into a single cell

3. Press the Alt+enter key at the separated space to add a forced line break, wrapping.

4. Merge Cells again

7. How to copy and paste line width

A: After pasting the paste option, you will have the option to keep the source column wide. As shown in the following figure

8. How Excel converts numeric quantities into text

A: Data-breakdown-Third Step selected text

9, Excel How to complete the null value of "0"

A: Position-condition-Null value-enter 0 in the formula bar, press Ctrl+enter to complete the input

10. Where is the excel2010 insertion special symbol?

excel2010 version of the special symbol, a lot of students in search of this problem, can be sure that the excel2010 version of this command. You don't have to bother looking for it, if you want to insert a symbol, you can find it from the Insert tab-symbol. In the micro-platform reply "special symbol" can see more symbol Input Method

11, Excel How to remove the name of the address bar

How Excel removes the name of the address bar, excel2003 version, Insert menu-name-definition-in the pop-up window find the name point Delete button, version 2010, Formula tab-Name Manager-Find the name point delete

12. How to display the week by date?

Q: How Excel displays the week by date

Answer: Formula

=text (A2, "AAAA")

=weekday (a2,2)

You can also display the date as a week by formatting

13, How does Excel hide the formula?

In Excel, how do you hide a formula so that other people can't see it?

A: Hiding formulas in Excel is set by formatting cells.

Select the cell that contains the formula, right-click the menu to format the cell, and then in the cell formatting window that pops up the tab, check the hidden formula options.

Last through the tools-protection-protection worksheet (excel2010 is through the review-Protect worksheet). Then you can't see the formula in the cell or the edit bar.

14, Excel table in the pull to copy the number, why not grow? How to increase?

A: When you copy numbers, you just have to press the CTRL key and then pull down, and the numbers will change.

15. Which is the square function in Excel?

Which is the square function in Excel?

In Excel, we sometimes need to calculate the square of a number, how to calculate, Excel provides two methods

1 using the caret ^

Example 3 of 2 times can be written =3^2 result is 9

2 Using the square function

=power (3,2) means 3 of 2, if 4 of 3 times can be written as =power (4,3)

16, Excel merged cell copy formula how to operate AH

Excel table, if you want to copy the formula in a cell, you will be prompted to interrupt your copy in the same way that we can fill the formula with a shortcut key.

Select all the ranges that contain the merged cells (the first cell contains a formula), double-click the formula in the edit bar, and then press CTRL + ENTER to fill.

17, Excel number can not sum, how to do?

Data imported into Excel is actually in the form of text (the numbers are right-aligned by default and the text is left-aligned), even if reformatting the cells is a number.

The following methods can help you quickly convert to a number that can be calculated

Select the number range, the first cell must include numbers, and the upper left of the cell has a green triangle, select the green triangle, the point converted to a number. As shown in the following figure

18, Excel produces random number, how to do?

A: Excel provides a function that can generate random numbers rand, which can be used to generate random numbers in a specified range

=rand () * (maximum-minimum) + decimal number. Like generating random numbers between 10~100.

=rand () *90+10

What if you want to generate a random integer?

=int (rand () *90+10) can also be =randbetween (1,100)

19. How to open root in Excel

Q: How do I open the square root in Excel, for example, 9 open 2 times, the result is 3

A: The square root in Excel can be completed with the ^ symbol, such as

The result of 9^ (1/2) is 3

20, Excel screening can not be used

Q: When you use AutoFilter in Excel, it's not available.

A: There are a number of reasons why a filter cannot be used, and the most common reason is that the worksheet or workbook is protected and you can check to see if the worksheet has added a protection password.

21, One-fourth How to fight

A: First enter 0 and then enter a space, and then enter 1/4.

22. How to restrict the use of filter buttons in Excel

A: How do you limit the use of filter buttons in Excel? The answer may be unexpected, if you are a EXCEL2003 user, you can execute: Tools menu-Options-Video-Object selection all do not show, you look again AutoFilter has been used before.

23, Excel even row how to select and delete?

A: There are many ways to excel with even rows, but the best way to do this is by auxiliary columns + AutoFilter. The specific steps are

1. In the final secondary column, enter the number 1 in line 2, select 1 rows and 2 rows to drag the copy down, and the even behavior of the secondary column is filled with 1 after the copy.

2. Filter-AutoFilter, select a row with a value of 1.

3. Select all of the even-numbered rows displayed.

If you delete even rows, you can delete even the filtered rows directly.

24. The first line hidden in Excel is displayed

A: Select the second row, then drag up to select, Unhide, or select all, format-line-unhide

25, Excel open the default setting to 03 version, if you do

Set the default open version 03 method;

To the installation directory in Excel 2003, change the version 03 Excel.exe to Excel2003.exe, and then you can set the default open Excel file to version 03 with the normal method.

26, Excel table calculation formula can not be used

The formula in the table still exists, but if you change the number of one of the cells, how does the formula end up? It's a 22-multiplied formula that automatically updates the results, and now it's not okay.

Enter a new number, still the original result

A: Tools menu-Options-recalculate you see whether it is manual or automatic

27, Excel each page displays the table head, how to set up?

Q: When you open a table, the table header is set at the top of each page because the table is longer.

A. In Excel Print Preview view, open the Sheet tab in the Page Setup dialog box, click the Compress dialog button to the right of the top header row text box, select the range of cells that contains the header and top headings, and then click the button to return to the Page Setup dialog box and click OK.

28, Excel Multi-conditional Sum function has what

In Excel, what functions can be used to complete a multiple conditional sum?

The SUMIF function is almost the same as the COUNTIF function. Multi-condition processing methods are as follows:

1. Multi-Item summation

=sum (SUMIF (b31:b35,{"A", "C"},c31:c35))

Version 2.03 can be replaced with the Sumproduct function.

=sumproduct ((MONTH (A3:A9) =3) * (b3:b9= "A") *c3:c9)

Version 3.07 can be replaced with sumifs:

=sumifs (D2:D11,A2:A11, "Company 1", B2:B11, "personnel department")

A multiple-condition rollup can also be done with the Sumproduct function.

=sumproduct (Condition 1 * Condition 2* condition 3* data column)

=sumproduct (a1:a10= "Finance") * (b1:b10= "cashier") *C1:C10) ' Statistics A is financial, while column B is the sum of the cashier's C-column data.