The most comprehensive Excel data editing technique in history

The most complete data editing technique in history, freeing you from the madness state of your daily data analysis and processing.

One, the hidden ranks

"Amazing, Excel appeared supernatural event, part of the area disappeared!" "A mm in the office ran to shout loudly, really frightened me to jump." A long time to retire to the famous detective (is small) to find out. After a small series pinch fingers, found that the original ranks are hidden, have not seen the whole of the sister paper is a fuss (of course, this is only the voice of the Heart).

A worksheet can hide data that is temporarily not needed for display. Select the rows or columns you want to hide, right-hide, and you're done hiding. You can also use a faster way to ctrl+9 hidden rows, shortcut keys ctrl+0 hidden columns. Select Include hidden row or column range, right-unhide, hidden range is canceled. For half a day mm said the supernatural incident is so. Go back and see if there's something hidden in the worksheet, maybe someone wrote you a love letter.

Second, discontinuous row and column insertion

Because reports often have data that needs to be added, how do you want to add insert summary data to your data?

Discontinuous lines added: Hold CTRL not put, in more than one need to insert the row position, click on the need to insert a row, click "Insert-insert work Line"; discontinuous columns added: Hold down CTRL, click on multiple columns where you want to insert the column, tap Insert-insert Work column. After completing the new column, editing the contents of each field completes the update maintenance of the report. Have you learned it?

Iii. rapid production of wage strips

When the monthly quick pay, most people are grinning hope, except ... In the face of the salary of hundreds of people, there is no experience to do internal bleeding of the compensation staff. How to crack it? Quickly learn a trick of the long lost in the rapid production of salary bar stunt.

First, you add a "secondary column" after the payroll table, and then you create a sequential sequence number, and then you copy all the secondary column ordinals, paste the ordinal below the last column cell of the secondary column, and thirdly, copy the first row field name of the payroll table, fill in the blank area to the left of the pasted sequential sequence, and finally, Delete the auxiliary column, everyone's pay bar is finished.

Iv. cell splitting and merging

Frequently encountered worksheet sort prompt error, what's going on?

The worksheet cannot complete the sort operation because the merged cells are used. You need to identify the merged cells section and edit them again. Press CTRL+F, in the Find and Replace dialog box, click the Format drop-down option, select "Format ...", "Find format", click Switch to "Align", tick "merge cells" and OK. When you're done finding settings, click Find All to find all the merged cells, select all merged cells to further color-mark them, or further cancel the merged cells. The "pit" in the work table is finally ruled out and will never be cropper again.

V. Quickly move or copy data

See the day of the dragon to kill the small partner (in fact, not small, accidentally exposed the age, crying dizzy t_t), must be the big moves of God technology impressive, there are wood have seen also secretly learned gestures, haha. Today, we will teach you a big move in Excel.

Click the target column you want to move, or even the range of areas, hold down the SHIFT key, drag the mouse to the location you want to place, and then complete the move by releasing the mouse. If you want to copy the target area while moving, hold down the CTRL key, drag the cursor to copy the content to the specified location, and then release the mouse and copy it while the move is complete. Learn excel in the universe moved, from now on the career invincible ~

Use custom format to quickly enter data

How to quickly enter the assessment results? Direct typing Everyone "qualified", "unqualified" very affect the efficiency of data entry, there is no quick way to complete the input quickly? The answer is yes.

With custom formatting, you can define a value of 1 = "qualified", 0 = "unqualified", set to: Select the area you want to set, press Ctrl+1 to bring up the Format Cells dialog box, select Custom, and define the type as: [= 1] "qualified"; [= 0] "unqualified", in order to distinguish clearly can be added color identification: [Blue][=1] "qualified"; Red [= 0] "unqualified". Use on the high-rise code, improve the efficiency of input data, instantly feel their own bang bang.

Display the currency symbol and the unit of amount at a glance

All day long by the financial statements currency unit toss the quick look over.

In some financial statements, you will be asked to add a currency symbol before the amount data. In the Start – Fiscal number format, you can set a uniform currency unit for a selected range of numbers, and the area of the currency unit is still a number and does not affect numerical calculations. For a single cell, you can press ALT + numeric keypad number to quickly enter common currency units, such as alt+165 input (￥), alt+36 input ($). Learned these, for the currency unit can be regarded as the rise posture, this round to the data posing good posture and so you toss.

Eight, paste selectively

I believe that everyone in office use the most skilled, when it is CTRL + C, CTRL + V, application scenarios such as writing papers, writing programs ... You know, it's a copy-and-paste veteran, but have you ever used "paste-in"? If it doesn't work, "paste-in" is a great way to use it completely beyond your wildest imagination.

Optional paste-Skip the empty unit, with the latest data update the original assessment points, because some people did not participate, did not participate in the original assessment points. If the direct copy and paste will overwrite the original content, using "paste-skip empty cell" can quickly solve the problem;

