chunky wholesale jewelry How to find the same content in the two workbooks from Excel? For example, find a column A in the workbook sheet1 and the phase of column A in the sheet2 workbook

wholesale cheap jewelry by the dozen EXCEL's little -known 35 tricks

. Create a classification drop -down list Filling

We often enter the name of the company into the form to maintain the consistency of the name , Using the "data effectiveness" function to build a classification drop list to fill in.

1. In Sheet2, enter different columns in different columns according to categories (such as "industrial enterprises", "commercial enterprises", "individual enterprises", etc., and establish a corporate name database.

2. Select column A (the name of the "Industrial Enterprise"). In the "Name" column, after entering the "Industrial Enterprise" character, press the "Enter" key to confirm.

If in the operation of the above, B, C ... columns are named "commercial enterprises", "individual enterprises" ...

3. Enter the column of "Enterprise Category" (such as Cype C), execute the "Data → Effective" command, and open the "Data Effective" dialog box. In the "Settings" label, click the drop -down button on the right side of "Allow", select the "Sequence" option, and enter "Industrial Enterprise", "Commercial Enterprise", "individual enterprises" ... … The sequence (separate in English comma between each element) to determine the exit.

The column (such as column D) that needs to enter the name of the enterprise, and then open the "data validity" dialog box. After selecting the "Sequence" option, enter the formula in the "Source" box: = Indirect (C1), determine the exit.

4. Select Cycles C4 (such as C4), click the down pull button on the right, and select the corresponding "Enterprise Category" to fill in the cell. Then select the C -column cell (such as D4) corresponding to the cell, and click the drop -down button to fill in the required enterprise name from the corresponding class name list.

Tips: When printing the report in the future, if you do not need to print the "Enterprise Category" column, you can select the column, right -click the mouse, select the "Hidden" option, and hide the column.

. Establish a new menu of "Common Documents"

In a new "Common Document" menu on the menu bar to add the commonly used workbook documents to it, so that it is convenient to call at any time Essence
1. Right -click the mouse in the blank space of the toolbar, select the "Custom" option, and open the "Custom" dialog box. In the "command" label, select the "new menu" item under the "category", and then drag the "new menu" below the "command" to the menu bar.

Plip the "Selected content" button and enter a name (such as "Common Documents") in the "Naming" box of the pop -up menu. 2. Select one item (such as the "Insert" option) under the "category"), and select one item (such as the "hyperlink" option) under the "command" on the right, and drag it into the new menu (commonly used document) And imitate the above operations named it (such as the "salary table", etc.), and establish the first workbook document list name.

repeat the above operation, add a few more document list names.

