Anzeige

Clean up Excel spreadsheets

If you have created an Excel table and it becomes more and more confusing over time, and perhaps some errors such as double entries have crept in, the whole thing must first be cleaned up in order to get a clear view again. Sometimes, however, you are presented with catastrophic tables created without a system, with the task of extracting usable data from them.

It helps if you know one or the other trick to make the whole thing go a little faster.

Excel Tabelle bereinigen

Topic Overview

Anzeige

And that’s exactly what this little tutorial is supposed to be about today. We share 4 extremely helpful ways to do things like remove duplicates in Excel, delete blank cells, and fix texts in a few simple steps. This can save you the day with very large Excel spreadsheets and free up space for other things.

Clean up Excel spreadsheets

If you have created an Excel table and it becomes more and more confusing over time, and perhaps some errors such as double entries have crept in, the whole thing must first be cleaned up in order to get a clear view again. Sometimes, however, you are presented with catastrophic tables created without a system, with the task of extracting usable data from them.

It helps if you know one or the other trick to make the whole thing go a little faster.

Excel Tabelle bereinigen

Topic Overview

Anzeige

And that’s exactly what this little tutorial is supposed to be about today. We share 4 extremely helpful ways to do things like remove duplicates in Excel, delete blank cells, and fix texts in a few simple steps. This can save you the day with very large Excel spreadsheets and free up space for other things.

Remove duplicates in Excel

Remove duplicates in Excel

There are different ways to remove duplicates in Excel, depending on which version of Excel you are using. Here are three options:

Option 1: Remove duplicates using Excel’s Remove Duplicates feature

  • Select the area where you want to remove duplicates.
  • Click on the “Data” tab in the menu bar.
  • Click “Remove Duplicates“.
  • Check the box next to the columns that may contain duplicates.
  • Click “OK“.

Option 2: Remove Duplicates Using Excel’s Conditional Formatting Feature

  • Select the area where you want to remove duplicates.
  • Click on the “Start” tab in the menu bar.
  • Click Conditional Formatting.
  • Click Highlight Cell Rules, then click “Remove Duplicates“.
  • Check the box next to the columns that may contain duplicates.
  • Click “OK“.

Option 3: Remove duplicates with a formula
To remove duplicates in Excel using a formula, you can combine the “COUNTIF” and “IF” function.

  • First, mark the column or areas where you want to remove duplicates.
  • Then create a new column next to the highlighted column.
  • In the first cell of the new column, enter the following formula: =IF(COUNTIF(A:A,A1)>1,”Duplicate”,”Unique”)
    (If your data is in a column other than column A, you must change the column label in the formula accordingly.)
  • Drag the formula down to apply it to all cells in the new column.
    Now filter the new column for “duplicate” and delete the corresponding rows.

The formula above compares each cell in the selected column to all other cells in the same column and counts the number of occurrences of each cell. If a cell occurs more than once, the new column will say “Duplicate“, otherwise “Unique“. You can then filter by “duplicate” to select and delete all rows with duplicates.

see fig.(click to enlarge)

Duplikate in Excel entfernen-Option 1
Duplikate in Excel entfernen-Option 2
cshow
Ads

There are different ways to remove duplicates in Excel, depending on which version of Excel you are using. Here are three options:

Option 1: Remove duplicates using Excel’s Remove Duplicates feature

  • Select the area where you want to remove duplicates.
  • Click on the “Data” tab in the menu bar.
  • Click “Remove Duplicates“.
  • Check the box next to the columns that may contain duplicates.
  • Click “OK“.

Option 2: Remove Duplicates Using Excel’s Conditional Formatting Feature

  • Select the area where you want to remove duplicates.
  • Click on the “Start” tab in the menu bar.
  • Click Conditional Formatting.
  • Click Highlight Cell Rules, then click “Remove Duplicates“.
  • Check the box next to the columns that may contain duplicates.
  • Click “OK“.

Option 3: Remove duplicates with a formula
To remove duplicates in Excel using a formula, you can combine the “COUNTIF” and “IF” function.

  • First, mark the column or areas where you want to remove duplicates.
  • Then create a new column next to the highlighted column.
  • In the first cell of the new column, enter the following formula: =IF(COUNTIF(A:A,A1)>1,”Duplicate”,”Unique”)
    (If your data is in a column other than column A, you must change the column label in the formula accordingly.)
  • Drag the formula down to apply it to all cells in the new column.
    Now filter the new column for “duplicate” and delete the corresponding rows.

The formula above compares each cell in the selected column to all other cells in the same column and counts the number of occurrences of each cell. If a cell occurs more than once, the new column will say “Duplicate“, otherwise “Unique“. You can then filter by “duplicate” to select and delete all rows with duplicates.

see fig.(click to enlarge)

Duplikate in Excel entfernen-Option 1
Duplikate in Excel entfernen-Option 2
cshow
Ads

Remove empty cells in Excel

Remove empty cells in Excel

To remove blank cells in Excel, there are different ways depending on which version of Excel you are using. Here are two options:

Option 1: Remove blank cells using Excel’s Conditional Formatting feature

  • Select the range where you want to remove blank cells.
  • Click on the “Start” tab in the menu bar.
  • Click Conditional Formatting.
  • Click “New Rule” and then “Format only cells that contain“.
  • From the Cell Value drop-down menu, choose equals and click in the box to the right of it.
  • Then click in any cell within your spreadsheet that is empty.
  • Choose a format for the blank cells to be selected, e.g. B. a background color.
  • Click “OK“.
  • Select and delete the marked blank cells.

