Excel how to modify the specified cell format in batches

Select two empty columns next to it.

For example, for m, n columns.

1: Write 1 in M1 and 2 in M2, select two cells (M1, M2), hold down Ctrl and drag to with the drag handle.

m150;(M column order 1, 2, 1, 2.......)Mark the 1 and 2 in the first 4 rows, e.g. to blue.

2: Write 1 in n1 and 2 in n2, select two cells (n1, n2), and drag the handle to n150 directly; (n column order 1, 2, 3, 4,.......)Mark the 1 and 2 in the first 4 rows, e.g. to blue.

3: Select any cell in column m, sort in descending order, the row with black 2 is the row in the original file to change the font, change it after selecting the region.

4: Select any cell in N column, sort in ascending order, and restore the data location.

Note that if your data is more than 150 rows, you need to drag the data in n columns to the end of the data row to prevent other data order changes from being restored.

Finally, delete the m and n columns.

It may still be a little troublesome with what you want, but it's better than choosing one by one. Hehe.

Select the cells you want to modify, and then set them, it's okay, merge the cells, it will automatically pop up a prompt, asking if you want to modify, you can choose to do it or not according to the situation, hehe.

1. Open the excel sheet that needs to be modified.

2. Drag the cursor to select the cell in the current number format.

3. Right-click and select the "Format Cell" option in the pop-up menu.

4. On the Format Cells page, change the number format to text format.

5. After clicking "OK", the previously selected cell format has been changed to text format.

Excel often needs to use the technique of modifying data format in batches, how to modify the data format in batches? The following is the method I brought about Excel** batch modification data format, I hope to inspire you after reading!

Excel**The method of modifying data formats in batchesBatch Modify Data FormatStep 1: We open the excel that we want to modify in batches, as follows:

Figure 1 shows the method of modifying data formats in batches by excel

Figure 2 of the method of modifying the data format in batches by excel**Step 2: Select the column we want to modify and right-click on the header, as follows:

Figure 3 shows the method of modifying data formats in batches

Figure 4 shows the method of modifying data formats in batchesModify Data Format in BatchesStep 3: We click "Format Cells" as follows:

Figure 5 of the method of modifying the data format in batches by excel**Step 4: Click "Text" and then "Custom", as follows:

Figure 6 of the method of modifying data format in batches by Excel**

Figure 7 of the method of modifying the data format in batches by excel**Modify Data Format in BatchesStep 5: We add " in the Type box as follows:

Figure 8 of the method of modifying the data format in batches by Excel**Batch Modify Data Format Step 6: After we click OK, it is as follows.

Figure 9 of the method of modifying the data format in batches is shown in Excel**

1. Open the excel** that you want to modify in batches, as follows:

2. Select the column you want to modify, and right-click on the table header, as follows:

3. Click "Format Cells" as follows:

4. Click "Text" first, and then click "Custom", as follows:

5. Add " in the "Type" box as follows:

6. After clicking OK, it is as follows:

7. You will see that they are all hash signs, it doesn't matter, this is because it can't be displayed. Make the cell a little wider, as follows:

After changing the format of the column cells from "Text" to "Date", copy the column and paste it in the original position to quickly activate all the cells in the column.

Select the data region, and press Done directly.

Insert 2 empty columns, enter the formula =a1 (assuming the column of the date is in) *1 in the first column, and all of them will be converted.

Then copy the converted column and paste it Special - Values & Formatting into the second empty column. Delete the original column.

1. Open the excel sheet that needs to be modified.

2. Drag the cursor to select the cell in the current number format.

3. Right-click and select the "Format Cell" option in the pop-up menu.

4. On the Format Cells page, change the number format to text format.

5. After clicking "OK", the previously selected cell format has been changed to text format.

1. First of all, we open the desktop of the computer, double-click the left mouse button to open the excel** in the computer.

2. Then we select the column that needs to change the cell format.

3. Then we select the data option in the menu bar at the top of the interface, and then click on the column option at the bottom.

3. Then we click on the separator option, and then click on the next option.

4. Then we select the comma, drop down to select the comma, and click Next.

5. We select the small green triangle on the cell. , that is, it can be converted to text format in batches.

Select all the cells you want to change the format, right-click, cell, format and select text format.

Select the cell area you want to reformat, click Start, Format, Format Cells, and select Text and OK in Category.

