Shulou(Shulou.com)06/01 Report--

What is the excel tip to improve the efficiency of data processing? I believe many inexperienced people are at a loss about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. How do you want to see the value of the function calculated in excel?

Ctrl+~: enters the function view

2. What are the ways to remove duplicate values?

A, data-data tool-Delete duplicate values (sort by the current selection: only the selected area is deduplicated; expand the selected area: this small area block will be jointly deduplicated (up and down until a blank line is encountered)

B. PivotTable provides options for removing duplicate values

C, start-conditional format-highlight cell rules-duplicate values

3. The EXCEL table filters out all rows with the second-to-last digit of 8.

= IF (MID (A1 Len (A1)-1) = "8", "Yes", "No")

4. What are the data requirements of the PivotTable?

The first row of each column of data contains the column header; (it must be included, otherwise the perspective data will be null)

Cannot contain empty rows or columns

Cannot contain empty cells; (you can include cells with null, and all cells with null will eventually be placed in the last column of the PivotTable without having to delete them directly! )

Cannot contain merged cells

Cannot contain similar fields

There are so many techniques for PivotTable and PivotChart that a summary will be written in the future.

5. How does the PivotTable report appear to be filtered quarterly and monthly?

Operation: put the selected area on the row-date on the row (right-combined-quarterly)-right-combined-year

6. How to implement the vlookup function?

7. The order you want to arrange is not in excel. What should I do?

Custom sorting: "Edit Custom list" under File-options-Advanced-General options-add the specified order.

After this setting, you can find the sort order in Custom sort when you continue to use sorting in the future!

In addition, this operation is for excel software, and when using PivotTable and slicing later, the sort involved will schema retrieve the sort in the custom list.

8. How to quickly select regions and fill in formulas in batches?

Drag by hand? Ctrl+Shift+ ↑↓←→? No,no,no! These are basically familiar to everyone, so I won't mention them here!

Scene: what if you want to fill in 10,000 rows after entering a formula, and there is no continuous data next to it, so you can't double-click to fill it automatically?

Solution: enter the cell range in the name box and press enter, which is equivalent to the effect of manual selection.

Step1: type A2:A10001 in the name box in the upper left corner, and then press enter, so 10 thousand lines are selected

Step2: enter the formula you want to fill in, press Ctrl+Enter, and you can fill the formula in these ten thousand lines.

9. Automatic completion function

The function of Excel is very powerful and frequently used, so it is very important to improve the input efficiency of the function.

Scenario: when entering a function name, many people enter the full name of the function, such as typing the full name of vlookup by hand, or clicking with the mouse after typing, which is not the most efficient method.

Solution: only need to enter the first few letters, wait until there are few options, then use the arrow keys to select the target function, and then press the TAB key (), Excel will automatically help us complete the full name of the function and the left parenthesis, and then we can directly enter the function, very efficient!

10. Formula troubleshooting (Debug)

Functions are so important, but they are bound to make mistakes, so everyone must learn how to debug formulas.

Scenario: what if you write complex function nesting formulas such as if or & string multiple values together and the formula goes wrong and doesn't get the desired result?

Option 1: in the edit bar, select part of the complete operation, press F9, you can display the results of the operation, which makes it convenient for us to debug in blocks. (remember to press the ESC key to cancel after the error, otherwise the result of the step-by-step operation will be retained in the formula ~)

Plan 2: first select the cell where the formula is located, press the formula evaluation function, and then constantly click "evaluation", you can display the results of the operation step by step according to the operation order of the whole formula, and find the cause of the error.

After reading the above, have you mastered what are the excel tips to improve the efficiency of data processing? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.