Excel Skills 53 Examples
(1)
1. How to add a fixed character in batches to existing cells?
For example: After inputting the unit’s personnel information in Excel, the superior requires that two more digits be added to all the original professional title certificate numbers, that is, two more digits must be added in front of each person’s certificate number. Number 13, if it is too troublesome to change one by one, then we can use the following method to save time and effort:
1) Assume that the certificate number is in column A, right-click after column A, Insert a column into column B;
2) Write in cell B2: ="13" amp; press Enter after A2;
3) Do you see the result is 13xxxxxxxxxxxxx? ? Place the mouse at position B2. Isn't there a small square dot under the cell? Hold down the left mouse button and drag it down until it ends. When you release the left mouse button, everything is changed.
If you add 13 after the original certificate number, write in cell B2: =A2 amp; "13" and press Enter.
2. How to set the number of recently run file names at the bottom of the file drop-down window?
Open "Tools", select "Options", then select "General", and change the number of files in the number of files input box under "Recently Used File List". If you do not want to display recently used file names in the menu, just remove the checkbox in front of "Recently used file list".
3. After inputting formats such as "1-1" and "1-2" in EXCEL, it will become January 1st, January 2nd, etc. What should I do?
This is caused by EXCEL automatically recognizing the date format. You only need to click the "Format" menu in the main menu, select "Cells", and then change the format of the cell under the "Number" menu label. Just set it to text format.
4. How to make EXCEL automatically save files at regular intervals like WORD?
Click the "Autosave" item in the "Tools" menu to set the interval for automatically saving the folder. If there is no "Autosave" menu item under the "Tools" menu, then execute "Add-in Macros..." under the "Tools" menu and select "Autosave" and "OK". Then just set it up.
5. When using Excel to make a multi-page table, how to make a title like a Word table, that is, the first row (or rows) of each page is the same. But not done with a header?
In EXCEL's File menu - Page Setup - Worksheet - Print Title; you can set the top or left title by pressing the Collapse dialog box button and delimiting the range with the mouse. In this way, Excel will automatically add the section you designated as the header on each page.
6. How to set weighted average in Excel?
Weighted average is often used in financial accounting and statistical work. It is not a very complicated calculation. The key is to understand that the weighted average is actually the total value (such as amount) divided by the total quantity. The unit average value is obtained, rather than the unit value obtained by simply averaging the individual unit values ??(such as unit price). In Excel, you can set up a formula to solve it (actually it is a division formula). The denominator is the sum of each magnitude, the numerator is the sum of the corresponding quantities, and the result is the weighted average of these magnitudes.
7. If there are multiple worksheets in an Excel file, how to set multiple worksheets to the same header and footer at one time? How can I print multiple worksheets at once?
Move the mouse to the name of the worksheet (if you do not set it specially, the name automatically set by Excel is "sheet1, sheet2, sheet3..."), then right-click, Select the "Select All Worksheets" menu item in the pop-up menu. At this time, all your operations are for all worksheets, whether it is setting headers and footers or printing your worksheets.
8. There is a serial number column in EXCEL. Due to the adjustment of the table, the serial numbers are all messed up. However, it is too slow and troublesome to manually change the serial numbers one by one. What method can be used to solve it quickly?
If the serial number should not change with the adjustment of other contents of the table, then the serial number field should be separated from other fields when making the EXCEL table, such as between "Total Score" and " Leave an empty column between "Ranking". In order not to affect the appearance of the display, you can set this empty column field to be hidden, so that the serial number will not be affected when adjusting the content of the table (data list).
9. In the salary table made with Excel2000, only the first person has the header of the salary slip (such as number, name, position salary...), and I want to output it as a salary slip. form. How to do it?
This problem should be solved like this: first copy a salary table, then select the worksheet option in the page settings, set the print worksheet row title, select the header of the salary slip, and then add Insert row page breaks between them, and then set the page length to the height of the salary slip.
I reinstalled the Chinese office 97 using a custom method. The Excel printing paper option only has A4. What should I do?
Just install any printer driver.
10. I cannot enter decimal points in Excel. When I press the decimal point, a comma is displayed. No matter how I set the options, it does not help. What should I do?
This is a rather special problem. I have spent more than ten hours on it, but to put it bluntly, it is very simple. In the Windows Control Panel, click the "Regional Settings" icon, select "Chinese (China)" in "Regional Settings" on the pop-up "Regional Settings Properties" dialog panel, and select "Chinese (China)" in the "Regional Settings Properties" dialog panel. In the "Number" attribute, change the decimal point to "." (it was "," before the change), and press the "OK" button to end. Then when you open Excel again, everything will be normal.
11. How to quickly select a specific area?
Use the F5 key to quickly select a specific area. For example, to select A2:A1000, the easiest way is to press the F5 key, the "Location" window will appear, and enter the area to be selected, A2:A1000, in the "Reference" column.
12. How to quickly return to the selected area?
Press Ctr BacksPae (the backspace key).
13. How to quickly locate a cell?
Method 1: Press the F5 key, the "Locate" dialog box will appear, enter the cell address you want to jump to in the reference column, and click the "OK" button.
Method 2: Click the cell address box on the left side of the edit bar and enter the cell address.
14. What are the special functions of “Ctrl＋*”?
Generally speaking, when processing a table with a lot of data in a worksheet, the entire table can be selected by selecting a cell in the table and then pressing the Ctrl+* keys. Ctfl+* The selected area is determined as follows: the largest area with data cells involved in radiating the selected cells to the surroundings.
15. How to quickly select all cells containing formulas in a worksheet?
Sometimes, it is necessary to protect all cells containing formulas in the worksheet, or to fill in a different color from other cells to remind users that data cannot be entered in the area with this color. The following method can help you quickly select all cells containing formulas: select "Edit"\"Position", click the "Positioning Conditions" button, select the "Formula" item in the "Location Conditions" dialog box, and press the "OK" button. Can.
16. How to quickly enter the same number in different cells?
Select a cell range, enter a value, and then press Ctrl + Ener keys to enter the same value at once in the selected cell range.
17. What should I do if I only remember the name of the function but cannot remember the parameters of the function?
If you know the name of the function you want to use but can't remember the format of all its parameters, you can use keyboard shortcuts to paste the parameters into the edit bar.
The specific method is: enter an equal sign followed by the function name in the edit bar, then press the Ctr + A keys, and Excel will automatically enter the "Function Guide - Step 2 of 2". This is particularly useful when using functions with easy-to-remember names and long lists of arguments.
18. How to drag and drop the selected cell or cells to a new location?
Hold down the Shift key to quickly modify the order of cell contents. The specific method is:
Select the cell, press the Shift key, move the mouse pointer to the edge of the cell until the drag and drop pointer arrow (hollow arrow) appears, and then hold down the left mouse button to perform the drag and drop operation. . When dragging up and down, the mouse will turn into a horizontal "work"-shaped mark at the boundary between cells. When dragging left and right, it will turn into a vertical "work"-shaped mark. After releasing the mouse button to complete the operation, the selected cell or cells will be dragged and dropped to a new location.
19. How to make the working space on the screen larger?
You can hide unused toolbars, maximize the Excel window, or select the "Full Screen" command in the "View" menu.
20. How to use the pop-up menu?
The pop-up menu includes some of the most commonly used commands in operations. Using them can greatly improve operation efficiency. First select an area, then right-click the mouse to bring up the pop-up menu, and select different commands according to the operation needs.
(2) 21. How to use the pop-up menu?
The pop-up menu includes some of the most commonly used commands in operations. Using them can greatly improve operation efficiency. First select an area, then right-click the mouse to bring up the pop-up menu, and select different commands according to the operation needs.
22. How to prevent Excel from automatically opening too many files?
When Excel starts, it will automatically open all files in the Xlstart directory. When there are too many files in this directory, Excel will not only take time to load too many files, but may also make errors. The solution is to move the files that should not be located in the Xlstart directory. In addition, you also need to prevent Excel from opening files in the alternate startup directory: select "Tools"\"Options"\"General" and delete everything in the "Alternate startup directory" column.
23. How to remove grid lines?
1) Remove the table lines in the editing window, click "Options" in the "Tools" menu, then select "View", find "Gridlines" and disable it;
2) Remove undefined table lines when printing
Sometimes this happens: you have undefined table lines when editing (the light gray table lines you see in the editing window are also light gray) , under normal circumstances, it will not print out when printing, but sometimes it just ignores the instructions and prints out, especially the Excel tables edited in some so-called "computer" VCDs.
To remove these table lines, just click the "File", "Page Setup", "Worksheet" menu, click the selection box to the left of "Gridlines", and deselect "Gridlines".
24. How to quickly format reports?
In order to create beautiful reports, the reports need to be formatted. There is a shortcut method, which is to automatically apply Excel's default table style. The method is:
Select the operating area, select the "AutoFormat" command in the "Format" menu, select a format style you are satisfied with in the format list box, and click the "OK" button . It should be noted that there are 6 "Apply Format Type" options below the format list box including "Number", "Border Line", and "Font". If the "x" in front of an item does not appear, when applying the table style This item will not be used.
25. How to quickly copy the cell format?
To copy a formatting operation to another part of the data, use the "Format Painter" button. Select the cell containing the required source format, click the "Format Painter" button on the toolbar, and the mouse will turn into a brush shape. Then click the cell to be formatted to copy the format.
26. How to add slashes to tables?
Generally, we are used to having slashes on tables, but the worksheet itself does not provide this function. In fact, we can use drawing tools to achieve this:
Click the "Draw" button, select "Straight Line", and the mouse becomes a cross. Move it to the starting position where you want to add a slash, hold down the left mouse button and drag to the ending position, release the mouse, and the slash will be drawn. In addition, you can use the "Text Box" button to easily add text below and below the diagonal line, but there is a border around the text. If you want to cancel it, select the text box, bring up the pop-up menu, and select "Object Format"\"Pattern" , select the "No Border" option.
27. How to quickly input numbers as text?
Add a single quote ""' before entering a number to force the number to be entered as text.
28. How to define your own function?
Users can customize functions in Excel. Switch to the Visual Basic module, or insert a new module table (Module), type the custom function VBA program in the blank program window that appears, press Enter to confirm and complete the writing work. Excel will Automatically checks its correctness. Thereafter, in the same workbook, you can use custom functions in the worksheet just like using Exed internal functions, such as:
Function Zm(a)
If a< 60 Then im="Failed"
Else Zm="Passed"
End If
End Function
29. How to How to call a custom function in a worksheet formula in a workbook that is different from the workbook in which the custom function resides?
The link method can be used when the workbook containing the custom function is open ( That is, when calling the function, add the name of the workbook where the function is located).
Assume that the workbook where the custom function Zm in the above example is located is MYUDF.XLS. If you want to call the Zm function in a worksheet formula in a different workbook, you should first ensure that MYUDF. Method:
=MYUDF.XLS! ZM(b2)
30. How to quickly input a data sequence?
If you need to enter some special data series such as project serial numbers and date series in the table, do not enter them one by one. Why not let Excel fill them in automatically? Enter the starting data in the first cell, enter the second data in the next cell, select these two cells, point the cursor to the fill handle on the lower right side of the cell, and drag in the direction to be filled. Fill handle, the cells dragged over will be automatically filled according to the sequence specified within Excel. If you could define some regular data that you often use (such as a list of office personnel) as a sequence for automatic filling in the future, wouldn't it be done once and for all? Select the "Options" command in the "Tools" menu, then select the "Custom Sequence" tab, enter the new sequence in the input box, pay attention to enter a half-width comma to separate each item 2 of the new sequence (for example: Zhang San , Li Si, Wang Er...), click the "Add" button to save the entered sequence.
31. Use the right mouse button to drag the cell fill handle?
The above example introduces the method of using the left mouse button to drag the cell fill handle to automatically fill the data series. In fact, using the right mouse button to drag the cell fill handle is more flexible. Enter data in a cell, hold down the right mouse button and drag the fill handle in the direction of the sequence to be filled. A menu containing the following items will appear: copy cells, fill with sequence, fill with format, fill with value ; Fill in days, fill in working days, fill in months, fill in years; sequence... At this time, you can choose a filling method according to your needs.
32. If you already have a sequence item in your worksheet and want to define it as an auto-fill sequence for later use, do you need to re-enter these sequence items according to the custom sequence method described above?
No need. There is a shortcut method: select the cell range containing sequence items, select "Tools"\"Options"\"Custom Sequence", and click the "Introduce" button to add the sequence items in the selected range to the "Custom Sequence" dialog box, press the "OK" button to return to the worksheet, and you can use this sequence item next time.
33. In the above example, if the sequence items you have created contain many duplicates, how should you deal with them so that there are no duplicates, so that you can use the "introduction" method to quickly create the required customizations? sequence?
Select the cell range, select "Data"\"Filter"\"Advanced Filtering", select the "Do not select duplicate records" option, and press the "OK" button.
34. How to securely protect workbooks?
If you don’t want others to open or modify your workbook, then try adding a password. Open the workbook, select the "Save As" command in the "File" menu, select "Options", enter the "Open File Password" or "Modify File D Command" according to the user's needs, and press "OK" to exit. After the workbook (sheet) is protected, important data in certain cell areas in the worksheet can also be protected to achieve double protection. At this time, you can do this: First, select the cell area that needs to be protected. , select the "Cells" command in the "Format" menu, select "Protect", select "Lock" from the dialog box, and click the "OK" button to exit. Then select the "Protect" command in the "Tools" menu, select "Protect Worksheet", enter the password twice as prompted and exit.
Note: Don’t forget to set a “password”.
35. How to prevent the colors and shading in cells from printing?
For those protected cells, you can also set the color and shading so that users can clearly see at a glance which cells are protected and cannot be modified, which can increase the efficiency of data input. Intuitive feeling.
But it brings a problem, that is, if the colors and shading are printed out when printing in black and white, the visibility of the table will be greatly reduced. The solution is: select "File"\"Page Setup"\"Worksheet" and select the "Cell Monochrome Print" option in the "Print" column. After that, the printed form will look the same as before.
36. What should I do if I forget the password for worksheet protection?
If you want to use a protected worksheet but have forgotten the password, is there any way? have. Select the worksheet, select "Edit"\"Copy", "Paste", copy it to a new workbook (note: it must be a new workbook), you can override the worksheet protection. Of course, I would like to remind you that it is best not to steal other people's worksheets in this way.
37. Function of "$":
Excel generally uses relative addresses to refer to cell locations. When copying a formula containing a cell address to a new location, The cell address in the formula will change accordingly. You can add the symbol "$" before the column number or row number to freeze the cell address so that it remains fixed during copying.
38. How to replace cell address with Chinese character name?
If you don’t want to use the cell address, you can define it as a name.
There are two ways to define a name: one is to select the cell range and directly enter the name in the "Name Box"; the other is to select the cell range you want to name and then select " Insert "\"Name"\"Definition" and type the person's name in the "Name in Current Workbook" dialog box. Formulas using names are easier to remember and read than formulas referenced using cell addresses. For example, the formula "=SUM (actual salary)" is obviously simpler and more intuitive than using cell addresses, and is less error-prone.
39. How to quickly enter discontinuous cell addresses in the formula?
It is troublesome to enter a relatively long cell range string in the SUM function, especially when the range is composed of many discontinuous cell ranges. At this time, you can hold down the Ctrl key to select discontinuous areas. After selecting the area, select "Insert"\"Name"\"Definition", name the area, such as Group1, and then use the area name in the formula, such as "=SUM(Group1)".
40. How to define local names?
By default, all names in the workbook are global. In fact, you can define a local name so that it is only valid for a certain worksheet. The method is to name the name in the form of "worksheet name! name".
41. How to name constants?
Sometimes giving a constant a name can save time having to modify and replace the constant throughout the workbook. For example, in a worksheet, you often need to use an interest rate of 4.9% to calculate interest. You can select "Insert"\"Name"\"Definition" in the "Name of the current workbook" box Enter "Interest Rate", enter "= 0.04.9" in the "Reference Position" box, and press the "OK" button.
42. Can the worksheet name contain spaces?
Yes. For example, you can name a worksheet "Zhu Meng". One thing to note is that when you call the data in this worksheet in other worksheets, you cannot use formulas like "=ZhU Meng! A2", otherwise Excel will prompt the error message "File Meng not found". The solution is to change the calling formula to "='Zhu Mg'! A2". Of course, when entering formulas, you'd better develop the habit of using the mouse to scroll through the Zhu Meng worksheet after entering the "=" sign, and then enter the remaining content.
43. What issues should be paid attention to when naming the worksheet?
Sometimes, in order to be intuitive, it is often necessary to rename the worksheet (the default table names in Excel are sheet1, sheet2...). When renaming, you should be careful not to use existing functions. The name is used as the name of tea, otherwise it would be unreasonable to confiscate the property under the following circumstances.
We know that the way to copy a worksheet in a workbook is to hold down the Ctrl key and drag the selected worksheet along the label row to a new location. The copied worksheet will be named "source worksheet (2)" Form naming. For example, if the source table is ZM, its "clone" table is ZM(2). In the formula, Excel will process ZM(2) as a function, causing an error. Therefore, the ZM(2) worksheet should be renamed.
44. How to split or cancel the split window?
When we enter data into a worksheet, during the scrolling process, especially when the header row disappears, we sometimes misremember the relative positions of the column headers. At this time, you can split the window into several parts, then keep the title part on the screen and only scroll the data part. The method is to click "Window" \ "Split Window" on the main menu. In addition to using the "Window"\"Cancel Split Window" command when canceling a split window, there is a shortcut: place the mouse pointer on the horizontal split or vertical split line or the intersection point of the double split, and double-click the mouse to cancel. Split window.
45. How to expand the workbook?
Select the "Tools"\"Options" command, select the "General" item, and use the up and down arrows in the "Number of worksheets in new workbook" dialog box to change the number of new worksheets to open. A workbook can have up to 255 worksheets, and the system default is 6.
46. How to reduce duplication of work?
When we actually use Excel, we often encounter repeated applications of some operations (such as defining superscripts and subscripts, etc.). In order to reduce duplication of work, we can define some commonly used operations as macros. The method is: select the "Macro" command in the "Tools" menu, execute "Record New Macro", and press the "Stop" button after recording. Macros can also be defined programmatically using VBA.
47. How to quickly modify data in batches?
Suppose there is an Excel workbook with all employee salary tables in it. Now you want to increase the subsidies of all employees by 50 (yuan). Of course you can use formulas to calculate, but in addition there is a simpler method of batch modification, that is, use the "Paste Special" function:
First enter 50 in a blank cell, select this cell, and select "Edit"\"Copy". Select the cell range you want to modify, for example, from E2 to E150. Then select "Edit"\"Paste Special", select the "Add" operation in the "Operation" column of the "Paste Special" dialog box, and press the "OK" key. Finally, you want to delete the 50 you entered in a blank cell at the beginning.
48. How to quickly delete specific data?
Suppose there is an Excel workbook with a large number of product unit prices, quantities and amounts. If you want to delete all rows with a count of 0, first select the range (including the header row), and then select "Data"\"Filter"\"AutoFilter". Select "0" in the "Quantity" column drop-down list, then all rows with a quantity of 0 will be listed. At this time, when all rows are selected, select "Edit"\"Delete Rows", and then press "OK" to delete all rows with a quantity of 0. Finally, cancel automatic filtering.
49. How to quickly delete empty rows in the worksheet?
The following methods can quickly delete empty rows:
Method 1: If the order of the rows does not matter, you can sort according to a certain column, and then you can easily delete the blank rows.
Method 2: If the order of the rows cannot be changed, you can first select "Insert"\"Column", insert a new column and fill in integers sequentially in column A. Then sort the rows in the table based on any other column so that all empty rows are concentrated at the bottom of the table, and all empty rows are deleted. Finally, reorder by column A, then delete column A to restore the original order of the rows in the worksheet.
Method 3: Use the method in the above example "How to quickly delete specific data", but select "Blank" in the drop-down lists of all columns.
50. How to use array formulas?
Array formulas in Excel are very useful for creating formulas that produce multiple values ??or operate on a group of values ??rather than a single value. To enter an array formula, you must first select the cell range used to store the results, enter the formula in the edit bar, and then press ctrl+Shift+Enter to lock the array formula. Excel will automatically add brackets "{}" on both sides of the formula. Don't type the curly braces yourself, otherwise Excel will think you are entering a text label. To edit or clear an array formula. You need to select the array area and activate the edit bar, the brackets on both sides of the formula will disappear, then edit or clear the formula, and finally press Ctrl+shift+Enter.
51. How to prevent the displayed or printed table from containing 0 values?
Normally, we do not want the displayed or printed table to contain 0 values, but to set its content to empty. For example, if you use the formula "=b2 c2 d2" in the total column in Figure 1, a 0 value may appear. How can you prevent the 0 value from being displayed?
Method 1; Use the formula with the If function to determine whether the value is 0, that is: =if (b2＋c2＋d2＝0, "", b2＋c2＋d2)
Method 2: Select "Tools" "\"Options"\"Window", remove the "Zero Value" option in "Window Options". Method 3: Use a custom format. Select the E2:E5 area, select "Format"\"Cell"\"Number", select "Custom" from the "Category" list box, and enter "G/General Format; G/General Format" in the "Format" box ;;" and press the "OK" button.
52. When using the Average function in Excel to calculate the average of cells, cells with a value of 0 are also included. Is there a way to exclude cells with a value of 0 when calculating the average?
Method 1: If the value in the cell is 0, you can use the above example "method of not displaying 0 value" to set its content to empty. At this time, the empty cell is processed into text, so you can directly use The Average function is calculated.
Method 2: Use the Countif function skillfully. For example, the following formula can calculate the average value of non-0 cells in the b2:B10 area: =sum(b2: b10)/countif(b2: b1o," lt；gt；0")
53. How to implement the "AutoCorrect" function in Excel?
Word users all know that using Word's "AutoCorrect" function can achieve fast data input. But there is no menu command similar to the "AutoCorrect" function in Excel. In fact, using the VloopuP function can cleverly solve this problem.