Share 50 commonly used Excel skills in work with everyone

Share 50 commonly used Excel skills in work with everyone

  1. Quick summation

To sum the data of cells with multiple rows and columns, you can directly select the cell range and press the combination keys [Alt], [+], and [=] to quickly sum it.

  1. Force line breaks within cells

When there is a lot of cell content, it will appear very wide and inconvenient to read. At this time, you can click on the cell to wrap, place the cursor in the edit bar, click on the position to wrap, press Alt+Enter to force the line to wrap.

  1. Multiple cells input simultaneously

Select all cells, enter content in the edit bar, and press the combination key [Ctrl+Enter] to achieve simultaneous input of multiple cells.

  1. Quick Hide Columns

When there is too much table content and it is necessary to hide data from a certain column in the worksheet, you can directly select the column and quickly drag it to the left to hide the selected column.

  1. Quickly adjust display scale

Click the cursor on any cell in the table, hold down the Ctrl key while scrolling the mouse wheel, and you can quickly zoom in or out on the worksheet display ratio.

  1. How to quickly delete rows of cells with a value of “0”

Press the combination key 【 Ctrl+F 】 to export the search dialog box, select the 【 Search 】 tab, enter: 0 in the input field, click 【 Find All 】, and then press the combination key 【 Ctrl+A 】 to display all cells in the table with content 0. Select the cells and right-click 【 Delete 】 to delete them.

  1. Repeat the previous input

After entering content in a cell, press Enter to enter the next cell, and then press Ctrl+D to quickly repeat the previous input.

  1. Fill in values:

When encountering numbering such as 1, 2, 3, etc., you can first enter several consecutive numerical values in the cells in sequence, then select the cell area. When the mouse moves to the bottom left and turns into a black cross, drag down to fill in.

  1. Batch processing of row height and column width

Click on the row/column in the table, select the area where the row/column needs to be unified, move the mouse over the line between the rows/columns, and wait for the mouse to change to black with a two-way arrow. When the mouse changes to black, drag the line between the row or column labels to achieve unified row height and column width distance.

  1. Column width automatically adapts to content

Select all rows/columns that need to be adjusted in the table area, place the mouse on the line between row or column labels, and double-click the left mouse button when the mouse changes to black with a two-way arrow.

  1. Insert multiple rows or columns at once

Select multiple rows or columns simultaneously in the table, right-click in the selection area, and select [Insert]. An area with the same number of rows or columns as the selected one will be inserted to the left or above of the selection area.

  1. Quickly move and select data

Select the data area that needs to be moved, move the mouse to the edge line of the area, and when the mouse arrow changes to a black solid state, press the shift button and click the left mouse button to drag to the correct position.

  1. Quickly locate edge cells

Select any cell in the data range, move the mouse over the bottom border of the cell, and when the mouse arrow changes to a solid state, double-click the bottom border of the cell to quickly jump to the last row of data in that column.

  1. Freeze Panes

Click on [View] – [Freeze Panes] – [Freeze First Row or Freeze First Column] in the menu bar. If you want to freeze both the first row and first column at the same time, click on the first cell in the upper left corner of the data area and select [Freeze Split Panes] in the freeze pane. If you want to unfreeze, click [Unfreeze Panes].

  1. Batch operation of adding Chinese characters before numbers

Select a range of numeric cells, press Ctrl+number 1 to open the cell format window, select Number – Custom, enter “Number: 00” in Type, and click OK.

  1. Find duplicate values

Select the area to search for data, click on 【 Start 】 – 【 Conditional Format 】 – 【 Highlight Cell Rules 】 – 【 Repeat Value 】, and set the format of the selected duplicate value to 【 Light Red Fill Color Dark Red Text 】

  1. How to copy and paste line widths

Copy the content within the table area, click on the blank cells to paste, and select [Keep Source Column Width] from the paste options on the bottom right side of the pasted area.

  1. Hide formulas within the table

Select the cell range where the formula is located, right-click on the menu and select “Format Cells”. In the pop-up dialog box, select the “Protect” tab and check the “Hide” option. Then click on “Review” – “Protect Sheet” in the menu bar.

  1. Set Excel to display header on each page

In the Excel print preview view, click [Page Settings], select the [Sheet] tab in the dialog box, click the icon on the right side of the [Top Title Row] text box, select the cell range where the header and top title are located, and then click the button to return to the [Page Settings] dialog box. Click [OK] to proceed.

  1. Wrap lines within an Excel table

After entering content in a cell, press the combination key [Alt+Enter] to wrap a line within the cell. After wrapping, simply enter the content.

  1. Set header and footer

Click on “Page Settings” – “Print Header and Footer” in the menu bar, and format the footer in the dialog box.

  1. One click creation of table file

Quickly create an Excel file by pressing the Ctrl+N key combination to create a new table.

  1. Quickly switch to another Excel window

When we need to view the file content of two tables, we can directly press the combination key 【 Ctrl+Tab 】 to switch the table window.

  1. Enter the ID number or the character string starting with 0

After selecting a cell range, right-click to select Set Cell Format, select the Number tab in the dialog box, and then click Text – OK under the category. After setting, directly enter the ID number or the character number string starting with 0.

  1. Quickly delete empty lines

