Must Collect Series: Excel Tips and Techniques Collection – A Complete Collection of  39 Tips and Techniques

Must Collect Series: Excel Tips and Techniques Collection – A Complete Collection of 39 Tips and Techniques

Excel Tips and Techniques Collection – A Complete Collection of 39 Tips and Techniques, Safe to Use After Collecting~Includes Table of Contents and Main Text

Tips Catalog

1、 Basic methods

  1. Quickly select all worksheets
  2. Quickly start Excel
  3. Quickly delete selected area data
  4. Renaming cells
  5. Select the entire range of cells in Excel
  6. Quickly move/copy cells
  7. Quickly modify the unit format order
  8. Thoroughly clear cell contents
  9. Select cells
  10. Name the worksheet
  11. Open multiple workbooks at once
  12. Quickly switch workbooks
  13. Select hyperlink text (Microsoft OFFICE Skills Competition winning work)
  14. Quick search
  15. Modify the default file save path
  16. Specify the folder to open
  17. Quickly switch between multiple Excel workbooks
  18. Quick access to help
  19. Shortcut to Create Help Files
  20. Double click on an edge of a cell to move the selected cell
  21. Double click on an edge of a cell to select a range of cells
  22. Quickly select discontinuous cells
  23. Select cells based on conditions
  24. Copy or move cells
  25. Completely delete cells in Excel
  26. Quickly delete empty lines
  27. Paste function of enter key
  28. Quickly close multiple files
  29. Select multiple worksheets
  30. Quick editing of multiple worksheets
  31. Moving and copying worksheets
  32. Deleting a worksheet
  33. Quick cell selection
  34. Quickly select the EXCEL region (winning works in the Microsoft OFFICE Skills Competition)
  35. Backup the workpiece book
  36. Automatically open workbook
  37. Quick browsing of long workbooks
  38. Quickly delete empty rows in the worksheet
  39. Draw a diagonal header
  40. Draw diagonal cells
  41. Select the same cell each time
  42. Quick Find Workbook
  43. Prohibit copying data from hidden rows or columns
  44. Create personalized cells

2、 Data input and editing skills

  1. Enter multiple values within a cell
  2. Increase the number of pages in the workbook
  3. Unique F4 key
  4. Import formatted text into Excel
  5. Quick line breaks
  6. Cleverly turning text into numbers
  7. Enter a value of 0 in the cell
  8. Format numbers as text
  9. Quickly switch between cells (Microsoft OFFICE Skills Competition winning work)
  10. Enter multiple test values consecutively within the same cell
  11. Enter numbers, text, date, or time1812 Quick input of Euro symbol
  12. Convert cell ranges from formulas to numerical values
  13. Quickly input ordered text
  14. Enter regular numbers
  15. Cleverly input commonly used data
  16. Quickly input special symbols
  17. Quickly enter the same text
  18. Quickly add units to numbers
  19. Cleverly input numbers with a large number of digits
  20. Convert WPS/WORD tables to Excel worksheets
  21. Cancel cell links
  22. Quick Pinyin Input
  23. Insert “√”
  24. Align with decimal points
  25. Define different input methods for different types of cells
  26. Quickly insert a WORD table in Excel
  27. Set Cell Font22
  28. Display multiple lines of text in a single cell23
  29. Import data from web pages into Excel spreadsheets
  30. Cancel hyperlink
  31. Edit cell content
  32. Set cell borders
  33. Set cell text alignment
  34. Enter Formula
  35. Use “scattered alignment” when entering person names (Microsoft OFFICE Skills Competition winning works)
  36. Hide all values in cells (Microsoft OFFICE Skills Competition winning work)
  37. Restore Hidden Columns
  38. Quickly hide/show the row and column of the selected cell (Microsoft OFFICE Skills Competition winning work)
  39. Completely hide cells
  40. Quickly input data using a dropdown list
  41. Quick input of custom phrases
  42. Set cell background color
  43. Quickly enter the same formula in multiple cells
  44. Enter the same content in multiple cells simultaneously
  45. Quickly enter date and time
  46. Safely insert copied cells between existing cells
  47. Do not discard the display of column headings in Excel
  48. View the values of sequence numbers equivalent to dates
  49. Quickly copy cell content
  50. Use custom sequence sorting (Microsoft OFFICE Skills Competition winning works)
  51. Quickly format Excel cells
  52. Fixed display of a certain column
  53. Quickly edit cells in Excel
  54. Use auto fill to quickly copy formulas and formats
  55. Add annotations to cells
  56. Automatic data input
  57. Quickly calculate a person’s age in Excel
  58. Quickly modify cell order
  59. Import data from web pages into Excel spreadsheets

