It returns a numerical value that represents the remainder when one number is divided by another. Excel RAND function can be used when you want to generate evenly distributed random numbers between 0 and 1. It returns a number between 0 and 1. It returns a number between the top and bottom range specified by the user. Excel ROUND function can be used when you want to return a number rounded to a specified number of digits. Excel SUMIF function can be used when you want to add the values in a range if the specified condition is met.
Excel RANK function can be used when you want to rank a number against a list of numbers. It returns a number that represents the relative rank of the number against the list of numbers.
For example, you can get the third largest value from a range of cells. Excel MAX function can be used when you want to get the largest value from a set of values. Excel MIN function can be used when you want to get the smallest value from a set of values. For example, you can get the third smallestvalue from a range of cells. It can be used to join text, numbers, cell references, or a combination of these.
Excel FIND function can be used when you want to locate a text string within another text string and find its position. It returns a number that represents the starting position of the string you are finding in another string. It is case-sensitive. Excel LEN function can be used when you want to get the total number of characters in a specified string.
This is useful when you want to know the length of a string in a cell. Excel LOWER function can be used when you want to convert all uppercase letter in a text string to lowercase.
Excel MID function can be used to extract a specified number of characters from a string. It returns the sub-string from a string. It returns a text string where a part of the text has been replaced by the specified string.
Excel REPT function can be used when you want to repeat a specified text a certain number of times. Excel SEARCH function can be used when you want to locate a text string within another text string and find its position. It is NOT case-sensitive. It returns a text string where an old text has been substituted by the new one. Excel TEXT function can be used when you want to convert a number to text format and display it in a specified format. It can be a useful VBA function if you want to quickly clean the data.
For example, if you want to split text based on a comma or tab or colon, you can do that with the SPLIT function. VBA InStr function finds the position of a specified substring within the string and returns the first position of its occurrence.
Best Excel Shortcuts. Conditional Formatting. Creating a Pivot Table. Excel Tables. Creating a Drop Down List. Or find it on the Home ribbon under Styles. Ever scroll down a large table of data only to forget which columns are which?
Freeze Panes is the answer here. You can freeze just the top row, first column or any number of either.
Identify the columns and rows of the area you want to freeze. Then select the cell immediately to the right of those columns and beneath those rows.
Go to the View tab and Freeze Panes in the Window section. Alt W F is the shortcut. There are two especially satisfying ways to use F4 in Excel. The first is when creating an Absolute Reference: F4 toggles you through the various options. The second is one that few people know about, but could seriously improve your Excel productivity. F4 repeats your last action, where available. This simple shortcut takes you straight to the end of the data in a column or row you are using.
Warning: If you have gaps in your data this will just take you down to the first gap. So if you want to get to the bottom fast, choose a column of data which has no gaps. Harness just a few of these ten items, and you can transform your typical work day.
Whether you want to help justify data-driven business decisions at a high level, or simply get home to your family earlier, mastering the right Excel functions is a quick and easy way to maximize your productivity. You have 1 free article s left this month. You are reading your last free article for this month. Worksheet formulas are built-in functions that are entered as part of a formula in a cell. These are the most basic functions used when learning Excel.
Below is a list of Excel formulas sorted by category. If you would like an alphabetical list of these formulas, click on the following button:.
Enter a value in the field above to quickly find functions in the list below. While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. Please re-enable javascript in your browser settings. Search Excel functions:. Convert currency into words Convert number into words Insert a double quote in a formula Test a string for an alphanumeric value How to Autonumber in Excel Delimit values with ASCII 29 character Creating a custom round function Creating a custom average function that excludes outlyers from the calculation.
Share on:. ROW WS. Returns the position of the first occurrence of a string in another string, starting from the end of the string.
0コメント