Excel Tips And Tutorials

Data manipulation is one of the most powerful abilities of Excel. Flash Fill is a tool in Excel that enables you to manipulate data in a cell according to your needs.

In this tutorial, we will learn how to switch first and last names in Excel with the help of Flash Fill.

Switch First and Last Name

Now we will walk through the steps of switching the first and last names in Excel, and we will use Flash Fill for this purpose.

Flash Fill works as follows: you define a new pattern, and Flash Fill imitates this pattern for all other cells. In this case, the pattern that we will create is going to be “LastName, FirstName”.

Step 1: Go to a cell that contains a full name. In the cell right next to it, write the pattern you want in a reverse fashion: “LastName, FirstName”.

Be careful that the first and last names are written correctly, just like the original cell.

Step 2: Press Enter on your keyboard to go to the cell below, or click on the cell below the new pattern.

Step 3: In the Home tab, click on the Fill icon in the Editing part in the Excel ribbon, and then click on Flash Fill.

This will automatically fill out all cells next to a full name, and your names will successfully be switched:

Using this method, you can use any pattern that includes a first name and a last name. For example, you can also abbreviate a first name:

As long as you provide a new name format, Flash Fill will automatically learn the pattern and apply it to other cells. It’s like magic, right?

This extremely easy method can enable you to perform all kinds of text manipulation. Learning to use Flash Fill is an excellent skill. Nice job!

You may want to check how to separate or combine first and last names in Excel and then sort by last name in Excel.

Copying filtered data onto a new sheet is a great way to segregate related data rows.

It allows us to have the extracted rows available anytime without re-applying the same filters.

We have to be careful, though — the hidden rows within the dataset might get included in the cells copied if we miss the crucial step of selecting only the visible cells.

In this tutorial, I’ll show you ways to select only the visible cells within a filtered dataset so that you can easily copy and paste them onto a different sheet.

1. Using a keyboard shortcut

1. Select all the cells within your dataset.

You can do this by first selecting one of the cells within the filtered records and pressing CTRL + A to select all.

Your entire filtered dataset should be highlighted (as shown in the image above).

2. Next, press the keyboard shortcut that selects only the visible cells.

Note that the keyboard shortcut varies for Mac and Windows. Please use the table below for reference.

After pressing the appropriate keyboard shortcut, notice that the hidden rows are excluded from the selection. You will see a white demarcation line between rows that have filtered-out rows that follow them.

4. You can now copy the selected cells and paste them somewhere else.

Press CTRL + C to copy.

Notice that the broken lines appear in between rows that have hidden cells after them. These lines let you know that only the visible cells are selected and copied.

5. Open the worksheet (or workbook) where you would like to paste the filtered cells.

Select a cell and press CTRL + V to paste.

And that’s it! You have your filtered cells copied onto a new worksheet or workbook.

2. Using the Go To Special menu

If you are not much of a fan of keyboard shortcuts and prefer doing tasks with your mouse, you can also select visible cells using the Go To Special menu.

1. Highlight all the cells within your filtered dataset. (Select one cell within the dataset and press CTRL + A to select all).

2. From the Home tab, go to Find & Select and click on Go To Special.

3. The Go To Special menu should appear.

From the list of options, select Visible cells only, then click OK.

4. That’s it! You now have only the filtered cells selected.

5. You can now proceed with copying it to a new worksheet or workbook.

3. Using the QAT command

If you regularly copy filtred cells, there is an even quicker method for you to select visible cells. All you need to do is add a Quick Access Toolbar (QAT) command that you can readily click whenever you need to select the visible cells.

To do this:

1. Click on the Customize Quick Access Toolbar button right above the Home tab.

This button has an inverted triangle with a thin line on top (as shown in the image below).

2. From the list of options that appear, select More Commands.

3. The Excel Options menu will appear with the Quick Access Toolbar tab opened.

Select All Commands from the “Choose commands from” dropdown menu.

4. Wait a bit until the list of commands under the dropdown menu gets updated and looks something like this:

5. Scroll down until you find the Select Visible Cells from the list of commands.

Once you find it, select it, and click on the Add >> button.

6. You should now see the Select Visible Cells command added to the list box on the right.

This list box contains all the commands added to your QAT.

After adding the command, click OK.

7. That’s it! You now have the Select Visible Cells in your top left toolbar.