3、 Graphics and chart editing skills

  1. Publish Excel generated graphics online
  2. Create chart connectors
  3. Convert Excel cells to image format and insert them into WORD
  4. Insert the WORD content into an Excel table in the form of an image
  5. Insert the content in WORD as an image link into an Excel table
  6. Process embedded charts in independent windows
  7. Show hidden data in the chart
  8. Add text boxes to the chart
  9. Establish a link between text and chart text boxes
  10. Add new data series to the chart
  11. Quickly modify the format of chart elements
  12. Create composite charts
  13. Use different coordinate axes for measuring different data series
  14. Set the chart you are satisfied with as a custom chart type
  15. Copy custom chart types
  16. Rotate the 3D chart
  17. Drag chart data points to change values in the worksheet
  18. Merge images into your chart
  19. Beautify the worksheet with graphics
  20. Merge text boxes with worksheet gridlines
  21. Quickly create default charts
  22. Quickly create embedded charts
  23. Change default chart type
  24. Quickly convert embedded charts to new worksheet charts
  25. Use the Chart toolbar to quickly set up charts
  26. Quickly select chart elements
  27. Create an Excel chart with one button
  28. Draw a straight line

4、 Editing skills for functions and formulas

  1. Cleverly using IF function to clear 0 in Excel worksheet
  2. Batch summation
  3. Sum the data of adjacent cells
  4. Sum data from non adjacent cells
  5. Use formulas to set weighted averages
  6. Automatic summation
  7. Use Notepad to edit formulas
  8. Prevent formula display in the edit bar
  9. Solve the quantity limit in SUM function parameters
  10. Switch between absolute and relative unit references
  11. Quickly view all worksheet formulas
  12. Implementation condition display 39

5、 Data analysis and management skills

  1. Manage Add ins
  2. Use super connections between worksheets
  3. Quickly link data online
  4. Cross table operation data
  5. View two columns of data that are far apart in Excel
  6. How to eliminate calculation errors after scaling (Microsoft OFFICE Skills Competition winning work)
  7. Use selective pasting commands to complete some special calculations
  8. WEB Query
  9. Perform quick calculations in Excel
  10. Automatically filter the top 10
  11. Performing operations on multiple cells simultaneously (Microsoft OFFICE Skills Competition winning work)
  12. Make Excel display error data prompts
  13. Use “hyperlink” to quickly jump to other articles

6、 Setting Techniques

  1. Customize menu commands
  2. Set menu divider
  3. Back up custom toolbars
  4. Share custom toolbars
  5. Use a single document interface to quickly switch workbooks
  6. Customize toolbar buttons

The detailed content is as follows:

1、 Basic methods

  1. Quickly select all worksheets

Right click on the worksheet tab below the work window, and select the “Select All Worksheets” command from the pop-up menu to ().

  1. Quickly start Excel

