0 1 Excel self-study plan
First stage foundation
Day 1-2
Learn the interface and basic operation of Excel; Create, save and open Excel files, enter data in tables, select cells and edit contents; Learn about shortcut keys and common toolbars
Day 3-4
Master mathematical formulas; Learn how to enter and copy formulas in cells.
Fifth day
Learn common functions and master how to use functions to edit functions in a worksheet.
The second stage? advanced
Day 6-8
Learn how to use conditional formatting; How to use the preset conditional format in the worksheet; How to customize conditional format
Day 9-10
Master how to use the filtering function; How to use sorting function
Day 1 1 day
Learn the basic principles and functions of PivotTable; How to create a PivotTable, and how to set up, filter and sort the PivotTable.
The third stage? promote
Day 12- 13
Master how to create a chart in Excel and how to adjust the size, color and style of the chart; How to add data labels and tables to charts
Day 14
How to use the advanced functions of Excel; How to use macros in worksheets
Day 15
Comprehensively apply the knowledge learned before to solve practical problems; Practice making tables, charts, pivot tables and macros in Excel.
0 1 Understand the Excel interface (Figure 3)
Tab: It includes main functions such as File, Home Page, Insert, Page Layout, Formula, Data, Review and View.
Ribbon: contains some commonly used shortcut tools, such as cut, copy, paste, bold, italic and underline.
Sheet tab: at the bottom of the page, all sheets in the current workbook are displayed, and different sheets can be switched.
Edit bar: below the ribbon, the formula or numerical value of the currently selected cell or range is displayed.
Workspace: The central area is the workspace where you can enter and edit data, formulas, charts, etc.
Status bar: At the bottom of the Excel interface, some information is displayed, such as sum, average and count of cell coordinate values.
02 master the basic operation of Excel
[New Workbook] Open Excel and click [New Workbook] or use the shortcut key CTRL+N.
[Enter data in worksheet] In Excel, click a cell, and then type the data you want to enter.
[Adjust column width and row height] Hover the mouse over the boundary of a column or row. When the cursor changes to a double-headed arrow, hold down the left mouse button and drag to adjust the width or height.
[Insert or delete cells, rows or columns] Select a cell, row or column, right-click and select Insert or Delete.
[Use Functions] Excel provides many built-in functions, such as SUM AVERAGE and IF, which can help you calculate data quickly. Enter the name and parameters of the function to be used in the formula field.
[Sort and Filter] Select a data area, and use the functions of "Sort" and "Filter" to sort or filter the data according to the specified conditions.
[Create Chart] Select the data area, click the Insert tab, and select the chart type in the chart ribbon to create a chart.
[Print Worksheet] Select the File tab, select Print, preview the printing effect, and select the printing options.
[Save Workbook] Select the File tab and select Save As to save the workbook in Excel format or other formats.
03 Excel common shortcut keys
Ctrl+ alphabet
Ctrl+C copy
Ctrl+F lookup
Ctrl+X cut
Ctrl+G pop-up dialog box
Ctrl+V paste
Ctrl+H substitution
Ctrl+Y recovery
Ctrl+) Alignment at both ends
Ctrl+z undo
Ctrl+K insert hyperlink
Ctrl+A select all
Ctrl+L create table
Ctrl+B bold
Ctrl+M text merge center
Ctrl+N new workbook
Ctrl+U underline
Ctrl+I italic
Ctrl+O open file
Ctrl+S save file
Ctrl+Q begins to share quickly.
Ctrl+p printing
Ctrl+R padding to the right
Create table
Ctrl+D auto-fill
Ctrl+W center
Ctrl+E closes the file.
Ctrl+ digital article
Ctrl+0
hidden columns
Ctrl+ 1
Displays the Cell Format window.
Ctrl+2
Add/Unbind
Ctrl+3
Add/Remove Font Tilt
Ctrl+4
Add/Delete Underline
Ctrl+5
Add/delete rows
Ctrl+6
Turn into
Ctrl+8
Show/Hide Outline Symbols
Ctrl+9
Hidden line
Ctrl+F 1
Expand/collapse ribbon
Ctrl+F4
Close the selected workbook window.
Ctrl+F5
Restores the size of the selected workbook window.
Ctrl+F6
Switch to the next workbook window
Ctrl+F8
Execute size commands.
Ctrl+F9
Minimize the workbook window to an icon.
Ctrl+F 10
Maximize/restore the selected workbook window
Ctrl+Shift+ digital article
Ctrl+Shift+0 unhide the column.
Ctrl+Shift+ 1 is set to a number format.
Ctrl+Shift+2 is set to time format.
Ctrl+Shift+3 is set to date format.
Ctrl+Shift+4 is set to currency format.
Ctrl+Shift+5 is set as a percentage format brush.
Ctrl+Shift+6 is set to technical format.
Ctrl+Shift+7 Add External Border
Ctrl+Shift+8 Select the area to use.
Ctrl+Shift+9 unhide the row.
Substitute article
Alt+Q operation description search box
Alt+F opens the file page.
Alt+H opens the Home tab.
Alt+N opens the insert tab.
Alt+P opens the page layout.
Alt+M opens the formula tab.
Alt+A opens the data tab.
Alt+R opens the review tab.
Alt+W opens the view tab.
Alt+= Fast Sum
Alt+ carriage return forced line break
Alt+J drop-down list
04 Excel common functions
SUM？ Data summation
SUMIF sums according to specified conditions.
SUMIFS sums according to multiple conditions.
Counting data counting
COUNTIF counts according to the specified conditions.
COUNTIFS counts according to multiple conditions.
MIN calculates the minimum value of multiple data.
MINIFS？ According to the specified conditions, calculate the minimum value that meets the conditions.
MAX calculates the maximum value of multiple data.
MAXIFS calculates the maximum value that meets the conditions according to the specified conditions.
AVERAGE calculates the average of multiple data.
LEFT extracts the specified number of characters to the left of the text.
RIGHT extracts the specified number of characters to the right of the text.
MID extracts the text from the specified position and specifies the characters of the long picture.
Text cell content format
IF conditional judgment
AND multi-conditional AND operation, that is, all conditions need to be met.
Or multiple conditions or operations, that is, any one condition is met.
VLOOKUP queries according to conditions and returns qualified results.
INDEX queries the contents of the specified location and returns.
MATCH finds the qualified content in the data area and returns the corresponding location.
Search in the specified data area, find the qualified data and return it.
WEEKNUM calculation week
DATEDIF calculates the difference between two dates.
05 Excel practical plug-in
Square lattice
Foolish operation, simple and easy to use! Super powerful
Text processing, batch entry, deletion tool, merge transformation, duplicate value tool, data comparison, advanced sorting, color sorting, merged cell sorting, spotlight, macro storage box.
Useful treasure
This is a permanent free Excel plug-in officially launched by Excelhome. The interface is not so exquisite, but the advantage is free. The operation is simple. You can merge worksheets and books with one click of the mouse.
Power query
You often encounter the situation that you need to merge multiple tables in your work. What would you do if multiple tables were in different workbooks?
Power Query can extract data from the required data sources and integrate data sources from different sources, which can greatly facilitate us to analyze and process data!
solver
Excel is a built-in plug-in, which can solve many optimization problems, such as linear programming and nonlinear programming. Just insert constraints, write down the objective function, and leave the rest to the solver.