# Excel Formula & Operation Skills

Source: Internet
Author: User
Tags modulus

Input & reference

1. Drag a cellArrow(When dragging, press Ctrl to fill the string in sequence)

2. Enter the date (CTRL +;) and current time (CTRL + Shift + ;)

3. Enter a number as a string (enter a 'before entering a number ')

3. Change multiple cells to the same content (press Ctrl to select multiple target cells, enter the content, and press Ctrl + enter to end the input)

4. Selective pasting

5. relative reference & absolute reference & hybrid reference (\$, You can use F4. you can use F4)

Note: When the relative reference is dragged or cut, the directed cell changes.

Formulas & Functions

Note: excel2010 Functions

1. sum (range) // if the sum range is across sheet, you need to add the sheet name in front! Cell range, for example:Sheet1! C2: C11

2. Average (range) // calculate the average value

3. INT (value) // integer

4. Round (numeric value, number of digits after the decimal point) // rounding

= Round (2.15, 1) // rounds 2.15 To a decimal place, and the result is 2.2.
= Round (2.149, 1) // round 2.149 to a decimal place and the result is 2.1
= Round (-1.475, 2) // round-1.475 to two decimal places and the result is-1.48
= Round (21.5,-1) // round 21.5 to the left of the decimal point and the result is 20

5. rounddown (numeric value, number of digits after the decimal point) // directly Truncation

= Rounddown (2.15, 1) // truncate 2.15 To a decimal place and the result is 2.1.
= Rounddown (2.149, 1) // truncates 2.149 directly to a decimal place and returns 2.1
= Rounddown (-1.475, 2) // truncates-1.475 directly to two decimal places and returns-1.47

6. Roundup (numeric value, number of digits after the decimal point) // carry directly

= Roundup (2.15, 1) // carry 2.15 directly to a decimal place and the result is 2.2
= Roundup (2.149, 1) // carry 2.149 directly to a decimal place and the result is 2.2
= Roundup (-1.475, 2) // carry-1.475 directly to two decimal places and the result is-1.48

7. Mod (numeric value, modulus) // Modulus

8. Count (range) // count object is a number

9. counta (range) // count the count objects of counta as non-empty cells

10. Max (range) // calculate the maximum value

11. Min (range) // calculates the minimum value

12. Product (range) // concatenation

13. sumif (condition range, condition, sum range) sumifs (sum range, condition range 1, condition 1, condition range 2, condition 2) // condition summation

14. countif (condition range, condition) countifs (condition range 1, condition 1, condition range 2, condition 2) // count the number of conditions

15. Now () // current date and time

16. Today () // today's date

17. date (year, month, day) // Date of Generation

18. Year (date) month (date) Day (date) // calculate the year, month, and day from the date

19. datedif (start date, end date, unit) // This function calculates the difference between two days

= Datedif ("2000-1-1", "2010-7-8", "Y") // 10
= Datedif ("2000-1-1", "2010-7-8", "M") // 126
= Datedif ("2000-1-1", "2010-7-8", "D") // 3841
= Datedif ("2000-1-1", "2010-7-8", "MD") // 7
= Datedif ("2000-1-1", "2010-7-8", "ym") // 6
= Datedif ("2000-1-1", "2010-7-8", "YD") // 189

20. Len (text) // evaluate the length of the text

21. Left (text, number of truncated digits) // obtain the text of the specified length on the left of the text string

22. Right (text, number of captured digits) // obtain the text of the specified length on the right of the text string

23. mid (text, start digit, cut digit) // obtain the text in the specified area of the text string

24. Upper (text) // converts English characters into uppercase letters

25. Lower (text) // converts English characters to lowercase letters

26. Proper (text) // converts the first character of an English character to uppercase, and the other part to lowercase.

27. Text (value, format) // output text content in the specified format

= Text (now (), "YYYY") // 2013
= Text (now (), "[dbnum1] yyyy-mm-dd") // May 29, Year 13
= Text (now (), "[dbnum2] yyyy-mm-dd") // the log of Wu Yue, yyyy-mm-dd.
= Text ("0123456789", "[dbnum2] 0000000000") // zero errors

Format description:

(:) Time Separator
(/) Date Separator

D. display the day with no leading zero (1-31)
Dd displays the day with a leading zero number (01-31)
Ddd is abbreviated to Sun-sat)
The full name of dddd indicates Sunday-Saturday)
M Displays the month (1-12) with no leading zero ). If M is directly following h or HH, it will be displayed in minutes instead of months.
MM displays the month (01-12) with a leading zero ). If M is directly following h or HH, it will be displayed in minutes instead of months.
Mmm stands for month (Jan-Dec) in short)
Mmmm stands for month (Januar-December)