If you want to automatically run Excel when starting the system, you can do the following: 1. Double click the “My Computer” icon, enter the Windows directory, and then open the “Start Menu \ Programs \ Start” folder in sequence; 2. Open the folder where Excel is located, drag the Excel icon to the “Start” folder with the mouse, and the shortcut to Excel will be copied to the “Start” folder. The next time you start Windows, you can quickly start Excel. If the Windows system has started, you can quickly start Excel using the following method: Method 1: Click on any Excel workbook in the “Start → Documents” command. Method 2: Use the mouse to drag the Excel application from “My Computer” to the desktop, and then select “Create Shortcut in Current Location” from the shortcut menu. When starting in the future, simply double-click the shortcut.

  1. Quickly delete selected area data

If you use the right mouse button to drag the fill handle of a selected cell range up or left (reverse) without dragging it out of the selection and releasing the right mouse button, some or all of the data in the selected range will be deleted (that is, the cell range that becomes gray and blurry during the drag process will be deleted after releasing the right mouse button).

  1. Renaming cells

Excel gives each cell a default name, and its naming convention is to add a horizontal label to the column label. For example, D3 represents the cell in the fourth column and third row. If you want to rename a cell, you can use the following two methods: 1. Simply click on a cell with the mouse, and its current name will be visible in the top left corner of the table. Then, select the name with the mouse and enter a new name. 2. Select the cell you want to name, click the “Insert ->Name ->Define” command to display the “Define Name” dialog box. Enter the name in the “Name in Current Workbook” box and click the “OK” button to (). Note: When naming cells, it is important to note that the first character of the name must be a letter or Chinese character. It can contain up to 255 characters, including upper and lower case characters. However, the name cannot have spaces and cannot be the same as the cell reference.

  1. Select the entire range of cells in Excel. If you want to quickly select the entire range of cells being processed, press “Ctrl+Shift+*”. Note: This command will select the entire column and column headings, rather than the blank cells surrounding the list – you will get the desired cells. This technique is different from the select all command, which selects all cells in the worksheet, including those that you do not intend to use.
  2. Quickly move/copy cells

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

  1. Quickly modify the unit format order

While dragging and dropping one or more selected cells to a new position, holding down the Shift key can quickly modify the order of cell contents. The method is to select a cell, press the Shift key, move the mouse pointer to the edge of the cell until the drag and drop pointer arrow appears, and then perform the drag and drop operation. When dragging up and down, the mouse will turn into a horizontal “work” symbol at the boundary between cells, and when dragging left and right, it will turn into a vertical “work” symbol. After releasing the mouse button to complete the operation, the order between cells will change.

  1. Thoroughly clear cell contents

First select the cell, then press the Delete key. Only the cell content is deleted, and its formatting and annotations are still retained. To completely clear cells, you can use the following methods: select the cells or range of cells you want to clear, click the “Edit → Clear” command, and the “Clear” menu will be displayed (). Choose the “All” command, and of course, you can also choose to delete any one of “Format”, “Content”, or “Annotation”.

  1. Select cells

Select a cell, point the mouse to it, and click the left mouse button; Select a range of cells, select the cell in the top left corner, then hold down the left mouse button and drag it to the right until you release the left mouse button at the desired location; To select two or more non adjacent cell ranges, after selecting a cell range, hold down the Ctrl key and then select another range; To select an entire row or column, simply click on the row number or column label, and the first cell of the row or column will become the active cell; If you click the button at the intersection of the row number and column label in the upper left corner, you can select the entire worksheet.

  1. Name the worksheet

For ease of memory and search, you can name the sheets 1, 2, and 3 in Excel with names that are easy to remember. There are two methods: 1. Select the sheet you want to rename, click the “Format ->Sheet ->Rename” command, and the name on the sheet label will be highlighted. Then, enter a new table name on the label. 2. Double click on the name at the bottom of the current worksheet, such as “Sheet1”, and then enter a new name.

  1. Open multiple workbooks at once

