| Back to logs list
Reprinted from 591333112 at 23:14 on December 1, 2009 Reading (loading. ..) Comments (0) Category: Personal Diary EXCEL classic use of Daquan (had to Favorites ah)
01, how the cell has 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 the certificate in each two-digit number before and added an 13, if the words one by one to change too much trouble, then we can use the following approach, save time and effort:
1) Assuming the certificate number in the A column, in column A and click the right mouse button, insert a for the B column;
2) write 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 original certificate number followed by 13 in cell B2 write: = A2 &
02, how to set the file pull-down window at the bottom of the last run number of the file name?
Open the If the menu displays the most recently used file name, then the
03, entered in the EXCEL such as
This is due to the date of EXCEL format for the automatic identification result, you just click the main menu text format on the line.
04, how to make it in EXCEL WORD the same as the automatic timer to save the file?
click on If the And then settings.
05, use Excel to do multi-page form, how to do that as a Word table title, the first line of each page (or lines) is the same. But not done with the header?
in the EXCEL file menu - Page Setup - Sheet - print titles; the top or left end of the title can be set, the dialog box by pressing the button folded, can be delineated with the mouse. This Excel will automatically add your page in the designated part of a header.
06, 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 fact that the weighted average of the total value (such as amount) divided by the total number of draw units, on average, rather than simply the value of the various units (such as price) that the average unit value obtained. In Excel, you can set the formula to solve (in fact, is a division equation), the denominator is the sum of all values, all elements are the corresponding quantities and its result is a weighted average of these values.
07, if an Excel file contains multiple worksheets, how to work more than one set to the same table headers and footers? How can one 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 is pop-up menu, select
08, EXCEL serial number in a 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 methods can be quickly resolved? If the number is not
other content as the forms change with the adjustment, then EXCEL form in the production sequence number for this field should be separated from other fields, such as
09, made with Excel2000 payroll, only the first section of Article individuals pay the first (such as number, name, position salary .......), want to output into the form of pay slips . How to do?
this issue should be resolved this way: first 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 everyone Insert page break between rows, and then set the wage-page article in the height of the. Heavy use custom mode again Chinese office, 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 a dozen hours this time,
Not 'and' lost '- Qzone log, but it plainly is simple. In the Windows control panel, click on number This then open Excel to all normal.
11, how to quickly select a specific region?
use F5 key to quickly select a specific area. For example, to select A2: A1000, the most convenient way is to press the F5 key, a
12, how to quickly return to select the region?
Press Ctr + BacksPae (or backspace).
13, how to quickly navigate to a cell?
Method One: Press F5 key, a
Method Two: Click the bar on the left cell address box, enter a cell address.
14, Next Ctrl + * key to select the entire table. Ctfl + * the selected area is so decided: 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 all worksheet cells contain formulas of the box as to protect, or fill other cells with different colors to remind the user's attention can not have this color in the input data within the region. The following methods can help to quickly select all the cells containing formulas: Select be.
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 a one-time in the selected range of cells to enter the same 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, you can use the keyboard shortcut to paste into the edit parameters of the column.
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 the When using the name easy to remember and has a long string of parameters function, the above method is particularly useful.
18, how to select one or more cells drop to a new location? hold down the Shift key
can quickly change 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 when the boundary between the cell becomes a horizontal to be drag and drop to the new location.
19, how to make the work space on the screen larger?
can not hide the toolbar, you can also maximize the Excel window, or in the
20, how to use the pop-up menu?
shortcut 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.
21, how to use the pop-up menu?
shortcut 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, how to prevent Excel automatically open too many files?
When Excel starts, it will automatically open Xlstart directory of all files. When too many files in the directory when, Excel file loading too time-consuming but also may be wrong. The workaround is to Xlstart directory should not be located in the files removed. Also, to prevent the alternate startup directory EXcel open file: Select the
23, how to get rid of grid lines?
1) remove the line in the table in the edit window
click failure;
2) to remove the undefined form lines when printing
happens sometimes: you are editing the table is not defined line (in the editing window to see is light gray form lines), under normal circumstances is not printed when you print out, but sometimes it has chosen to lose control to the print, especially the so-called To remove these form lines, just click
24, how to quickly format the report?
statements to create beautiful, the need for reports to be formatted. A quick way, Excel will automatically apply the default table style. Is: the selected operating area, select Note that the format list box below, includes a When not using the.
25, how to quickly copy the formatting of the cell?
to a format operation to another part of the data, use the Select the desired source format that contains the cell, click on the toolbar of the
26, how to add a slash at the table?
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 To add a slash to move the start position, press and hold the left mouse button drag to the end position, release the mouse, slash to draw out. In addition, the use of
27, how to quickly enter numbers as text?
enter the number in front of a single quotation mark
28, how to define your own functions?
in Excel, the user can customize the function. Switch to the Visual Basic module, or insert a new module sheet (Module), a blank window that appears, type a custom function in VBA program, press Enter to complete the preparation of confirmation, Excel will automatically check the correctness. Thereafter, within the same workbook, you can and use the same internal function Exed in the worksheet using a custom function, such as:
Function Zm (a)
If a
If you do not want people to open or modify your workbook, then the idea to add a password now. open the workbook, select need to separately enter the data protection, play a double protection feature, now you can do: First, the need to protect the range of cells selected, select Select the : Do not forget you set a 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 it poses a 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 . After printing out the form on the face as ever.
36, sheet protection passwords forgotten how to do?
If you want to use a protected worksheet, but the passwords and forget, there are ways to visit?. selected worksheet, select worksheet protection. of course, remind you better not steal other people's work this approach the table.
37, , when the address of a cell containing the formula copied to a new location,
Twenty-six man loved that lie - Qzone log, the formula will cell address with the change. you can before the column number or line number to add the symbol remains fixed when the copy.
38, how to use character names instead of cell address?
If you do not want to use the cell address, can be defined as a name.
name defined in two ways: one is the range of cells selected after the name, wages), range of cells string is very troublesome, especially when discontinuous range of cells for many regional composition. then press and hold the Ctrl key to select discontinuous regions. Regional selected and select By default, all the names in the workbook are global. In fact, you can define a local name, so that a sheet only effective method is named as a Name working table, 4.9% are often required to calculate the interest rate, you can choose Enter A2 form this habit, that is, type br> 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 sheet to reach the selected location of the new copy into the worksheet to the processing, thus wrong. which should give ZM (2) re-sheet a name.
44, how to split or cancel the split window?
When we input data to a worksheet , in the scroll down the process, 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, rolling only the data part. The method is in the main menu, click 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 the 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 put the operation of some commonly used to define macros. The method is: Select the define macro with VBA programming.
47, how to quickly modify the data volume?
if there is an Excel workbook, which has all the wages table. now want to increase subsidies for all workers 50 (million),
Space for 30 people to open!, of course, you can use formula, but otherwise there is a simpler way to batch change, that is using the cells, select select
If there is an Excel workbook, in which a large number of products, unit price, quantity and amount. If you want all rows to delete the number 0, the first selection (including the header row), and then select The following are several ways to quickly remove the empty line:
Method One: If the row order does not matter, you can sort a column, then you can easily delete blank lines.
Method Two : If you can not change the order of the line, you can first select the are concentrated to the bottom of the table, deleting all blank lines. Finally, A column reordering, column A and then delete and restore the original worksheet in the order of the rows.
Method Three: Using the example specific data can create generate more value or of a set of values rather than a single value operations formula. To enter an array formula, you must first choose to store the results of the range of cells, in the formula bar enter the formula, and then press ctrl + Shift + Enter key combination to lock the array formula , Excel will automatically add the brackets on both sides of the equation The brackets will disappear, and then edit or remove the formula, press the Ctrl + shift + Enter keys.
51, how not to display or print out the table contains the value 0?
Usually, we do not want to display or print out the table contains the value 0, but set to empty its contents. For example, Figure 1 Total column if you use how to make a value of 0 does not show? Method One; use with If function to determine whether the value is 0 the formula, namely: = if (b2 + c2 + d2 = 0, From the Average function in Excel using the average calculation of cells, the cell value of 0 is also included. Is there any way to exclude in the calculation of the average cell value of 0?
Method One: If the cell value of 0, available on the cases of > Method Two: Using Countif function, for example, the following formula to calculate the b2: B10-Africa region average of 0 cells:
= sum (b2: b10) / countif (b2: b1o, ; 0 In Excel, but not similar to the