Excel tabulation skill set, how to add a fixed character to the existing cell volume?

For example: when entering the unit’s personnel data in Excel, if two larger numbers are added to the title of the original certificate, that is, two digits are added to each person before the certificate number. A change of 13 is too troublesome, then we can use the following method to save time and energy:

1) Assume that the certificate number is in column A, right-click on the column and insert a column B< /p>

2) Write in cell B2: ="13" & press Enter after A2;

3) Do you see the result of 13xxxxxxxxxxxxx? Place the mouse at position B2. There is a large dot at the bottom of the cell. Press the left mouse button and drag downward

until it ends. When you release the left mouse button all are replaced as well. Then add original certificate No. 13 and write it in cell B2: = A2 & "13"

and press Enter.

How to set the name of the recently run file at the bottom of the file drop-down window?

Open "Tools", select "Options", and then select the number of files in the recently used file list in the input box under "General" to change the number of files.

BR />If the names of recently used files are displayed in the menu, then the checkbox before "List recently used files" can be removed.

3. Enter in EXCEL such as "1- 1", "1 - 2" and other formats become January 1st, January 2nd date format, what should I do?

This is because Excel will automatically recognize it as a date format, you just need to Click the "Format" menu in the main menu, select "Cell", and sign the number menu mark

Just set the format of the cell to text format.

How to save a word clock file in EXCEL?

Click "Autosave" in the "Tools" menu to set the time interval for the project to be automatically saved to a folder. If you click the "Autosave" menu under the "Tools" menu

Personal, then execute "Add File..." "AutoSave" "OK" under the "Tools" menu, and then set.

How to make a multi-page table with Excel, like the first row (or line) of the Word table is the same as the title page but without using a title

?

Set the Excel file menu - Page - Worksheet - Print title; set the top or left end title to fold the press area

After the mouse dialog box button, you can delimit the range like this in Excel Will be automatically assigned as title on every page.

How to set weighting in Excel? Average

The weighted average is often used in financial accounting and statistical work. It is not a very complicated calculation. The key is to understand the total value of the weighted average

(if it can be set It is determined as the average unit value obtained by dividing the average value of the total number of cells derived in Excel

Formula), rather than simply the value of each cell (unit). Solving (actually a division equation), the denominator is all the money and the numerator, and the result is the weighted average of the corresponding number

values.

If an Excel file contains multiple worksheets, how to first set up the same header and footer for multiple worksheets? How to print multiple worksheets