Paste---value, paste only the data, without the number format (such as the number of decimal places, percent, etc.), border, font color, fill color, notes and other content;

Paste-in-value and number format, paste numeric and numeric formats, retain the number of decimal places, percent semicolons, etc., ignoring the border, font, fill color, notes and other content;

Paste-in-value and source format, full paste copy of the value of the content and cell settings, notes and other content;

Paste selectively, but also not only to complete these, but also can be pasted to transpose the series, paste annotations, paste as a picture, directly to the copied numbers pasted into the target area for arithmetic. How many of these you have used, and quickly re-learn to paste it ~

IX. system Export data cannot be summed

ERP and other systems export Excel, in the data summary, often encounter summary results of 0, this is the situation of God horse?

Originally, this is because the system sometimes stores data that is actually stored in the text type, or that the data contains invisible characters (such as spaces, empty characters, tab characters) that are actually obtained as text when exported. In general, if the upper-left corner of the data is marked with a green triangle, you can select the Marked Data area, select "Convert to Number" in the "exclamation point" drop-down box, the text can be loaded as a number, if there is no green mark in the upper-left corner of the data, the data contains other invisible characters, the data- Columns, the wizard separates characters from numbers and completes the data summary.

X. Finding and replacing data

The vast data in the sea to find her 1100 degrees, twilight look back, the data is in the corner of the table. If you can learn how to find the number of replacements, why not find so hard?

The amount of data in a worksheet is a lot more, it is obviously inefficient to use the naked eye to find and change individual data in thousands of, tens of thousands of of the data, and the "Find and replace" function is almost seconds. Press the Ctrl+f shortcut key, bring up the Find/Replace dialog box, enter what you want to find, click Find All or Find Next, find the target data, if you want to find the wrong data, you can switch to replace, or shortcut ctrl+h, fill in "Find what", replace with XX data. Learn this trick, still need to struggle to find?

Xi. quickly replace different data

The replacement function of the small partners know that the replacement can only replace the same data to the same other data, but sometimes the data content more, need to replace the different data to another identical data, is there any high (TOU) effect (LAN) method? Let's take a look at the following example.

Need to the various sections of the description of the failure to replace the content of "failed", the patient of the small partner said that the amount of data to change, but the worksheet has a large hundreds of rows of data, which should be changed to what move time? The small partner who is good at discovering the law says that it can be completed in a few times. The top (TOU) effect (LAN) of the small partner looked down, saw his hands fall, select the range of data to look for, press the F5 key, in the "Targeting" dialog box, select the location condition is "column content difference Cells", click OK, This time to replace the contents of the cell is a magical completion of the selection, in the formula bar to enter the replacement "failed", press Ctrl+enter, all the content needed to replace the instant complete replacement.

12. Replace the asterisk in the worksheet

The multiplication character "X" in the worksheet requires switching the input keyboard to complete the input, often substituting "*" instead of "X" for input. To make it easier to read after printing, replace "*" with "X".

Select the column data and press CTRL+H to bring up the Replace dialog box. Find the content input "*", replace with the input "x", the result is all the contents of the cell is replaced by "X", and we want the result is obviously just replace "*" character. Because "*" as a wildcard, the expression is any character, plus "~" can be said to remove the wildcard itself to find only "*" characters. First undo the operation, in the Find content "*" preceded by "~", that is, find content "~*", replace the content unchanged, replace again, this time get is the right result we want.

13. Replace by the specified format

In addition to finding replacement data, can I find replacements by cell-style? It's just something you can't think of, no Excel can't do.

First, press ctrl+f/h to enter the Find and replace function, and go to "options" to see more features for finding replacements. Second, from "Find content" after the "format" drop-down OPTION select "Select Format from Cell", when the cursor becomes "eyedropper" can copy the selected cell format, select the cell format you want to find; Choose format from the "Format" drop-down option after "Replace with" to set the cell number format, border, Font and color, fill color, etc., after the completion of the settings, click "Replace All" to complete the replacement of the cell format; Finally, to let the next time you continue to use the Find and replace function, not affected by the formatting you have set, you need to clear the format of the found content has been set.

14. Batch replacement of discontinuous cell values

How can I quickly complete a discontinuous cell with a large amount of less than 60 that needs to be replaced with the same value 60 at the same time?

Ctrl+f Bring up the Find dialog, find the content input "*", click "Find All", in all the values found, click on the values to sort, get small to large sequence of values, press SHIFT to select the first number less than 60 and the last number, complete all less than 60 number selection. Close the Find and Replace dialog box, you can see all the numbers less than 60 are selected at the same time, just enter the replacement number 60 in the formula bar, press Ctrl+enter to be able to bulk complete the change replacement.

The most comprehensive Excel data editing technique in history (RPM)