Click on this button whenever you want to only select the visible cells in your worksheet.

To use it in your filtered dataset:

1. Select all the cells in your dataset.
2. Click on this button. Notice that Excel immediately selects the visible cells.
3. You can now press CTRL + C to copy the visible cells and paste them onto a new sheet.

Conclusion

Excel’s data filter is a great way to sift through your records and only capture the set of data that meets the criteria that you have set. But please remember that when you copy a filtered data set, you must ensure that only the visible cells are selected. You can choose from any of the methods above to do this.

When working with exported data, there will be times when we will have only the month and year included in the dataset (the day is nonexistent).

It was okay until you realized that, for very particular reasons, you must turn these two bits of information into an actual date.

In this tutorial, I will teach you how you could convert your month and year to an actual date. Also, in the latter part of this article, I will teach you how to do it in reverse – from date to month and year.

Steps to convert month and year to date in Excel

Since I have no idea how your month and year are currently formatted, we have to do some data prep to ensure that we are working on the same data format.

Our goal is to have our month and year look something like this:

* The month is in numerical format (1 to 12).

* The month and year are on separate cells.

If your data already looks something like the image above, you can skip the following data prep sections.

Data Prep: Split Month and Year and place them on separate cells using the “Text to Columns” option

We have to split our Month and Year and place them in separate cells.

If they are joined together by a space, a dash (-), a slash (/), or any other symbol (similar to the image below), the fastest way to split them is by using the “Text to Columns” option.

To do this:

1. Highlight all the cells containing the year and month. Do not include the header.

2. From the Data menu, click on Text to Columns.

3. The Convert Text to Columns Wizard will appear. From the list of options, select Delimited. Then, click Next >.

4. Now, select the appropriate delimiter for your data.

In my example, a space separates the month and year (e.g., Jan 2012).

Since this is the case, I have unchecked all other delimiters and only ticked the Space checkbox.

You know you have selected the correct delimiter once you see your month and year on separate columns in the Data preview.

If your delimiter doesn’t exist in the list of available options, tick the Other checkbox and type the symbol that separates your month and year.

Once you’re happy with the result, click Next >.

5. You should now reach the final step in the Wizard.

We are not going to change anything on this step. Just click the Finish button.

That’s it! You should now see your month and year in separate columns.

Data Prep: Split Month and Year and place them on separate cells using the LEFT() and RIGHT() Excel formulas

If your month and year are on a single cell but don’t have a space or any symbol in between them, we are to split them using Excel formulas.

Below are some of the sample formulas that you can use. Note that the following formulas assume that your month and year are on cell A2.

If your month comes before your year, you only need to swap the LEFT() and RIGHT() functions and adjust the character length accordingly.

Once you’ve added the appropriate formulas, copy them and paste them as values.

To do this, highlight all cells containing formulas and press CTRL + C. Once the cells are enclosed with broken lines, press CTRL + ALT + V. From the Paste Special menu, select Values, and click OK.

Data Prep: Convert the Month in Text Format (e.g., February or Feb) to Numerical Format (e.g., 2)

Now that we have the month and year in separate columns, we move on to the last step for Data Prep.

You may skip this step if your month is already in numerical format (1 to 12).

But if it is in text format (e.g., February, Feb), please follow these steps:

1. Insert a new sheet.

2. Copy your Month column and paste it into column A of the new sheet.

3. On cell B2, add the following formula:

=IF(LEFT(A2,3)="JAN", 1, IF(LEFT(A2,3)="FEB", 2, IF(LEFT(A2,3)="MAR", 3, IF(LEFT(A2,3)="APR", 4, IF(LEFT(A2,3)="MAY", 5, IF(LEFT(A2,3)="JUN", 6, IF(LEFT(A2,3)="JUL", 7, IF(LEFT(A2,3)="AUG", 8, IF(LEFT(A2,3)="SEP", 9, IF(LEFT(A2,3)="OCT", 10, IF(LEFT(A2,3)="NOV", 11, IF(LEFT(A2,3)="DEC", 12))))))))))))

This formula will get the numerical value of the month.

4. Copy cell B2 and paste it onto the remaining rows.

5. You should now see the corresponding numerical values of your months.

6. Once you’re happy with the result, highlight all the cells with formulas in column B and copy them (press CTRL + C).

