Microsoft Excel is a spreadsheet software written by Microsoft for computers using Windows and Apple Macintosh operating systems. Intuitive interface, excellent calculation function and chart tools, coupled with successful marketing, make Excel the most popular personal computer data processing software. Below I have compiled the relevant knowledge of excel, welcome to read the collection.
Excel quickly links the data on the Internet.
You can quickly establish links to online workbook data in the following ways:
1. Open the workbook containing the data to be linked online, select the data in the workbook, and then click the "Edit → Copy" command;
2. Open the workbook that needs to be linked, and click the cell in the upper left corner in the area where the linked data needs to be displayed;
3. Click Edit → Paste Special, and in the Paste Special dialog box, select the Paste Link button.
If you want to create a link without opening the Internet workbook, you can click the cell to be linked, and then type (=) and the URL address and workbook location, for example: =/[filel.xls].
How excel Sets Cell Borders
Select all the cells to which you want to add a border, click Format → Cells, select the border tab (), then select the required line style in the style, and specify different colors in the colors, then click the outer border button, and finally click OK. To set the style of the selected cell with rotated text, click Format → Cell, select the Border tab, and then use the preset "Outer Border" and "Inner Border" buttons. The border is applied to the border of the cell, and it will rotate by the same angle as the text.
How to cancel hyperlinks in excel
Here are two ways to cancel hyperlinks conveniently in Excel.
1. If you are typing a URL or email address, please press Enter after typing, and what you just typed will turn blue. At this point, click the smart tag and choose the Unlink command.
2. If a hyperlink already exists in the worksheet, right-click the cell and select Unlink from the shortcut menu.
Import data from web pages into Excel tables.
The table information on the web page can be copied directly from the browser to Excel with excellent results. You can select information and copy it, then paste it into Excel, or you can select information and drag it into Excel. Using this "drag-and-drop" method to transmit and process any network-based table data will be very simple and very fast. In Excel2002, you can open an Html file just like an Excel worksheet and get the same function, format and editing status.
Excel custom menu command
You can customize the options menu according to your own needs. Click Tools → Customize to open the Commands tab (), and then select a menu category to add or delete in the Categories window on the left. If you want to add a menu command, just select it in the Command box on the right and drag it to the corresponding menu item. After the menu opens automatically and a black line appears, insert it into the position indicated by the black line and click the left mouse button in the blank. If it is a delete menu command, just open the menu and select the command to be deleted, press the left mouse button and drag it to the "Command" box in the figure. When the schematic diagram is open, you can also open the menu, right-click and choose the Delete command.
Skills of automatically filtering the top 10 maximum or minimum values in excel
Sometimes, you may want to use automatic filtering for numeric fields to display the top n maxima or minima in the data list. The solution is to use "top 10" for automatic filtering. When you select the "Top 10" option in the drop-down list of the automatic filter value field, the "Top 10" dialog box will appear. The so-called "Qian 10" is a general term and is not limited to Qian 10. For example, you can select the largest or smallest number and define any number as needed.
Further reading
1. What can Excel do?
Excel, as an important part of Microsoft Office family, can do many things and has very powerful functions. Its basic function is to record, calculate and analyze data. In practical application, it is small enough to be used as a general calculator, or to record personal income and expenditure, calculate loans or savings, etc. Large enough to carry out professional and scientific statistical operations, through the calculation and analysis of a large number of data, it provides effective reference for the formulation of the company's financial policies.
In practical industrial applications, teachers, office secretaries, government audit departments, statistical departments and general staff will use Excel to a greater or lesser extent. A large number of practical application experiences show that if we can skillfully use Excel, our study and work efficiency will be greatly improved. When it is used in enterprise management, it will directly improve the economic benefits. In other words, it is worthwhile to spend some time learning Excel.
Second, the basic concepts
Before entering the specific operation study, there are several basic concepts, and understanding these concepts is the basis for further study.
(1) file
In technical terms, a file is an information entity stored on a disk. With computers, we can say that we are dealing with documents almost every day. For beginners, to understand that there are different types of files, they can be distinguished by suffixes. Take Microsoft Office series as an example. The suffix of document file created by Word is DOC, workbook file created by Excel is XLS, and presentation file created by PowerPoint is PPT.
However, opening this type of file requires different applications. Some beginners copy a workbook file created by Excel from another computer to their own computer, but they can't open it anyway. Later, they learned that the reason was that Excel application was not installed on their computers.
Therefore, in order to carry out the following study smoothly, please install Excel2003 on your computer now. Although other versions of Excel can be imitated, since this series of courses are based on Excel2003, readers are advised to use Excel2003 as much as possible to learn.
(2) Workbooks and worksheets
Learning and using Excel must deal with two concepts, namely "workbook" and "worksheet". As mentioned earlier, a workbook is a file created by Excel, and a worksheet is an integral part of a workbook. It can be understood vividly that a workbook is a notebook and a worksheet is every page in this notebook. Operation and use of Excel, most of the work is done in the worksheet.
Third, the working environment and basic operation of Excel
(a) Start Excel
Before starting Excel2003, please make sure that the software has been installed on your computer.
We can start Excel in three ways.
1, through the start menu
Operating Steps: Click the "Start → All Programs → Microsoft Office → Microsoft Office Excel2003" command on the desktop in turn, and the splash screen of Excel 2003 will appear for a while, and then you can start Excel 2003.
2, through the desktop shortcut
Operating Steps: If a shortcut to Excel2003 has been established on the desktop, double-click the shortcut icon to start it.
3, through the Excel workbook file
Operating Steps: If the workbook file created by Excel is stored in the computer, you can double-click the file to start Excel.
(B) understand the working environment of Excel
After starting Excel2003, we will see the working window of Excel. If you are a complete novice, the working environment may feel a bit complicated. If you have experience in using applications such as Word in the Office family, you should feel a little deja vu. Because their menus, toolbars, task panes and editing windows have roughly the same layout.
Let's introduce the working environment. You need to know the names of various parts, such as menus, toolbars, task panes, etc. Because we will use these names in the following operations.
(1) The name of the title bar program and the name of the current workbook file are displayed here.
(2) The menu bar can send commands to Excel by selecting the corresponding menu commands. Excel classifies similar commands into the same menu item, which is convenient for users to remember, and the menu can be customized according to their own habits.
(3) The toolbar can send corresponding commands to Excel by clicking the corresponding buttons. The toolbar can be customized according to your own habits.
(4) Formula Bar After entering data or formula in the cell, the input content will be displayed here. It is quite convenient to edit input data or formulas here.
(5) 256 letters from A to IV in the column title. Each letter represents one of the 256 columns in the worksheet, starting from the English alphabetical order A to Z, then AA, AB, AC, and so on to AZ. AZ is followed by BA, BB, BC, and so on to BZ, and the last column is IV. Click the column header to select the entire column.
(6) Active Cell Indicator This indicator with a black border represents the currently active cell. The so-called activity is the currently selected cell.
(7) The number of row titles from 1 to 65536, with each number representing one row of the worksheet. Click the row header to select the entire row of cells. At the same time, because there are 256 columns, the total number of cells in Excel worksheet is 256× 65536 =16777216. We can identify a cell by using column headings and row headings, such as A4 for column A, the cell in row 4, and so on.
(8) Task Pane This is a new function that appeared in the 2002 version, which is convenient for quickly selecting related tasks, such as creating a new workbook and using the clipboard.
(9) Vertical scroll bar Use this scroll bar to scroll the worksheet vertically.
(10) Horizontal scroll bar Use this scroll bar to scroll the worksheet horizontally.
(1 1) Sheet Labels Each label represents a different sheet in the workbook, and the name of each sheet is displayed on the label. Click the tab to switch to the appropriate worksheet. A workbook can contain any number of worksheets, but for the convenience of management, it is best not to have too many worksheets.
(c) Using Excel menus and toolbars
If you want to skillfully use Excel to complete your own tasks, you must master menus and toolbars.
If we want Excel to do something, we must give it a command. There are usually four ways to issue commands: using menus, using shortcut keys, using shortcut menus and using toolbars.
The following is explained by the operation of specific tasks.
Task: Enter the character "Chinese" in cell A 1 and copy the contents into cell A5.
1 Use the menu
Operating steps:
(1) Select cell A 1 and enter "Chinese" in it. You can see that the insertion point cursor in the cell is still blinking. Excel waits for us to enter other contents. Because the input is confirmed, you can see that the active cell indicator jumps to cell B 1 by pressing the Enter key of the keyboard.
(2) Now click the cell A 1 with the mouse, and you can see that the active cell indicator returns to the cell A 1. Click "Edit" in the menu and select "Copy" in the expanded menu options. You can see that a black and white scroll bar appears around the cell A 1, indicating that the copy command has been executed.
(3) Then select A5 cell, click Edit in the menu, and select Paste in the menu option. You can see that the contents of cell A 1 are copied to cell A5, and press Enter on the keyboard to complete the operation.
Description: In step 1, why did you press Enter after entering "Chinese" and then reselect cell A 1? This is because when "Chinese" is entered, the insertion point cursor flashes in the cell. At this time, if the copy command is executed directly through the menu, the command will be aimed at the insertion point cursor in the cell. Because the cursor is not selected, it is equivalent to not copying anything. And press Enter to re-select the cell A 1, and you can see that there is no point cursor inserted in the cell A 1, so at this time, the copy command is executed through the menu, which is to copy the content "Chinese" in the cell A 1.
A notable difference is that clicking on cell A 1 activates it, making it the active cell, and you can directly enter data or formulas and execute commands such as copying and pasting. Double-click cell A 1 to edit, and you can modify data or formulas internally. Other cells and so on.
2 Use shortcut keys
If you find it troublesome to expand the menu step by step, you can use shortcut keys to operate. Most commonly used commands have shortcut keys. For example, the shortcut key for copying is "Ctrl+C", and Excel places the shortcut keys of common commands on the right side of the corresponding commands in the menu list.
Operating steps:
(1) Delete "Chinese" in cell A5 first, then click on cell A 1 with the mouse and press the keyboard combination key "Ctrl+C". As before, a black and white scroll bar appears around A 1 cell, indicating that the copy command has been executed.
(2) Select A5 cell, press the keyboard combination key "Ctrl+V", you can see that "Chinese" has been pasted in A5 cell, and press ENTER to complete the operation.
3 Use the shortcut menu
Operating steps:
(1) Delete "Chinese" in cell A5 first, then select cell A 1, click the right mouse button, and select "Copy" from the pop-up menu option.
(2) Then select A5 cell, click the right mouse button, select Paste in the pop-up menu, and press enter to complete the operation.
4 Use the toolbar
Operating steps:
(1) Delete "Chinese" in cell A5 first, then select cell A 1 and click the "Copy" icon in the toolbar.
(2) Select cell A5, click the "Paste" icon in the toolbar, and press Enter on the keyboard to complete the operation.
Through a simple task, this paper introduces the common methods of issuing commands to Excel. Don't underestimate the learning of these basic skills, and you will rely on them to complete more complicated tasks in the future.
(d) understanding the dialog box
If you are careful enough, you will find that some menu options have ellipsis (three small black dots) after the command. When you click this command, a dialog box will be displayed.
The purpose of the dialog box is that Excel can get more information through it in order to complete more complex commands. For example, we choose the "File → Page Setup" command to display the "Page Setup" dialog box, where we can set parameters such as page orientation, scale and page size. Only after Excel accepts this information through this dialog box can we execute our commands accurately. It is quite convenient to operate through the dialog box.
The details of using the dialog box will be introduced in detail in the following specific tasks. It is a very good method to master the operation method of dialog box in practice.
(5) Create a new workbook
To use Excel, you must first create a workbook. When Excel is started, it will automatically create a workbook named Book 1. By default, it contains three worksheets named Sheet 1, Sheet2 and Sheet3. If we want to start a new project, we can create a new workbook in the following three ways.
Method 1: Use the menu.
Operation method: Select the menu "File → New" command, and Excel will open the "New Workbook" task pane on the right side of the window, and click the corresponding option.
Method 2: Use the toolbar.
How to operate: Click the "New" button on the common toolbar to create a new workbook.
Method 3: Use shortcut keys.
Operation method: press the keyboard combination key "Ctrl+N" to create a new workbook.
(6) Open the existing workbook.
In practical work, we will always open the existing workbook and modify and add it, so it is necessary to learn to open the existing workbook.
There are five ways to open an existing workbook.
Method 1: Use the task pane.
How to do it: In the "Open" area of the "Start Work" task pane panel, click the workbook to be opened.
Method 2: Use the file menu list.
How to operate: Click the "File" menu and select the workbook to open in the following list.
Note: The above two methods are suitable for opening recently used workbooks.
Method 3: Use the file menu to open the command.
Operation method: Click the menu "File → Open" command to pop up the "Open" dialog box, through which you can locate the storage location of the workbook file, select the workbook file to open, and click the "Open" button.
Method 4: Use toolbar buttons.
Operation method: Click the "Open" button on the common toolbar to pop up the "Open" dialog box. Other operations are the same as method 3.
Method 5: Use shortcut keys.
Operation method: Press the keyboard combination key "Ctrl+O" to pop up the "Open" dialog box. Other operations are the same as method 3.
(7) Save the workbook.
From the beginning of study, we should pay attention to the importance of keeping exercise books. Whether it is human negligence or unexpected circumstances, the fruits of hard work may be wasted.
The operation of saving the workbook is simple, and there are three methods.
Method 1: Use the menu "File → Save" command.
Method 2: Click the Save button on the general toolbar.
Method 3: Button and keyboard key combination "Ctrl+S".
If it is the first time to save, all three methods will open the Save As dialog box. First, select the location where you want to save the file, then enter the file name in the file name column and click the Save button.
(8) Close the workbook and exit Excel.
When all the work is finished, we should close the workbook to free up memory.
There are two ways to close a workbook:
Method 1: Use the menu "File → Close" command.
Method 2: Click the Close Window button on the title bar of the workbook.
Note: The above method only closes the workbook without exiting the Excel program. If you need a new project, you can create a new workbook to do new work as described above.
There are two ways to quit Excel programs.
Method 1: Select the menu "File → Exit" command.
Method 2: Press the keyboard combination key "Alt+F4".
;