Summary of worksheets and cells

Copy worksheet: select the worksheet to be copied → hold down the ctrl key → left mouse button drag can be

Rename the worksheet: select the worksheet → double-click on the left button of the mouse to modify the name

Change the color of the worksheet labels: selected worksheets → right mouse button → click the color of the label of the worksheet → set the The topic requires the color

Move the worksheet: select the worksheet → right mouse button → move or copy the worksheet → select the appropriate location can be

Delete the worksheet: select the worksheet → right mouse button → delete

Move across the workbook: open the workbook 1 → the cursor is positioned in the need to move the worksheet → open the workbook 2 → select the worksheet in the workbook 2 → right mouse button → move or copy → choose to move to which a workbook → and then select the following selected worksheet before one of the worksheets → click OK

Cell format: numerical, monetary, text, customized, etc.

Input 001, 002 ...... text number: Select the need to set the number of cells in the region → digital tab set the cell format for text → in the first cell enter 001 → cell below enter 002 → selected 001, 002 → mouse to the lower right corner → black ten heart arrow drag down to

Input 001, 002 ...... numerical value numbering: in the The first cell input 1 → the lower cell input 2 → selected 1, 2 → mouse to the lower right corner → black ten heart arrow drag down can be → and then select the region of the input number → right mouse button to set the cell format → to the numerical value → to retain the 0 decimal place → and then find the customization → will be deleted at the type of the original content → enter "000 "can be

Set the date format: select the need to set the date of the region → right mouse button → set the cell formatting → to find the date → set

Set the date of November 21, 2022? Friday this date format: select the date area → right mouse button → set the cell format → find the date → select a format with the year, month and day → customize → come to the type → remove the "; @" → enter a space after → enter "aaaa" can be

Set the cell box line: selected area → to the font to find the border → set the corresponding box line can be

Remove the grid line: View tab → uncheck the grid line

Set the cell less than 15 is displayed as a drought: selected area → right mouse button to set the cell formatting → customize the type of the original deleted → enter "[<15]drought"→click OK

Set the discount for 0 cells show "-" → greater than 0 cells show as a percentage → and retain 0 decimal places (such as 15%): Selected area → right mouse button to set cell Format → Customize → type at the original deleted → enter "[= 0]" - "; [& gt; 0] 0%" → or enter "0%;;" - "" Click OK to

Conditional Format Summary

Highlight the cell rules: greater than, less than, between, equal to, duplicate value

Item selection rules: the value of the largest, smallest of the 10 items → This can be modified according to the needs of the number of

Use of the Conditional Format will not be less than 110 points in the results of the results of the cell with a color fill: Selected data area → come to the Format Conditional Format → highlight the cell rules → other rules → set greater than or equal to the conditions → set the value of 110 → Format → Fill → set the color can be

Set up a region of the cell value is greater than a fixed value of 110% of the highlighted cell Format: Style ?ú New Rule ?ú Use the formula to determine the cell format to set the cell ?ú Reference to the first cell of the region ?ú Cancel the absolute reference ?ú Then enter "> (reference to a fixed value of the cell) and the absolute reference multiplied by 110%" ?ú Remove the format to set the color of the font color fill color can be

the use of conditional formatting to display the results of the first: Select a section of the results of the data area → Conditional Format → Project Selection Rules → value of the largest 10 items → 10 can be changed to 1 can be → and then set the requirements of the color and fill can be

Only in the form of data strips to show the format of the cell: Select the data area → Conditional Format → Data Strip → select the appropriate format → came to the Conditional Format → Data Strip → other rules → to display only data bars check → click OK

Set the table format summary

To have a merger after the center of the table to display the title of the application of an appropriate table format: select the merger of the following contents of the center → style → set the table format → select a style

Set the table format of some of the basic operations: we can set the table name, the transformation of the table tab Tools tab to set the table name, into the region, title row, summary row, border row, the first column, etc. → check

Sorting summary

Sorting basis: you can set the value, cell color, font color

Sorting order: ascending, descending, custom sequences

Sorting of the table content: Data → Sorting Set the main keywords→Set the basis for sorting→Set the order→Check Include Headings

Set a custom sequence: File tab→Options button→Advanced→Drag the wheelbar to the lowest end→Edit the customized list→Manually enter the sequence→Knock Enter for each input→Click Add to confirm

Sort according to the set custom sequence: Data tab→Sort button → set the main keywords → set the basis for sorting → set the order → select the custom sequence → find the sequence set in advance → click OK

Screening knowledge

Screening is divided into: automatic screening, advanced screening

Automatic screening in the number of filtering: Cursor placed in the first row of cells in the data area → data tab → filtering → click on filtering the drop-down box → you can filter the content of the data → data screening to find the relevant conditions you need

Automatic screening in the text screening: the cursor is placed in the text area of the first row of cells → Data tab → screening → click on the screening of the drop-down box → you can filter the content of the text screening text screening text screening to find the relevant conditions you need

Advanced Screening: First set up the conditions of the region → will be positioned in the results of the cursor in the table → data tab → advanced screening button → choose to display the results in the original region → list area with the mouse to refer to the need for advanced screening of the data area → conditions of the region with the mouse to refer to the conditions of the region set up ahead of time → copied to the selection of the need to start storing the results of the region → click on the OK can be

Location of the conditions of the summary of the test points

Location of common shortcut keys: CTRL + G

Location of conditions: location of annotations, constants, formulas, null values, ......

Delete all the formula of the error value: select all the data ?ú Start tab ?ú Find and select the drop-down box ?ú Location of the conditions ?ú Formulas ?ú Tick the error value ?ú OK ?ú delete Delete can be

On the data of all empty cells enter the number 1: Select the data→→Start tab→Find and select the drop-down box→Locate the conditions→Tick the empty value→OK→Mouse does not move→Enter 1→press ctrl + enter can be

Merge the calculation of the test point summary

Will be the four worksheets of the data in order to sum the way to merge into a new worksheet: Cursor positioning in the results table → to the data → merger calculation → function to find the function required by the topic → this question point summation → reference position → mouse reference to the first table of data → and then click Add → reference to the second table → click Add → until the 4 table data are quoted → check the first line → the leftmost column

Note: merger calculations can not be dealt with in the first 2 columns of text of a number of tables, the first column is generally The first column is text, the second column of data can be processed

Summary of data validity points

Data validity of the main assessment: allow input conditions, input information, error warnings, etc.

Conditions: decimals, integers, sequences, length of the text, customized

Three styles of error warnings: stop, warnings, information

To the gender column settings Data validity can only be entered for men and women and set the corresponding error alerts: Select the need to set the data validity of the region → Data tab → Data Validity → Settings → Allow the selection of sequences → Source → enter "male, female" → error warning → style selection stop → title input "information input errors "

To add a drop-down selection of data validity to the departmental input: select the area where the data validity needs to be set → Data tab → Data Validity → Settings → Allow Selection of Sequences → Sources → manually enter the departmental and then comma separated or reference to the departmental region → OK

Simulation of the analysis of the summary of the points

Data simulation analysis of three important points: simulation of the table ?ú Program Manager ?ú Summary of the program

Simulation of the table: simulation of the first cell of the table to enter the formula ?ú Selected data area ?ú Data tab ?ú Simulation Analysis ?ú Simulation table ?ú Reference to the data model In the corresponding rows and columns of the cell → OK can be

Note: If you need to copy the formula to the cell, after copying the completion of the ESC key to exit the editing state, and then paste

Program Manager: Switch to the Data tab → Simulation Analysis → Program Manager → Add → Enter the program name → Enter the referenced cell → Click OK to enter the data → OK

To create the Program Summary: Switch to the Data tab → Simulation Analysis → Program Manager → Summary → in the results of the cell area references to the data model contains the formula of the cell

Classification Summary Summary Exam Summary

Use the classification of the summary function to find out the sum of the data type, each group of data is not divided into pages: first of all, the classification of the sorting of the field → Data tab → Classification Summary → Classification Field Selection needs to be Summation of the type of summation → summary method to select the summation → select the summary item → check the appropriate type → uncheck each group of data paging → click OK

Note: the application of the table format of the data table should be selected in its entirety → right mouse button → form → transformed into a region → click on the OK; Otherwise, the classification of the summary tab will be grayed out can not be used

Note: Classification of the summary of the former must remember to sorting, sorting, Sorting

Pivot table summary

Insert the pivot table steps: select the data area → Insert tab → pivot table → choose to place the pivot table location → select the appropriate fields placed in the row labels, column labels, value summary mode, report filtering area

Pivot table data grouping is mainly divided into species: the first is the date (according to the) month, quarter), the second is on the value (for the number of information in the numerical interval)

The use of pivot tables to the two-dimensional table into a one-dimensional table: the keyboard press ALT + D + P → open the pivot table wizard → Add Data → Adjust the layout

Note: Set up a pivot table, the title gives a specific field placed in the position in accordance with the title to the title, the easiest way to do so; encountered no give specific requirements, only to give a sample chart to distinguish their own fields placed in the position