Move the mouse to the name of the worksheet (if you do not set it specifically, Excel automatically sets the name "Sheet1, Sheet2, Sheet3... .

"), then right-click, and in the pop-up menu, select the "Select All Worksheets" menu item, and then all your operations will be directed to the entire worksheet,

Tube to set header and footer or print your worksheet.

8. Excel has a serial number column adjustment table. Serial numbers, everything is messed up. It can manually change the serial numbers one by one. It is too slow and troublesome. What side effects can be solved quickly?

?

If the serial number is adjusted in a form with other content that should not be changed, then produce the EXCEL sheet

Separate, such as the serial number score in this field and other fields "," In order not to affect the appearance of the display, the "Empty Column" field is set to hidden, so adjusting the table

(data table) will not affect the serial number.

The salary obtained in Excel2000 only has the header (such as ID, name, position salary...) and is output as the first salary in a salary bar.

The problem should be solved: first copy the Payroll Worksheet' option is checked, then in Page Setup, set Print Worksheet row header, select the correct job

Payroll bar column header, then insert a row Page breaks between each person, and then the page length is set to the height of the salary bar.

Use customization and then reinstall

The printing paper option of Excel in Office97 with text only has A4, what should I do? Printer drivers can be installed easily.

10 I cannot enter decimal points or commas in Excel. No matter how I set the options, it does not help. What should I do?

This was a unique problem that I had to spend a dozen hours on, but it was quite simple to put it bluntly. In the Windows "Control Panel", click the "

Regional Settings icon, select "Chinese (China)", in "Regional Settings", in the pop-up "Regional Settings Properties" dialog panel Regional Settings Properties BR p>Replace the decimal point with "(no change", ") in the "Number" property of the dialog panel, and then click the "OK" button to end.

Everything is normal.

11. How to quickly select a specific range to open Excel

The F5 key can quickly select a specific range, for example, select A2:A1000. The easiest way is to press F5 Key, "Locate" window, enter the selected area A2:A1000 in the "Reference" column.

12 How to quickly return to the selected area?

Press Ctr + BacksPae (i.e. backspace key).

13. How to quickly locate cells?

Method: Press the F5 key when the "Locate" dialog box appears. Enter the cell address of a city you want to jump to and click the "OK" button.

Method 2: Click in the cell address box on the left side of the edit bar and enter the cell address.

In general, when processing a large amount of data in the form of a worksheet, select a table cell by one, and then press the Ctrl + * keys You can select the entire

month form. Ctfl + *The area covered by the selected cell is the largest?

> 15. Quickly select worksheet cells containing formulas?

Sometimes, it is necessary to protect all cells in a worksheet that contain formulas, or fill other cells in different colors to remind users not to do this Enter data in a color area. The following method can help quickly select all cells containing formulas: select "Edit"\"Positioning", click the

>Bit Conditions" button in the "Positioning Criteria" dialog box In the box, select the "Formula" item and press the "OK" button.

16. How to quickly enter the same content in different cell numbers BR />Select the cell area, enter the value, and then press Ctrl + Enter key to enter the selected cell area The values ??in are the same, one-time

17. I only remember the name of the function parameter, but I don’t have to remember it. What should I do?

If you know the name of the function you want to use but can't remember all the parameter formats, you can use keyboard shortcuts to paste the parameters into the edit field

The specific method is: Enter an equal sign in the edit bar followed by the function name, and then press Ctr + A, Excel will automatically enter the Function Guide - Step 2

2". When the name is easy to remember, there is a long list Parameters, the above method is particularly useful.

18 How to drag and drop the selected cell or cells to a new location?

Hold down the Shift key to quickly move the cells.

The specific method is: select the cell, press the Shift key, move the mouse pointer to the edge of the cell until a drag-and-drop pointer arrow (hollow arrow) appears, hold down Mouse and dragging and dropping operations. Dragging the mouse up and down will change the boundaries between the "work" signs in the cells horizontally, and dragging left and right becomes vertical

Click the "work" icon, release the mouse button to complete the operation, select one or more cells and drag them to a new location.

19. How to make a larger cell on the screen. Space?

The toolbar cannot be hidden. You can also maximize the Excel window, or select the "Full Screen" command in the "View" menu.

20. How to use the pop-up menu. Medium?

The pop-up menu includes some of the most commonly used command operations, using them can greatly improve operational efficiency. First select an area and then click the mouse to bring up the pop-up menu. , different commands are required according to the operation.

21 How to use it in the pop-up menu?

The pop-up menu includes some of the most commonly used command operations, and using them can greatly improve operational efficiency. . First select an area and then click the mouse to bring up the pop-up menu, with different commands depending on the operation.

22 How to prevent Excel from automatically opening too many files? p>

When Excel starts, it will automatically open all files in the XLSTART directory.

When there are too many files in a directory, Excel loading too many files is time-consuming

but may also be an error. The solution is to delete the Xlstart directory which should not be there. In addition, to prevent Excel from opening the alternate startup directory text

section: Select "Tools"\"Options"\"Normal" to delete all the contents of the alternate startup directory in the 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) Delete undefined table line printing

Sometimes this happens: when you edit an undefined table line (you see a light gray table in the editing window Line), under normal circumstances, printing

does not print out, and sometimes printing out of control occurs, especially for some so-called "computer" VCD editing Excel spreadsheet

samples. To delete these table lines, just click "File", "Page Setup" in the "Sheet" menu, click the "Gridlines" to the left of the selection box, and deselect "Gridlines" That's it.

24. How to quickly format a report?

Statement to create the desired format for a report. A quick way to automatically apply Excel's default table styles. :