7. Go back to your original sheet. Click on the first cell in your Month column and press CTRL + ALT + V. The Paste Special menu will appear. Select Values and click OK.

8. That’s it! Your month should now be in numerical format.

Using the DATE() Formula

Once our Month and Year columns resemble the image below, we have completed the data prep.

We will now proceed to the main course – the actual conversion of month and year to date.

1. Add another column beside YEAR. We can name it DAY.

In this column, enter the number you intend to set as the day for the dates. It could be any number from 1 to 31.

In my example above, I’ve added the first and last day of the month as my days.

2. Next, add another column after DAY. We can name it DATE.

This is where we’ll add the DATE() formulas to generate the dates based on the month, day, and year specified. 

3. In cell D2, add this formula:

=DATE(B2, A2, C2)

Don’t worry if you have sorted your fields in a different order. You can change the formula accordingly.

You only have to remember that the DATE() function gets the following parameters (in this order): year, month, and day.

4. Copy cell D2 to the remaining cells in the DATE column.

5. That’s it! You should now have your actual dates.

Remember to choose the number for your DAY wisely if you want to have the dates correctly reflect the Month and Year.

It may not be ideal to choose 31 as the day of the dates, considering that not all months have 31 days.

In my example above, notice that in cell D8, the result was 7/1/2010 even if the month, day, and year are 6, 31, and 2010 respectively. It should have resulted in 6/31/2010, but since this is not a valid date, Excel automatically gets the next closest day — 7/1/2010.

6. Once you’re happy with the outcome, copy the DATE column and paste it as values in your original dataset.

Steps to convert date to month and year in Excel

To reverse the process and convert the date to month and year, you can do either of the following options.

If you want to have month and year in separate columns, use the following formulas:

(Note that the sample formulas below assume that your date is in cell A2). 

If you want to have the month and year merge and share the same column:

Conclusion

Converting dates can be tricky if you’re not adept with the options available in Excel. But I hope the suggestions above will help you easily convert dates without much hassle.

Data sorting (or the process of arranging data based on a field or a set of fields) allows us to analyze data more effectively.

If you want to temporarily sort your data in a particular order but want to be able to revert to the original data arrangement anytime, you’re in the right place.

In this tutorial, I’ll show you ways to unsort or revert to the original data order in Excel.

Revert to the Original Data by Undoing the Data Sort

If you have just applied data sorting to your data by dates (and have not closed the file yet), you can go back to the original data arrangement by simply undoing the steps.

To do so, just press CTRL + Z (or Command + Z for Mac).

You may need to do it a number of times until you see your data sorted back to its original state.

IMPORTANT:

• If you have performed other tasks in the file after data sorting, these actions will have to be undone too.

• Please note that by default, Excel only allows up to 16 actions that can be undone. If you have done more than 16 actions post-data sorting, then this method may not work.

• You may need to find a way to acquire a copy of the data in its original arrangement or manually re-arrange the data. Once you do so, you can refer to the succeeding methods to revert to the original order anytime.

Revert to the Original Data using a “Sort Order” Column

This method will work if you have not sorted your data yet – meaning it’s still in its original state.

1. Before applying any data sort, insert a new column next to your data.

We’ll name this helper column ‘Sort Order’.

If your data is on a table, the new column will automatically be added as part of the table (as shown below).

If not, you must reselect the entire data (including the new column) before sorting.

2. Next, we’ll add sequential numbers to this column – starting with 1.

By now, you may notice that we are adding this column to record the current order of the data rows.

Type ‘1’ in the first row and drag the fill handler down (up until the last row).

Notice that the column is now filled with 1’s.

Click on the Auto Fill Option that appears and select Fill Series.

The sequential numbers should now appear in the column.

Now, you’re all set!

3. You can now continue sorting the data however you like – you can sort them alphabetically, sort them by last name, or in ascending or descending order based on the numbers.

4. Once done, you can revert to the original data order by simply sorting the data using the Sort Order column we have added. That’s it!

You can hide this column (or delete it) when you no longer need it.

Revert to the Original Data using a Data Backup

If you don’t like adding a new column to your dataset, you can do the most basic technique to easily revert to the original data – create a copy of the worksheet or the file prior to data sorting.

To create a copy of the worksheet:

1. Right-click on the sheet and select Move or Copy.

2. The Move or Copy menu will appear.

Select where you intend to insert the copied sheet (see Before sheet).

Tick the Create a copy textbox and click OK.

3. And that’s it! You have successfully created a copy of your dataset.

You can now proceed with applying your desired data sort.

IMPORTANT: Remember to apply the data sort on the duplicate copy (not the original one).

To create a copy of the file:

1. Close the workbook.

2. Right-click on the file and select Copy.

3. Paste it on the same folder (or on a different folder, if you like).

Right-click on a blank space inside the folder and select Paste.

You can rename the file if you want.

And that’s it! You can now proceed with applying your desired data sort.

Just remember to do it on the duplicate file, not on the original one.

Conclusion

Sorting data has many uses – it helps us understand, for example, which group performed better based on the numbers. We can also use it to quickly find records after sorting them alphabetically using custom autofill.

Whatever the purpose, there will be instances when we also need to go back to the original arrangement of the data. I hope the suggested methods above help you do so with ease.

Excel is a great way to store, compute, and sort information; however, there may be times when you want to save the tables in Excel as images for use on the web, brand print material, or for training.

Use the Copy Feature in Excel to Save an Excel Table as an Image

1. Highlight the table that you want to save as an image.

2. Click the down arrow next to the Copy button in the Clipboard group and click the “Copy as Picture…” selection.

3. Open up your favorite image editor. You can use Paint™, Paint 3D™, Photoshop™,  GIMP™, or Photos™ on Mac™. For this tutorial, we will use Paint™.

4. Click the Paste option in Paint, and the image appears on the canvas.

5. Resize the canvas by moving the small white rectangles on the canvas with the mouse so that they match the size of the picture.

6. Edit the image as needed and save the file.

Use the Print Screen Button to Save an Excel Table as an Image

1. Open the worksheet you wish to save as an image and press the Print Screen (PrtScr) button on your keyboard.

2. Open a new canvas in your image editing software, and press CTRL + V or CMD + V if you use a Mac.

3. The screenshot will appear on the canvas and will need editing to isolate the Excel table from the complete image.

Note: if your keyboard does not have a Print Screen button, Windows users can use the Fn + Windows Logo Key + Space Bar combination to take a screenshot. Mac users can take a screenshot with CMD + Shift + 3 together.

Use Shortcut Keys to Save an Excel Table as an Image 

1. Once your worksheet is open in Excel, press CTRL + C or CMD + V for a Mac to store the picture in the computer’s memory.

2. Open your image software and press CTRL + V or CMD + V on a Mac to paste the picture on the blank canvas.

3. Edit the picture to fit your needs.

Conclusion

Now you have discovered the different methods for saving an Excel table as an image. Explore the many other topics on our blog to continue increasing your knowledge of Excel.

You can also save the data or workbook without formulas if you want another option of saving the Excel table.

Have you ever imported data to Excel from an external source with many unwanted hyperlinks that came along?

Also, you might have come across multiple undesired hyperlinks automatically generated by Excel for emails and URLs inserted into a workbook.

Unwanted hyperlinks can cause undue trouble. Every time you select a cell containing a hyperlink, excel would automatically redirect you to the destination link.

Hyperlinks that are not needed can be removed from Excel with sheer ease.

Continue reading to know how you can instantly remove hyperlinks in Excel.

Remove Hyperlink in Excel

To better learn the easy two-step process of removing hyperlinks from selected cells in Excel, let’s stipulate an example below.

Remove hyperlinks from selected cells

The data set below collates different links containing information relevant to various topics. As soon as the links are input, Excel recognizes them as hyperlinks and formats them into blue color and underlines them.

To remove the hyperlinks from only some selected cells, here are the steps that you need to follow.

Step 1:

Select the cells containing the hyperlinks that you want to be removed.

Step 2:

Right-click by keeping the cursor on the selected cells to see the pop-up menu as below.

From the pop-up menu that opens up, select the option, ‘Remove Hyperlinks’.

Excel removes hyperlinks from the selected cells as evident below.

That is it. Removing hyperlinks from Excel is only that easy.

However, what if you have an Excel file that is densely packed with hyperlinks? Is it not hectic to select each cell to remove the hyperlink therefrom? It definitely is. Here is a shortcut that may help you with it.

Remove all hyperlinks together 

Continuing with the same example stipulated above, to remove hyperlinks from the entire spreadsheet at the same time, follow the steps below.

Step 1:

Press ‘Ctrl+A’. This is the shortcut to select all the contents of an active sheet.

Step 2:

Right-click against the selected cells to have the pop-up menu opened. Opt for ‘Remove Hyperlinks’ as follows.

Excel removes hyperlinks from the entire spreadsheet as follows.

Pro Tip: Prevention of Automatic Navigation to the Destination Link

As soon as you select a cell that contains a hyperlink, you will be navigated to the destination link. Or in case of an email address, Excel would present you with the option to send an email to the subject address.

If Excel redirects you to the destination link automatically upon selecting a cell, you may turn it off as follows.

File > Options > Advanced > Editing options

Check the option for 'Use CTRL + Click to follow hyperlink'. Once this is done, Excel would present you with the option to press ‘Ctrl’ to follow a link. Merely selecting a cell in Excel wouldn’t navigate you to the destination page.

Suggested Tutorial: How to Extract URL from Hyperlinks in Excel

Put Off Automatic Hyperlinks in Excel

It is often the case that you’ve to work with Excel files containing excessive Hyperlinks. For example, the Human Resource department of a Company may have to maintain a record for all of its employees, including their names, numbers, and email addresses.

Whenever fed into Excel, email addresses are recognized by Excel as hyperlinks. If you don’t want the file to contain hyperlinks, such settings of Excel might prove problematic. This is because every time you feed the details of a new employee into the Excel sheet, Excel would automatically turn the email address into a hyperlink.

Removing each hyperlink time and time again is time-consuming and frustrating. To prevent Excel from generating hyperlinks automatically, you can disable the same from the settings. Here’s how you can do it.

Step 1:

Go to File > Options as follows.

Step 2:

Selecting options would open up a window of Excel options as follows.

From the said window, choose Proofing > AutoCorrect Options as highlighted above. This would open up a pop-up window.

Choose ‘AutoFormat as you type’ and uncheck the box for ‘Internet and network paths with hyperlinks’ as highlighted below.

This will turn off the automatic hyperlinking within Excel. Now, as you type or Copy / Paste links in Excel, those would remain as text, and Excel would not automatically turn them into Hyperlinks.

Pro tip: This setting will be applied to the entire Excel application. Any workbook that you create or edit with Excel will have the same settings applied.

However, after these settings are applied, you can still add hyperlinks to one or more cells by right-clicking on the desired cell. From the pop-up menu that then opens up, select ‘Link’. This would open up a dialogue box where you can insert the destination link to be added as hyperlink to that cell.

Conclusion:

Hyperlinks make an intelligent feature of Excel. However, undesired hyperlinks are often a nuisance. Hope this article helped you learn all that you needed to know how to remove hyperlinks in Excel.

If you’ve ever worked in an HR role, you know how challenging it can be to maintain a large volume of personal details for multiple employees, including their CNIC numbers, IBANs, Passport numbers, or even cellphone numbers.

All of these numbers are often accompanied by dashes that may appear in the center, at the beginning, or the end of the number.

Manually removing the dashes is definitely not an option as this could take hours or even days if dealing with a substantial amount of data. Additionally, there is a high chance of data loss or errors due to accidental backspacing and clicks.

3 Methods to Remove Dashes in Excel

1.  Find and Replace Function

The very well-known ‘Find and Replace’ function can help you remove dashes from Excel only in a few clicks. Take a look below to see how.

The data above represents the IBANs for a list of employees. As evident, these numbers are paired with multiple dashes that must be removed.

To do so, simply select the Column containing the dashes (i.e. Column B) and go to the Find and Replace function as follows.

Home > Editing > Find & Select > Replace

Pro Tip: Press Ctrl + H to instantly open up the Find and Replace dialogue box in Excel.

Against ‘Find’, enter dashes (-), whereas, leave the box against ‘Replace’ vacant as we want the dashes to be removed.

Hit ‘Replace All’ to have the results as follows.

Related Tutorial: How to Remove Commas in Excel

2.  The Substitute Function

Next on the list is the Substitute Function that works on the pattern of the ‘Find and Replace’ Function. Syntax of the Substitute Function reads as follows.

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

• Text: The text where changes are to be made.
• Old_text: The text to be replaced
• New_text: The text to be replaced with
• Instance: Optional. The instance where changes are made. All instances are replaced if omitted.

You may access the Substitute function from the Functions Library as follows.

Home > Editing > Find & Select > Replace

In the example given above, to use the substitute function to remove dashes from Employee IBANs in Excel, compose it as follows.

=SUBSTITUTE (B2, “-“, “”)

Where B2 defines the cell containing dashes. The Old text is set as “-“, and the new text is left blank “” as we want the dashes to be replaced with nothing but removed.

Note: Both the strings for the Old and New text must be enclosed in double quotation marks.

Hit Enter to see the results and drag the fill-handle to yield similar results against the whole list of IBANs.

Related Tutorial: How to Remove Leading Zeroes in Excel

3.  Kutools

The third and last option on this list involves Kutools. Putting in formulas to remove dashes may prove a little hectic and technical for new Excel users. Kutools offer an in-built tool for character removal from Excel.

Continuing the same example as above, to remove dashes between IBANs from Excel, follow these simple steps.

Step 1:

Select the Column containing the dashes i.e. Column B and then,

Go to Kutools > Text > Remove Characters

This opens up the ‘Remove characters’ window, as shown below.

Step 2:

Under the option to ‘Remove characters’, choose Custom and select dash (-) from the dropdown menu.

Take a look at the data post-dashes removal through the Preview Pane on the right and Hit ‘Okay’ if everything looks good.

Excel filters out dashes from the selected range, as shown below.

Note: Kutools is an add-in to Excel. If you do not see the tabs ‘Kutools’ or ‘Kutools Plus’ on your Excel Ribbon, you probably do not have it installed in your Excel. But no worries, you can easily download it from the web and have it added to your Excel.

Bottom Line

Try either of the above methods to remove unwanted dashes from your dataset in Excel. Or try installing Kutools to ease the job through an in-built character removal function.

Suggested Tutorial: How to Strikethrough in Excel?

Data exported from a system are usually in a fixed report format. Sometimes though, it even includes unnecessary rows in between the data.

In my example below, notice that there’s a total row added between each record.

These rows may be helpful in certain instances. But if you intend to create charts from these data or maybe add formulas in your file to do some calculations, these rows can be bothersome.

You can manually delete these rows, but if you’re working on large datasets, that would be too much work.

In this tutorial, I’ll show you ways to mass delete every other (or every nth) row in Excel.

Before we start, please ensure that there is no other data next to the dataset. Otherwise, they may get deleted in the process. You may want to create a backup of the file first or copy the dataset onto an empty sheet.

Delete every other row in Excel by filtering odd or even rows 

This method is perfect if you want to delete odd or even rows in your dataset.

1. On a new column next to the dataset, type the following formula:

These formulas will return TRUE if the current row is odd or even (depending on the function you have used).

We want to mark the rows we will delete with TRUE, so be sure to add the right formula.

2. Drag the Fill Handler until you reach the last row in your dataset. Doing so will copy the formula to the remaining rows.

3. Once you have added all formulas, check if all the rows you intend to delete are marked as TRUE.

If you see that the “row markers” are correct, it’s time to filter the data.

4. Select all the cells within your dataset (including the header).

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

5. Go to the Data tab and click on the Filter button under the Sort & Filter section.

You should see the filter buttons added on the header row.

6. Click the filter button on top of the formula we have previously added, uncheck the FALSE option, and click OK.

This will filter the records to only display rows marked as TRUE.

7. Highlight all the cells in your dataset that are marked as TRUE.

Remember not to include the header row.

8. From the Home tab, click the Find & Select button and click Go To Special.

9. In the Go To Special menu, select ‘Visible cells only’ and click OK.

10. Notice that the rows in your dataset are now individually selected.

11. Right-click on one of these rows and select Delete Row.

12. A prompt will appear to confirm the process. Click OK to continue.

Remember that this action deletes the entire row, which means any data next to the dataset gets deleted.

13. And that’s it! You have successfully removed all the selected rows.

14. Let’s now remove the filters to view the dataset again.

From the Data tab, click on the Filter button.

15. You now have successfully deleted the odd or even rows from your dataset.

Remove the formulas you have added as “row markers” and you’re all set.

Delete every nth row in Excel by filtering the nth row

If you intend to delete every third, fourth, fifth, etc., row in your dataset, then this method is for you.

We are going to follow the same steps listed in the previous method, except that we will use a different formula to mark the rows we are going to delete.

1. On a new column next to the dataset, type the following formula:

=MOD(ROW()-[header row],[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 delete. So, if you’d like to delete every 3^rd row in your dataset, set this to 3.

Examples:

In case you don’t know, MOD() is a function in Excel that returns the remainder of the number divided by a number.

With the formula above, we are looking at marking the rows that we intend to delete with 0 (which means that the row is divisible to the nth row we have set).

2. Copy the formula to the remaining cells in the worksheet.

See if all the rows you intend to delete are marked with zero. If not, you may need to adjust the formula.

3. Select a cell within the dataset and press CTRL + A. Doing so will select all items in the dataset.

4. Go to the Data tab and click on the Filter button under the Sort & Filter section.

The filter buttons will appear on the header row of your dataset.

5. Click the filter button on top of the formula we have just added.

Adjust the filters so that only zero (0) appears in the dataset.

6. Once the filters are applied, highlight all visible rows in the dataset (those that are marked with 0). Remember not to include the header in the selection.

7. Go to the Home tab, click the Find & Select button, and click Go To Special.

8. In the Go To Special menu, select ‘Visible cells only’ and click OK.

9. The rows in your dataset should now be individually selected.

10. Right-click on one of these rows and select Delete Row.

11. A prompt will appear warning you that this action will delete the entire row. Click OK to continue.

12. And that’s it! The rows that you have selected are now deleted.

13. To view the dataset again, remove the filters by going to the Data tab and clicking on the Filter button.

14. You now have a “clean” dataset. Remove the formulas you’ve previously added, and you’re all set.

Related Tutorial: How to Delete Columns in Excel

Delete every other row in Excel using a VBA Code

If you regularly need to delete every other row in Excel, it may be best to have a macro do this for you.

1. While your Excel file is open, press ALT + F11. This should open the VBA Editor.

2. Go to the Insert menu and click on Module.

3. On the new module added, paste the following code:

Option Explicit

Sub DeleteEveryOtherRow()
    Dim rng As Range, nthRow As Long, ctr As Long, lastrow As Long
    
    On Error Resume Next
    ThisWorkbook.ActiveSheet.ShowAllData
    On Error GoTo 0
    
    On Error Resume Next
    Set rng = Application.InputBox(prompt:="Select your entire dataset (excluding the headers):", Title:="Range Selection", Type:=8)
    If Err.Number > 0 Then Exit Sub
    On Error GoTo 0
    
    nthRow = Application.InputBox(prompt:="Every which row should be deleted? (e.g., 1 for first, 2 for second)", Title:="Row to be Deleted", Type:=1)
    If nthRow <= 0 Then Exit Sub
    
    With ThisWorkbook.ActiveSheet
        lastrow = rng.Rows.Count
        nthRow = nthRow * -1
        
        For ctr = lastrow To 1 Step nthRow
            rng.Rows(ctr).Delete
        Next
    End With
End Sub

Now, it’s time to test the code. But before we do that, please create a backup of your dataset. Remember that changes done by a macro cannot be undone.

To run the code:

1. Click on any line on the code and press F5.

2. A prompt will appear asking you to select your entire dataset.

3. Go back to your workbook and select from your worksheet the range that contains your dataset.

IMPORTANT: The headers should not be included in your selection.

4. Once you have selected the entire dataset, click OK.

5. Another prompt will appear asking you to enter ‘every which row you would like to be deleted’.

In my example above, I typed ‘3’ because I wanted to delete every third row in my dataset.

6. After typing your row number, click OK.

7. And that’s it! Every nth row in your dataset should now be deleted.

To reuse this code next time, remember to save the workbook in .xlsm or .xlsb format.

Also, for easier use, you may want to add a new button in your worksheet and link the button to the code that we have added. This way, you don’t need to open the code each time you want to run it.

Conclusion

As you can see, deleting every other row (or every nth row) is quite simple. You only need to add the correct formula for Excel to determine which row to delete. After that, you only need to filter the records based on these “row markers”, delete the visible rows, and you’re all set!

Related Tutorials:

Insert the Same Text in Every Other Row in Excel

How to Alternate Row Colors in Excel

How to Remove Blank Rows in Excel

How to Copy Row Height in Excel