Excel table making skills?

1. Quickly select all worksheets.

Right-click the worksheet tab below the work window and select Select All Worksheets from the pop-up menu ().

2. Quickly start Excel

If you want to run Excel automatically in the startup system, you can do this: 1. Double-click the "My Computer" icon to enter the Windows directory and open the "Start Menu \ Programs \ Startup" folder in turn; 2. Open the folder where Excel is located, and drag the Excel icon to the "Start" folder with the mouse. At this time, the shortcut of Excel will be copied to the "Startup" folder, so you can quickly start Excel the next time you start Windows. If you start the Windows system, you can quickly start Excel in the following ways: Method 1: Click any Excel workbook in the Start → Document command. Method 2: Drag the Excel application from "My Computer" to the desktop with the mouse, and then select "Create Shortcut in Current Location" from the shortcut menu. When starting later, just double-click the shortcut.

3. Quickly delete the data in the selected area.

If you drag the fill handle of the selected cell area up or left (reversely) with the right mouse button, and release the right mouse button without dragging it out of the selected area, some or all data of the selected area will be deleted (that is, the cell area that becomes gray during dragging will be deleted after releasing the right mouse button).

Step 4 Rename the cell

Excel gives each cell a default name, and its naming rule is to add horizontal labels to column labels. For example, D3 represents cells in the fourth column and the third row. If you want to rename a cell, you can use the following two methods: 1. As long as you click a cell with the mouse, you will see its current name in the upper left corner of the table, then use the mouse to select the name and enter a new name. 2. Select the cell to be named, click Insert → Name → Define to display the Define Name dialog box, enter a name in the Name in Current Workbook box, and click OK (). Note: When naming a cell, it should be noted that the first character of the name must be a letter or a Chinese character. It can contain up to 255 characters, including uppercase and lowercase characters, but the name cannot contain spaces or be the same as the cell reference.

5. Select the entire cell range in Excel.

In Excel, if you want to quickly select the entire cell range you are working on, press "Ctrl+Shift+ *". Note: This command will select the entire column and column headings instead of the blank cells around the list-you will get the cells you need. This method is different from the Select All command, which selects all the cells in the worksheet, including the cells you don't intend to use.

6. Quickly move/copy cells

First select a cell, then move the mouse pointer to the cell border, press the left mouse button and drag it to a new position, and then release the button to move. To copy a cell, just press Ctrl before releasing the mouse.

7. Quickly modify the unit format order.

When you drag and drop one or more selected cells to a new location, you can quickly change the order of cell contents by holding down the Shift key. The method is: select a cell, hold down the Shift key, move the mouse pointer to the edge of the cell until a drag-and-drop pointer arrow appears, and then drag and drop. When dragging up and down, the mouse will become a horizontal I-shaped symbol at the cell boundary, and it will become a vertical I-shaped symbol when dragging left and right. After releasing the mouse button to complete the operation, the order between cells will change.

8. Clear the cell contents completely

Select the cell first, and then press the Delete key. At this point, only the contents of the cell are deleted, and its format and comments remain. To completely clear cells, you can use the following methods: Select the cell or range of cells to clear, and click Edit → Clear to display the Clear menu (). You can choose to delete any one of the format, content or comments.

9. Select a cell

Select a cell, point the mouse at it and click the left mouse button; Select a cell area, select the cell in the upper left corner, and then hold down the left mouse button and drag it to the right until the left mouse button is released at the required position; To select two or more nonadjacent cell ranges, hold down the Ctrl key after selecting a cell range, and then select another range. To select an entire row or column, simply click the row number or column label, and the first cell in the row or column will become the active cell. If you click the button at the intersection of row number and column label in the upper left corner, you can select the whole worksheet.

10. Name the worksheet

In order to facilitate memory and search, Excel sheets1,sheet2 and sheet3 can be named as easy-to-remember names. There are two methods: 1. Select the worksheet to rename, and click Format → Worksheet → Rename. At this point, the name on the worksheet tab will be highlighted, and then enter a new table name on the tab. Double-click the name at the bottom of the current worksheet, such as "Sheet 1", and then enter a new name.

1 1. Open multiple workbooks at once.

