How do the cells have a fixed volume by adding characters?
Example: in the unit's personnel data entered in excel, due to the higher level in the original certificate of title number two all together, that is to before each person's certificate number and added an 13-digit, if the words one by one to change too much trouble, then we can use the following methods save time and effort:
1) Assuming the certificate number in the A column, in the A column and click the right mouse button, insert a column for the B column;
2) is written in cell B2: = mouse into the B2 position, the bottom of the cell is not a small side point up, hold the left mouse button drag down until the end. When you release the left mouse button to change when all good. If the back of the original certificate number Add 13 to write in the cell B2: = A2 & ; Tools recently used file name, then like the format it will become the Jan. 1, Jan. 2, date the form, how do?
This is due to the date of EXCEL format for the automatic identification result, you just click the main menu of the ; menu, select Time to save the file?
click on then the implementation of the the form, how do like a Word table heading, namely the first line of each page (or lines) is the same. but not with a header to complete?
in the EXCEL file menu - Page Setup - work Form - print titles; the top or left side of the title can be set, the dialog box by pressing the button folded, can be delineated with the mouse. so Excel will automatically add your page in the section designated as the header.
6, in Excel, how to set up a weighted average?
weighted average in the financial accounting and statistical work is often used, is not a very complicated calculation, the key is to understand the total value of the weighted average is actually (such as amount) divided by the average number of units derived, rather than simply the value of the various units (such as price) that the average unit values obtained. In Excel, you can set the formula to solve (in fact, is a division equation), denominator is the sum of all values, all elements are the corresponding quantities and its result is a weighted average of these values.
7, if in an Excel file contains multiple worksheets, and how multiple the first set to the same worksheet headers and footers? how to print multiple sheets?
mouse over the name of the table at work (if you do not have special set of words, Excel automatically sets the name sheet1, sheet2, sheet3. and then right click in the pop-up menu, select or print your worksheet.
8, EXCEL serial number in the column, because of the adjustment table, serial number all messed up, you can manually one by one if the number is far too slow to change too much trouble, what method can solution?
If serial number is not content with the adjustment of other forms change, then the EXCEL form in the production sequence number for this field should be separated from other fields, such as .
9, made with Excel2000 payroll, only the first section of Article individuals pay the first (such as number, name, job wages. want to output into the form of pay slips. how to do?
this issue should such a solution: to copy a payroll, and then work in the Page Setup menu option is selected, set the table row print job title, selected the first section of Article wages, and then inserted between every line in the page break, and then Page length set to the height of the wage bar. using a custom way of heavy equipment over the Chinese office97, Excel's only A4 paper an option, how do? easily install a printer driver on it.
10, in Excel, can not enter the decimal point, press the decimal point, the display is a comma, no matter how setup options to no avail, how to do?
This is a rather special problem, I have spent more than a dozen this hours, but it plainly is simple. In the Windows control panel, click on Chinese (China) OK method is to press the F5 key, a (ie, the backspace key).
13, how to quickly navigate to a cell?
Method One: Press F5 key, a single city
general, when dealing with a work table, a lot of data table, a table by the selected cell, and then press Ctrl + * key to select the entire table. Ctfl + * the selected area is so decision: According to the selected cell to the surrounding radiation data involved are the largest regional cell.
15. How to quickly select all worksheet cells that contain formulas?
Sometimes, the need for a worksheet All the cells contain formulas Giga to protect, or fill other cells with different colors to alert the user be careful not to have this color in the input data within the region. The following methods can help to quickly select all cells containing formulas: Select
16, how fast the different cells enter the same number of content?
selected range of cells, enter the value, then press Ctrl + Ener key, you can achieve in the selected range of cells to enter the same time value.
17, just remember the name of the function, but can not remember the function of the parameters, how do?
If you know the name of the function to use, but can not remember all the parameters of its format, Keyboard shortcuts can be pasted into the editor column of the parameter.
specific method is: Enter an equal sign in the bar then access the function name, and then press Ctr + A button, Excel will automatically enter Step 2 of 2. location?
Shift-can quickly modify the order of the cell contents.
specific method is: the selected cell, press the Shift key, move the mouse pointer to the edge of the cell, drag the pointer until the arrow ( hollow arrow), then press and hold the left mouse button to drag and drop operation. drag the mouse up and down between the cell boundary will become a horizontal shaped logo, release the mouse button to complete the operation, the selected one or more cells to be drag and drop to a new location.
19, how to make the screen bigger work space?
can not have the Hide the toolbar, you can also maximize the Excel window, or in the some of the most commonly used commands operations, using them can greatly improve operational efficiency. First, select a region, then right-click to bring up the pop health menu, select a different order according to operational needs.
21, how Use the shortcut menu?
pop-up menu includes some of the most commonly used commands operations, using them can greatly improve operational efficiency. First, select a region, then right-click to bring up the pop-Health menu Select a different command based on operational needs.
22, to prevent too many files automatically open Excel?
When Excel starts, it will automatically open all files in the directory Xlstart. When too many files in the directory when, Excel Load time-consuming and there are too many files may be wrong. The solution is to Xlstart directory should not be located in the files removed. Also, to prevent the alternate startup directory EXcel open file: Select the ; option form lines
click undefined when printed form lines
sometimes happens: you are editing an undefined form lines (see the edit window is light gray form lines), under normal circumstances is not in print print out, can sometimes lose control to the print out has chosen, especially the so-called ;, Formatting Reports?
to produce beautiful reports, need to be formatted reports. There quick way, Excel will automatically apply the default table style. method is: the selected operating area, select in the ;, items.
25, how to quickly copy the formatting of the cell?
to a format operation to another part of the data, use the cell, click on the toolbar of the add a slash?
general we are used to form a slash, and the sheet itself does not provide this functionality. In fact, we can use the drawing tools to accomplish this: Click the ;, the mouse into a cross. to move the location to add a slash at the beginning, hold down the left mouse button and drag to the end position, release the mouse, slash to draw out. Also, use the button you can easily add text above and below the diagonal, but the text around the border, in order to cancel it, the optional text box, bring up the pop-up menu, select
28, how to define your own functions?
in Excel, the user can customize the function. switch to the VisualBasic modules, or insert a new module sheet (Module), a blank window that appears, type a custom function VBA program, press Enter to complete the preparation of confirmation, Excel will automatically check its accuracy. Since then, within the same workbook, you can and use the same internal function Exed in the worksheet using a custom function, such as:
FunctionZm (a)
Ifa <60Thenim = l failed Book a different worksheet within the workbook, the formula calls a custom function?
can contain custom function under the premise of open workbook, using the link method (that is, with time of the call where the function workbook name). Suppose the previous example where a custom function Zm workbook as MYUDF.XLS, now in a different worksheet in the workbook formulas Zm function call, first make sure MYUDF.XLS is opened, and then use the following link method: = MYUDF.XLS! ZM (b2)
30, how to quickly enter the data series?
If you need to enter items such as the table number, date of sequences and some special data series , do not enter one by one, why not let Excel automatically fill? In the first cell, enter the initial data, the next cell, enter the second data, the two cells selected, move the cursor point to the right cell below the fill handle, drag along the direction you want to fill the fill handle, drag across the cell by Excel automatically populated within the prescribed sequence. If you can often use some of their regular data (such as List of Office), defined as the sequence for future auto-fill, it not once and for all! Select Enter the new sequence, note that in the new sequence to enter the half-angle of the two symbols to be separated by a comma (example: Joe Smith, Sally, hh), click the
31, using the right mouse button drag the fill handle cell
the above example, describes the use of the left mouse button drag the fill handle to automatically fill the cell method of data series. In fact, the use of a cell filled with the right mouse button drag handle is more flexible. in a cell, enter data, press and hold the right mouse button to fill sequence along the direction of drag the fill handle, there will be a menu that contains the following: copy the cells to populate the sequence, Fill in format to the value of fill; to fill the number of days to days of the charge, the monthly charge to fill in; series hh At this point, you can select a fill method.
32. If your Work has been a sequence of items in the table, trying to define it automatically fill sequence for future use, whether in accordance with the above described method of the custom sequence to re-enter the serial key?
not. there quick way: items selected range of cells containing sequences, select ; customize sequence duplicate entries, how it should be no duplicate entries in order to use the ; You do not want people to open or modify your workbook, then the idea to add a password now. open the workbook, select the needs of each type cell area to protect important data, play a double function of protection, then you can do: First, the need to protect the range of cells selected, select Select the table > increase the protection of those cells, you can also set the color and shading to allow users to clear, from the color to see the protection of those units can not be changed Giga, which can increase data input intuitive feeling. but with to the problem, that is, if even when printing in black and white color and shading all play out, the form of visibility is significantly impaired. The solution is: select
If you want to use a protected worksheet, but they have forgotten the password, there are ways to visit?. selected worksheet, select Copy it to a new workbook (Note: must be a new book), we can transcend the worksheet protection. of course, remind you better not steal other people's work this approach the table.
37, $ Ex-line or column to add the symbol cell address, can be defined as a name.
name defined in two ways: one is the range of cells selected after the To name a range of cells, and then select than with reference to the formula cell address easier to remember and read, such as the formula Enter the cell address discontinuity?
SUM function in the range of cells to enter a long string of very troublesome, especially when the area is composed of a lot of the time not continuous range of cells. then press and hold the Ctrl key, the discontinuous region selection. regional selection and select SUM (Group1) Table effective method is named as a Replace this constant time. For example, in a sheet of 4.9% is often required to calculate the interest rate, you can choose The name spaces do?
can. For example, you can name a worksheet formula, your best form this habit, that is, type
sometimes in order to direct and often give the rename a worksheet (Excel default tea table is sheet1, sheet2 .....), should pay attention to the best not rename existing function name to use to make tea table name, or produced in the following cases will charge Qi justice. We know that the workbook is to copy the worksheet, press Ctrl health and dragging along the tag line of the worksheet selected to reach the new location, copy into the work table to the 2) as a function to handle, so wrong. which should give ZM (2) re-sheet a name.
44, how to split or cancel the split window?
When we give an input data sheet In the process of rolling down, especially when the title line disappears, sometimes I remember correctly the relative position of the column headings. then the window can be split into several parts, then part of the title remains on the screen does not move, only scroll through the data section. The method is in the main menu, click In addition, there are shortcuts: the mouse pointer over the horizontal split or vertical split or dual split the money line intersection, double-click the mouse you can cancel the split window.
45, how to workbook expansion?
Select workbook can have up to 255 sheets, the system default is 6.
46, to reduce duplication of effort?
the practical application of Excel, we often encounter repeated application of some operations (such as the definition of the upper and lower standard, etc.) . In order to reduce duplication of effort, we can define some commonly used to operating as a macro. The method is: Select the subsidies for employees increased by 50 (million), of course, you can use formula, but otherwise there is a simpler way to batch change, that is using the Enter the 50 selected this cell, select entered 50.
48, how to quickly delete specific data?
if there is an Excel workbook, in which a large number of products, unit price, quantity and amount. If you want all the number of rows deleted is 0, the first selected area (including the header row), and then select of all the line number is 0. At this point all the lines is selected in the case, select line. Finally, the abolition of automatic selection.
No comments:
Post a Comment