{"id":247247,"date":"2025-05-19T12:11:28","date_gmt":"2025-05-19T12:11:28","guid":{"rendered":"https:\/\/peraltafinancing.com\/analytics\/10-ways-to-clean-data-in-excel-sheets\/"},"modified":"2025-05-19T12:11:28","modified_gmt":"2025-05-19T12:11:28","slug":"10-ways-to-clean-data-in-excel-sheets","status":"publish","type":"post","link":"https:\/\/fivemor.com\/?p=247247","title":{"rendered":"10 Ways to Clean Data in Excel Sheets"},"content":{"rendered":"


\n<\/p>\n

\n

Every data analyst has had that sinking feeling when opening a new spreadsheet, seeing unformatted numbers, inconsistent entries, random blank cells, and duplicates everywhere! Cleaning up this data is essential to start working on it. Whether you\u2019re putting together a quarterly report, consumer behaviour analysis, or trend forecasting, the quality of your interpretation depends on how well you\u2019ve cleaned the data first. Cleaning data in Excel is not just a technical step; it\u2019s the basic foundation that converts raw information into astute insights for businesses. In this article, I will explain to you what data cleaning is and guide you on how to remove duplicates and clean data in Excel.<\/span><\/p>\n

What is Data Cleaning in Excel?<\/span><\/h2>\n

Cleaning data in Excel Sheets involves identifying and fixing errors, getting rid of inconsistencies, and removing duplicates<\/a> and inaccuracies. Examining the raw data to identify and handle outliers \u2013 such as duplicate entries and missing values using Excel\u2019s built-in functions<\/a> and tools ensures more accurate and reliable results.<\/span><\/p>\n

What are the Characteristics of Clean Data?<\/span><\/h2>\n

Clean data can be identified based on the following characteristics:<\/span><\/p>\n

    \n
  1. Accuracy:<\/strong> Data should reproduce the real value without giving room to errors.<\/span><\/li>\n
  2. Completeness: <\/strong>All necessary values are present, with very little missing.<\/span><\/li>\n
  3. Consistency:<\/strong> Similar data follows the same format throughout the dataset.<\/span><\/li>\n
  4. Uniformity: <\/strong>Units of measurement, abbreviations, and naming conventions should be standardized.<\/span><\/li>\n
  5. Uniqueness: <\/strong>There should be no unnecessary duplicate records in the dataset.<\/span><\/li>\n
  6. Validity: <\/strong>Data must fall within an acceptable range and meet the defined rules.<\/span><\/li>\n
  7. Timeliness: <\/strong>Data should be up to date and relevant to the time of analysis.<\/span><\/li>\n<\/ol>\n

    How to Clean Data in Excel Sheets?<\/span><\/h2>\n

    In this section, we\u2019ll explore some of the standard techniques used to clean data in Excel Sheets:<\/span><\/p>\n

    1. Remove Duplicates<\/span><\/h3>\n

    Duplicate records can seriously skew one\u2019s analysis, giving false perceptions of volume or frequency. Suppose the same customer was counted twice in sales numbers; this would lead to a discrepancy in the entire dataset. Hence, it\u2019s important to remove duplicates for accurate data analysis.<\/span><\/p>\n

    Steps to Remove Duplicates<\/h4>\n
      \n
    1. Select the range of data (Including headers) to remove duplicates from.<\/span><\/li>\n<\/ol>\n
      \"How<\/figure>\n
        \n
      1. Go to the Data<\/strong> tab in the menu bar.<\/span><\/li>\n<\/ol>\n
        \"How<\/figure>\n
          \n
        1. Click on Data cleanup<\/strong> and select Remove duplicates<\/strong>.<\/span><\/li>\n<\/ol>\n
          \"\"<\/figure>\n
            \n
          1. Here, you\u2019ll get a pop-up to select the columns from which you wish to remove duplicate values. You can choose to select all or some specific columns only.<\/span><\/li>\n<\/ol>\n
            \"How<\/figure>\n
              \n
            1. Click on Remove duplicates<\/strong> to get the duplicates removed.<\/span><\/li>\n<\/ol>\n
              \"How<\/figure>\n

              Also Read: Microsoft Excel for Data Analysis<\/a><\/em><\/p>\n

              2. Standardize Formats<\/span><\/h3>\n

              Inconsistent formatting is an obstacle to data analysis. Even elementary tasks, such as sorting, can fail when dates, numbers, or text use different formats or conventions, so it\u2019s necessary to standardize the formats of the data.<\/span><\/p>\n

              Steps to Standardize Formats<\/h4>\n
                \n
              1. Select the column or required range of data that you need to standardize, like in this example, we\u2019ll be choosing the column containing dates.<\/span><\/li>\n<\/ol>\n
                \"How<\/figure>\n
                  \n
                1. From the menu bar, go to Format<\/strong> and then choose Number<\/strong>.<\/span><\/li>\n<\/ol>\n
                  \"How<\/figure>\n
                    \n
                  1. Choose the format you want to follow from the list. Here we\u2019ll select Date<\/strong> and it will convert the selected data to that format.<\/span><\/li>\n<\/ol>\n
                    \"How<\/figure>\n
                      \n
                    1. You have other formatting options that you can choose from as well.<\/span><\/li>\n<\/ol>\n

                      3. Clean Text Data<\/span><\/h3>\n

                      Every text analysis starts with cleaning. Raw text data frequently contains inconsistencies like extra spaces, inappropriate cases, typos, or special symbols. This may interfere with grouping, filtering, or interpretation. Without sufficient cleaning, the most advanced methods or models will struggle to yield results of value.<\/span><\/p>\n

                      Steps to Clean Text Data<\/h4>\n

                      Let\u2019s consider this dataset<\/span><\/p>\n

                      \"sample<\/figure>\n
                        \n
                      1. Capitalize the first letter of each word using the PROPER function. The formula of this function: =PROPER(cell)<\/span><\/li>\n<\/ol>\n
                        \"PROPER<\/figure>\n
                          \n
                        1. Remove the extra spaces present using the TRIM function. The formula is written as: =TRIM(cell)<\/span><\/li>\n<\/ol>\n
                          \"TRIM<\/figure>\n
                            \n
                          1. Convert text to either all uppercase or lowercase format using the \u201cLOWER\u201d & \u201cUPPER\u201d functions. The formula is written as: =LOWER(cell)<\/span> or =UPPER(cell)<\/li>\n<\/ol>\n
                            \"LOWER<\/figure>\n
                              \n
                            1. We can use the combination of two of these functions to clean the data more comprehensively. The formula for this is written as: =FIRST FUNCTION(SECOND FUNCTION(cell))<\/span><\/li>\n<\/ol>\n
                              \"Combining<\/figure>\n

                              Also Read: Data Cleaning for Beginners \u2013 Why and How?<\/a><\/em><\/p>\n

                              4. Fill Missing Values<\/span><\/h3>\n

                              There might be some cases where you\u2019ll see missing values, and these values might create blind spots in your analysis. Filling your data with some random values is not the solution, but there are several ways to handle these gaps appropriately.<\/span><\/p>\n

                              Steps to Fill Missing Values<\/h4>\n

                              Consider the following dataset<\/span><\/p>\n

                              \"sample<\/figure>\n
                                \n
                              1. You can easily fill in missing numerical values using the AVERAGE formula. This will add the calculated average, which is a more realistic value within the existing range. The formula can be written as: =AVERGAGE(min,max)<\/span><\/li>\n<\/ol>\n
                                \"Filling<\/figure>\n
                                  \n
                                1. For categorical data, you can use logical assumptions like \u201cNot Available\u201d or \u201cUnknown\u201d wherever suitable.<\/span><\/li>\n<\/ol>\n
                                  \"Filling<\/figure>\n
                                    \n
                                  1. You can also use Smart Fill<\/strong> to detect patterns and then fill in missing values.<\/span><\/li>\n<\/ol>\n
                                    \"Using<\/figure>\n

                                    5. Validate the Data<\/span><\/h3>\n

                                    Data validation is the process that controls and sets the rules for what can be entered into cells and what cannot. Using this to prevent errors is much easier than fixing those errors later.<\/span><\/p>\n

                                    Steps for Data Validation<\/h4>\n
                                      \n
                                    1. Select the row or column with the data you need to validate.<\/span><\/li>\n<\/ol>\n
                                      \"Data<\/figure>\n
                                        \n
                                      1. Go to the Data<\/strong> tab on the menu bar and select Data validation<\/strong>.<\/span><\/li>\n<\/ol>\n
                                        \"Data<\/figure>\n
                                          \n
                                        1. Choose the specific criteria of validation under the validation rule<\/strong>, such as whole numbers, dates, lists, etc.<\/span><\/li>\n<\/ol>\n
                                          \"data<\/figure>\n
                                            \n
                                          1. Then set the specific parameters or the various options that can be added in the cell, like date or time in a particular format, the name of departments, etc.<\/span><\/li>\n<\/ol>\n
                                            \"Data<\/figure>\n
                                              \n
                                            1. Once set, you will have your data validated.<\/span><\/li>\n<\/ol>\n

                                              Also Read: Advanced Microsoft Excel for Data Analysis<\/a><\/em><\/p>\n

                                              6. Apply Conditional Formatting<\/span><\/h3>\n

                                              There will be some visual cues that might help us identify the potential issues in the data quickly by highlighting the values that meet specific criteria. For data cleaning purposes, they can basically highlight duplicate values, flag outliers, identify missing values, and mark the cells containing formulas with errors.<\/span><\/p>\n

                                              Steps for Conditional Formatting<\/strong><\/p>\n

                                                \n
                                              1. Select the range of the data you wish to clean.<\/span><\/li>\n<\/ol>\n
                                                \"Conditional<\/figure>\n
                                                  \n
                                                1. Go to the Format<\/strong> tab on the menu bar and choose the option Conditional formatting<\/strong>.<\/span><\/li>\n<\/ol>\n
                                                  \"Conditional<\/figure>\n
                                                    \n
                                                  1. Choose the type of rule you want to apply (highlight cells rules, top\/bottom rules, etc.)<\/span><\/li>\n<\/ol>\n
                                                    \"Conditional<\/figure>\n
                                                      \n
                                                    1. Then define the formatting styles and the required conditions. For example, here I am applying \u2018highlight cells in the specified column, which are greater than 2000, in red.\u2019<\/span><\/li>\n<\/ol>\n
                                                      \"conditional<\/figure>\n
                                                        \n
                                                      1. Once set, click on Done<\/strong>.<\/span><\/li>\n<\/ol>\n

                                                        7. Power Query<\/span><\/h3>\n

                                                        There is an advanced data cleaning method called \u2018Get & Transform\u2019 which is available in newer versions of Microsoft Excel. It is used for more complex data cleaning purposes. It offers robust options for cleaning and reshaping the data before putting it into your spreadsheet.<\/span><\/p>\n

                                                        If you\u2019re using Excel 2016 or a later version, it comes with built-in Power Query functionality. Else, you can add it as an add-in in Excel 2010 and subsequent versions.<\/span><\/p>\n

                                                        Steps to Use Power Query<\/h4>\n
                                                          \n
                                                        1. Click on the Get Data<\/strong> button in the Power Query<\/strong> tab, and you\u2019ll get a drop-down menu having numerous file types like csv file, webpages, etc.<\/span><\/li>\n<\/ol>\n
                                                          \"\"<\/figure>\n
                                                            \n
                                                          1. Choose your data source.<\/span><\/li>\n<\/ol>\n
                                                            \"Power
                                                            Source: <\/span>Power Query<\/span><\/a><\/figcaption><\/figure>\n
                                                              \n
                                                            1. When a data source is chosen, Excel will prompt for a connection that requires certain information based on the type of source. For a source such as a file, you\u2019ll be asked to provide the file path (browsing to the location). On the other hand, for a web source, you\u2019ll need to enter a valid URL.<\/span><\/li>\n
                                                            2. Once the source is specified for loading, the following option may arise. You may be requested to pick a sheet, table, or range and then enter your credentials to authorize.<\/span><\/li>\n
                                                            3. Make sure to review the columns while selecting only those that you really require. Either load or transform your data for it to show up in the Power Query Editor, where further cleaning takes place.<\/span><\/li>\n<\/ol>\n
                                                              \"Power
                                                              Source: <\/span>Power Query<\/span><\/a><\/figcaption><\/figure>\n
                                                                \n
                                                              1. You can even filter your data according to your requirements using Power Query. For example, you can deal with missing data or remove columns by following these steps:<\/span><\/li>\n<\/ol>\n