How to use Excel to tab

How to use Excel tabulation:

1. How to add a fixed character to a batch in an existing cell?

For example: in the personnel information in the unit, after entering in Excel, because the superior requirements in the original title certificate number of all add two, that is, each person's certificate number before adding two digits 13, if a change of words is too troublesome, then we can use the following methods, save time and effort:

1 assuming the certificate number in column A, after a column click the right mouse button, insert a column for column B;

2 write in B2 cell: = "& A2 after carriage return;"

3 Do you see the result is 13xxxxxxxxxxxxx? Mouse put to the B2 position, the bottom of the cell is not a small square point, press the left mouse button down

Drag until the end. When you release the left mouse button, all are changed. If you add 13 after the original certificate number, write it in cell B2: =a2 & "13"

Rear carriage return.

2, how to set the file Drop-down window of the bottom of the most recently run the number of filenames?

Open tools, select Options, select General, and change the number of files in the File Number entry box below the recently used file list

Can If you do not display the most recently used file name in the menu, remove the check box before the recently used file list.

3, in Excel input such as "1-1", "1-2" and so on after the format it becomes January 1, January 2, and other date forms, how to do?

This is because Excel automatically recognizes the date format as a result, you just click on the main menu of the Format menu, select "Cell", and then the "Number" menu label

Sign the format of the cell as text.

4, in Excel How to make it like word of automatic timing save files?

Click the "Auto Save" item on the "Tools" menu to set the time between automatically saving folders. If you do not have an auto save menu under the Tools menu

Item, then execute "Auto Save" and "OK" on the "Tools" menu "Add Macro ...". Then you can set it up.

5, how to use Excel pages of the table, how to do a title like Word table, that is, the first line of each page (or a few lines) is the same. But not

Use a header to complete?

In Excel's File menu-page setup-Worksheet-Print the title, you can do the top or left header settings, by pressing the Collapse dialog box button,

The mouse to delimit the range can be. Excel will automatically add your assigned section to each page as the header.

6. How to set the weighted average in Excel?

Weighted average is often used in financial accounting and statistical work, not a very complicated calculation, the key is to understand that the weighted average value is actually the total value

(such as the amount) divided by the total number of units average, rather than simply the unit value (such as unit price) after the average to get the unit value. In Excel

Can be set to solve the equation (in fact, is a division formula), the denominator is the sum of the values, the numerator is the corresponding number of the sum, it is the result of these

The weighted average value of the measure.

7. If you have multiple worksheets in an Excel file, how do you set multiple worksheets to the same header and footer at a time? How to print at once

multiple worksheets?

