How to Select Only Filtered Cells 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.

Steps to select all cells in a filtered data set

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.

Computer SystemKeyboard ShortcutNotes
WindowsALT + ;ALT key and semicolon (;).
MacCMD + SHIFT + ZCommand and shift keys and letter Z.

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.

How the filtered cells look like after selecting only the visible cells

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

Press CTRL + C to copy.

Copy visible cells on a filtered dataset

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.

Filtered cells copied onto a new worksheet or workbook

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).

Highlight all cells within the filtered dataset

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

Steps to access the "Go To Special" menu

3. The Go To Special menu should appear.

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

Select the "Visible cells only" option from the Go To Special menu

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

How the filtered cells look like after selecting only the visible cells

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).

How to access the "Customize Quick Access Toolbar" button

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

Select "More Commands..." from the list of options under the "Customize Quick Access Toolbar"

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

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

Select "All Commands" from the "Choose commands from" dropdown menu in the Quick Access Toolbar

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

How the "Customize Quick Access Toolbar" menu looks like after selecting "All Commands" in the list of commands to choose from

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.

From the list of commands, look for "Select Visible Cells". Once you find it, 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.

How the Customize Quick Access Toolbar look like after adding the "Select Visible Cells" command

After adding the command, click OK.

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

The "Select Visible Cells" button added in the Quick Access Toolbar (QAT)

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.

How to Convert Month and Year to Date in Excel?

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:

Target output of data prep

* 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.

IMPORTANT:

Before doing the data prep, please copy your Year and Month column to a new sheet and perform the data prep steps there. We want to ensure that your dataset is safe from accidental alterations.

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.

Sample Year and Month that are merged with a space or symbol in between

To do this:

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

Highlight the cells in the Year and Month column

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

Steps to access the "Text to Columns" option

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

Select "Delimited" from the list of options in Convert Text to Columns Wizard

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.

Select "Space" as the delimiter

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.

Add slash (/) as delimiter

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

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

Last step in the Convert Text to Columns 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.

Sample output when year and month are separated by the Text to Columns option

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.

Sample Year and Month merged without any space or symbol in between

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.

SAMPLE YEAR AND MONTHFORMULA TO EXTRACT MONTHFORMULA TO EXTRACT YEARNOTES
201201=RIGHT(A2,2)=LEFT(A2,4)The year and the month have a fixed number of digits (4 and 2, respectively).  
20121=RIGHT(A2, LEN(A2)-4)=LEFT(A2,4)The year has a fixed number of digits (4), while the month has either 1 or 2.  

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.

Copy and paste the resulting month and year as values

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.

Copy 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.

Add the formula for getting 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).

Copy all cells with formula

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.

Steps to paste as values

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

Month in numerical format

Using the DATE() Formula

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

Target output of 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.

Enter the number you intend to set as Day for the dates

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.

Sample output after applying the DATE() formula

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). 

MONTHYEAR
=MONTH(A2)
Results in the numerical value of the month.  
=YEAR(A2)
Results in the 4-digit value of the year.
=TEXT(A2, “mmm”)
Results in the first three letters of the month (e.g., Jan, Feb).  
=TEXT(A2, “yyyy”)
Results in the 4-digit value of the year.
=TEXT(A2, “mmmm”)
Results in the complete name of the month (e.g., January, February).  
=TEXT(A2, “yy”)
Results in the last two digits of the year (e.g., 22 for 2022)

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

MONTH AND YEARRESULT
=YEAR(A2) & “-“ & MONTH(A2)
Combine the Year and Month with a dash (-).  
2022-01
=MONTH(A2) & “/” & YEAR(A2)
Combine the Month and Year with a slash(/).  
01/2022
=TEXT(A2, “yyyymm”)
Combine the Year and Month without a space or symbol in between.  
202201

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.

How to Unsort in Excel: Revert to Original Data

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.

PRO TIP:

If you want to view again the data sort that you have previously applied, you can press CTRL + Y to redo the steps.

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).

Add the 'Sort Order' column in your dataset.

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).

Drag the Fill Handler down (up to 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.

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

The sequential numbers should now appear in the column.

Sequential numbers appear after selecting Fill Series in the Auto Fill Option.

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.

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

2. The Move or Copy menu will appear.

Steps to create a copy of the selected worksheet.

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.

Right-click on 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.

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.

How to Delete Every Other Row in Excel? (Also Every nth Row)

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.

An example of a dataset with rows to be deleted in between

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:

To get the odd rows:=ISODD(ROW())
To get the even rows:=ISEVEN(ROW())

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.

A cell with the ISODD(ROW()) 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.

Drag the Fill Handler down up to the last row in dataset to apply the same formula.

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

Check if all rows that 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).

Select all the cells within the 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.

Steps to add filter to the selected cells.

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.

Uncheck 'FALSE' in the filter list 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.

Highlight all cells 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.

Steps to open the Go To Special menu.

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

Select 'Visible cells only' from the Go to Special menu.

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

Example of how visible rows in filtered dataset are individually selected.

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

Right-click on of the visible rows and select 'Delete Row'.

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