In the operation area, select the "AutoFormat" command in the "Format" menu, select the formatting style in the "Format" list box, and click the "OK" button

button. It should be noted that the list box below the format includes "Digital", "Borderline", "Font" and "6" format type options.

The "X" in front does not appear in the applied table style. Use Project.

25. How to quickly copy the format of a cell?

To copy the formatting operation to another part of the data, you can use the "Format Painter" button to select the source. To format a cell, click the "Format Painter" button, turn the mouse on the toolbar into a brush shape, and then click "Format Cell" to copy the formatting over it.

26. How to add a slash table?

Generally we use it to form a slash, but the worksheet itself does not provide this function. In fact, we can use the drawing tool to achieve this: click the "Draw" button, select "Straight Line", the mouse turns into a cross, this adds the starting position of the slash, hold down the left mouse button and Drag to the end position and release the mouse

Draw a diagonal line. Additionally, using the "Text Box" button, you can easily add text below the slash, but with a border around the text. To remove it, select the text box to bring up the pop-up menu and select "Object Format" "\"Pattern", select "Borderless".

27. How fast are numbers entered as text?

Add a single quotation mark " ' before entering the number to force the number to be entered in text form.

28 How to define your own function?

Users can customize Define the function in Excel. Switch to the Visual Basic module, or insert a new module table (module). In the blank program window that appears, type the custom function VBA program and press Enter to confirm the compilation. Excel will automatically check for correctness. Thereafter, in the same workbook, you use Exed internal functions to use custom functions in the worksheet, such as:

Function ZM (a )

if <60 then IM if

tail function = 'failure

else ZM = "pass"

end

29 Can a custom function of a worksheet formula in a workbook call a custom function?

Can a custom function be included in the method of opening the workbook (that is, when the function When called, add the link under the premise of the name of the workbook in which the function is located.

The custom function Zm assumes that in the previous example, the workbook MYUDF.XLS ZM function is now called. For different workbooks, you should first make sure

PAUL MYUDF.XLS is open, and then use the following link: =MYUDF.XLS!ZM(B2)

30. How to quickly enter a data series?

If you need to enter some special data series, such as the serial number of the entries in the table, and the date sequence, why can't you let Excel fill it in automatically without entering one after another?

How about charging? Enter data in the next cell starting from the first cell, select two cells, and move the cursor to the bottom of the cell

Drag the fill handle in one direction along with the fill handle, dragging the entire cell as needed to automatically fill the Excel internal sequence.

Capacity will be increased

Having frequently used certain legal data (such as office lists), defining sequences for future autofill will be done once and for all! Select the "Options" command in the "Job

" menu, then select the "Custom Sequence" tab, enter a new sequence in the input box, note that the symbol half-width to enter the new sequence 2< /p>

Comma separated (for example: Zhang San, Li Si, Wang,...), click the "Add" button to be saved in the input sequence

31 cells, use the mouse. Right-click and drag the fill handle

For example, use the left mouse button to drag the cell fill process to automatically fill the data series.

In fact, use the right mouse button to drag the fill handle of the cell

For more flexibility, enter data in the cell, hold down the right mouse button and drag the fill handle along the fill sequence, which will include the following dishes

Single: Copy cells, fill sequence, format fill, fill value; fill days, work day fee, monthly fee, year full sequence

......At this time , you need to select a fill

32 if you have a sequence of items in your worksheet and want it to be defined as a custom sequence for later use as described above

Autofill sequence method that requires re-entering these sequences?

Method that does not require fast track: select the range of cells, the sequence containing the items, select "Tools"\"Options"\"Custom Sequence" and click "Introduction"

button, the sequence items of the selected area are added to the "Custom Sequence" dialog box, click the "OK" button to return to the worksheet, you can use this sequence item next time

>

33. The above example, if you already have a fertile sequence of entries containing many duplicate entries, how to deal with it so that there are no duplicates before you can use "Introduction" quickly

Create a desired custom Define serialization?

For the selected cell range, select the "Data\" Filter\"Advanced Filtering" "Select Unduplicated Records" option, you can press the "OK" button