The following methods can be used to quickly open multiple workbooks: Method 1, open workbooks (*. Xls), hold down the Shift key or Ctrl key, use the mouse to select multiple adjacent or non-adjacent workbooks, select them all, then right-click and select the open command, and the system will start Excel 2002 and open all the selected workbooks. Method 2: Copy multiple workbook files that need to be opened at one time to the folder C: \ Windows \ Application Data \ Microsoft \ Excel \ XL Start. When Excel 2002 is started in the future, all the above workbooks will be opened at the same time. Method 3 Start Excel 2002, click Tools → Options to open the options dialog box, click the General tab, enter the full path of a folder (such as d:\Excel) in the box after "Open all files in this item at startup", and click OK to exit. Then copy the workbook that needs to be opened at the same time to the folder above. When you start Excel 2002 in the future, all files in the above folder (including documents in non-Excel format) will be opened. Method 4 In Excel 2002, click File → Open command, hold down Shift key or Ctrl key, select multiple adjacent or non-adjacent workbooks in the file list of the pop-up dialog box, and then press Open button to open multiple workbooks at a time. Method 5: Open all the workbooks that need to be opened at the same time by the above method, and then click the "File → Save Workspace" command to open the "Save Workspace" dialog box and save by name. In the future, as long as you open the workspace file with Excel 2002, all the workbooks contained in the workspace will be opened at the same time.

12. Quickly switch workbooks

For a few workbook switches, just click the window where the workbook is located. To switch between multiple workbooks under multiple windows, you can use the Window menu. The name of the open workbook is listed at the bottom of the window menu. To switch directly to a workbook, you can choose its name from the Window menu. The Window menu can list up to 9 workbooks. If there are more than 9 workbooks, the Window menu contains a command called Other Windows. If you choose this command, a dialog box will appear, listing all open workbooks in alphabetical order. Just click on the desired name.

13. Selected hyperlink text (winning work of Microsoft Office Skills Competition)

If you need to select hyperlink text in Excel without jumping to the target, you can click and hold the cell while pointing to it.

14. Quick search

Before performing a search operation, you can determine the search area within a range of cells, the entire worksheet (you can select any cell in this worksheet), or multiple worksheets in the workbook. When entering search content, you can use a question mark (? ) and asterisk (*) as wildcards to facilitate the search operation. Question mark (? ) represents a character, and an asterisk (*) represents one or more characters. The question to note is, since the question mark (? ) and asterisk (*) as wildcards, then how to find the question mark (? ) and asterisk (*)? Just add a tilde (~) before these two characters.

15. Modify the default file saving path.

Start Excel 2002, click Tools → Options to open the Options dialog box, and in the General tab, change the contents of the default file location box to the full path of the folder to be located (). When you create a new Excel workbook and save it in the future, the system will open the Save As dialog box and directly navigate to the folder you specified.

16. Specify an open folder.

We can click Start → Run, enter the regedit command, open the Registry Editor, and expand HKEY _ Current _ User \ Software \ Microsoft \ Office \10.0 \ common \ OpenFind \ Places \ UserDefinedPlaces. Create a new primary key named "mydoc" below, and then create two new "string values" in this primary key, named "Name" and "Path", with values of "My File" (you can name it at will) and "d:\mypath" (the full path of the location folder). When you open it in Excel 2002, the "My File" item will be displayed. Click this item to enter the "d:\mypath" folder.

17. Quickly switch between multiple Excel workbooks.

Press Ctrl+Tab to switch between open workbooks.

18. Get help quickly

For the toolbar or screen area, press the key combination "Shift+F 1" and the mouse will turn into an arrow with a question mark. Click the toolbar button or screen area with the mouse, and a help window will pop up, showing the detailed help information of the element.

19. Shortcuts to create help files

Excel help file is a compiled HTML help file Xlmain 10.chm, which is stored in the installation directory \Office 10\2052. Click and drag the file to the Windows Quick Start toolbar. After that, whether Excel is running or not, you don't need to call the Office Assistant. Clicking this icon on the Windows Quick Start toolbar will bring up Excel Help.

20. Double-click the edge of a cell to move the selected cell.