Option 2: Remove Blank Cells Using Find Function

To remove blank cells using find function in Excel, you can follow the steps below:

  • Mark the relevant table in which the check is to take place.
  • From the “Home” tab, go to “Find and Select” and then to “Go
  • In the dialog window, select “Content” at the bottom left.
  • Here you now have various options to search for and select content within your selected table.
  • So select “Empty Cells” here and confirm with “OK
  • Now all empty cells in your spreadsheet will be highlighted and you can simply delete them by pressing “DEL” on your keyboard.

see fig.(click to enlarge)

Leere Zellen in Excel entfernen mit bedingter Formatierung
Leere Zellen in Excel entfernen mit Suchfunktion
cshow
Ads

To remove blank cells in Excel, there are different ways depending on which version of Excel you are using. Here are two options:

Option 1: Remove blank cells using Excel’s Conditional Formatting feature

  • Select the range where you want to remove blank cells.
  • Click on the “Start” tab in the menu bar.
  • Click Conditional Formatting.
  • Click “New Rule” and then “Format only cells that contain“.
  • From the Cell Value drop-down menu, choose equals and click in the box to the right of it.
  • Then click in any cell within your spreadsheet that is empty.
  • Choose a format for the blank cells to be selected, e.g. B. a background color.
  • Click “OK“.
  • Select and delete the marked blank cells.

Option 2: Remove Blank Cells Using Find Function

To remove blank cells using find function in Excel, you can follow the steps below:

  • Mark the relevant table in which the check is to take place.
  • From the “Home” tab, go to “Find and Select” and then to “Go
  • In the dialog window, select “Content” at the bottom left.
  • Here you now have various options to search for and select content within your selected table.
  • So select “Empty Cells” here and confirm with “OK
  • Now all empty cells in your spreadsheet will be highlighted and you can simply delete them by pressing “DEL” on your keyboard.

see fig.(click to enlarge)

Leere Zellen in Excel entfernen mit bedingter Formatierung
Leere Zellen in Excel entfernen mit Suchfunktion
cshow
Ads

Remove formatting in Excel

Remove formatting in Excel

To quickly remove formatting in Excel, do the following:

  • Select the area to remove formatting.
  • Then go to the “Edit” section on the far right via the “Home” tab and select the sub-item “Clear Formats“.
  • The second option would be to mark the desired table area and then copy it with the key combination “CTRL+C“.
  • Then click in a free area of your spreadsheet or use a new one, and then select “Values” from the context menu under “Insert“.
  • In this way, the pure cell values are copied without any formatting.

see fig.(click to enlarge)

Excel Formatierung entfernen Option 1
Excel Formatierung entfernen Option 2

To quickly remove formatting in Excel, do the following:

  • Select the area to remove formatting.
  • Then go to the “Edit” section on the far right via the “Home” tab and select the sub-item “Clear Formats“.
  • The second option would be to mark the desired table area and then copy it with the key combination “CTRL+C“.
  • Then click in a free area of your spreadsheet or use a new one, and then select “Values” from the context menu under “Insert“.
  • In this way, the pure cell values are copied without any formatting.

see fig.(click to enlarge)

Excel Formatierung entfernen Option 1
Excel Formatierung entfernen Option 2

Clean up texts in Excel

Clean up texts in Excel

To perform text cleanup in Excel, we would like to offer two ways:

Possibility Number 1:

  • Highlight the area where you want to perform text cleanup.
  • Click on the “Data” tab and then click on the “Text to Columns” button in the “Data Tools” section.
  • In the Text Conversion Wizard, select the Delimited option and click Next.
  • Select the delimiters used in your text, such as space, tab, or comma, and click Next.
  • Select the appropriate data format for each column. For example, for a column of dates, you can choose the date format.
  • Click “Finish” to convert the data.

Possibility 2:

  • Use Excel’s Find and Replace feature to remove or replace specific strings or characters in text.
  • Click the Find and Replace button in the Edit section of the Home tab.
  • Enter the string of characters you want to find or the character that you want to replace, and then enter the new text.
  • Click Replace to remove or replace all occurrences of the string or character.
  • Save the file with the cleaned data.

By following these steps, you can clean up text in Excel and format your data cleanly and consistently.

To perform text cleanup in Excel, we would like to offer two ways:

Possibility Number 1:

  • Highlight the area where you want to perform text cleanup.
  • Click on the “Data” tab and then click on the “Text to Columns” button in the “Data Tools” section.
  • In the Text Conversion Wizard, select the Delimited option and click Next.
  • Select the delimiters used in your text, such as space, tab, or comma, and click Next.
  • Select the appropriate data format for each column. For example, for a column of dates, you can choose the date format.
  • Click “Finish” to convert the data.

Possibility 2:

  • Use Excel’s Find and Replace feature to remove or replace specific strings or characters in text.
  • Click the Find and Replace button in the Edit section of the Home tab.
  • Enter the string of characters you want to find or the character that you want to replace, and then enter the new text.
  • Click Replace to remove or replace all occurrences of the string or character.
  • Save the file with the cleaned data.

By following these steps, you can clean up text in Excel and format your data cleanly and consistently.

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Search for other topics:

About the Author:

Michael W. Suhr
Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Winter Specials 2024: Word & Excel Templates

Special Offers 2023: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Winter Specials 2024: Word & Excel Templates

Special Offers 2023: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Title

Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top