3. Select a menu item (such as "salary table", etc.) in the "Common Document" menu, right -click the mouse, and in the pop -up shortcut menu, select the "Specific Super Link → Open" option, Open the "All link of the Hyperlink" dialog box. By pressing the drop -down button on the right side of "Finding range", position the corresponding workbook (such as "salary .xls", etc., and select the workbook document.

Repent the above operations to super link the menu item and the workbook document corresponding to it.

4. When you need to open a workbook document in the "Common Document" menu in the future, just open the "Common Document" menu and click the corresponding options.

Tip: Although we drag the "hyperlink" option into the "Common Document" menu, it does not affect the "hyperlink" menu item and "commonly used" toolbar in the "insert" menu. The function of the "Insert the Super Link" button.

. Let different types of data be displayed in different colors

In the salary table, if you want to make the total salary greater than 2,000 yuan displayed in "red" The total salary of the yuan is displayed in "Blue", the total salary below 1,000 yuan is displayed in "brown". Others can be displayed in "Black", we can set it like this.

1. Open the "salary table" workbook, select the "total salary" list, execute the "format → condition format" command, and open the "conditional format" dialog box. Click the drop -down button on the right side of the second box, select the "greater than or equal to" option, and enter the value "2000" in the back box. Click the "Format" button to open the "unit format" dialog box and set the "color" of "font" to "red".

2. Press the "Add" button, and set the operation of the above operation (greater than equal to 1500, the font is set to "blue"; less than 1000, the font is set to "brown").

3. After the setting is completed, press the "OK" button. Look at the salary table, is the data of the total salary displayed in different colors according to your requirements.

. Making "Professional Symbol" toolbar

In editing professional forms, some special professional symbols are often required. In order to facilitate the input, we can make a belonging to ourselves ourselves "Professional Symbol" toolbar.

1. Execute the "Tools → Macro → Recording New Macro" commands, open the "Recording New Macro" dialog box, enter the macro name, such as "Fuhao1", and will exist in the "Personal Macro Workbook" in Hongbao. Then "OK" starts recording. Select the "relative reference" button on the "Recording Macro" toolbar, then enter the required special symbols into a cell, and then click the "Stop" button on the "Record Macro" toolbar to complete the macro recording.

If in the operation above, record the input "macro" of other special symbols one by one.

2. Open the "Custom" dialog box. In the "toolbar" label, click the "New" button to pop up the "New Toolbar" dialog box, enter the name- "Professional Symbol",, "Professional Symbol", After confirmation, a tool bar appears in the working area.

The switch to the "command" label, select the "macro" below "category", drag the "custom button" item below the "command" to the "professional symbol" column (how many are there How many buttons are dragged for special symbols).

3. Select one of the "Custom Buttons" and name them named them.

4. Right -click the naming button. In the shortcut menu that pops up, select the "Specify Macro" option, open the "Specific Macro" dialog box, and select the corresponding macro (such as Fuhao1, etc.) , Determine withdrawal.

repeating this step to link the button to the corresponding macro.

5. Turn off the "Custom" dialog box. In the future, you can use the "Professional Symbol" toolbar like a normal toolbar to quickly enter the professional symbols to the cell.

5. Save multiple printing pages with "View Noodle Manager"

The workheets often need to print different areas, and use the "noodle manager" Bar.

1. Open the worksheet that needs to be printed. Use the mouse to drag on the line (or column) that do not need to be printed. Select them and then right -click the mouse. In the subsequent shortcut menu, select "Hide "Options, hidden (or columns) that do not need to be printed.

2. Execute the "View → Vision Manager" command, open the "Face Manager" dialog box, click the "Add" button, pop up the "Add View Noodle" dialog box, and enter a name ( After "above report"), click the "OK" button.

3. Show the hidden line (or column), and repeat the above operation to "add" other print vision.

4. When you need to print a certain table in the future, open the "Facial Manager", select the name of the form that needs to be printed, and click the "Display" button. Come out, simply set, typeset, press the "print" button on the toolbar, everything is OK.

6. Let data sort on demand

If you want to sort the employee according to the department's department. What should I do in the order of strokes? Customized sequences can be used to sort.

1. Execute the "Format → Options" command, open the "Options" dialog box, enter the "custom sequence" tab, enter the sequence of the department in the box under the "input sequence" (such as "Organ, team, one workshop, second workshop, three workshops", etc.), click the "Add" and "OK" button to withdraw.

2. Select any cell in the "department" column, execute the "data → sorting" command, open the "Sort" dialog box, click the "Options" button, and pop up the "Sorting Options" dialog box. Press the drop -down button, select the customized sequence just now, press the "OK" button to return twice, and sort all data as required.

Seven. Thoroughly hide the data

The content in some cells in the worksheet does not want to make a viewer, so I have to hide it.

1. Select the cell (region) that needs to be hidden, execute the "format → cell" command, open the "cell format" dialog box, and select "Classification" of the "digital" label " Customize the "option, and then enter" in the box "below the" type "on the right";;;; "(segment number in three English states).

2. Then switch to the "protection" label, select the "hidden" option, and exit by pressing the "OK" button.

3. Execute the "Tools → Protection → Protection Workheet" command, open the "Protection Workheet" dialog box, set the password, and return "OK".

In this settings, the content of the above cells is no longer displayed, that is, using Excel's transparent function cannot make it shape.

Tips: Under the "protection" label, please do not clear the "∨" number in the check box in front of the "Lock", so as to prevent others from deleting the data you hidden.

8. Let the Chinese and English input methods intelligently appear

In the editing form, some cells must be entered in English, and some cells must enter Chinese in Chinese , Repeatedly switching the input method is really inconvenient. Why not set it, let the input method intelligently adjust?

In selection of the cell area that needs to be entered in Chinese, execute the "data → effective" command, open " Data validity "dialog box, switch to the" input method mode "label, press the drop -down button on the right side of the" Mode ", select the" Open "option, and exit" OK ".

Is when elected in the cell that needs to enter Chinese in the cell that needs to enter a cell in the area of ​​the Chinese input method (the first Chinese input method in the input method list) The Chinese input method is automatically closed.

. Let "Automatic Correction" enter the unified text

Do you often worry about entering some fixed texts, such as "Computer News"? Then Look down.

1. Execute the "Tools → Automatic Correction" command to open the "Automatic Correction" dialog box.

2. Enter "PCW" in the box below "replacement" (can also be other characters, "PCW" with a lowercase), enter the "computer in the" replacement to "box below "Report", then click the "Add" and "OK" buttons.

3. If you need to enter the above text in the future, just enter the "PCW" character. At this time, you can consider the "PCW" appliance at this time, and then confirm that it will be done.

X. The custom function in Excel

excel function is rich, but it cannot meet all our needs. We can customize a function to complete some specific operations. Below, we will be a function that calculates a trapezoidal area:

1. Execute the "Macro → Visual Basic Editor" menu command (or press "Alt F11" shortcut key) to open Visual Basic Edit window.

2. In the window, execute the "Insert → Module" menu command, insert a new module -module 1.

3. Enter the following code in the "code window" on the right:

Function v (a, b, h)
v = h*(a b)// 2
end Function

4. Close the window and custom function to complete.

In the use of a custom function like a built -in function.

This prompt: The customized function of the above method can usually only be used in the corresponding workbook.

eleven, the picture below the header

The background added to the worksheet, it is lined with the entire worksheet. Can you just line under the head? ?

1. Execute the "Format → Workheet → Background" command, open the "Workheet Background" dialog box, select the picture that needs to be used as the background, press the "Insert" button, set the picture to the entire entire Under the working meter.

2. While holding down the CTRL key, use the mouse to drag in the cell (area) that does not need to lined the picture, and select these cells (regions) at the same time. 3. Press the drop -down button on the right side of the "Filling Color" on the "Format" toolbar, and select "White" in the subsequent "palette".
In this settings, pictures left under the cells, and there is no picture under the element (area) selected above (in fact, the picture is covered by "white").

This the picture lined under the cell does not support printing.

. Twelve, use the characters "

wholesale sterling silver religious jewelry Enter formula = if (countif ($ a $ 1: $ 1: $ a $ 50, sheet2! $ A1)> 1, $ a1, "") in Sheet1's B1, fill in the formula down.

melody wholesale jewelry This problem is a bit complicated. I usually operate like this.
1. Copy column data in SHEET1 and column data in sheet2 to column A in Sheet3. For the sake of comparison, the data in column A in sheet2 can be set to a color; r r
2. Name the column A in Sheet3 to "AA" (the name of the random); method: Select column A -enter "AA" in the name box;
3, the B1 unit in sheet3 Enter the formula in the grid = Countif (AA, A1), and use the filling handle to copy the formula.
4. At this time, you will find that there are different numbers in it. 1 indicates that there is only one of the same data, 2 indicates that there are 2 same data, and the class is pushed in order. Using the screening function to sieve out data other than 1, you can see that those data are the same. Because different colors were marked at the beginning, it was very clear when viewed.
It hope to help you and enter it purely.