There is a quick way to move the selected cell in the worksheet: put the mouse pointer on one side of the selected cell, and pay attention to the side related to the moving direction, that is, to move down, put the mouse pointer at the bottom of the cell; If you want to move to the right, put the mouse pointer on the right side of the cell; And so on. At this point, the mouse pointer changes to the shape of a white arrow. Double-click the edge of the selected cell and the mouse pointer will move to a specific cell in the selected direction. If the adjacent cells in this direction are blank cells, move them to the farthest blank cell in turn; If the adjacent cell in this direction is a non-blank cell, it will be continuously moved to the farthest non-blank cell.

2 1. Double-click the edge of a cell to select a range of cells.

Similar to the previous technology, if the Shift key is pressed while double-clicking the cell border, the area from the cell to the farthest blank cell or non-blank cell is selected according to whether the adjacent cell in the direction is blank or non-blank.

22. Quickly select discontinuous cells.

Press the key combination Shift+F8 to activate the Add Selection mode. At this point, the word "Add" will be displayed in the status bar at the bottom of the workbook, and then you can select it by clicking discrete cells or cell ranges respectively without holding down the Ctrl key.

23. Select cells according to conditions.

Click Edit → Locate, and click Location Criteria in the pop-up Location dialog box. According to the type of region to be selected, select the cell type () in the Positioning Criteria dialog box, such as Constant and Formula. At this time, you can also check the items such as numbers and words, and click OK.

24. Copy or move cells

To copy or move cells from one location to another, you can select the source cell, click Cut or Copy in the edit menu, select the target cell, and then select Paste in the edit menu. The source cell will be moved or copied to the target cell.

25. Delete the cells in Excel completely.

Do you want to completely delete cells (including their formatting and comments) from the worksheet? Just select the cell you want to delete and press "Ctrl+- (minus sign)". Select the way to move cells in the pop-up dialog box, and the surrounding cells will move to fill the space left after deletion.

26. Quickly delete blank lines

Sometimes, in order to delete the blank lines in the Excel workbook, it may be inconvenient to find out the blank lines one by one and then delete them. You can do this by using the autofilter function by inserting a new row (all blank) in the table, then selecting all rows in the table, clicking Command Data → Filter → Autofilter, and selecting blank from the drop-down list at the top of each column. After selecting all data, click Edit → Delete Line and click OK to delete all blank lines. Note: An empty row is inserted to avoid deleting the first row of data.

27. Enter key paste function

The Enter key also has a paste function. When the flashing copy border mark (dotted box) appears in the copy area, press Enter to realize the paste function. Note: When there is a flashing copy border mark, do not use the Enter key to switch between cells in the selected area. At this point, you should use the Tab key or arrow keys to switch cells.

28. Quickly close multiple files

Hold down the Shift key, open the File menu, and click the Close All command to quickly close all currently opened files.

29. Select multiple worksheets.

If you select a group of adjacent worksheets, you can first select the first table, hold down the Shift key, and then click the label of the last table; If you select nonadjacent worksheets, hold down the Ctrl key and click the label of each table you want to select. To select all worksheets in the workbook, choose Select All Worksheets from the table label shortcut menu.

30. Quickly edit multiple worksheets.

If you want to operate multiple identical worksheets at one time and omit the subsequent copy and paste operations, you can adopt the following methods. Holding down the Shift key or Ctrl key with mouse operation, and selecting multiple adjacent or non-adjacent worksheet tabs at the bottom of the workbook can realize various batch processing. Generally speaking, the operation is as follows: 1. Page setup, you can quickly set the same page for the selected worksheet. 2. Enter the same data, you can quickly enter the same data in multiple worksheets. 3. Quick operation, that is, performing a series of the same operations in multiple worksheets, such as setting font size, font and color, merging and canceling cells, etc. 4. Enter the formula, quickly enter the same formula, and calculate the formula juice.

3 1. Move and copy worksheets

You can not only move and copy worksheets in one workbook, but also move or copy tables in other workbooks. To move a worksheet, simply click the tab of the table you want to move and drag it to a new location. To copy a worksheet, simply select the worksheet, press Ctrl, and then drag the table to a new location. Of course, several tables can be moved and copied at the same time in this way. After moving, tables that were not adjacent before can become adjacent tables.

32. Delete the worksheet