Y or YY represents year (00-99) by two digits)
YYY or yyyy indicates the year in four digits (0000-9999)
H displays the hour with no leading zero (0-23)
HH displays the hour with a leading zero number (00-23)
M Displays the score (0-59) after H or HH with no leading zero.
MM displays the minute (00-59) after H or HH with a leading zero number.
S: The second (0-59) must be displayed after H or HH with no leading zero.
SS displays the second (00-59) with a leading zero number after H or HH.
AM/PM is used with the uppercase am symbol for 12 hours before noon, and with the uppercase PM for 12 hours after noon.
AM/PM is used with the lowercase am symbol 12 hours before noon, and with the lowercase PM 12 hours after noon.
A/P is used with the uppercase a symbol 12 hours before noon, and with the uppercase P symbol 12 hours after noon.
A/P is used with the lowercase a symbol 12 hours before noon, and with the lowercase P symbol 12 hours after noon.

(0) Digit placeholder. Display a digit or zero
(#) Digit placeholder. Display a number or nothing
(.) Decimal place placeholder
(%) Percent symbol placeholder
(,) Sub-digit placeholder
(E-e +) Scientific format
@ Character placeholder. Show characters or spaces
& Character placeholders. Show characters or nothing

28. numberstring (numeric, type) // convert a positive integer to uppercase

= Numberstring (1234567890,1) // 1.2 billion 34,567,890
= Numberstring (1234567890,2) // you can pick up hundreds of millions of Alibaba Cloud Resources
= Numberstring (1234567890,3) // May 26, 1234

29. and/or (condition 1, condition 2,..., condition 30), not (condition 1) // condition Logic

30. & // character connection

31. Indirect (reference region path text) // return the reference specified by the text string

= Indirect ("master table! \$ D \$2: \$ d \$1000 ") // reference the \$ d \$2: \$ d \$1000 region of the primary table named sheet.

32. search (text to be searched, text string, [start Index]) Find (text to be searched, text string, [start Index]) // return the location index of the text to be searched (index starts from 1)

= Search ("C", "Excel and access") // return 3
= Find ("C", "Excel and access") // return 3

Differences:
A. the find function is case sensitive, while the search function is case insensitive.
= Search ("e", "Excel and access") // return 1
= Find ("e", "Excel and access") // return 4

B. The search function supports wildcards, but the find function does not.
= Search ("ce? "," Excel and access ") // return 3
= Find ("ce? "," Excel and access ") // The error" # Value! "is returned !"

Others

1. wildcard support (Search & replace range: can be the current sheet and the entire work thin file; the object can be values, formulas, and annotations)

? // Represents a character
* // Multiple characters
Note: search? ,*,~ Use ~? ,~ *,~~

2. Data Validity

3. Save the file as a CSV file (a comma-separated text file)

4. Import external data

5. Freeze

6. sort and filter

7. Charts

8. Shortcut Keys

CTRL + Page down // The Next sheet
CTRL + page up // previous sheet

F4 // repeat the previous command or operation (if possible)
ESC // cancel the input in the cell or edit bar
Enter // complete the cell input from the cell or edit bar, and (default) Select the following Cell
Tab // complete the cell input and move a cell to the right of the worksheet
Alt + enter // you can create a new row in the same cell.

Home [end] // move to the beginning/end of the row
CTRL + home [end] // you can move it to the beginning or end of the worksheet.
CTRL + Shift + home [end] // you can extend the selected range of cells to the beginning/end of a worksheet.
CTRL + A // select the entire worksheet. If the worksheet contains data, press Ctrl + A to select the current region,
Press Ctrl + A again to select the current Region and Its summarized rows. Press Ctrl + A for the third time to select the entire worksheet.

Direction key // move a cell up, down, left, or right in the worksheet
CTRL + arrow keys // move to the edge of the current data area in the worksheet.
Shift + arrow keys // expand the selected range of a cell.
CTRL + Shift + arrow keys // extend the selected range of cells to the last non-empty cell in the column or row of the active cell,
Or, if the next cell is empty, the selected range is extended to the next non-empty cell.

Related Keywords:

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

## A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

• #### Sales Support

1 on 1 presale consultation

• #### After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

• Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.