Excel is the most magical and important software in MS office series. Stop foolishly clicking around with the mouse! Use these 10 basic skills to improve work efficiency and get twice the result with half the effort1. "Ctrl PgDn / PgUp" conversion worksheet2. Use "Ctrl directional character" to quickly convert cells
Of course, you can just press the direction key and move it grid by grid. But if you want to quickly convert cells, long press "Ctrl" before pressing the direction key, and you can achieve the effect of "key step like flying" in an instant.3. Skillfully use the "shift" key to select dataUse "Ctrl Shift" to select cells vertically and horizontally, so as to select the area you want.
4. Double click auto fill dataTo automatically fill in the same column with functions, don't long press the mouse and try to pull down to the last column - what if your table has hundreds of rows? Double click the lower right cross of the calculated cell to easily fill in the complete column of data.5. Let the three shortcut keys quickly unify the data format
Do you want the data to appear to two decimal places? Use Ctrl Shift!.How much is the unit? Use Ctrl Shift $.What percentage do you want? Use Ctrl Shift%.
Isn't it tired to click the mouse so many times? Use these three shortcut keys to realize the uniform format of the speed of light, and the latter two should be particularly easy to remember.6. Lock the cell with F4
When filling data according to functions in Excel, sometimes you want the cells you reference to change when they drop down, and sometimes you don't think so. When you want to "absolute reference" - that is, lock the cell, you must add a $sign before the row and column. Want to type these dollar symbols manually? It's crazy... How much time will it waste, you know? In fact, there is a simple technique, that is, after you select the cell, press F4 to enter the dollar symbol and lock it; If you continue to press F4, you will cycle back one by one: Lock cells, lock numbers, lock uppercase letters, and unlock.
7. Summarize statistics with countif and SUMIFCountif is a function algorithm that counts the number of cells that meet established conditions within a specific range. The first parameter is the range you want to count; The second parameter is your condition. For example: = countif (A1: a10,1), that is, within the range of A1: A10, the cell content is 1 number.COUNTIF(range,criteria)
The SUMIF function is used to sum several cells, ranges, or references based on specified conditions. Below is the total number of views we are calculating for each author. In terms of scope, the format of the author's name is inconsistent, but the total number is the same. Column B corresponds to the name of the corresponding author, and column e corresponds to the number of views of each author's work.SUMIF(range,criteria,sum range)8. Use vlookup to find the data you want
Vlookup is a search function by column, and finally returns the value corresponding to the required query column order of the column. Here we can see a data table. The first column is the ranking of law schools. We can use vlookup to create a query of the top five schools.VLOOKUP(lookup value,data range,column number,type)The first parameter needs to enter the value to find. Here we use the number of rankings we need. The second parameter here, enter the region we want to find (absolute reference). For the third parameter, you need to enter the number of columns of the returned data in the search area. For the fourth parameter, because we want to find it accurately, enter "false" or "0".
9. Merge cells with "&"We now have a column of last names and a column of names. We can use the "&" symbol to create a column of data that combines last names and first names. In Excel, "&" refers to merging two or more texts. Don't forget to type a space between your last name and first name. Your function should look like this format = [first name] & "& [last name].10. Generate random number with rand function
You can use the rand() function to generate a single random number, which is less than 1 but greater than or equal to 0. Just leave the parentheses empty without typing any parameters. The new random number will be regenerated with the update of the workbook. You can also press F9 to recalculate the worksheet to update the random number, but remember to be careful when operating.RAND()