Click Edit → Delete Worksheet, and then click OK, and the worksheet will be permanently deleted from the workbook. Note: The "Delete Worksheet" command cannot be restored, nor can the deleted worksheets be restored.

33. Quickly select cells

When selecting cells (rows and columns), hold down the Shift key and select consecutive cells (rows and columns). When selecting cells (rows and columns), hold down the Ctrl key and select discontinuous cells (rows and columns).

34. Quickly select Excel area (winning works of Microsoft Office Skills Competition)

In Excel, if you want to quickly select a certain area in the workbook, just click the upper left corner cell of the selected area, hold down the Shift key at the same time, and then click the lower right corner cell of the selected area. In addition: hold down the Ctrl key and use the mouse to select multiple nonadjacent areas at will. In fact, the selected area has nothing to do with the order. Just select a corner cell in the rectangular area first, and then click the corner cell while holding down the Shift key.

35. Back up the workbook

Click File → Save to open the Save As dialog box, press the drop-down button next to the tool in the upper right corner, and then click General Options. In the pop-up dialog box, select "Make backup" () option, and click OK to save. After modifying the workbook in the future and saving it, the system will automatically generate a backup workbook, which can be directly opened for use.

36. Automatically open the workbook

(Winner of Microsoft Office Skills Competition) As long as we put a shortcut to the workbook that needs to be opened automatically in the folder C: \ Prograrnfiles \ Microsoft Office \ Office10 \ xlstart, Excel will automatically open the corresponding workbook every time it starts.

37. Quickly browse long workbooks

When browsing a long table, press Ctrl+Home to return to the upper left corner of the current worksheet (that is, cell A 1), and press Ctrl+End to jump to the lower right corner of the worksheet containing data. In addition, if you select something, you can move it clockwise on the four corner cells of the selected content by repeatedly pressing "Ctrl+". (period) ".

38. Quickly delete blank lines in the worksheet.

If the user wants to delete the blank lines in the Excel worksheet, the general method is to find out all the blank lines and delete them line by line, but it is inconvenient to do so. Here's a quick way to delete blank rows in a worksheet: first, open the worksheet to delete blank rows, click the "Insert → Column" command in the open worksheet, so as to insert a new X column, and fill in the integers in the X column in turn, and then sort the rows in the table according to any other column, so that all the blank rows are concentrated at the bottom of the table. Delete the data of X column in all blank rows, reorder by X column, and then delete X column.

39. Draw a diagonal title

In general, when making a header in Excel, the first row of the table will be used as the header, and then the text will be entered. However, such headers are relatively simple, not to mention diagonal headers. Can you realize diagonal header in Excel? Here's how to do it: Since the cell as the diagonal header is larger than other cells, adjust the size of the first cell in the table first. Then click the selected cell, click Format → Cell to open the cell format window, select the alignment tab, select vertical alignment at the top, select the word wrap check box under the text control (), then select the border tab, and press the outer border button to connect the header outline. Now, double-click the first cell to enter the editing state, and enter words, such as "Project" and "Month". Then, put the cursor in front of the word "item" and press the space bar continuously to move these four words backward (because we have set the text control to "word wrap" in the cell properties, when the word "month" exceeds the cell, it will automatically switch to the next line). Now click anywhere in the table to exit the first cell, and a beautiful diagonal title is finished.

40. Draw diagonal cells

Using the two diagonal buttons on the Borders tab of Excel, you can draw a left diagonal and a right diagonal in a cell. If you want to draw multiple diagonals in a cell, you must use the drawing tool. The method is: open the drawing tool in Excel, click the line button, and drag the cursor after it becomes a small cross, so as to draw as many diagonals as needed. As long as the drawing is correct, the diagonal can be automatically extended or shortened with the cell. As for the other table lines of diagonal cells, add them as described above. Of course, it is troublesome to enter data in diagonal cells. The usual practice is to make the data wrap in the cell (press "Alt+ ENTER"), and then add spaces to put the data in the appropriate position.

4 1. Select the same cell at a time

