Hello, everyone. It’s been a long time since I shared any Microsoft Excel-related skills with you. Today, I will share with you some important Excel skills, so that you can understand them at a glance and learn them quickly. Now without further ado, let’s jump right in.
Table of Contents
Only extract numbers
- To extract only numbers from messy strings, we can do this with the help of a Word document.
- Just paste the data into a Word document, then press Ctrl+H to bring up the replacement
- Enter in Find Content: [!0-9]
- Then click More to check the use of wildcards and click replace all. Only the numbers will be kept after this.
Split similar items
- This is still a Word document, paste the data into the Word document, and press Ctrl+H to open the replacement dialog box.
- Find content: Set as a separator, here is a comma
- Replaced with: set as a paragraph mark
- Then click replace all and paste it back into Excel
Quickly create dynamic charts
- Press Ctrl+T to convert the table into a super table, then insert a column chart.
- Then click Switch Row and Column to switch the row and column position of the following chart.
- Finally, you only need to insert a name slicer in Table Design and click the slicer to achieve the effect of dynamic icons.
Decimal Number
- Formula 1: =C2/100, you will get a decimal.
- Formula 2: =INT(D2), the INT function will round the number and only keep the integer part.
Quickly enter the current time
- First, enter the NOW function in a cell to get the current moment.
- Then select the data area, click [Data Validation], allow the setting to Sequence, and set the result to the cell where the NOW function is located.
- Finally, set the cell format of the selected range to time.
Hide data
Select the data area that needs to be hidden, open the setting window of the Excel cell, and then click Custom, enter the following code in **;**;**;**, and click OK.
Automatic verification of Similar data
This method is only suitable for checking tables with the same format, both text and numbers can be checked.
- First, select a table, click Conditional Formatting to find New Rule, and select Use a Formula to determine the format.
- The formula is: =B4<>G4
- B4 is the first data of the left table, G4 is the first data of the right table
- Then click Format and choose a color you like in Fill, and the difference data will be automatically set in color.
Automatically hide the 0 value
Click File and bring up the Excel options, click Advanced and find Show zero values in cells with zero values, and remove the checkmark to hide all 0 values in the Excel table. For formula results, 0 is also used.
Keyword labeling color
- Select the data area, and then click Conditional Formatting to find Highlighting Rules and select Text Contains.
- Enter keywords on the left side of the interface that pops up, then select a color you like on the right side, and click OK.
