Jessica - ProlificExcel.com

How to Select Alternate Rows in Excel? (or Every Nth Row)

There are plenty of possible reasons we need to select alternate or every nth row in Excel.

It could be:

To change these rows’ cell formatting or color to highlight them and make the dataset more readable.
To delete the contents of these rows. (Note: we have a separate article for this. You may also want to check out “How to delete every other row“.)
To copy and paste them on a different sheet.

Whatever your reason may be, I will show you quick and easy ways to select every odd, even, or nth row in Excel.

Note that each method is designed to achieve a particular task. Some may be useful for highlighting rows. Others are useful for filtering the dataset so you can readily transfer every nth row to somewhere else.

I suggest you go over each method, so you will have something to use in the future in case you need it.

Highlight Alternate Rows by Formatting the Data as Table

The quickest way to highlight alternate rows in Excel is to format your dataset as a table. To do this:

1. Select your entire dataset.

You can do so by clicking on one cell from your dataset and pressing CTRL + A.

2. Once the entire dataset is selected, press CTRL + T. This will trigger Excel to convert the selected cells into a table.

You will see the Create Table menu pop up. The range that you have selected will appear in it.

The "Create Table" menu with the previously selected range added in its textbox.

If your range has headers, tick the “My table has headers” checkbox. Otherwise, untick the checkbox.

Once done, click OK.

3. And that’s it! You have converted your data range into a table with alternate row colors.

Example of a range converted into a table with alternate row colors.

4. If you like, you can change the color of the alternate rows in your table. With the table selected, go to the Table Design tab.

You can select from the preformatted table designs.

You can also be creative and create your very own table design. All you need to do is click on the New Table Style option at the bottom of the Table Designs.

The New Table Style menu will appear.

The "New Table Style" menu with certain table elements formatted.

Choose a table element and click the Format button to change its format.

In my example above, I have updated the following table elements:

First Row Stripe (odd rows in your dataset)
Second Row Stripe (even rows in your dataset)
Header Row

Once you have configured all the desired elements, click OK.

Go back to the list of table designs and select your custom design.

The table style that you have previously added will appear in the list of Table Designs.

And that’s it! You now have your dataset with the alternate rows highlighted.

Example of a table with custom design style applied.

Highlight Alternate or Every Nth Row Using Conditional Formatting

This method is perfect for those who don’t want to convert their data into a table. It allows you to specify which nth row needs to be highlighted. It doesn’t necessarily have to be the odd or even rows. It could be every 3^rd, 4^th, 5^th, or whatever row in your dataset.

1. Select all cells in your dataset except for the header.

Select all cells within the dataset except for the header.

2. From the Home tab, go to the Styles section and click on Conditional Formatting >> New Rule.

Steps to create a new conditional formatting rule.

3. The New Formatting Rule menu will appear.

From the list of Rule Types, select “Use a formula to determine which cells to format”.

4. Now, it’s time to define the formula that will serve as the basis for highlighting a particular row.

You can choose from the following formulas:

=ISEVEN(ROW())=TRUE
- Use this to highlight all even rows.

=ISODD(ROW())=TRUE
- Use this to highlight all odd rows.

=MOD(ROW()-[header row], [nth row])=0
- Use this to highlight every nth row.
- [header row] refers to the row where your header row is. If it’s in row 1, then type 1. Set this to 0 if your dataset doesn’t have a header.
- [nth row] refers to the nth row in the dataset you would like to highlight. So, if you’d like to highlight every 3^rd row in your dataset, set this to 3.

5. Once you have identified your formula, type it in the “Format values where this formula is true” textbox.

Add the formula in the "Format values where this formula is true" textbox.

In my example above, I have used this formula: =MOD(ROW()-1,5)=0

Notice that my header is in row 1. My target is to highlight every 5^th row in my dataset.

Rows in my sample dataset that I intend to highlight.

6. After adding your formula, click the Format button to select the color you want to highlight these rows.

Steps to select the cell format to apply to the rows.

The Format Cells menu will appear. Go to the Fill Tab and select your desired background color. Once selected, click OK.

7. You will be redirected back to the previous menu where you’ll see a preview of the format you selected.

In the "Edit Formatting Rule" menu, click OK to apply the rules you have set.

If you want to change it, click the Format button again. But if you’re already happy with it, click OK.

8. And that’s it! You should now have every nth row in your dataset highlighted.