The following method can be used to quickly open multiple workbooks: Method 1: Open the folder where the workbook (*. xls) is located, hold down the Shift or Ctrl keys, and use the mouse to select multiple workbooks that are adjacent or not adjacent to each other. Select them all, then right-click and select the “Open” command. The system will start Excel 2002 and open all the selected workbooks mentioned above. Method 2: Copy multiple workbook files that need to be opened at once to the C: \ Windows \ Application Data \ Microsoft \ Excel \ XLSTART folder. When starting Excel 2002 in the future, all the aforementioned workbooks will also be opened at the same time. Method 3: Start Excel 2002, click the “Tools ->Options” command, 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 workbooks that need to be opened simultaneously to the above folder. When starting Excel 2002, all files in the above folder (including non Excel formatted documents) will be opened. Method 4: In Excel 2002, click the “File → Open” command, hold down the Shift or Ctrl keys, select multiple adjacent or non adjacent workbooks in the pop-up dialog box file list, and then press the “Open” button to open multiple workbooks at once. Method 5: Using the above method, open all the multiple workbooks that need to be opened at the same time, and then click the “File → Save workspace” command to open the “Save workspace” dialog box and name it “Save”. In the future, as long as the workspace file is opened in Excel 2002, all workbooks included in the workspace will be opened simultaneously.

  1. Quickly switch workbooks

For a small number of workbook switches, click on the window where the workbook is located. To switch between multiple workbooks under multiple windows, you can use the Window menu. The bottom of the Window menu lists the names of open workbooks. 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, the “Window” menu contains a command called “Other Windows”. When this command is selected, a dialog box will appear that lists the names of all open workbooks in alphabetical order. Simply click on the desired name.

  1. Select hyperlink text (Microsoft Office Skills Competition winning work). If you need to select hyperlink text in Excel without jumping to the target, you can click and hold to select the cell when pointing to it.
  2. Quick search

Before performing a search operation, the search area can be determined to be within a certain range of cells, the entire worksheet (optionally any cell within this worksheet), or multiple worksheet ranges in the workbook. When entering search content, you can use question marks (?) and asterisks () as wildcard characters to facilitate search operations. The question mark (?) represents one character, and the asterisk () represents one or more characters. The important issue to note is, since the question mark (?) and asterisk () are used as wildcards, how can we find the question mark (?) and asterisk ()? Just add a tilde (~) before these two characters.

  1. Modify the default file save path

Start Excel 2002, click the “Tools ->Options” command, open the “Options” dialog box, and in the “General” tab, modify the content in the “Default File Location” box to the full path of the folder you need to locate. In the future, when creating a new Excel workbook and performing a “save” operation, the system will open the “save as” dialog box and directly navigate to the folder you specified.

  1. Specify the folder to open

We can specify the folder to open the file by clicking “Start → Run”, entering the regedit command, opening the “Registry Editor”, expanding HKEY-CURRENT User \ Software \ Microsoft \ Office \ 10.0 \ Common \ Open Find \ Places \ UserDefinedPlaces, creating a new primary key named “mydoc” below, and then creating two “string values” in the primary key named “Name” and “Path” with values “My Files” (can be named as desired) and “d: \ mypath” (locate the full path of the folder). Close the “Registry Editor” and restart the computer. In the future, when performing the “Open” operation in Excel 2002, a new “My Files” project will be added to the left side of the dialog box. Click on this project to enter the “d: \ mypath” folder.

  1. Quickly switch between multiple Excel workbooks by pressing “Ctrl+Tab” to switch between open workbooks.
  2. Quick access to help

For a toolbar or screen area, press the Shift+F1 key combination, 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 displaying detailed help information for that element.

  1. Shortcut to Create Help Files