Sometimes, in order to test a formula, it is necessary to repeatedly enter multiple test values in a cell. However, every time you enter a value and press Enter to view the results, the current cell will be moved to the next cell by default, and the original cell must be re-selected with the mouse or the up arrow, which is extremely inconvenient. If you press Ctrl+Enter, the problem will be solved immediately. You can view the results, and the current cell is still the active cell.

42. Find the workbook quickly

You can search using any word in the worksheet. The method is: 1 Click the "Open" button in the toolbar, and in the "Open" dialog box, enter the full name or partial name of the file, which can be replaced by wildcards; 2. In the text attribute edit box, enter the text to be searched; 3. Click "Start Search".

43. It is forbidden to copy the data in hidden rows or columns.

If you copy a data range that contains hidden columns (or rows) and paste it into a new worksheet, Excel will paste the hidden columns. To avoid this, you can select the data range to copy, then choose Edit → Locate, click the Location Criteria button to open the Location Criteria dialog box, select the Visible Cells option, and then copy and paste the selected range to obtain the desired results.

44. Make personality cells

If your table needs special cells such as diamonds and triangles, you can use the following methods: first, enter data in the cells, then open the drawing toolbar and find the required graphics in the submenu of AutoShape → Basic Shape. After clicking, the cursor turns into a small cross, and you can draw the cell with the required shape by dragging it from the upper left corner to the lower right corner. If the contents of a cell are overwritten, you can right-click the cell you just drew and choose Format AutoShape from the shortcut menu. Open the AutoShape Format and Colors and Lines tabs, and select No Fill Color from the Color → Fill drop-down list. After confirmation, the original contents in the cell will be displayed. If you select the option "Size and position change with cells" in the Properties tab, it will automatically change size with cells.

First, data input and editing skills

45. Enter multiple values in a cell.

Sometimes, we need to enter multiple values in a cell continuously to see the effect of other cells that reference the cell. But every time you enter a value and press Enter, the active cell moves down by default, which is very inconvenient. In fact, you can use the following methods: click the mouse to select a cell, then hold down the Ctrl key and click the mouse again to select this cell. At this point, a realistic wireframe will appear around the cell, then enter the data, and then press Enter to hold it still.

46. Increase the number of pages in the workbook.

Click Insert → Worksheet to view the worksheet with the new name at the bottom. A workbook can have up to 255 worksheets.

47. Strange F4 key

There is a shortcut key in Excel that plays an extremely prominent role, that is F4. As a "repeat" key, F4 can repeat the previous operations, which is effective in many cases, such as adding or deleting a row in the worksheet, then moving the insertion point and pressing F4 to add or delete another row, without using the menu at all.

48. Import formatted text into Excel

1. Enter formatted text in Windows Notepad, and each data item will be separated by spaces. Of course, you can also use commas, semicolons and Tab keys as separators. After the input is completed, save the text file and exit; 2. Open the saved text file in Excel and the "Text Import Wizard-1 in Step 3" dialog box will appear. Select "Delimiter" and click "Next"; 3. Select the text data item separator in the Text Guide Wizard-Step 2 of 3 dialog box. Excel provides Tab keys, semicolons, commas and spaces for you to choose from. Please note that several separator options here should be single. You can see the effect of vertical bar separation in Preview Column Effect. Click "Next"; 4. In the "Text Guide-Step 3, Step 3" dialog box, you can set the type of data, which generally does not need to be changed, and Excel will automatically set it to "normal" format. The General data format converts numeric values into numeric formats, date values into date formats, and the rest of the data into text formats. Just click the "Finish" button.

49. Fast line feed

When we use Excel to make tables, we often encounter the situation that we need to enter one or several lines of text in a cell. If we enter a line and press Enter, it will move to the next cell instead of wrapping. There is a simple and practical operation method to realize line break: after entering the first line in the selected cell, press Alt+ ENTER at the line break to enter the second line, then press Alt+ ENTER to enter the third line, and so on.

50. skillfully convert text into numbers

It is found that some data imported into Excel through text files or other financial software actually exist in the form of text (numbers are aligned to the right by default and text is aligned to the left), even if the cell format is reformatted into numbers, it will not help. There is a way to quickly convert these files back to numbers: fill in the blank cell with the number 1, then select this cell, execute the copy command, then select the range to be converted, and select Multiply in Paste Special, and you will find that they all become numbers.