Example of a dataset with a conditional formatting applied to select every 5th row.

Select Alternate or Every Nth Row in Excel Using the “Go to Special” Method

This method is perfect for any kind of purpose. You can use this to color every nth row, copy them into a different sheet, insert the same text to every other row, delete every other row, etc.

IMPORTANT:

If you intend to delete rows, be sure to have no other data next to the dataset. Otherwise, they too will be deleted in the process. You may want to create a backup of your file first, or copy the dataset into a new sheet, just in case.

For this method, we will add a formula that will mark the rows we intend to select.

You can use either of these formulas:

=ISEVEN(ROW())
- Use this to select all even rows.

=ISODD(ROW())
- Use this to select all odd rows.

=MOD(ROW()-[header row], [nth row])=0
- Use this to select every nth row.
- [header row] refers to the row where your header row is. If it’s in row 1, then type 1. Set this to 0 if your dataset doesn’t have a header.
- [nth row] refers to the nth row in the dataset you would like to select. So, if you’d like to select every 3^rd row in your dataset, set this to 3.

1. On a new column next to your dataset, type the formula that you have chosen to use.

In my example above, I used this formula: =MOD(ROW()-3, 2)=0

Notice that my header is in row 3. I wanted to select every 2^ndrow in my dataset.

2. After adding the formula, drag the Fill Handler down until the last row in your dataset to copy the formula to these cells.

3. Our goal is to mark all rows that we intend to select with TRUE.

Check the first few rows in your dataset and see if you have successfully marked all the rows you intend to delete with TRUE.

Check the first few rows in your dataset and see if you have achieved this goal.

4. Once you’re satisfied with the result, select all the cells in your dataset.

You can do so by selecting one cell within the dataset and pressing CTRL + A to select all.

Select a cell within your dataset and press CTRL + A to select all cells.

5. From the Data tab, go to the Sort & Filter section and click on the Filter button.

Steps to add filter to the selected cells.

6. Filters will be added to your dataset.

Example of what the dataset looks like once the filters are added.

7. Click the filter on the column we have added. Untick the FALSE checkbox and click OK.

Untick the FALSE checkbox in the filter column.

8. Your dataset will then be filtered to only show rows marked as TRUE.

Example of what happens to the dataset once the filter is applied.

9. While all the cells in the dataset are selected, go to the Home tab.

From the Editing section, click on Find & Select >> Go To Special.

10. In the Go To Special menu, select Visible cells only and click OK.

11. And that’s it! Every nth row in your dataset is now individually selected.

Example of what the dataset looks like if only the visible cells are selected.

12. You can now do whatever you want with these cells.

You can change the cell formatting or the cell colors to highlight them.

If you want to copy them to other cells, you can do so by pressing CTRL + C to copy.

If you want to delete them, right-click on one of the cells and select Delete Row.

Conclusion

As you can see, there are plenty of ways to select alternate or every nth row in Excel. The first two methods show you the quickest ways to highlight rows. The last method, on the other hand, is perfect for all selection purposes.

Check our related tutorial about removing the header or footer in your sheet.

How to Freeze Top Row and First Column in Excel?

If you have worked on a large dataset before, you would know the importance of always having the field headers visible even as you scroll through the sheet to go over the rest of the data.

It saves us a ton of time from scrolling back on top or to the left just to remember what field we are looking at.

In this article, I will show you ways to keep a particular area in your worksheet “locked” or “frozen” even as you move to a different section in your sheet.

How to Freeze the Top Row in Excel?

Datasets in a typical format usually have the headers in the first row of the worksheet (as shown in the image below).

Example of a dataset with the headers in row 1.

To freeze the top row of a sheet, go to the View tab.

From the Window section, click Freeze Panes and select Freeze Top Row.

And that’s it! The top row is locked on top even as you scroll down.

Sample output after freezing the top row

The gray horizontal line serves as a marker indicating that the row on top of it is frozen.

How to Freeze Multiple Rows in Excel?

If you want to freeze more rows (other than the first row), you only need to:

1. Select the entire row just below the rows you intend to freeze.

In my example above, I wanted to freeze rows 1 to 4, so I selected row #5.

2. Once the entire row is selected, go to the View tab.

From the Window section, click Freeze Panes.

From the list of options that appear, select Freeze Panes.

3. And that’s it! The rows above the selected row are now frozen.

Sample output after freezing multiple rows in Excel

How to Freeze the First Column in Excel?