The Excel help file is a compiled HTML help file Xlmain10.chm, stored in the installation directory \ Office10 \ 2052 directory. Click and drag this file onto the Windows Quick Launch toolbar. Afterwards, regardless of whether Excel is running or not, and without calling the Office Assistant, clicking on this icon on the Windows Quick Launch toolbar will bring up Excel Help.

  1. Double click on one side of a cell to move the selected cell. There is a shortcut to moving the selected cell within the worksheet: place the mouse pointer on one side of the selected cell, making sure to select the side related to the direction of movement. To move it down, place the mouse pointer at the bottom of the cell; If you want to move to the right, place the mouse pointer to the right of the cell; And so on. At this point, the mouse pointer changes to a white arrow shape. Double click to select an edge of the cell, and the mouse pointer will move along the selected direction to a specific cell. If adjacent cells in this direction are blank cells, they will be moved to the farthest consecutive blank cell; If the adjacent cells in this direction are non blank cells, they will be moved to the farthest consecutive non blank cell.
  2. Double clicking on an edge of a cell to select a range of cells is similar to the previous technique. If you double-click on the cell border and press the Shift key, select the range from this cell to the farthest blank or non blank cell based on whether the adjacent cells are blank or non blank cells in this direction.
  3. Quickly select discontinuous cells

Press the combination key “Shift+F8” to activate the “Add Selection” mode. At this time, the status bar below the workbook will display the word “Add”. In the future, click on discontinuous cells or cell ranges separately to select, without holding down the Ctrl key.

  1. Select cells based on conditions

Click the “Edit → Positioning” command, click the “Positioning Condition” button in the opened “Positioning” dialog box, and select the type of cell you want to select in the “Positioning Condition” dialog box, such as “Constant”, “Formula”, etc. At this time, you can also check items such as “Number” and “Text”. After clicking the “OK” button, all cells that meet the conditions will be selected.

  1. Copy or move cells

To copy or move a cell from one location to another, select the source cell, click the “Cut” or “Copy” command on the “Edit” menu, select the target cell, and select the “Paste” command from the “Edit” menu. The source cell will be moved or copied to the target cell.

  1. Completely delete cells in Excel

Do you want to completely remove a cell (including its formatting and comments) from the worksheet? As long as you select the cells that need to be deleted, then press “Ctrl+- (minus sign)” and select the way to move the cells in the pop-up dialog box, the surrounding cells will be moved to fill the space left after deletion.

  1. Quickly delete empty lines

Sometimes, in order to delete empty rows in an Excel workbook, you may find and delete them one by one, which is very inconvenient. You can use the automatic filtering function to achieve this. The method is to first insert a new row (completely empty) in the table, then select all rows in the table, click the “Data → Filter → Auto Filter” command, and at the top of each column, select “Blank” from the drop-down list. With all data selected, click “Edit ->Delete Rows” and then press “OK” to delete all empty rows. Note: Inserting an empty row is to avoid deleting the first row of data.

  1. Paste function of enter key

The Enter key also has a paste function. When there are flashing copy border markers (dashed boxes) in the copied area, pressing the Enter key can achieve the paste function. Note: Do not use the Enter key to switch between cells within the selected area when there are flashing copy border markers. In this case, you should use the Tab or arrow keys to switch between cells.

  1. Quickly close multiple files

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

  1. Select multiple worksheets

If selecting 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 selecting non adjacent worksheets, hold down the Ctrl key and click the label of each table to be selected in sequence; To select all worksheets in the workbook, you can choose the “Select All Worksheets” command from the table label shortcut menu.

  1. Quick editing of multiple worksheets

If you want to operate multiple identical worksheets at once and omit subsequent copy and paste operations, you can use the following methods. By holding down the Shift or Ctrl keys and using the mouse, multiple adjacent or non adjacent sheet labels can be selected at the bottom of the workbook to perform batch processing in multiple ways. In general, there are several operations to be carried out: 1. Page settings, which can quickly set the same page for the selected worksheet. 2. By inputting the same data, it is possible to quickly input the same data in multiple worksheets. 3. Quick operation, perform a series of identical operations in multiple worksheets, such as setting font size, font, color, merging and revoking cells, etc. 4. Enter a formula, quickly input the same formula, and perform formula calculation.

  1. Moving and copying worksheets

