The "Compatibility" function ribbon has been added to the new function to facilitate the normal use of documents in different versions.

"Sparkline" three styles: line chart, column chart, profit and loss chart

Divided into: ribbon bar, toolbar, title bar, name box, formula edit box, worksheet label, drawing toolbar

File: New, open, save, print files, recently used workbooks

Home: Clipboard, Fonts, Alignment, Numbers, Styles, Cells, Editing

Insert: graphics, formulas, special symbols, word art, charts and other elements

Data: Get external data, joins, sort and filter, data tools and outlines

Review: Proofreading, Chinese Simplified and Traditional Chinese conversion, language, comments, changes; used for proofreading, revising and other operations on Excel tables

View: workbook view, display, aspect ratio, window, macro; used to set the view type of the table window

Column chart: such as percentage stacked column chart, three-dimensional stacked column chart, three-dimensional column chart, etc.

Bar chart: such as clustered bar chart, stacked bar chart, three-dimensional percentage stacked bar chart, etc.

Line chart: such as line chart, data point line chart, percentage stacked data point line chart, etc.

Pie chart: used to display the proportion of the items that make up the data series relative to the overall size of the items, such as composite pie charts, three-dimensional pie charts, separated pie charts, separated three-dimensional pie charts, etc.

XY scatter plot: It can display the relationship between the values ​​of multiple data series, and can also draw two sets of numbers into a series of XY coordinates, such as scatter plots, smooth line scatter plots, smooth line scatter plots without data points, and polyline Scatter plots etc.

Area chart: Emphasizes the magnitude of change over time and shows the relationship of parts to the whole, such as stacked area chart, percentage stacked area chart, three-dimensional area chart, three-dimensional percentage stacked area chart, etc.

Donut chart: shows the relationship between parts and the whole, and can contain multiple data series at the same time. Such as: donut chart, separated donut chart

Radar chart: Each category has its own numerical axis, radiating from the center; lines connect all values ​​in the same order, allowing you to compare the total values ​​of a large number of data series; such as: data point radar chart, filled radar chart

Surface chart: Find the optimal combination between two sets of data; such as: three-dimensional surface chart, three-dimensional surface chart (frame), surface chart (top view), surface chart (top view frame chart)

When selecting a reference method with the mouse, Excel defaults to an absolute reference to the cell.

A1 mode: Excel's default reference style, columns are represented by uppercase letters, from A to IV, with a total of 256 columns. R1C1 method: File-Option-Formula; columns and rows are represented by numbers

Absolute reference: Add a $ sign before the row number and column number (the formula will not change after copying and pasting); such as $A$1

Relative reference: (the formula will be updated after copying and pasting);

Mixed reference: a cell containing an absolute reference coordinate and a relative reference coordinate; (absolute reference row, relative reference column such as B$5; absolute reference column, relative reference row such as $B5)

When moving or cutting a formula, whether the cell is a relative reference or an absolute reference, the contents of the formula will not change after the move.

Reference other worksheet formats in the same workbook: Referenced Worksheet! The referenced cell is Sheet1!A5

Other worksheet formats that reference different workbooks: [Referenced workbook name] Referenced worksheet! The referenced cell is such as [a]Sheet1! A5

Advantages of using name references in worksheets

Name definition rules

11 types of functions: database, date and time, engineering, finance, information, logic, query and reference, mathematics and trigonometry, statistics, text, user-defined functions

MAX function: find the maximum value - syntax: MAX (number1, number2...) such as MAX (3.5.7) = 7

RANK function: Ranking function: RANK (number, ref. [order]) sequence sorting

SUM function: Sum function: SUM (number1, number2…)

AVERAGE: Average function: AVERAGE (number1, number2…)

LEN function: calculates the length of characters; returns the length of a double-character string, and spaces are also counted as characters

TRIN function: removes excess leading and suffix spaces from a specified string

String interception characters

date function

The worksheet is composed of 256*65536 cells; the cell is the basic unit

The cell address consists of a letter identifying the column and a number identifying the row (for example, A2 means column A, row 2)

Absolute address: an address reference that does not change as the cell changes. For example, if you enter "=$A$2" in a certain cell, it will mean "column A, row 2" no matter where the data is.

Relative address: changes with the position. For example, the reference formula of cell C4 is "=A2". When C4 becomes D6, the reference format becomes "=B4"

Excel provides two wildcard characters: "?" represents a single character, and "*" represents all characters

Edit cells: Double-click the cell to enter data; if you want to cancel, press Esc

When the mouse clicks on a cell, the border color turns black, it is called the "current cell", and the address is displayed in the address bar.

Move a cell: Select a cell, place the mouse pointer on the cell border, and press the left button to drag it to a new location; to copy, press the Ctrl key before releasing the mouse.

Rename the worksheet: 1. Double-click the name under the current worksheet, such as "Sheet1" and enter the new name; 2. Start - Cell Format - Rename the worksheet

Quickly select the entire worksheet content you are working on: "Ctrl Shift * (↑↓←→)"

Quickly select a certain area: click on a cell and hold down the Shift key, then click on the cell in the area you want to select.

When selecting cells (rows, columns), hold down the Shift key at the same time to select consecutive cells (rows, columns); hold down the Ctcl key at the same time to select discontinuous cells (rows, columns)

Merge cells: "Home" tab - "Cells" ribbon, click "Format" - "Format Cells" - "Alignment" tab - "Text Control" field, select "Merge Cells" and check √ .

If the text or number in a cell exceeds the column width, the printed text will be truncated and the printed number will appear as "######"

The occurrence of #VALUE is usually caused by incorrect parameters in the formula: 1. Non-numeric cells may be used during calculations 2. The formula does not conform to function syntax 3. Forgot to press the shortcut key Ctrl Shift Enter when using curly brackets

Sort: Select the data area to be sorted, open the "Data" functional area of ​​the ribbon bar and select the "Sort" function in "Arrange and Filter"; determine the sorting conditions and order, and set the sorting parameters in the "Arrange" dialog box. Column, order, etc., click OK

Calculate the subtotal and total values ​​in the data list: 1. Arrange the data to be subdivided and summarized first 2. Select the arranged data area and click the "Data" "Subtotal" button 3. Set the classification field in the subtotal window , summary method, select the summary item 4. Press the "OK" button to complete the classification and summary

Create headers and footers: Click the "Header and Footer" command in the "Insert" ribbon. If you need to use the built-in headers and footers, open the "Header" or "Footer" drop-down list. choose from

Set the printing scaling ratio: Click the "Page Layout" ribbon, open the "Page" tab of the "Page Setup" dialog box, select the scaling ratio and enter the required scaling ratio.

Adjust the space around the report: "Page Layout", open "Page Settings" and click "Margins" to adjust the values ​​​​for the top, bottom, left, right, header and footer.

Encrypt Excel files: "File" - "Information" - "Protect Workbook" - "Encrypt with Password", set a password in the pop-up "Encrypt Document" dialog box

Set a permission password for the workbook: "File" - "Save As", click "General Options" in the "Tools" drop-down to set two types of passwords; if you do not want other users to open the workbook, enter the "Open Permission Password" text box Enter the password in "Modify Permission Password"; if you do not want other users to modify the workbook but can view it, you need to enter the password in "Modify Permission Password"; you can also set two passwords at the same time

Set the read-only mode: "File" - "Save As" select the file you want to set, click "Tools" to open "General Options", select "Recommend Read Only" and click OK, then click on the "Save As" dialog box save button