Warning prompt that appears when you delete a row in Excel.

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.

Example of what happens when you delete filtered rows.

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

From the Data tab, click on the Filter button.

Steps to remove the filters in selected cells.

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

Sample output after deleting odd rows in Excel.

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 3rd row in your dataset, set this to 3.

Examples:

=MOD(ROW()-1, 3)This formula gets every third row in the dataset if the header row is in row 1.
=MOD(ROW()-2, 3)This formula gets every third row in the dataset if the header row is in row 2.
Cell with formula for deleting every 3rd row on a dataset whose header starts on row 2.

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.

Copy 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.

Select a cell within the dataset and press CTRL + A to select all items in the dataset.

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

Steps to add the filter to selected cells.

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.

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.

Once the filters are applied, highlight all visible rows in the dataset (those that are marked with 0).

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

Steps to open the 'Go To Special' menu

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

Select 'Visible cells only' from the 'Go To Special' menu.

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

Example of what happens when only visible cells are selected in a filtered dataset.

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

Right-click on one of the selected 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.

Sample output of what happens once filtered rows are deleted.

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

Sample output removing the filters.

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.

Steps to insert a new module in the VBA Editor.

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.

Steps to run a code from the module.

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

First prompt that appears once the code runs.

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.

Select all the cells within your dataset (except for the headers).

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’.

The second prompt that appears once the code runs.

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.

Sample output after running the code.

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

How to Alternate Row Colors in Excel?

Adding alternate row colors in your data is a great way to enhance its readability. It makes it easier for your intended audience to find a record and see its related information. Plus, it’s so nice to look at!

When you look at the image below, I’m pretty sure your eyes are immediately drawn to the table with alternate row colors.

Sample dataset with alternate row colors and without

Adding alternate colors (also known as color banding) can immediately amp up your data and make it more professional-looking.

Before you proceed, you might want to freeze the top rows first so you get to see the header names as you scroll down the page.

1. Alternate Row Colors in Excel by Formatting the Cells as a Table

This method is probably my favorite as it offers preset formats that you can readily choose from and only involves a few steps!

1. Select your entire dataset.

Select entire dataset

2. From the Home menu, go to the Styles section and click on the Format as Table button.

Steps to format selected cells as table

A list of preformatted table designs will appear. Select the design of your liking – one with alternate row colors.

3. Once selected, the Create Table prompt will appear. Click OK to continue.

The Create Table prompt will appear. Click OK to continue.

NOTE: If you don’t want to convert your cells into a table, don’t worry. We can easily convert them back to regular cells later on.

4. And that’s it! You already have your data in alternate row colors. Easy-peasy, right?

Sample output after formatting selected cells as table

What’s great about having your data in table format is that when you add new rows at the bottom or delete rows in between, Excel automatically adds or adjusts the alternate colors.

QUICK TIP:

If you want to add the alternate colors to the columns, instead of rows, you only need to:

  1. Select a cell within the table.
  2. Go to the Table Design menu.
  3. From the Table Style Options, uncheck the Banded Rows checkbox and tick the Banded Columns checkbox.
From the Table Style Options, uncheck the Banded Rows checkbox and tick the Banded Columns checkbox.

You’ll then have alternate colors for your columns (as shown below).

Sample output after alternating column colors

To convert your table back to the normal range:

1. Right-click on any cell within the table.

2. From the dropdown list, select Table >> Convert to Range.

Steps to convert table back to normal range

3. A prompt will appear to confirm the process. Click Yes to continue.

4. That’s it! Your cells are now converted back to a normal range with the table design still intact.

Note that if you add new rows to the range, the alternate row colors are no longer automatically added. Use the Format Painter to copy the cell format from the existing rows to the new ones.

2. Alternate Row Colors in Excel Without a Table by Adding Conditional Formatting

This next method may involve more steps, but it allows you to pick the alternate colors to apply upfront.

Also, this gives you the option to specify how many rows before an alternate color is added. (More about this in the latter part of the article).

1. Select the cells you want to apply the alternate row colors to.

If you like, you can select the entire column where your data are so that alternate colors are still added to the bottom rows even if they have no data yet.

Or you can opt to hide the rows containing blank cells for a cleaner look.

Select the entire column where your data are so that alternate colors are still added to the bottom rows even if they have no data yet.

2. From the Home menu, go to the Styles section and click on the Conditional Formatting button.

Steps to add Conditional Formatting

From the list of options, select New Rule…

3. The New Formatting Rule form will appear.

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

Steps to add a conditional formatting based on a formula

Once selected, type either of these formulas in the formula textbox:

  • =MOD(ROW(),2)=0
  • =ISEVEN(ROW())

These two formulas are only checking if the current row is even.

If this condition is met, Excel will apply the cell format that you will specify next.

Click on the Format… button.

The Format Cells menu will appear.

Select the cell color for the alternate rows

Go to the Fill tab and select the color you would like to apply to the rows in even numbers.

The selected color will appear in the Sample section at the bottom. If you’re happy with it, click OK.

Click OK again to the New Formatting Rule menu.