Not only can worksheets be moved and copied within one workbook, but tables can also be moved or copied to other workbooks. To move a worksheet, simply click on the label of the table you want to move with the mouse and drag it to a new location. To copy a worksheet, simply select the worksheet, press the Ctrl key, and then drag the table to a new location. Of course, this method can move and copy several tables simultaneously. After moving, previously non adjacent tables can become adjacent tables.

  1. Deleting a worksheet

Click the “Edit ->Delete Sheet” command, and then click the “OK” button to permanently delete this table from the workbook. Note: The “Delete Sheet” command cannot be restored, and deleted sheets cannot be restored.

  1. Quick cell selection

When selecting cells (rows, columns), holding down the Shift key simultaneously can select consecutive cells (rows, columns). When selecting cells (rows, columns), holding down the Ctrl key simultaneously can select discontinuous cells (rows, columns).

  1. Quick Selection of Excel Region (Microsoft Office Skills Competition Award winning work) In Excel, to quickly select a certain area in the workbook, simply click on the cell in the top left corner of the area you want to select, hold down the Shift key, and then click on the cell in the bottom right corner of the area you want to select. Additionally, holding down the Ctrl key and then using the mouse allows you to select multiple non adjacent areas at will. In fact, selecting a region is not related to order. You only need to first select a corner cell of the rectangular region, and then press Shift while clicking on the diagonal cell.
  2. Backup the workpiece book

Click the “File → Save” command to open the “Save As” dialog box. Press the dropdown button next to “Tools” in the upper right corner, click “General Options”, and in the subsequent dialog box, select the “Generate Backup” option () and click the “OK” button to save. After modifying the workbook in the future and saving it, the system will automatically generate a backup workbook that can be opened and used directly.

  1. Automatically open workbook

We just need to place a shortcut to a workbook that needs to be automatically opened in the C: \ Programn Files \ Microsoft Office \ Office10 \ XLStart folder, and Excel will automatically open the corresponding workbook every time it starts.

  1. Quick browsing of long workbooks

When browsing a table with a long content, pressing the “Ctri+Home” key can return to the top left corner of the current worksheet (i.e. A1 cell), and pressing the “Ctrl+End” key can jump to the bottom right corner of the worksheet containing data. In addition, if you have selected some content, you can move clockwise on the four corner cells of the selected content by repeatedly pressing “Ctrl+.” (period).

  1. Quickly delete empty rows in the worksheet

If a user wants to delete empty rows in an Excel worksheet, the general method is to find all the empty rows and delete them row by row, but this method is very cumbersome and inconvenient. Here is a quick way to delete empty rows from a worksheet: First, open the worksheet where you want to delete the empty rows, click the “Insert → Column” command in the open worksheet to insert a new column X, fill in integers in order in column X, and then sort the rows in the table according to any other column, so that all empty rows are concentrated at the bottom of the table. Delete the data in column X from all empty rows, reorder it in column X, and then delete column X.

  1. Draw a diagonal header

In general, when creating a header in Excel, the first row of the table is used as the header, and then text is entered. However, such a header is relatively simple, let alone a diagonal header. Can a diagonal header be implemented in Excel? Here is the specific method: because the cells that serve as diagonal headers are larger than other cells, first adjust the size of the first cell in the table. Then click to select the cell, click the “Format → Cells” command, and the “Cell Format” window will pop up. Select the “Align” label, select the vertical alignment method as “Top”, select the “Autowrap” checkbox under “Text Control” (), then select the “Borders” label, press the “Outer Borders” button to make the outer border of the table header line. Then, press the “Slash” button below to add a diagonal line to this cell. After setting it, click the “OK” button, and an additional diagonal line will appear in the first cell of Excel. Now double-click on the first cell to enter editing mode, and enter text such as “Project” and “Month”. Then place the cursor before the “Item” word and press the spacebar continuously to move these four words back (because we have set the text control to “auto wrap” in the cell properties, when the “Month” word exceeds the cell, it will automatically wrap to the next line). Now click anywhere in the table, exit the first cell and take a look. A beautiful diagonal header is complete.


No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *