{"id":6072,"date":"2023-12-22T11:44:25","date_gmt":"2023-12-22T11:44:25","guid":{"rendered":"https:\/\/mammoth.io\/?p=6072"},"modified":"2026-03-02T17:59:31","modified_gmt":"2026-03-02T17:59:31","slug":"data-cleaning-in-excel","status":"publish","type":"post","link":"https:\/\/mammoth.io\/mammoth_v2\/data-cleaning-in-excel\/","title":{"rendered":"Data Cleaning in Excel: 8 Best Techniques and Tips [2026]"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">In a utopian world, data cleaning in excel wouldn&#8217;t be needed. You sheets would always be clean and properly formatted.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">But in reality, data is riddled with formatting issues, duplicate rows, missing values, spelling mistakes, improper capitalizations, lack of structure, and so much more.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Struggling with messy data in Excel? Fix it with Mammoth!<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Join thousands of data pros who streamline their workflow with Mammoth. Start your free trial today and see how Mammoth simplifies your data prep.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/app.mammoth.io\/#\/sign-up\">Try Mammoth For Free<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you&#8217;re a professional still looking to skip past the repetitive process of data cleaning to more meaningful work, stick around to learn how to clean your data in two ways:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Manually in Excel (best suitable for one-off tasks)<\/li>\n\n\n\n<li>Automatically in <a href=\"https:\/\/mammoth.io\/mammoth_v2\/signup\" title=\"\">Mammoth<\/a> (most suitable for continuous data management).<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Here are the 8 most common data cleaning in Excel tasks:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><a href=\"https:\/\/claude.ai\/chat\/21feb76a-eee7-4f50-989a-d0b08aa5986d#duplicates\">Remove duplicates<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/claude.ai\/chat\/21feb76a-eee7-4f50-989a-d0b08aa5986d#standardize\">Standardize formats<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/claude.ai\/chat\/21feb76a-eee7-4f50-989a-d0b08aa5986d#casing\">Even out casing and remove extra spaces<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/claude.ai\/chat\/21feb76a-eee7-4f50-989a-d0b08aa5986d#split\">Split delimited data<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/claude.ai\/chat\/21feb76a-eee7-4f50-989a-d0b08aa5986d#replace\">Find and replace<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/claude.ai\/chat\/21feb76a-eee7-4f50-989a-d0b08aa5986d#extract\">Extract prefixes and suffixes<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/claude.ai\/chat\/21feb76a-eee7-4f50-989a-d0b08aa5986d#spelling\">Check for spelling and typos<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/claude.ai\/chat\/21feb76a-eee7-4f50-989a-d0b08aa5986d#fill\">Fill missing values<\/a><\/li>\n<\/ol>\n\n\n\n<h2 class=\"wp-block-heading\">The Hidden Cost of Repetitive Data Cleaning<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before diving into the techniques, consider this: if you&#8217;re cleaning data manually in Excel every week, you&#8217;re likely spending 10-15 hours per month on repetitive tasks.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>That&#8217;s 120-180 hours per year<\/strong>. Time that could be spent on analysis, strategic projects, or building insights that actually drive decisions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Analysts commonly tell us:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>&#8220;We spend way too much time on data prep&#8221;<\/li>\n\n\n\n<li>&#8220;80% of my time is just cleaning data&#8221;<\/li>\n\n\n\n<li>&#8220;Manual Excel processes are killing us&#8221;<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">The question isn&#8217;t whether these tasks need to be done &#8211; it&#8217;s whether you want to repeat them every week or automate them once.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/mammoth.io\/mammoth_v2\/pricing\/\">Calculate your time savings with Mammoth \u2192<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. Remove duplicates {#duplicates}<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Before you go ahead with any cleaning, you should understand the origins of your data. How it was recorded, where &amp; how it is stored, how it is imported, etc.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Often, different bits of data are stored as different datasets, and in different databases. When you bring together this data, you might see duplicated rows.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once you have an understanding of the origins of the data and the duplication, this is how you can remove it:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Excel<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To remove duplicates in Excel,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select a column or range<\/li>\n\n\n\n<li>Go to the &#8220;Remove duplicates&#8221; tool under the <em>Data<\/em> option and you&#8217;ll see a modal like this one:<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2024\/06\/remove-duplicates-in-excel.jpeg\" alt=\"Data Cleaning in Excel: Removing duplicates screens in Excel\"\/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select the columns you want to remove duplicates from and check the headers box if your dataset has headers<\/li>\n\n\n\n<li>Click OK.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">While this looks straightforward, it gets repetitive with every data pull. Here&#8217;s how to do it once and get automated data cleaning for Excel files.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Mammoth<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To remove duplicates in Mammoth,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the <em>Transform<\/em> menu,<\/li>\n\n\n\n<li>Select the <em><a href=\"https:\/\/docs.mammoth.io\/content\/feature_guide\/tasks\/remove.duplicates.html?highlight=duplicate%20rows&amp;_gl=1*1upxd1c*_gcl_au*MzE3NTg4NjIuMTc2MjE3NjMzNg..\">Remove duplicate rows<\/a><\/em> function under <em>Reshape, Group and Aggregate<\/em><\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-wp-embed is-provider-arcade wp-block-embed-arcade\"><div class=\"wp-block-embed__wrapper\">\n<iframe class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" title=\"removing-duplicates\" src=\"https:\/\/demo.arcade.software\/87Jdozp6eJ2N23a7fnGH?embed#?secret=y7ZFS84MtA\" data-secret=\"y7ZFS84MtA\" width=\"800\" height=\"441\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Remove Duplicates function in Mammoth<\/em><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ignore columns if you want to, otherwise apply the rule on the whole dataset<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2024\/06\/mammoth-remove-duplicates-1024x372.jpeg\" alt=\"Data Cleaning in Excel: Removing duplicate rows in Excel\"\/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Click Apply.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">The task gets recorded in the pipeline and runs every time there is new data inflow in the dataset, not requiring additional effort from your end.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2024\/06\/mammoth-task-pipeline.jpeg\" alt=\"Data Cleaning in Excel: A data transformation task pipeline in Mammoth showing the actions user is about to take.\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Real-world example:<\/strong> <a href=\"https:\/\/mammoth.io\/mammoth_v2\/case-study\/starbucks\/\">Starbucks<\/a> processes over 1 billion rows of multi-country sales data monthly. Before Mammoth, their team spent 100+ hours on manual data consolidation each month. With Mammoth&#8217;s automated pipelines, this now runs with zero manual hours.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/app.mammoth.io\/#\/sign-up\">See how Mammoth automates duplicate removal \u2192<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">2. Standardize formats {#standardize}<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">One of my most common challenges with data cleaning is fixing format and structure.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">While most databases contain structured data, there are times when formats are wrongly detected, or there are mismatches in data formats from two complementary datasets.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Detecting and assigning the correct data type and format is crucial to performing the right calculations. Incorrect data types can even restrict you from performing datatype-specific functions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For example, if your numeric data is formatted as text, you&#8217;ll not be able to perform arithmetic functions on them. Or if a date column is formatted as dd-mm-yy in one dataset, and as a time stamp dd-mm-yy hh:mi:ss in another, there will be a mismatch.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Excel<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In Excel, you can use Power Query to detect and change the data type and formats.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s how,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to Power Query<\/li>\n\n\n\n<li>Select a data source. It will automatically detect data types based on the column values<\/li>\n\n\n\n<li>Go to the Query > Edit<\/li>\n\n\n\n<li>Select a column, and then select Transform > Detect Data Type<\/li>\n\n\n\n<li>Choose a data type\/format from the drop-down.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Alternatively, you can use the Text to Column function in Excel to change the data types &amp; formats.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is how,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select a cell or column<\/li>\n\n\n\n<li>Right-click on the cell or column and select Format Cells from the menu. Alternatively, press ctrl+1 in Windows or Command+1 in Mac to open the Format widget<\/li>\n\n\n\n<li>Change the data type or the format of the column<\/li>\n\n\n\n<li>Click OK.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/12\/format-cells-in-excel-1024x989.jpg\" alt=\"Data Cleaning in Excel: Formatting Cells\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Format Cells wizard in Excel<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Changing data types in Excel is a lot of work especially when you want to change data types of multiple columns at a time.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Mammoth<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You can change data types of multiple columns at once,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select a single column or select multiple columns from the column browser<\/li>\n\n\n\n<li>Choose the Convert data type function<\/li>\n\n\n\n<li>Select datatypes for the columns<\/li>\n\n\n\n<li>And apply all at once.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2024\/06\/mammoth-columns-1024x550.jpeg\" alt=\"Data Cleaning in Excel: Standardizing data in Mammoth\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">This task also gets added to the pipeline and runs automatically on data updates.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/app.mammoth.io\/#\/sign-up\">Try automated format standardization \u2192<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Excel vs. Mammoth: Time Comparison<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>Task<\/th><th>Manual Excel (Weekly)<\/th><th>Mammoth Setup<\/th><th>Ongoing Time<\/th><\/tr><\/thead><tbody><tr><td>Remove Duplicates<\/td><td>10 min<\/td><td>2 min once<\/td><td>0 min<\/td><\/tr><tr><td>Standardize Formats<\/td><td>20 min<\/td><td>5 min once<\/td><td>0 min<\/td><\/tr><tr><td>Fix Casing\/Spaces<\/td><td>15 min<\/td><td>3 min once<\/td><td>0 min<\/td><\/tr><tr><td>Split Columns<\/td><td>15 min<\/td><td>3 min once<\/td><td>0 min<\/td><\/tr><tr><td>Find &amp; Replace<\/td><td>25 min<\/td><td>5 min once<\/td><td>0 min<\/td><\/tr><tr><td>Extract Text<\/td><td>10 min<\/td><td>2 min once<\/td><td>0 min<\/td><\/tr><tr><td>Fill Missing Values<\/td><td>15 min<\/td><td>3 min once<\/td><td>0 min<\/td><\/tr><tr><td><strong>Total per week<\/strong><\/td><td><strong>110 min<\/strong><\/td><td><strong>23 min once<\/strong><\/td><td><strong>0 min<\/strong><\/td><\/tr><tr><td><strong>Total per year<\/strong><\/td><td><strong>95+ hours<\/strong><\/td><td><strong>23 min<\/strong><\/td><td><strong>0 hours<\/strong><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>That&#8217;s 95 hours of your life every year spent on repetitive tasks.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/mammoth.io\/mammoth_v2\/resources\/case-studies\/\">See how Mammoth customers save time \u2192<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">3. Even out casing and remove extra spaces {#casing}<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Casing and extra spaces in text columns are again basic formatting issues analysts regularly come across. Having consistent formatting across your datasets prevents erroneous calculations or blank cells.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Excel<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Fixing casing in Excel is quite a manual process,<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s how you can do it,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Insert a new column (Say col B) next to the text column<\/li>\n\n\n\n<li>In the new column, write the formula \u2013 =UPPER(cell address) \u2013 Converts to UPPERCASE<br>=LOWER(cell address) \u2013 Converts to lowercase<br>=PROPER(cell address) \u2013 Converts to Proper Case<\/li>\n\n\n\n<li>Drag to apply to all the cells in the column<\/li>\n\n\n\n<li>Copy the values in col B and Paste the values (without the formula) in the text column.<\/li>\n\n\n\n<li>Now, delete column B.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Removing extra spaces can be a task of its own in Excel. The TRIM function in Excel removes all leading, trailing, and extra spaces barring one space between words.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s how you can use it,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Insert a new column (say col B) next to the text column you want to trim<\/li>\n\n\n\n<li>Write the formula \u2013 TRIM(cell address) in the new column<\/li>\n\n\n\n<li>Drag to all the cell<\/li>\n\n\n\n<li>Copy the values from col B and paste them into the text column. All extra spaces will be removed.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">In Mammoth<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To fix casing or extra spaces in Mammoth, you don&#8217;t need to use separate functions. Just use the Text formatting function and fix everything in one go, like this \u2013<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the Transform menu<\/li>\n\n\n\n<li>Find the <a href=\"https:\/\/docs.mammoth.io\/content\/feature_guide\/tasks\/text.transform.html?highlight=formatting&amp;_gl=1*11l5ff7*_gcl_au*MzE3NTg4NjIuMTc2MjE3NjMzNg..\">Text formatting<\/a> under Text functions<\/li>\n\n\n\n<li>Change the casing of text functions and normalize spacing<\/li>\n\n\n\n<li>Click Apply.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-wp-embed is-provider-arcade wp-block-embed-arcade\"><div class=\"wp-block-embed__wrapper\">\n<iframe class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" title=\"change-casing\" src=\"https:\/\/demo.arcade.software\/9WgOeywvIYVhQrZvxRiL?embed#?secret=hZkUdeVO7C\" data-secret=\"hZkUdeVO7C\" width=\"800\" height=\"441\"><\/iframe>\n<\/div><figcaption class=\"wp-element-caption\"><em>Changing casing in Mammoth<\/em><\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">In Mammoth, you apply the rule once and it gets automated forever. On new data pulls, the data goes through the same data-cleaning steps automatically. You are not required to clean new data again and again.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/app.mammoth.io\/#\/sign-up\">Automate text formatting in Mammoth \u2192<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">4. Split delimited data {#split}<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Many times a few columns contain a lot of information delimited by a comma or space. You might need to split them into multiple columns for data processing. This process is also called Data Parsing.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Excel<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Parsing data in Excel goes like this,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select the cell or column that contains the text you want to split.<\/li>\n\n\n\n<li>Select Data > Text to Columns.<\/li>\n\n\n\n<li>In the Convert Text to Columns Wizard, select Delimited > Next.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2024\/06\/text-to-columns-excel-1024x785.jpeg\" alt=\"Data Cleaning in Excel: Splitting delimited data in Excel\"\/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select the Delimiters for your data. For example, Comma, Space, Semicolon, or Tab. You can see a preview of your data in the Data preview window.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/12\/text-to-col-step2-excel-1024x777.jpg\" alt=\"Data Cleaning in Excel: Converting Text to Columns\"\/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select Next.<\/li>\n\n\n\n<li>Select the Destination in your worksheet which is where you want the split data to appear.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2024\/06\/select-destination-excel-1024x782.jpeg\" alt=\"Data Cleaning in Excel: Converting Text to Columns\"\/><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select Finish.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">NOTE: Be careful when defining the destination columns, otherwise the information in the existing columns may be overwritten.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Mammoth<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To split delimited data in Mammoth, you just have to,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the Transform menu<\/li>\n\n\n\n<li>Find the <a href=\"https:\/\/docs.mammoth.io\/content\/feature_guide\/tasks\/split.column.html?highlight=split&amp;_gl=1*1rv7k30*_gcl_au*MzE3NTg4NjIuMTc2MjE3NjMzNg..\">Split into Multiple Columns<\/a> under Column functions<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-wp-embed is-provider-arcade wp-block-embed-arcade\"><div class=\"wp-block-embed__wrapper\">\n<iframe class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" title=\"split_column_function\" src=\"https:\/\/demo.arcade.software\/gRPp2AUGSG8p41ekbDGB?embed#?secret=MC3G3yXGVr\" data-secret=\"MC3G3yXGVr\" width=\"800\" height=\"494\"><\/iframe>\n<\/div><figcaption class=\"wp-element-caption\"><em>Navigating to the Split column function in Mammoth<\/em><\/figcaption><\/figure>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select the column you want to split into multiple columns<\/li>\n\n\n\n<li>Specify the delimiter<\/li>\n\n\n\n<li>Select the number of columns you want to split the column into<\/li>\n\n\n\n<li>Enter names for the new columns<\/li>\n\n\n\n<li>Click Apply.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-wp-embed is-provider-arcade wp-block-embed-arcade\"><div class=\"wp-block-embed__wrapper\">\n<iframe class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" title=\"split_column_details\" src=\"https:\/\/demo.arcade.software\/BMumB5Eucx7uyqS1HtzL?embed#?secret=qC0RlbwQtJ\" data-secret=\"qC0RlbwQtJ\" width=\"800\" height=\"494\"><\/iframe>\n<\/div><figcaption class=\"wp-element-caption\">Splitting columns in Mammoth<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Instead of going through multiple steps in Excel, you can apply the function from a single widget in Mammoth. Further, you can split the column into as many columns as you like.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Not to mention, the rule automatically applies to all new data in the future.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/app.mammoth.io\/#\/sign-up\">See column splitting in action \u2192<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">5. Find and replace {#replace}<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Datasets may store values in multiple variations. For instance, <em>Cupcake<\/em> could be stored as <em>cupcake<\/em>, <em>CUPCAKE<\/em>, <em>CUPcake<\/em>, <em>cUPcake<\/em>, and so on. It&#8217;s important to standardize these values before processing them.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Excel<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">There is no bulk way to replace multiple values in Excel, however, you can use the Find and Replace function to replace values one by one.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s how,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select the cell or column<\/li>\n\n\n\n<li>Go to Home > <a href=\"https:\/\/docs.mammoth.io\/content\/feature_guide\/tasks\/findandreplace.html?highlight=find%20replace&amp;_gl=1*15rr8he*_gcl_au*MzE3NTg4NjIuMTc2MjE3NjMzNg..\">Find and Replace<\/a><\/li>\n\n\n\n<li>Enter a value in the <em>Find what<\/em> option<\/li>\n\n\n\n<li>Enter the value you want to replace it with<\/li>\n\n\n\n<li>Click on &#8220;Replace All&#8221; if you want to apply it to the whole sheet.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2024\/06\/find-and-replace-excel-1024x461.jpeg\" alt=\"Data Cleaning in Excel: The find and replace functionality in Excel\"\/><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">In Mammoth<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Mammoth offers two functions to standardize values \u2013 <strong>Find and Replace<\/strong> and <strong>Bulk Replace<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The Find and Replace function can be applied to multiple columns, or even the whole dataset at a time.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Whereas the Bulk Replace function works on one column at a time but is a much more intuitive function than the Find and Replace function. Standardizing values works like a charm with the Bulk Replace function.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is how you can replace the values with Find and Replace in Mammoth,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the Transform menu<\/li>\n\n\n\n<li>Find the Find and Replace function under Label, Filter and Replace<\/li>\n\n\n\n<li>Select a column<\/li>\n\n\n\n<li>Enter the value you want to replace<\/li>\n\n\n\n<li>Enter a new value you want to replace with<\/li>\n\n\n\n<li>Click Apply<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2024\/06\/find-and-replace-mammoth-1024x589.jpeg\" alt=\"Data Cleaning in Excel: Find and replace in Mammoth\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">And, with the Bulk Replace function,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the Transform menu<\/li>\n\n\n\n<li>Find the <a href=\"https:\/\/docs.mammoth.io\/content\/feature_guide\/tasks\/bulkreplace.html?highlight=bulk%20replace&amp;_gl=1*15rr8he*_gcl_au*MzE3NTg4NjIuMTc2MjE3NjMzNg..\">Bulk Replace<\/a> function under Label, Filter, and Replace<\/li>\n\n\n\n<li>Select a column<\/li>\n\n\n\n<li>Put all similar values in a group<\/li>\n\n\n\n<li>Enter a new value for each group<\/li>\n\n\n\n<li>Click Apply.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/12\/bulk-replace-mammoth-1024x319.jpg\" alt=\"Data Cleaning in Excel: Bulk Replace in Mammoth\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Bulk Replace function in Mammoth<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">And just like that you have replaced multiple values in a column with a fraction of effort. This will also apply to all new data the dataset may get.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Customer story:<\/strong> <a href=\"https:\/\/mammoth.io\/mammoth_v2\/case-study\/inspired-learning-group\/\">Inspired Learning Group (ILG)<\/a> operates 23 schools across the UK. Each school submits reports with inconsistent naming conventions. Before Mammoth, consolidating these took 8+ hours monthly. With Mammoth&#8217;s Bulk Replace automation, this runs automatically with zero manual hours.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/app.mammoth.io\/#\/sign-up\">Try Bulk Replace automation \u2192<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">6. Extract prefixes and suffixes {#extract}<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Certain values such as OrderID, ItemID, or Model number often nest a lot of information like demography details, manufacturing details, processing details, and so on.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">While this could be valuable information for analysis, it&#8217;s difficult to use until we extract it as a separate value.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Excel<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In Excel, we use the LEFT and RIGHT functions to extract prefixes or suffixes.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This is how,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Insert a new column next to the column you want to extract text from<\/li>\n\n\n\n<li>Select the cell or column<\/li>\n\n\n\n<li>Write the formula \u2013 =LEFT(text, [number of chars])<\/li>\n\n\n\n<li>Drag the rule to apply to all the cells.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2023\/12\/Left-func-excel.jpg\" alt=\"Data Cleaning in Excel: Left function\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">LEFT function in Excel<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Mammoth<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You can simply use the &#8220;Extract Text&#8221; to extract text,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the Transform menu<\/li>\n\n\n\n<li>Find the <a href=\"https:\/\/docs.mammoth.io\/content\/feature_guide\/tasks\/text.extract.html?highlight=extract%20text&amp;_gl=1*15rr8he*_gcl_au*MzE3NTg4NjIuMTc2MjE3NjMzNg..\">Extract Text<\/a> under Text functions<\/li>\n\n\n\n<li>Select extraction method (first N characters, last N characters, text between delimiters)<\/li>\n\n\n\n<li>Name the new column<\/li>\n\n\n\n<li>Click Apply.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/mammoth.io\/mammoth_v2\/wp-content\/uploads\/2024\/06\/extract-text-mammoth.jpeg\" alt=\"Data Cleaning in Excel: Extracting text in Mammoth\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">7. Check for spelling and typos {#spelling}<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Misspelled terms or typos can occur anywhere. It&#8217;s good practice to check your data for spelling mistakes.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Excel<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The spell check function in Excel is great for spell checks,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select a cell or column<\/li>\n\n\n\n<li>Go to Review > Spelling<\/li>\n\n\n\n<li>Excel would suggest the correct spelling or similar words if there are wrong spellings. You can choose the value you want and it will get replaced everywhere in the column.<\/li>\n\n\n\n<li>If all spellings are correct, it will show a checkmark.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">In Mammoth<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Mammoth currently doesn&#8217;t offer spell check as a built-in feature.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">8. Fill missing values {#fill}<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Missing values stem from poor data records or data entry errors. You can choose to either remove the missing values or impute and fill them.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Having complete data helps in data processing &amp; modeling and you should strive to have zero blank cells in your data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">However, if there are missing values in a column that you are not going to need at all for your analysis, you can simply ignore those.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Excel<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To fill in missing values in Excel,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Highlight the range starting before and after the missing values<\/li>\n\n\n\n<li>Click Home > Editing > Fill > Series.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">This might not work if the data is not in a sequential order.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">In Mammoth<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">To fill in missing values in Mammoth,<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the Transform menu<\/li>\n\n\n\n<li>Find the <a href=\"https:\/\/docs.mammoth.io\/content\/feature_guide\/tasks\/fill.missing.values.html?_gl=1*7htrv4*_gcl_au*MzE3NTg4NjIuMTc2MjE3NjMzNg..\">Fill missing values<\/a> under Column functions<\/li>\n\n\n\n<li>Select if you want to fill with the value above or with the value after<\/li>\n\n\n\n<li>Click Apply.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/app.mammoth.io\/#\/sign-up\">Start automating your data cleaning \u2192<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What Mammoth Customers Say<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">HP: Journal Entry Automation<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Challenge:<\/strong> Finance team spent entire afternoons manually creating journal entries from multiple Excel files.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Result:<\/strong> 4-hour manual process reduced to 15-minute automated workflow. Now runs daily instead of monthly.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Starbucks: Multi-Country Data Processing<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Challenge:<\/strong> Processing 1 billion+ rows across 17 countries with different formats, currencies, and naming conventions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Result:<\/strong> Eliminated 100+ hours of monthly manual work. Data consolidation that took 20 days now completes in hours.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Financial Services: Complex Data Consolidation<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Challenge:<\/strong> Consolidating machine sales data across 15 Excel sheets with complex accounting rules took 3-4 months.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Result:<\/strong> Quarterly process now completes in approximately 2 hours.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/mammoth.io\/mammoth_v2\/resources\/case-studies\/\">Read more customer stories \u2192<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Frequently Asked Questions<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>How long does it take to learn Mammoth?<\/strong><br>Most analysts are productive within 15 minutes. If you understand Excel logic, you&#8217;ll understand Mammoth &#8211; it&#8217;s visual and intuitive with no coding required.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Will Mammoth work with my data?<\/strong><br>Mammoth handles Excel files (XLSX, XLS, CSV), databases (SQL Server, MySQL, PostgreSQL), cloud storage (Google Drive, OneDrive, S3), and 100+ data connectors.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>What if I need to change something later?<\/strong><br>Every pipeline is fully editable. Add steps, remove steps, modify logic &#8211; changes take seconds and apply to all future data automatically.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Is my data secure?<\/strong><br>Mammoth is SOC 2 Type II certified and ISO 27001 compliant. Your data is encrypted at rest and in transit, with role-based access control and detailed audit logs.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>How much does Mammoth cost?<\/strong><br>Plans start at $16\/month for individual analysts. Most organizations achieve ROI within 2-4 weeks. <a href=\"https:\/\/mammoth.io\/mammoth_v2\/pricing\/\">View pricing \u2192<\/a><\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Data Cleaning in Excel: Conclusion<\/h1>\n\n\n\n<p class=\"wp-block-paragraph\">If you are still going through the same rigorous data cleaning routine every time, this is the time to change. Adapt to intuitive and automated solutions like Mammoth Analytics for smooth data management and processing.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Struggling with messy data in Excel? Fix it with Mammoth!<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Join thousands of data pros who streamline their workflow with Mammoth. Start your free trial today and see how Mammoth simplifies your data prep.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/app.mammoth.io\/#\/sign-up\">Try Mammoth For Free<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Ready to Stop Repeating the Same Data Cleaning Tasks?<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Option 1: Start Your Free Trial<\/strong><br>Set up your first automated pipeline and see how it works with your actual data.<br><a href=\"https:\/\/app.mammoth.io\/#\/sign-up\">Start Free Trial<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Option 2: See It In Action<\/strong><br>Schedule a personalized demo to see how Mammoth handles your specific data cleaning challenges.<br><a href=\"https:\/\/mammoth.io\/mammoth_v2\/book-demo\/\">Book a Demo<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Option 3: Learn More<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/mammoth.io\/mammoth_v2\/platform\/\">How Mammoth Works<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/mammoth.io\/mammoth_v2\/resources\/case-studies\/\">Customer Success Stories<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/mammoth.io\/mammoth_v2\/pricing\/\">Pricing Plans<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/mammoth.io\/mammoth_v2\/comparison\/alteryx\/\">Compare Mammoth to Alteryx<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In a utopian world, data cleaning in excel wouldn&#8217;t be needed. You sheets would always be clean and properly formatted. But in reality, data is riddled with formatting issues, duplicate rows, missing values, spelling mistakes, improper capitalizations, lack of structure, and so much more. Struggling with messy data in Excel? Fix it with Mammoth! Join [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":6142,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"content-type":"","footnotes":""},"categories":[15],"tags":[71],"class_list":["post-6072","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-data-cleaning-quality"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/posts\/6072","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/comments?post=6072"}],"version-history":[{"count":25,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/posts\/6072\/revisions"}],"predecessor-version":[{"id":19340,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/posts\/6072\/revisions\/19340"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/media\/6142"}],"wp:attachment":[{"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/media?parent=6072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/categories?post=6072"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mammoth.io\/mammoth_v2\/wp-json\/wp\/v2\/tags?post=6072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}