That’s it! There’s now a cell color to your rows in even numbers.

Sample output after applying the conditional formatting -- alternate row colors

Pretty cool, right? You can stop here.

But if you would like to add color to the other set of rows, that is, the odd number rows, redo the same steps as above, except that in the formula textbox, type either of the following formulas:

  • =MOD(ROW(),2)>0
  • =ISODD(ROW())

These formulas will check if the current row is an odd number.

2.1. Add alternate color to every nth number of rows

There are certain types of datasets where two or more data rows are referring to just one record.

For these kinds of datasets, it would be ideal to highlight multiple rows at a time so that it would be easier to identify rows that are related to each other.

In my example below, there are 3 members for each group – which is equivalent to 3 rows per record.

Sample dataset with fixed number of rows per record

Note that the number of rows per record is fixed.

Apply the same steps in adding the conditional formatting and use this formula:

=MOD(ROW()-[starting row],[row count]*2)+1<=[row count]
  • [starting row]
    • Refers to the first data row (or the row that comes after the header).
    •  If your header is in row 1, your [starting row] would be 2.
  • [row count]
    • Refers to the number of rows to be grouped.
    • In my example above, [row count] would be 3.

With my example, my formula would be:

=MOD(ROW()-2, 3*2)+1<=3

And that’s it! You will have alternate colors for every nth number of rows.

Sample dataset with alternate row colors for every nth number of rows

You can manually add the row color for the header to have it stand out, like what I did in the example above.

But for tables with calculations, you might consider deleting every other row if you find that the data in them isn’t needed.

2.2. Add alternate row colors based on a specific field

If the number of rows per record varies, don’t worry. There is still a way for you to have alternate row colors.

Before adding the conditional formatting, we need to identify first the column that serves as the basis for saying that a group of rows is related to each other.

We will refer to this column as our “Parent Field”.

In my example below, my “Parent Field” is column A (Group Name).

Sample dataset with "Parent Field" -- field that is used as basis for saying that a group of rows are related to each other

Sort the data by this column so that related rows are next to each other.

Sort the data by the "Parent Field" column so that related rows are next to each other.

Next, go to the last column in your dataset, and add a new column.

In this new column, we will add a formula to determine if the current row belongs to the same group as the previous row.

I suggest adding it as the last column so you can easily hide it later.

In my case, I added it in column F (as shown below).

Add the formula next to the last column of your dataset.

The formula to use is:

=MOD(IF(ROW()=2,0,IF($A2=$A1,F1,F1+1)),2)

Add this formula to the same row as your first data row (e.g., row 2).

We are going to modify this formula to match your dataset.

1. Change the highlighted letters with the column letter where your “Parent Field” is located.

Screenshot of formula with certain letters highlighted to easily identify sections that need to be updated to match your dataset.

2. Change the highlighted letters with the column letter where you have placed this formula.

Screenshot of formula with certain letters highlighted to easily identify sections that need to be updated to match your dataset.

3. Change the yellow highlighted numbers with the row number containing your first data row.

Change the green highlighted numbers with the row number minus 1.

Screenshot of formula with certain numbers highlighted to easily identify sections that need to be updated to match your dataset.

Copy this formula to the rest of the cells in that column.

Copy the formula to the rest of the cells in the column.

This formula marks rows with 1’s and 0’s to identify rows within the same group.

Now, it’s time to add conditional formatting.

Follow the same steps in adding the conditional formatting but use this formula: =$F1=1

Change the letter “F” with the column where you have added the formula. 

And that’s it! You should now have alternate row colors added.

Sample dataset with alternate row colors based on a specific field

Hide the column containing the formulas by right-clicking on the entire column and selecting Hide.

2.3. Add alternate colors to columns

If you would instead like to apply alternate colors to your columns, use either of the following formulas:

SectionFormula
Even Column=MOD(COLUMN(), 2)=0 =ISEVEN(COLUMN())  
Odd Column=MOD(COLUMN(), 2)>0 =ISODD(COLUMN())  

Edit the conditional formatting

If you want to choose a different color for the rows (or columns):

1. From the Home menu, click on the Conditional Formatting button and select Manage Rules…

Steps to view the existing conditional formattings.

2. The Conditional Formatting Rules Manager will appear.

Steps to edit a conditional formatting

In the Show formatting rules dropdown list, select “This Worksheet”.

This will ensure that all the conditional formatting within the worksheet appears in the list of rules.

Next, select the formatting rule that you would like to edit. Look for the formula that you have previously added. Once selected, click the Edit Rule button.

You will then see the same menu when you added the conditional formatting. Click on the Format button to select a different cell color.

Remove the Conditional Formatting

If you changed your mind and would like to remove the alternate colors altogether:

  1. Select the cells with alternate rows or columns.
  2. Go to the Homemenu and click on theConditional Formatting button.
  3. Select Clear Rules >> Clear Rules from Selected Cells.
Steps to remove all conditional formatting

Conclusion

Adding alternate row colors is a simple yet effective way to give your data a more professional appeal. I hope the suggestions above have helped you achieve the format that you are looking for.