0 1. How to add fixed characters to existing cells in batches? For example, after the company's personnel information is entered into excel, because the superior requires adding two digits to the original title certificate number, that is, adding two digits before everyone's certificate number. If it is too much trouble to change one by one, then we can save time and effort by the following methods: 1) Assuming that the ID number is in column A, click the right mouse button after column A 2) Write in cell B2: = "=" 13 "&A2 and press enter; 3) See that the result is13xxxxxxxxxxxxxxxxx? When the mouse is placed at B2 position, isn't there a small square dot under the cell? Press the left mouse button and drag it down until it is over. When you release the left mouse button, everything changes. If 13 is added after the original certificate number, write: = a2 &；; Enter after "13". 02. How to set the number of recently run file names at the bottom of the file drop-down window? Open Tools, select Options, and then select General, and change the number of files in the number of files input box under Recent Files List. If the recently used file name is not displayed in the menu, just clear the check box in front of the Recently Used File List. 03. After inputting the formats of "1- 1" and "1-2" in EXCEL, it will become the date formats of 1 and 1. What should I do? This is because EXCEL automatically recognizes the date format. You just need to click the format menu in the main menu, select the cell, and then set the cell format as text format under the number menu label. 04. How to save files automatically and regularly in EXCEL like WORD? Click AutoSave on the Tools menu to set the time interval for automatically saving folders. If there is no AutoSave menu item under the Tools menu, please execute Add-ins ... Under the Tools menu, select AutoSave and OK. Then set it. 05. When using Excel to make a multi-page table, how to make a title like a Word table, that is, the first line (or lines) of each page is the same. But not with the head? In EXCEL's File menu-Page Setup-Worksheet-Print Title; You can set the title at the top or left. Press the button of the collapse dialog box and use the mouse to delimit the range. In this way, Excel will automatically add the part you delimit on each page as the header. 06. How to set the weighted average in Excel? Weighted average is often used in financial accounting and statistical work, and it is not a very complicated calculation. The key is to understand that the weighted average is actually the unit average obtained by dividing the total value (such as the amount) by the total quantity, rather than the unit value obtained by simply averaging each unit value (such as the unit price). In Excel, you can set a formula to solve it (in fact, it is a division formula), the denominator is the sum of all quantities, and the numerator is the sum of all corresponding quantities, and its result is the weighted average of these quantities. 07. If an Excel file contains multiple worksheets, how to set multiple worksheets as the same header and footer at a time? How to print multiple worksheets at once? Move the mouse to the name of the worksheet (if there is no special setting, the name automatically set by Excel is "sheet 1, sheet2, sheet3 ..."), then right-click and select the menu item "Select All Worksheets" in the pop-up menu. At this time, all your operations are aimed at all worksheets, whether it is setting headers and footers or printing your worksheets. 08. There is a serial number in Excel. Due to the adjustment of the table, the serial numbers are all out of order, but 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, the serial number field should be separated from other fields when making EXCEL tables, such as leaving a blank column between "Total Score" and "Rank". In order not to affect the beautiful display, you can set this empty column field as hidden, so that the serial number will not be affected when adjusting the contents of the table (data list). 09. Only the first person in the payroll made by Excel 2000 has the header of the payroll (such as number, name, post salary ...) and wants to output it as a payroll. How come? This problem should be solved as follows: first copy a payroll, then select the worksheet option in the page setup, set the print worksheet row title, select the header of the payroll, then insert a row page break between everyone, and then set the page length to the height of the payroll. I reinstalled Chinese office in a customized way, and there is only A4 in Excel's printing paper options. What should I do? Just install a printer driver. You cannot enter decimal points in10.excel. If you press the decimal point, you will see a comma. No matter how you set the options, it won't help. What should you do? This is a special problem. It took me more than ten hours to put it bluntly. In the control panel of Windows, click the "Regional Settings" icon, select "Chinese (China)" in the pop-up "Regional Settings" dialog panel, and change the decimal point to ". In the "regional settings" dialog box "Numbers" property panel, click "OK" button to finish. Then open Excel and everything will be fine. 1 1. How to quickly select a specific area? Use F5 to quickly select a specific area. For example, the easiest way to select A2: A 1000 is to press F5 to open the navigation window and enter A2: A 1000 in the quotation column. 12. How to quickly return to the selected area? Press Ctr+ Backspace (that is, Backspace). 13. How to locate cells quickly? Method 1: Press F5 to open the Location dialog box, enter the address of the cell to jump to in the reference column, and click OK. Method 2: Click the cell address box on the left side of the edit bar and enter the cell address. 14, special function of "CTRL+*" Generally speaking, when dealing with a table with a lot of data in a worksheet, you can select a cell in the table and then press CTRL+* to select the whole table. The selected area of CTFL+* is determined as follows: the largest area containing data units radiates around according to the selected units. 15. How to quickly select all cells containing formulas in the worksheet? Sometimes, you need to protect all cells in the worksheet that contain formulas, or fill them with a different color from other cells to remind users not to enter data in areas with that color. The following methods can help you quickly select all cells that contain formulas: Select Edit Positioning, click Positioning Criteria, select formula items in the Positioning Criteria dialog box, and then press OK. 16. How to quickly enter the same number in different cells? Select a cell range, enter a value, and then press Ctrl+ Ener to enter the same value once in the selected cell range. 17, only remember the name of the function, not the parameters of the function. What should I do? If you know the name of the function you want to use, but don't remember all its parameter formats, you can use keyboard shortcuts to paste the parameters into the formula bar. The specific method is: enter an equal sign followed by the function name in the edit field, and then press CTR+A, and Excel will automatically enter "Function Wizard-Step 2-2". This method is especially useful when using functions with easy-to-remember names and long parameter lists. 18. How do I drag and drop the selected cell or cells to a new location? Hold down the Shift key to quickly change the order of cell contents. The specific method is: select a cell, hold down the Shift key, move the mouse pointer to the edge of the cell until a drag-and-drop pointer arrow (hollow arrow) appears, and then hold down the left mouse button to drag and drop. When dragging up and down, the mouse will become a horizontal I-shaped symbol at the cell boundary, and it will become a vertical I-shaped symbol when dragging left and right. When the mouse button is released, the selected cell or cells will be dragged and dropped to the new location. 19. How to enlarge the workspace on the big screen? You can hide unused toolbars, maximize the Excel window, or choose Full Screen from the View menu. 20. How to use the pop-up menu? The shortcut menu contains some commonly used commands in operation, which can greatly improve the operation efficiency. Select an area first, then click the right mouse button to pop up a pop-up menu, and select different commands according to the operation needs. 2 1. How to use the shortcut menu? The shortcut menu contains some commonly used commands in operation, which can greatly improve the operation efficiency. Select an area first, then click the right mouse button to pop up a 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 is started, it will automatically open all files in the Xlstart directory. When there are too many files in this directory, Excel is not only time-consuming, but also may load too many files. The solution is to delete files that should not be in the Xlstart directory. In addition, it is also necessary to prevent EXcel from opening files in the alternate startup directory: select the tool option Normal and delete all the contents in the column of the alternate startup directory. 23. How to remove grid lines? 1) Remove the table line in the editing window, click Options in the Tools menu, and then select View to find the grid line and make it invalid; 2) Exclude table lines that are not defined when printing. Sometimes, table lines that are not defined when editing (also light gray table lines are seen in the editing window) will not be printed when printing, but sometimes they will be printed, especially some Excel tables edited in the so-called "computer" VCD. To delete these table lines, simply click the File, Page Setup and Sheet menus, click the selection box to the left of Grid Lines, and then deselect Grid Lines. 24, how to quick format report? In order to make a beautiful report, it is necessary to format it. A shortcut is to automatically apply the default table style of Excel. The method is: select the operation area, select the AutoFormat command from the format menu, select a format style you are satisfied with in the format list box, and press the OK button. It should be noted that under the format list box, there are six "Apply Format Type" options, including numbers, border lines and fonts. If the "x" before the item does not appear, it will not be used when applying the table style.

The above is the related content of the book EXCEL Spreadsheet Use Skills (1) shared by Bian Xiao. For more information, you can pay attention to Global Ivy and share more dry goods.