First, if it is a single cell.

Select the cell--- right-click--- set the cell formatting --- text --- OK--- and then double-click to enter the cell enter.

Or precede what the cell writes'By default, the number is written in text format.

Second, if it is a whole column.

Select the data --- column --- in the column--- --- the next step to step 3--- and select the data format ---of the column"Text"---Finish.

Method steps.

Start by opening excel and you might have to deal with a bunch of **, as follows:

Click on one of the worksheets, and on the view page, find the command for "Macro".

Tap Macros, and then tap Record Macro

In the [Record Macro] interface, you can name the newly created macro and set shortcut keys for it, so as not to conflict with the original shortcut keys of Office. It is usually named CTRL+N, and then click OK.

Next, every step you make will be memorized by the [macro], pay attention to the operation accurately, otherwise it will increase the amount of calculation, and errors will inevitably occur. Such as modifying fonts, adjusting page layouts, etc.

When these operations are complete, click Macro to stop recording.

Next, you can modify the format according to the macro definition in another **. Click Macros to view macros.

In the view macro interface, find the macro name you defined, and then click [Execute], you will find that the original format has been changed. If you define a shortcut, you can also press the shortcut key, such as CTRL+N, to quickly change it.

You can send a few sample sheets for me to see. To make it clear that the table is standard.

Format Painter can be used in several different workbooks.

The next time you design, set up worksheet protection.

Let them only enter in a specific range of cells (preferably from the drop-down menu) and not make changes.

Copy the correct worksheet and format it in the other table with Paste Special.

You should use the lock function when you send it out, which cells are allowed to be modified, which cells cannot be modified, which can enter numbers, and which can enter text.

It's useless to take back the format brush, you can only change it one by one.

Method: 1. Open Excel**, and use the mouse to select the row or column (which can be multiple rows and multiple columns) to set "conditional formatting" with the mouse.

2. Click "Start" -- Conditional Formatting -- Highlight Cell Rules.

3. Set "Xiaochun First In" to "--0" and set --fill in yellow.

The format painter is sufficient, and double-clicking the format painter can be modified continuously.

If you have merged rows, your requirements may not be met. Because the celery is reskinned to two adjacent lines, one is an odd line and the other is an even line, and you can't tell which format it should be set to after merging (even or odd lines are not in the right format).

If you don't have merged rows and columns, you can use conditional formatting to format them

Select G5:L5 with the mouse, click "Format Destroyer" - >Conditional Format", and set the condition to:

Formula = mod(row(),2) = 0

Click "Format" - > Font "-> Color", select "Red", then select "Bold", and click "OK" twice.

Use the format painter to copy the g5:l5 conditional formatting to the data range you want.

Batch comparisons can be made using the array formula and().

How to compare, but also according to the situation in the AND use different comparison formulas:

1. If it is a comparison of forced text, you can use the exact mentioned by frank2012308, in this case: the empty cell is not equal to 0, but it is equal to the empty string. For example, compare A1:

a10 and b1: b10, the formula is as follows:

and(exact(a1:a10,b1:b10)), and then press Ctrl+Shift+Enter (both sides of the formula appear, the same below).

Note: In this case, if A1 is an empty cell, and B1 is the formula "=A1", then the comparison with Exact will be unequal, so B1 will be converted to numeric data 0 by default. When compared with excat, they are all forcibly converted to text, i.e

On the other hand, the string "0" is equal to the value 0, because the value 0 is forcibly converted to the text "0" by excat

2. It is not a forced text comparison, you can directly use the = sign, at this time: the empty cell is equal to 0, and it is also equal to the empty string, but the string "0" is not equal to the value 0. As follows:

and(a1:a10=b1:b10), then press Ctrl+Shift+Enter

Attached: Difference test between numeric value and string:

A1: ="2"

B1: 2

C1 format the cell as text and type: 2

D1 directly enters: '2

When judging with =, a1 is equal to c1 is equal to d1, but not equal to b1 (because c1 has already formatted the text before entering 2, although 2 is entered, excel will treat it as a string.)"2", and d1'2 is forcibly converted to text "2").

B1 and C1, although they look the same in the cell input box, are not equal, because one is text and the other is a string.

On the contrary, C1 and D1 are equal because the two cells are recognized as text by Excel, although the input is different.