--- title: Seven functions that make Excel a great data cleansing tool description: Clean sales data faster with Excel. Here we list seven of the program’s most useful data cleansing functions. image: https://www.league.co.za/media/1843/mpull_blog_post_22.jpg --- [![](https://gclb2b.com/app/uploads/2020/07/GCL-main-logo-white.png)](https://gclb2b.com) - [About Us](https://gclb2b.com/about-us/) - [Our Services](https://gclb2b.com/our-services/) - [B2B Telemarketing](https://gclb2b.com/our-services/b2b-telemarketing/) - [Lead Generation and Appointment Setting](https://gclb2b.com/our-services/b2b-telemarketing/appointment-setting/) - [Channel Development](https://gclb2b.com/our-services/b2b-telemarketing/channel-development/) - [Event Support](https://gclb2b.com/our-services/b2b-telemarketing/event-support/) - [Inbound Call Handling](https://gclb2b.com/our-services/b2b-telemarketing/inbound-call-handling/) - [International Telemarketing](https://gclb2b.com/our-services/b2b-telemarketing/international-telemarketing/) - [Webinar Support](https://gclb2b.com/our-services/b2b-telemarketing/webinar-support/) - [Lead Nurture](https://gclb2b.com/our-services/b2b-telemarketing/lead-nurture/) - [Data Services](https://gclb2b.com/our-services/data-services/) - [Data Cleansing](https://gclb2b.com/our-services/data-services/data-cleansing/) - [Data Enrichment](https://gclb2b.com/our-services/data-services/data-enrichment/) - [Data Analysis and Transformation](https://gclb2b.com/our-services/data-services/data-analysis/) - [Data Sourcing](https://gclb2b.com/our-services/data-services/data-sourcing/) - [Database Building](https://gclb2b.com/our-services/data-services/database-building/) - [Opt-In Databases](https://gclb2b.com/our-services/data-services/opt-in-databases/) - [Total Addressable Market](https://gclb2b.com/our-services/data-services/total-addressable-market/) - [Market Research](https://gclb2b.com/our-services/market-research/) - [Prospect Profiling](https://gclb2b.com/our-services/market-research/prospect-profiling/) - [Sales & Marketing Consultancy](https://gclb2b.com/our-services/sales-marketing-consultancy/) - [News](https://blog.gclb2b.com/blog) - [Resources](https://gclb2b.com/resources/) - [Careers](https://gclb2b.com/careers/) - [Open Positions](https://gclb2b.com/careers/open-positions/) - [Contact Us](https://gclb2b.com/contact-us/) - [About Us](https://gclb2b.com/about-us/) - [Our Services](https://gclb2b.com/our-services/) - [B2B Telemarketing](https://gclb2b.com/our-services/b2b-telemarketing/) - [Lead Generation and Appointment Setting](https://gclb2b.com/our-services/b2b-telemarketing/appointment-setting/) - [Channel Development](https://gclb2b.com/our-services/b2b-telemarketing/channel-development/) - [Event Support](https://gclb2b.com/our-services/b2b-telemarketing/event-support/) - [Inbound Call Handling](https://gclb2b.com/our-services/b2b-telemarketing/inbound-call-handling/) - [International Telemarketing](https://gclb2b.com/our-services/b2b-telemarketing/international-telemarketing/) - [Webinar Support](https://gclb2b.com/our-services/b2b-telemarketing/webinar-support/) - [Lead Nurture](https://gclb2b.com/our-services/b2b-telemarketing/lead-nurture/) - [Data Services](https://gclb2b.com/our-services/data-services/) - [Data Cleansing](https://gclb2b.com/our-services/data-services/data-cleansing/) - [Data Enrichment](https://gclb2b.com/our-services/data-services/data-enrichment/) - [Data Analysis and Transformation](https://gclb2b.com/our-services/data-services/data-analysis/) - [Data Sourcing](https://gclb2b.com/our-services/data-services/data-sourcing/) - [Database Building](https://gclb2b.com/our-services/data-services/database-building/) - [Opt-In Databases](https://gclb2b.com/our-services/data-services/opt-in-databases/) - [Total Addressable Market](https://gclb2b.com/our-services/data-services/total-addressable-market/) - [Market Research](https://gclb2b.com/our-services/market-research/) - [Prospect Profiling](https://gclb2b.com/our-services/market-research/prospect-profiling/) - [Sales & Marketing Consultancy](https://gclb2b.com/our-services/sales-marketing-consultancy/) - [News](https://blog.gclb2b.com/blog) - [Resources](https://gclb2b.com/resources/) - [Careers](https://gclb2b.com/careers/) - [Open Positions](https://gclb2b.com/careers/open-positions/) - [Contact Us](https://gclb2b.com/contact-us/) [![gcl-white-logo-2](https://blog.gclb2b.com/hs-fs/hubfs/gcl-white-logo-2.png?width=140&height=56&name=gcl-white-logo-2.png "gcl-white-logo-2")](https://blog.gclb2b.com/) - [Home](http://www.gcldirect.com/) - [Services](http://www.gcldirect.com/lead-generation-services) - [B2B Telemarketing](http://www.gcldirect.com/lead-generation-services/b2b-telemarketing/) - [Appointment Setting](http://www.gcldirect.com/lead-generation-services/b2b-telemarketing/appointment-setting/) - [International Telemarketing](http://www.gcldirect.com/lead-generation-services/b2b-telemarketing/international-telemarketing/) - [Event Support](http://www.gcldirect.com/lead-generation-services/b2b-telemarketing/event-support/) - [Channel Development](http://www.gcldirect.com/lead-generation-services/b2b-telemarketing/channel-development/) - [Inbound Call Handling](http://www.gcldirect.com/lead-generation-services/b2b-telemarketing/inbound-call-handling/) - [Marketing Data](http://www.gcldirect.com/not-the-lead-generation-service/marketing-data/) - [Data Cleansing](http://www.gcldirect.com/lead-generation-services/marketing-data/data-cleansing/) - [Data Management](http://www.gcldirect.com/lead-generation-services/marketing-data/data-management/) - [Data Enrichment](http://www.gcldirect.com/lead-generation-services/marketing-data/data-enrichment/) - [Channel Mapping](http://www.gcldirect.com/lead-generation-services/marketing-data/channel-mapping/) - [Opt-in Databases](http://www.gcldirect.com/lead-generation-services/marketing-data/opt-in-databases/) - [Data Sourcing](http://www.gcldirect.com/lead-generation-services/marketing-data/data-sourcing/) - [Prospect Profiling](http://www.gcldirect.com/lead-generation-services/marketing-data/prospect-profiling/) - [Market Research](http://www.gcldirect.com/lead-generation-services/marketing-data/market-research/) - [Database Building](http://www.gcldirect.com/lead-generation-services/marketing-data/database-building/) - [Business Insight](http://www.gcldirect.com/lead-generation-services/marketing-data/business-insight/) - [Inbound Marketing](http://www.gcldirect.com/lead-generation-service/inbound-marketing/) - [Content Marketing](http://www.gcldirect.com/lead-generation-services/inbound-marketing/content-marketing/) - [SEO Strategy](http://www.gcldirect.com/lead-generation-services/inbound-marketing/seo-strategy/) - [Marketing and Sales Automation](http://www.gcldirect.com/lead-generation-services/inbound-marketing/marketing-sales-automation/) - [Social Selling](http://www.gcldirect.com/lead-generation-services/inbound-marketing/social-selling/) - [Paid Media](http://www.gcldirect.com/lead-generation-services/inbound-marketing/inbound-marketing-strategy/) - [Inbound Marketing Strategy](http://www.gcldirect.com/lead-generation-services/inbound-marketing/inbound-marketing-strategy/) - [Holistic Lead Generation](http://www.gcldirect.com/lead-generation-services/holistic-lead-generation/) - [About](http://www.gcldirect.com/company-profile/) - [Resources](http://www.gcldirect.com/resources/) - [Careers](http://www.gcldirect.com/careers) - [Latest Careers](http://www.gcldirect.com/careers/latest-careers/) - [Blog](https://blog.gclb2b.com/blog) - [Contact](http://www.gcldirect.com/contact/) [![CALL US NOW](https://no-cache.hubspot.com/cta/default/1604661/1f41ec7a-7629-4e9f-a478-c72e3048233c.png)](https://cta-redirect.hubspot.com/cta/redirect/1604661/1f41ec7a-7629-4e9f-a478-c72e3048233c) ### Welcome To Our Blog # Seven functions that make Excel a great data cleansing tool [Jenny Reardon](https://blog.gclb2b.com/blog/author/jenny-reardon) | 6 July 2016 Share:   - [Tweet](https://twitter.com/share) ![7 functions that make Excel a great data cleansing tool](https://www.league.co.za/media/1843/mpull_blog_post_22.jpg "7 functions that make Excel a great data cleansing tool") Much of the sales data you import is not in the format you want it. In fact, some of it isn’t even usable at first. The most illegible gibberish is rife with misspelled words, improper cases, non-printing characters, and unwanted trailing spaces. Before you even begin to work with such data, you first have to give it a good clean. Luckily, Excel contains many features to help you get your new data into the format you want it. While some of these tasks can be performed with the quick click of a button, other, more complex tasks require you to manipulate one or more columns with a formula to create usable values. Here, I’ve listed seven of these [data cleansing](http://www.gcldirect.com/services/data/cleansing) functions in order of complexity, starting with the easiest to use. ##### **1) Fix spelling errors** Nothing leaves a worse first impression than a spelling mistake, but Spell Check can ensure that your reputation stays unmarred by typos. Look for this function under the *Tools* dropdown. ##### **2)  Delete all formatting** The chances of you importing data that’s already in the format you want is pretty low. Once you have imported data from an external source, you’ll have to clear the formatting before you can use it. To do this, simply select the data set, click on the ‘Clear’ dropdown, and then select ‘Clear Formats’. ##### **3)  Remove extra spaces** Most extra spaces are difficult to spot, but trailing spaces are not even visible. The TRIM function gives you an alternative to the painfully slow hunt-and-peck approach. Syntax: =TRIM(text) ##### **4)  Select and treat all blank cells** Blank cells can cause much confusion, but you can fill them in with a ‘0’ or ‘Not Available’ by following these steps: 1. Select data set 2. Press F5 for the dialogue box 3. Click on ‘Special…’ button 4. Select ‘Blanks’ 5. Click ‘OK’ ##### **5)  Use *Find and Replace* to select data** This function is very useful for finding and changing specific values and formulas. To learn how to add and remove line breaks, replace formatting, and change cell references with this time saver, I suggest you read this [blog](http://trumpexcel.com/2014/07/find-and-replace-in-excel/) on [data cleansing](http://www.gcldirect.com/services/data/cleansing) by [trumpexcel.com](http://trumpexcel.com). **6)  Highlight errors** To find errors in your data, you can use the dialogue box and the following steps: 1. Select data set 2. Press F5 for the dialogue box 3. Click on ‘Special…’ button 4. Select ‘Formulas’ 5. Uncheck all options except ‘Errors’ 6. Click ‘OK’ ##### **7)  Remove duplicates** Duplicate entries can be a real problem, especially if you regularly combine data from multiple records. The *Remove Duplicates* function weeds out those extra entries, but you need to know which columns to select. If you leave all columns for a data set selected, only rows that are completely identical will be affected. For a more targeted approach to [data cleansing](http://www.gcldirect.com/services/data/cleansing) see this [blog](http://www.excel-easy.com/examples/remove-duplicates.html)  by [excel-easy.com](http://www.excel-easy.com). ##### **Ask the expert** Sometimes you just don’t have the time or resources for a large [data cleansing](http://www.gcldirect.com/services/data/cleansing) job. In these cases, it’s best to give your data to a specialist whose expertise can ensure that you have the best data possible for your next campaign. GCL Direct has 26 years experience in [data management](http://www.gcldirect.com/services/data/data-management), and can answer any questions you may have about data cleansing and management. To learn more about our services, visit our [website](http://www.gcldirect.com/services/data/data-management) for more information. [![New Call-to-action](https://no-cache.hubspot.com/cta/default/1604661/e570d486-bf1e-4263-98ac-743cdff0860a.png)](https://cta-redirect.hubspot.com/cta/redirect/1604661/e570d486-bf1e-4263-98ac-743cdff0860a) Image Credit: [League Digital](https://www.league.co.za/articles/agile-vs-waterfall-what-you-need-to-know-about-various-website-development-methodologies/) ### Categories - [Account Based Marketing (18)](https://blog.gclb2b.com/blog/topic/account-based-marketing) - [ai crm (1)](https://blog.gclb2b.com/blog/topic/ai-crm) - [Appointment Setting (12)](https://blog.gclb2b.com/blog/topic/appointment-setting) - [B2B Appointment Setting (18)](https://blog.gclb2b.com/blog/topic/b2b-appointment-setting) - [B2B Data (19)](https://blog.gclb2b.com/blog/topic/b2b-data) ### RESOURCES ![GCL_-_Workbook_Front_Cover_no_text.png](https://blog.gclb2b.com/hs-fs/hubfs/GCL_Resources_How-to-convert-MQLS-into-SQLS-300x214.jpeg?width=358&height=377&name=GCL_Resources_How-to-convert-MQLS-into-SQLS-300x214.jpeg) [How To Convert MQLs Into SQLs](https://landing.gcldirect.com/how-to-turn-an-mql-into-an-sql) ![GCL_-_Workbook_Front_Cover_no_text.png](https://blog.gclb2b.com/hs-fs/hubfs/GCL_Resources_How-to-create-a-winning-b2b-sales-pitch-300x214.jpeg?width=358&height=377&name=GCL_Resources_How-to-create-a-winning-b2b-sales-pitch-300x214.jpeg) [How To Create A Winning B2B Sales Pitch](https://landing.gcldirect.com/your-guide-to-winning-more-sales-pitches) ![GCL_-_Workbook_Front_Cover_no_text.png](https://blog.gclb2b.com/hs-fs/hubfs/GCL_Resources_The-guide-to-holistic-lead-generation-300x214.jpeg?width=358&height=377&name=GCL_Resources_The-guide-to-holistic-lead-generation-300x214.jpeg) [The Guide To Holistic Lead Generation](https://landing.gcldirect.com/the-guide-to-holistic-lead-generation) #### Subscribe To Our Resources Simply enter your email address to receive  alerts of new resources **![](https://blog.gclb2b.com/hs-fs/hubfs/layout/logo.png?width=113&name=logo.png)**   **HEAD OFFICE** 2nd Floor, Charter House, 52 Charlotte Street, Birmingham. B3 1PX [0121 452 2020](tel:01214522020) [0121 452 1900](tel:01214521900) [info@gclb2b.com](mailto:info@gclb2b.com) #### Recent Posts #### Recent Tweets Copyright 2020 GCL | All Rights Reserved ![](https://googleads.g.doubleclick.net/pagead/viewthroughconversion/965882982/?value=0&guid=ON&script=0) ![](https://dc.ads.linkedin.com/collect/?pid=70877&fmt=gif)