When it is necessary to delete empty rows in the table area, you can directly select a column in the table, press the combination key [Ctrl+G] to export the positioning dialog box, check the positioning condition [Empty Value], click [OK], and then delete the entire row.

  1. Delete all images in the worksheet

Press the combination key [Ctrl+G] to export the positioning dialog box. Select [Object] as the positioning condition, click [OK], and then click delete.

  1. Quickly enter scores

Directly entering the score “1/4” in the cell will change to date format after entering. We can first enter “0” in the cell, then press the spacebar and directly enter the score “1/4” to display it directly as a score.

  1. Quickly select continuous data regions

After selecting a cell within the table area, hold down the Ctrl+Shift+arrow keys (up, down, left, right) simultaneously to quickly select the corresponding direction of the data area.

  1. Double digit display of date

When the date in the cell data range needs to be displayed in two digits, such as on January 1, 2020, you can directly select the data in the table, press [Ctrl]+number [1] to bring up the [Set Cell Format] dialog box, select the [Number] tab, click [Custom], set the type to: yyyy/mm/dd, and click [OK] to complete.

  1. Quickly swap rows or columns

Select the rows/columns in the table, hold down the Shift key, and when the mouse pointer changes to a cross shape, drag to the desired position.

  1. Quickly input long numbers with more than 15 digits

To switch to English mode, first enter a single quotation mark in the cell, and then enter a number.

  1. Quick sorting

Select any cell in the data area, select [Start] – [Sort] in the menu bar, select the main keywords for sorting such as date, grade, etc., and choose ascending or descending order.

  1. Quickly set a password for files

Click on 【 Review 】 – 【 Protect Sheet 】 in the menu bar, set the password in the dialog box, and click 【 OK 】 to complete the process.

  1. View two worksheets simultaneously

Click on “View” – “Compare Side by Side” in the menu bar, select the worksheet to be compared in the dialog box of the side by side window, and click “OK”.

  1. Keep the contents of all cells after merging

Select a range of cells and drag the column width to the width that can accommodate all merged cells. Click the [Start] tab in the menu bar, select [Align Both Ends], and merge the contents of multiple cells into one cell. In the separated space, press the combination key [Alt+Enter] to add a forced line break, and then merge the cells to retain all cell contents.

  1. Batch cancellation of annotations

After selecting the cell range containing annotations, click on “Review” – “Delete Annotation” in the menu bar.

  1. Quickly close Excel files

To quickly close an Excel file, simply press the Ctrl+W key combination and save the changes in the pop-up dialog box.

  1. Quickly hide rows/columns within the table

When you need to hide a row in the table, you can directly press the combination key [Ctrl+9]

  1. Print multiple pages of content onto one page

Click on 【 File 】 – 【 Print Preview 】 – 【 Page Settings 】 in the menu bar, select the 【 Page 】 tab, zoom and adjust, select 【 Other Settings 】, set it to 【 1 Page Width 】, 【 1 Page Height 】, and then click 【 OK 】.

  1. Print discontinuous areas in the worksheet

When printing, you do not need to print a whole page. You can press the Ctrl key while selecting the area in the table that needs to be printed, and then click File Print Area Set Print Area.

  1. Cell superscript number input

For example, in square meters (m2), you can enter m2 in the cell, select 2, press the combination key [Ctrl+1] to open the cell settings dialog box, and check [Superscript] in the font special effects.

  1. Table row to column conversion, column to row conversion

Copy the table area within the cell, right-click and select “Paste Special”. In the dialog box, select “Invert” to quickly convert rows to columns and columns to rows.

  1. Quickly align names

When entering a name, there will be two or three characters of the name. First, select the name cell area, right-click and select [Set Cell Format], click the [Align] tab, select [Scattered Alignment (Continuous)] for [Text Alignment], and then click [OK].

  1. Calculate the average value

When you need to calculate the average value of data in a table, enter:=AVERAGE in the cell where you want to calculate the average value, double-click the function, pull out the table area, and then press Enter to quickly obtain the average value of the values in the area.

  1. Quickly generate √ and × symbols with boxes

Enter uppercase R or S in the target cell (note: R is a check mark √, S is an error mark ×), then click on the font style in the menu bar and select Set Font: Wingding2 to generate boxed √ and × with just one click.

  1. Segmented display of phone numbers

Enter the formula=XT in the target cell, double-click the function, and pull the number of columns in the phone number cell area. Enter (B2, “000 0000 0000”) to display the phone number in segments.

  1. Quickly draw a diagonal header

After moving the cursor to the target cell, click [Insert] – [Shape] in the menu bar, select a straight line style, hold down the [Alt] key, and drag the mouse from the starting point in the top left corner of the cell to the bottom right corner.

  1. Quickly freeze the first row and first column

Select the B2 cell in the table, click on 【 View 】 – 【 Freeze to 1st Row A Column 】 in the menu bar to complete.

  1. Quick data extraction

First, enter the data to be extracted in the cell, move the mouse to the bottom right corner of the cell, and when a black solid icon appears, drag down to fill in to quickly extract the required data.

  1. Hide worksheet

When you need to hide the entire worksheet, right-click on the worksheet at the bottom of the table and select [Hide].

Leave a Reply

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