Move the mouse to the name of the worksheet (if you don't have a special setting, Excel automatically sets the name "Sheet1, Sheet2, Sheet3 ...)."

"), then right-click, select" Select All Sheets "menu in the pop-up menu, and all your actions are for all worksheets, not

Whether to set headers and footers or to print your worksheet.

8, Excel has the serial number one column, because of the adjustment to the table, the serial number is all messed up, but if manually a change ordinal number is too slow too troublesome, with what side

Method can be solved quickly?

If the serial number should not change as the rest of the table changes, then you should make the Excel table with the fields

Separate, such as in the "Total score" and "rank" between the blank open a column, in order not to affect the appearance of the display, you can set this empty column field to hide, so in the adjustment table

The contents of the list will not affect the serial number.

9, with the Excel2000 made of wages, only the first person has a pay bar head (such as number, name, post wages ...), want to output into wages

form of the article. How do you do it?

This problem should be resolved: first copy a payroll, and then select the worksheet options in the page setup, set the print worksheet row headers, select a good worker

Bar, and then insert a line page break between each person, and then set the page length to the height of the pay bar. Use a custom way to reload the

Wen Office97,excel of the paper options only A4 one, how to do? You can install a printer driver casually.

10, in Excel decimal points can not be entered, by the decimal point, the display is a comma, no matter how to set options are useless, how to do?

This is a very special problem, I have spent more than 10 hours for this, but frankly speaking is very simple. In the Windows Control Panel, click

Regional Settings icon, on the Regional Settings Properties dialog panel that pops up, select Chinese (China) in the Regional Settings properties

On the dialog panel, in the number attribute, change the decimal point to "." (Before change is ","), press "OK" button to end. Then open Excel on a

It's all normal.

11. How to select a specific area quickly?

Use the F5 key to quickly select a specific area. For example, to select a2:a1000, the easiest way to do this is to press the F5 key to appear in the "Locate" window, and in the "reference

field, enter the area a2:a1000 you want to select.

12. How to quickly return to the selected area?

Press Ctr+backspae (that is, the backspace).

13. How to quickly locate a cell?

Method One: Press F5 key, appear "Locate" dialog box, enter the cell address that wants to jump to in the reference column, single city "OK" button can.

Method Two: Click the cell Address box to the left of the formula bar and enter the cell address.

14, the special function of "ctrl+*"

In general, when you work with a table with lots of data on a worksheet, select a cell in the table, and then press the ctrl+* key to select the entire

a table. Ctfl+* The selected area is determined by the largest range of data cells involved in the radiation around the selected cell.

15. How do I quickly select all cells that contain formulas in a worksheet?

Sometimes you need to protect all cells in a worksheet that contain formulas, or fill in colors that are different from other cells, to remind users that you cannot

Enter data in the area with this color. The following methods can help you quickly select all cells that contain formulas: Select Edit, locate, click the

Bit Condition button, select the formula item in the Locate Criteria dialog box, and then press the OK button.

16. How to quickly enter the same number of contents in different cells?

Select a range of cells, enter a value, and press CTRL + Ener to enter the same value at once in the selected range of cells.

17, only remember the name of the function, but do not remember the parameters of the function, how to do?

If you know the name of the function you want to use, but you can't remember all of its parameter formats, you can use a keyboard shortcut to paste the parameters into the formula bar.

In the formula bar, enter an equal sign followed by the function name, then press ctr+ A, Excel automatically enters the function guide-Step 2

of 2 ". This approach is especially useful when you use a function that is easily memorized and has a long string of parameters.

18. How to drag and drop one or more selected cells to a new location?

Hold down the SHIFT key to quickly change the order of cell contents.

Select the cell, hold down the SHIFT key, and move the mouse pointer to the edge of the cell until the drag-and-drop pointer arrow (the hollow arrow) appears.

And then hold down the left mouse button to drag and drop the operation. Drag up and down when the mouse at the boundary between the cells will become a horizontal "work"-like sign, drag left and right will become vertical

Straight "Work"-like flag, release mouse button when the operation is complete, one or more of the selected cells is dragged to the new location.

19. How to make the work space on the screen become larger?

You can hide unused toolbars, or you can dramatically Excel windows, or select the full Screen command on the View menu.

20. How to use the Quick display menu?

The Quick Display menu includes some of the most commonly used commands in the operation, which can greatly improve operational efficiency. First select an area, and then click the mouse

Right health can bring up the quick display menu, according to the operation need to select different commands.

21. How to use the Quick display menu?

The Quick Display menu includes some of the most commonly used commands in the operation, which can greatly improve operational efficiency. First select an area, and then click the mouse

Right health can bring up the quick display menu, according to the operation need to select different commands.

22. How do I prevent Excel from automatically opening too many files?

When Excel starts, it automatically opens all the files in the XLStart directory. When there are too many files in the directory, Excel loads too many files that are time-consuming

And there could be mistakes. The workaround is to remove files that should not be located in the XLStart directory. Also, prevent Excel from opening the text in the alternate startup directory

Items: Select tools option "Normal" to delete all content in the "Alternate startup directory" column.

23. How do I get rid of the grid line?

1 drop the table line in the edit window

Click Options on the Tools menu, and then select View to locate gridlines to invalidate them.

2 Remove the undefined form line when printing

Sometimes this happens when you edit a table line that you don't define (it's also a light gray form line seen in the edit window), usually in print

Will not print out, but sometimes do not listen to the print out, especially some so-called "computer" VCD in the edited Excel table is this

Sample. To get rid of these table lines, just click the Select box on the left side of gridlines by clicking the file, Page Setup, sheet menu,

Deselect gridlines to select the line.

24, how to quickly format the report?

In order to make a beautiful report, you need to format the report. There is a quick way to automatically apply Excel preset table styles. The method is: Select

Action area, choose AutoFormat from the Format menu, select a format style you like in the Format list box, press OK to

button can be. Note that the Format list box has 6 application formatting options including number, border line, font, and so on, if an item

The previous "X" does not appear, and the item is not used when the table style is applied.

25, how to quickly copy the format of cells?

To copy a formatting operation to another part of the data, you can use the Format Painter button. Select the cell that contains the source format you want, click the toolbar

The Format Painter button on the, the mouse changes to a brush shape, and then clicks the cell you want to format to copy the formatting back.

26. How do I add a slash to the table?

Generally we have a diagonal line on the table, and the worksheet itself does not provide that functionality. In fact, we can use the drawing tools to achieve this: click the drawing

button, select "Straight line" and the mouse becomes a cross. Move it to the beginning of the slash you want to add, hold down the left mouse button and drag to the end position, release the mouse,

The slash is drawn. In addition, you can use the text box button to easily add text to the bottom of the slash, but the text has a border around it, to cancel it, you can

Select the text box, bring up the Express menu, select "Object Format" "Pattern", select "No Border" item can be.

27. How to quickly enter numbers as text?

You can force a number to be entered as text by adding a single quote "" to the number before entering it.

28, how to define their own functions?

Users can customize functions 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, press ENTER to confirm the completion of the work, Excel will automatically check its correctness. After that, in the same work-book, you

You can use custom functions in worksheets as you would with exed internal functions, such as:

Function Zm (a)

If a< Then im= ' fail '

Else zm= "Pass"

End If

End Function

29. How do I call a custom function in a worksheet formula in a workbook that differs from a custom function in a workbook?

You can use a linked method (that is, the name of the workbook that contains the function when you call the function), if the workbook containing the custom function is opened.

Assuming that the custom function in the previous example ZM is in the workbook Myudf.xls, now you want to call the ZM function in a worksheet formula in a different workbook, you should first

The Myudf.xls is opened and then uses the following link method: =myudf. Xls! ZM (B2)

30. How to quickly enter data sequence?

If you need to enter some special data series, such as the item serial number in the table, the date sequence, and so on, do not enter each entry, why do not let Excel automatically fill in

Charge it? Enter the starting data in the first cell, enter the second data in the next cell, select the two cells, and point the cursor to the right of the cell

Below the fill handle, drag the fill handle along the direction you want to fill, and the dragged cells will automatically fill in the sequence defined within Excel. If you can make your own

Some regular data (such as the Office personnel list) that have been used frequently will be defined as a sequence for automatic filling later. Select "Work

With menu, select the Custom Series tab, enter a new sequence in the input box, and note that you want to enter a half corner of each 2 of the new sequence.

The comma of the symbols is delimited (for example: John, Dick, Wang two ...), click the "Add" button to save the entered sequence.

31. Drag the cell fill handle with the right mouse button

In the example above, the method of using the left mouse button to drag a cell fill handle to automatically populate a data series is described. Actually, drag the cell fill handle with the right mouse button

is more flexible. To enter data in a cell, hold down the right mouse button and drag the fill handle along the direction you want to fill the sequence, and the dishes that contain the following items appear

Single: Copy cells, fill them in a sequence, fill them in a format, fill them with values, fill them by days, fill them with a working day, filling them with a month, and filling them with years; series

...... At this point, you can choose a way to fill as needed.

32. If you have a sequence item in your worksheet that you want to define as an automatic fill sequence for later use, do you need to follow the custom sequence described above

method to re-enter these sequence entries?

No need. Shortcut method: Select the range of cells that contains the sequence items, choose Tools Options, Custom series, click Introduce

button to add the sequence items of the selected range to the Custom Series dialog box, and press OK to return to the worksheet, which you can use next time.

33. In the example above, if you have a number of duplicates in a given sequence item, what should be handled so that there are no duplicates so that you can use the "introduced" method to quickly

Create the desired custom sequence?

Select the range of cells, choose Data Filter Advanced Filter, select the Do not select Duplicate records option, and press OK to