7 Microsoft Excel Tricks You Probably Didn't Know

Excel tips: Seven practical tips to improve efficiency

This article will share seven practical Excel tips to help you improve your work efficiency, and even experienced Excel users can benefit from it.

1. Hide data but not hide cells

Excel does not have a command to directly hide data in cells, but this function is very practical. For example, in a heat map, the color clearly expresses the data meaning without showing specific values.

Similarly, the scores in the table may be derived from calculations, and their values ​​themselves are of little significance, and the data strips have clearly shown the comparison between employees. Therefore, the total score does not need to be displayed.

When printing salary charts, in order to protect employee privacy, salary data needs to be hidden.

To hide data but not cells, after selecting the cell, click the "Number Format" dialog launcher in the "Number" group of the "Start" tab.

Select "Custom" in the menu on the left and enter ";;;" (three semicolons) in the "Type" field.

Click "OK" and the number in the selected cell will disappear, but the data will still exist.

To view hidden data or formulas, select the cell and you can see the formula bar at the top of the spreadsheet.

2. Convert numerical data into representative text

There are several ways to generate text from data, including the XLOOKUP function, conditional formatting, and SWITCH function. However, many techniques require setting up separate columns for data and its representative text.

In column D of this data table, I hope Excel converts positive numbers to "profit", negative numbers to "loss", and zeros to "break-even".

Select the relevant cell, right-click, and then click the "Number Format" dialog launcher in the "Start" tab "Number" group.

Click "Custom", enter "Profit"; "Loss"; "Break-even" in the "Type" field, making sure that each phrase is enclosed in double quotes and separated by semicolons. It is important to enter the phrases in this order – the first one is for positive numbers, the second one is for negative numbers, and the third one is for zeros.

Click "OK" and the number will be changed to the text you defined in the previous step, allowing you to quickly evaluate the data.

3. Quickly cut, copy, replace and insert cells, rows and columns

Mouse and keyboard shortcuts allow you to move, copy and insert cells, rows, and columns more efficiently.

• Replace: Click and drag Directly select, click and drag a cell, row or column to the target position, and click "OK" to confirm the replacement data.

• Copy and replace: Ctrl key Click and drag Select a cell, row or column, hold down the Ctrl key, and drag to the copy position. Excel will not prompt to replace data, please be sure to confirm before operation.

• Cut and insert: Shift key Click and drag Insert a cell, row, or column between existing data, hold the Shift key and click and drag.

• Copy and insert: Shift Ctrl key Click and drag Create a copy of a cell, row or column without losing any data. Press and hold Shift and Ctrl keys to click and drag.

4. Repeat the last operation (F4 key)

F4 keys can repeat the last operation, including pasting, inserting and deleting rows, adding cell formatting, and adding new worksheets. This is more efficient than repeating clicks on the menu.

For example, insert a new column between existing columns. After the first operation, select the target column and press F4 to repeat the insertion operation.

Note that the F4 key acts differently in different situations, for example, when entering a formula, it is used to switch relative, absolute, and mixed references.

5. Insert, select and uncheck multiple check boxes at the same time

Excel's checkbox tool (added in June 2024) is ideal for creating inventory, tracking task progress and quickly visualizing data.

To select or uncheck multiple check boxes at the same time, first select the cell containing the check boxes, and then press the Spacebar.

6. Add the current (fixed) date and time

Use shortcut keys to add fixed date and time:

• Ctrl ; Insert the current date
• Ctrl Shift ; Insert the current time
• Ctrl ; Space Ctrl Shift ; Insert the current date and time

7. Copy Excel window

It is very convenient to view different areas or multiple tabs at the same time when working with large worksheets. Click the New Window icon in the View tab to copy the workbook. Any changes to one replica are automatically updated to another replica.

Press Windows Z key to rearrange the way the copied Excel window is displayed on the screen.

Mastering these Excel tips and understanding common mistakes can significantly improve productivity.

The above is the detailed content of 7 Microsoft Excel Tricks You Probably Didn't Know. For more information, please follow other related articles on the PHP Chinese website!