To freeze the first column in your worksheet, go to the View tab.

Steps to freeze the first column in Excel

From the Window section, click Freeze Panes and select Freeze First Column.

And that’s it! The first column in your sheet is now locked and will no longer move even as you scroll to the right.

Sample output after freezing the first column

The gray vertical line serves as an indicator that the column before it is frozen.

How to Freeze Multiple Columns?

If you want to freeze more columns (other than the first one), you only need to:

1. Select the entire column that comes after the columns you intend to freeze.

In my example above, I wanted to freeze columns A, B, and C, so I selected column D.

2. Once you have selected the entire column, go to the View tab.

From the Window section, click Freeze Panes and select Freeze Panes from the list of options.

Steps to freeze multiple columns in Excel

3. And that’s it! The columns before the selected column are now “locked” even as you scroll to the right.

Sample output after freezing multiple columns

How to Freeze Both the Rows and Columns in Excel?

If you have headers on both the top and left sides of your dataset, you will need to freeze both rows and columns. To do this:

1. Find the intersection between the top rows and the left columns that you intend to freeze.

2. Then, select the cell just after that intersection.

Select the cell just after the intersection between the top rows and left columns you intend to freeze.

In my example above, I wanted to freeze rows 1 to 3 and columns A to C, so I selected cell D4.

3. Once the cell is selected, go to the View tab.

Steps to freeze both rows and columns in Excel

From the Window section, click Freeze Panes and select Freeze Panes from the list of options that pop up.

4. And that’s it! You now have your top rows and left columns locked and frozen even as you scroll up and down.

Sample output after freezing both the rows and columns.

The gray horizontal and vertical lines serve as a marker to indicate which rows and columns are frozen.

INSIGHT:

As you may have noticed, we can only freeze the rows on top and the columns on the left. We cannot do it with the rows at the bottom and the columns on the right.

Freezing Panes is both top and left-oriented. Therefore, if you wish to keep a particular set of cells always visible, you will need to place them on the rows at the top or on the columns at the left.

How to Unfreeze the Rows and Columns in Excel?

To unfreeze the rows and columns, you only need to:

Go to the View tab.
From the Window section, click Freeze Panes and select Unfreeze Panes from the list of options that appear.
You’ll know that the panes are no longer frozen once the gray horizontal and vertical lines disappear.

Why are the “Freeze Panes” Buttons in Excel Disabled?

There are three possible reasons why the “Freeze Panes” buttons in your View tab are disabled:

You are in the middle of editing the content of a cell (e.g., typing a formula in the formula bar). Solution: Finish writing the formula and press Enter (or press ESC to discontinue the changes you are working on).
Your worksheet is protected. Solution: Unprotect the worksheet first.
You are in the Page Layout view. Solution: Switch the view to Normal.

Alternative to Freezing Panes in Excel: Split Panes

“Split Panes” is a considerable alternative to freezing panes if you intend to compare records against each other. With this method, you can keep a particular set of rows or columns visible as you scroll through the sheet.

The steps in splitting panes and freezing panes are pretty much the same.

If you want to split some rows, select the entire row that follows after them.
If you want to split some columns, select the entire column that follows after them.
If you want to split some rows and columns, select the cell where the rows and columns intersect.

Once the appropriate row, column, or cell is selected, go to the View tab.

From the Window section, click on Split.

A thick gray line will appear on the sheet. This line indicates where the rows or columns split.

Notice also that there is one scroll bar added for each pane.

Multiple scroll bars are added whenever you split panes

You can use these scrollbars to move to a different area for each pane.

Please remember that this method is not the same as “freezing the rows or columns” per se. It is only giving us a split view of the cells. So, if you happen to scroll through each pane and move to the same area, your worksheet can look something like this:

Example of what happens when you scroll through each pane and move to the same area.

Both panes now have the same contents.

Conclusion

As you can see, freezing the top rows or left columns can be done in just a few clicks. If you are freezing more than one row or column, you only need to pay attention to which cell (or row or column) you need to select before freezing the panes. I hope this article helps!

How to Remove Commas in Excel?

A comma (,) is a tool that we use to separate words in a text or digits in a number so that we can better read and understand them at one glance. There are, however, instances when we need to remove them from our dataset.

In this tutorial, I will show you different ways to remove the comma from a number and text in Excel.

Remove Commas from a Number in Excel

When removing commas from a number in Excel, the first step is to determine whether the commas are part of the value inside the cell or they were only added as part of the cell formatting.

To determine which reason applies to your cell, you only need to:

Click on the cell that contains the number.
Look at the formula bar and see if you can find a comma in there.

Example of a comma showing in the cell but not in the formula bar

If you cannot find the comma, it means that the comma is part of the cell’s number formatting; therefore, you need to change the number formatting to remove it.

Example of a comma showing in both the cell and the formula bar

If you see the comma, it means that the cell, even though it contains a number, is being treated by Excel as text.

Remove Commas from a Number by Changing the Number Format

1. Highlight all the cells containing numbers with commas.

2. Go to the Home tab and click on the small arrow on the rightmost side of the Number section.

The Format Cells menu should appear.

If you like keyboard shortcuts, you can press CTRL + 1 to do this.

3. You have two options to remove the comma:

You can set the format to General and click OK.

Steps to set cell's number format to 'General'

Or you can set it to Number, uncheck the ‘User 1000 separator (,)’ option, and click OK.

Steps to set cell's number format to 'Number' with the comma removed

4. And that’s it! Commas are now removed from the numbers inside your selected cells.

Sample output after removing commas from numbers by changing the cells' number format

Remove Commas from a Number in Text Format

If you see the comma in the Formula Bar whenever you click on the cell, it means it was added as part of the value. Even though the cell contains a numeric value, Excel sees it as text, which means you can’t use it in calculations.

To remove the comma, we will have to retrieve the numeric value using the NumberValue() function.

On a new column, type the following formula: =NUMBERVALUE(A2)

Change “A2” with the appropriate range address of your cell.

Sample output after adding the NUMBERVALUE() function

And that’s it! You have successfully extracted the numeric value from the selected cells.

Copy this formula to the remaining cells by dragging the Fill Handler down, and you’re all set.

Drag the fill handler down to copy the formula to the rest of the cells.

NOTE: If you still see the comma after entering the formula, update the cells’ number formatting (as described in the previous method).

Remove Commas from a Text in Excel Using Find and Replace

1. Highlight all the cells where you would like to remove the comma.

2. Go to the Home tab. Under the Editing section, click on Find & Replace >> Replace.

The Find and Replace menu will appear.

If you like keyboard shortcuts, you can simply press CTRL + H.

3. In the Find What textbox, type a comma. Leave the Replace With textbox blank and click on the Replace All button.

And voila! All commas will disappear from the selected cells.

Sample output after applying the Find and Replace method

Remove Commas from a Text in Excel Using the SUBSTITUTE() Function

This method is perfect for those who don’t want to modify the dataset and prefer to have a new column store the texts without the commas.

Also, you’ll have the option NOT to remove all the commas inside the cell. You can freely select which comma to remove. Pretty sweet, right?

The SUBSTITUTE() function allows us to replace one or more text strings with a different text string.

=SUBSTITUTE(text, old_text, new_text, [instance_num])

text refers to the range containing the text that you would like to remove the comma from.
old_text refers to the text that you would like to replace which, in this case, is the comma.
new_text refers to the replacement of the old text. Since our goal is to remove the commas, we will set this to blank.
[instance_num] is optional. Leave this blank if you want to remove all instances of commas inside the text. However, if you only want to remove one of the commas inside the text, use this parameter to specify which instance of the comma to remove. (e.g., 1 for the first, 2 for the second)

You can copy either of these formulas:

=SUBSTITUTE(A2,”,”,””)	This removes all instances of the comma inside the text.
=SUBSTITUTE(A2,”,”,””,1)	This removes the first instance of the comma.

Change “A2” with the appropriate range address of your cell.

If applicable, change “1” with the instance of the comma that you would like to remove.

Example of using the SUBSTITUTE() function to remove the first instance of comma.

Copy this formula to the remaining cells, and you’re all set.

Remove Commas from a Text in Excel and Separate Data in Different Columns

This method will not only remove the comma from the text but will also split the data and place them each in separate columns.

1. Highlight all cells that you would like to split into multiple columns.

IMPORTANT:

Since this method will split your data into multiple columns, the columns next to the selected cells should be empty and have no data.

You could temporarily copy the cells on a different sheet and just copy them back later into your dataset.

2. Go to the Data tab. From the Data Tools section, click on Text to Columns.

Steps to open the Convert Text to Columns Wizard

The Convert Text to Columns Wizard will appear.

3. Select the Delimited option and click Next.

4. From the list of Delimiters, ensure that only the Comma checkbox is checked and click Next.

5. In the last step of the Wizard, you can configure the data format for each column.

But we will skip this step and leave the default format for now. You can update the number format of the columns later.

Let’s complete the Wizard and click on the Finish button.

Complete the Convert Text to Columns Wizard and click Finish.

6. And that’s it! Your data is now split into multiple columns.

Sample output after splitting data into multiple columns.

Conclusion

As you can see, the approach we use to remove the commas in our cells will depend on the type of data we have on our cells (whether it’s a text or a number). It also depends on whether we would like the data to be split into multiple columns or not.

Whatever your intended purpose may be, I hope you find the suggested methods helpful.

How to Remove Blank Rows in Excel?

Blank or empty rows in between a dataset can be cumbersome to look at and work on.

We often encounter this type of format with datasets exported from a system or a database.

With the blank rows in between, it’s hard to go over the data as you are forced to scroll down just to see the remaining data entries.

You can’t even select all the data by pressing CTRL + A.

Example of what happens when you press CTRL + A to select all cells in the dataset

(Notice that Excel can only select the data entries before the blank rows.)

Also, if you try to apply the filter after selecting the headers, Excel is not able to capture all the records in the dataset (as seen in the example below).

Exmple of what happens when you apply filter after selecting the headers in a dataset with blank or empty rows.

If you want the filter to work correctly with this format, you will have to either select the entire columns or manually select all the records in the dataset – which can be very inconvenient.

In this tutorial, I’ll show you different ways to remove these empty rows so you can easily work on your data.

Use the “Go to Special” option to Remove Blank or Empty Rows in Excel

The first step to removing blank or empty rows is to select the entire dataset.

Since we can’t make use of CTRL + A to select it, we can do either of the following:

a) Select the entire columns of the dataset (as shown below).

b) Or select the first cell and scroll down until you find the last cell of the data set. Then, while pressing the SHIFT key, click on it.

If you don’t have other data at the bottom of the data set, you may want to use the first option since that is the quickest way to select the cells.

After highlighting the cells, press CTRL + G.

The “Go To” menu should appear. Once you see it, click on the Special… button.

Click on the "Special" button from the "Go To" menu.

You will then see the Go To Special menu. Select Blanks and click OK.

Steps to select blank cells from the "Go To Special" menu

Notice that the blank or null cells are highlighted.

Example of what happens when blank or null cells are highlighted

Right-click on one of these highlighted cells and select Delete.

Right-click on one of the highlighted cells and select Delete.

The Delete menu will appear. Select Shift cells up and press OK.

Select "Shift cells up" from the "Delete" menu and click OK.

And voila! You have now removed the blank rows from your dataset.

Note This

Remember to select the Shift cells up option (not Shift cells left).

This step is crucial to ensure that the remaining cells within your data move up after deleting the blank rows.

Example of what happens when you select "Delete Shift Cells Up"

If you select the Shift cells left option, the cells on the right will move towards your dataset.

Example of what happens when you select "Delete Shift Cells Left"

If the cells on the right don’t have any contents (same as the image above), it will look like nothing is happening in the data set. However, if you have other datasets on the right side of your sheet, the datasets will all be mixed up.

You can use the Shift cells left option if you’re trying to delete columns in between the data set.

Using the Auto Filter to Remove Blank or Empty Rows in Excel

Another way to remove blank or empty rows is to use the Auto Filter. We will copy the dataset to a different sheet or workbook. If that’s not a problem, then this would be a good option too.

Select first the cells within the data set.

Same as the steps specified above, you can either:

Select the entire columns of the data set; or
Select the first cell up to the last cell within the data set.

Once the cells are highlighted, apply the filters by going to the Data menu and selecting Filter.

You should then see the filters added to the header row.

Steps to add filter to dataset with blank rows

Once the filters are in place, add a filter so that the blank rows are excluded from the data set.

To do this:

Select the filter in one of the fields.
Uncheck (Blanks) – this will be the last item in the filters.
Click OK.

Select the filter in one of the fields, uncheck "(Blanks)" and click OK.

You will now see the data set without the blank rows in between.

Once you see this, copy the filtered data set (CTRL + C) and paste it (CTRL + V) on a different sheet or workbook.

Using the FILTER() Function to Remove Blank or Empty Rows in Excel

This method is probably my favorite as it involves the least number of steps.

IMPORTANT:

FILTER() is a dynamic array function that only works on Office 365. If you’re using other versions of Office, I’m sorry, but this won’t work for you.

The FILTER() function uses the following syntax: =FILTER(Array, Include, [Empty])

“Array”is the data range to apply the filter to.
“Include” is the logical expression to use to filter the data.
“Empty” is the text you would like to appear if there are no results to display after applying the filter. This field is optional and can be left blank.

On a new worksheet (or on an empty section in the current worksheet), select a cell and type the FILTER() formula.

Add the FILTER() formula to one of the cells.

In my example above, I have used this formula: =FILTER(A:D,A:A<>””)

Change “A:D” with the range address where your dataset is located.
Change“A:A” with a column that can be used for filtering out blank rows.

And that’s it! As you press enter, you will immediately see your dataset without the empty rows.

What’s so cool about this is that if there are new entries added to the specified range, they will be immediately reflected in the filtered dataset.

Sorting the Records to Remove Blank or Empty Rows in Excel

If the previous method didn’t work for you, there’s still an easy way to remove the empty rows with just a few steps.

IMPORTANT: This method requires reordering your dataset. If this is not an issue, please proceed.

1. Select the entire column (or all cells within your dataset).

2. Go to the Data tab and click on the Sort button inside the Sort & Filter section.

3. In the Sort menu, select the field that we will use as the basis for sorting the records (it can be any field).

In the Sort menu, select the field to use as the basis for sorting the records (it can be any field).

You can also change the sort order to either ascending or descending order – it doesn’t matter.

Once done, click OK.

And that’s it! Blank rows will “disappear” from your dataset.

Using Power Query to Remove Blank or Empty Rows in Excel

As mentioned, we mainly get empty rows from files exported from a system or a database.

If you regularly receive these kinds of files and would prefer to have the empty rows automatically removed, then this next method is for you.

1. Set up a Power Query to load the file.

(If you have an existing Power Query already set up on your data, you can skip this step and add the rule specified in step #4.)

On a new workbook, go to Data >> Get Data >> From File >> From Excel Workbook.

2. The Import Data menu will appear. Select the file that contains your dataset and click Import.

Select the file to import from the "Import Data" menu of the Power Query.

3. In the Navigator menu, select the sheet that contains your dataset and click on the Transform Data button.

4. Wait for the data to load.

Once the Power Query Editor appears, click on Remove Rows >> Remove Blank Rows.

5. You will see the empty rows filtered out from your dataset.

Once you see that, click on the Close & Load button.

Once you see the empty rows filtered out from your dataset, click on the "Close & Load" button.

And that’s it! Your Power Query Table is all set up.

Sample output after removing blank rows using Power Query

If you receive a new export file, place it in the same folder as the current file and replace the existing one. Make sure that the filename is the same.

Then, open the workbook with the Power Query Table, right-click on the table and select Refresh to get the latest set of data. The Power Query will automatically remove the empty rows from the dataset.

Conclusion

Blank or empty rows within a dataset are at times inevitable especially if you’re working on an exported file. I hope with the methods suggested above, you will find it easy to remove these unnecessary rows from your dataset.

How to Remove Leading Zeros in Excel?

Excel, by default, automatically removes leading zeroes whenever you enter numbers. So, you might be wondering why there are leading zeroes in the Excel file you received.

Well, there are two possible reasons for that. It could be because:

The cell is in text format.

This can happen if there’s an apostrophe at the start of the cell. Apostrophe (‘) is a special character that tells Excel to treat a cell as a text. If you are working on an Excel file exported from a system, there’s a great chance you’ll encounter this.

Remember that adding an apostrophe is just one way of converting a cell into a text. A cell can also be converted into text by simply changing its number format – so check on that too.

The cell is formatted to have a fixed number of digits.

If you have a 3-digit number entered in a cell that was formatted to always have 5 digits, Excel will automatically fill in the missing 2 digits with leading zeroes.

There are plenty of ways to remove the leading zeroes in Excel. They vary depending on the reason for having them. I’ve arranged the methods below to tackle first the most common causes. I suggest you go through each of them and see what works on your data.

Error Check to Remove Leading Zeroes in Excel

Excel will typically warn you if you have cells that contain numeric values but a formatted as text. You will notice this when you see small green triangles added on the left side of these cells (as shown below).

Small green triangles on the left side of cells appear whenever the cells contain numeric values but are formatted as text

If you don’t see them, it’s most likely that Background Error Checking is disabled on your Excel.

To enable it, simply go to File >> More >> Options.

In the Excel Options menu, select Formulas.

Go to the Error Checking section, tick the “Enable background error checking” checkbox, and click OK.

Steps to enable background error checking in Excel

You should now see the small green triangles on the left side of your cells.

(If you don’t see them still, please proceed to the method that follows after this).

Select all these cells, and a yellow warning icon will crop up on the first cell selected.

A yellow warning icon crops up after selecting cells with green triangles.

Click on that icon and select Convert to Number.

And that’s it! All leading zeroes should now disappear.

Sample output after letting Excel convert cells to number format

With this approach, we have taken advantage of Excel’s default setting of removing leading zeroes from numbers.

Changing the Number Format to Remove Leading Zeroes in Excel

If the previous method didn’t work for you, it might be because your cells are in a “Special” format where leading zeroes are automatically added so that it reaches a particular number of digits.

To change this:

1. Select all the cells with leading zeroes.

2. Go to the Home tab. Look at the current number formatting of these cells from the Number Section. If it is “Special”, change the number formatting to General (or any other number formatting appropriate for your data).

Steps to change selected cells' number formatting

And that’s it! You should no longer see any leading zeroes in your selected cells.

Sample output after changing cells' number formatting to General

Using “Paste Special” to Remove Leading Zeroes in Excel

If you don’t want to alter the cells containing leading zeroes and would only want the numbers on a different column, then you can do the following:

1. Select a blank cell on the worksheet and press CTRL + C to copy it.

2. Next, select all the cells with leading zeroes.

3. Then, press CTRL + ALT + V to open the Paste Special menu.

Select the Add Operation from the Paste Special menu and click OK.

Select Add under the Operation section and click OK.

And that’s it! You should now have removed all the leading zeroes from your cells.

Sample output after using the Paste Special option to remove leading zeroes.

This method worked because it added a zero (or a blank cell) to all the cells with leading zeroes, which in the process, converted the cells into numbers.

Using the VALUE Function to Remove Leading Zeroes in Excel

Another way to remove leading zeroes without altering the original column is to use the VALUE() function.

VALUE() is a text function in Excel that converts a text string that looks like a number into an actual number.

On a new column, type the following formula: =VALUE([range])

As you press enter, the leading zeroes will disappear.

Copy this formula to the remaining rows, and voila! No more leading zeroes for the entire column.

Using Text to Columns to Remove Leading Zeroes in Excel

If you have a lot of rows to remove the leading zeroes from, this may be the best option for you.

1. Highlight the entire column that contains leading zeroes.

Select the entire column with leading zeroes.

2. Go to the Data tab. From the Data Tools section, click Text to Columns.

3. The Convert Text to Columns Wizard will appear.

Select Delimited and click Next.

In the Text to Column Wizard, select Delimited.

4. Uncheck all the Delimiters checkboxes and click Next.

In the Text to Columns wizard, uncheck all "Delimiters" checkboxes.

5. In the Column Data Format, select General.

Notice there’s a note in the Wizard that says:

'General' converts numeric values to numbers, date values to dates, and all remaining values to text.

This is the Text to Column feature that we are making use of.

In the Text to Column wizard, select the General format.

Click Finish to close the Wizard.

You should now see all the leading zeroes removed from your selected column.

Sample output after running the Text to Columns wizard

If you have used Text to Columns before and worry that this may affect other columns alongside the current field, don’t worry. Only the selected column gets updated with the settings we selected in the Wizard.

Remove Leading Zeroes from an Alphanumeric Data

The methods described above involved the conversion of the cell into a number format.

If you are working on data with alphanumeric characters (a combination of both letters and numbers) and you only want to remove the leading zeroes, then this next method is for you.

1. On a blank column, type this formula:

=RIGHT(A2,LEN(A2)-FIND(LEFT(SUBSTITUTE(A2,"0",""),1),A2)+1)

Change all instances of “A2” with the range where your alphanumeric data is.

Add formula to remove leading zeroes from a cell with alphanumeric characters.

2. Once you click enter, the cell should result in an alphanumeric character with no leading zeroes.

3. Copy this formula to the remaining rows in your cells, and you’re all set up.

Conclusion

Leading zeroes are often intentionally added for a particular purpose. However, there are instances when we need to remove them. I hope the methods provided above have helped you do so with ease.