Last updated on Jul 16, 2024
- All
- Data Cleaning
Powered by AI and the LinkedIn community
1
Why data types matter
2
How to check your data types
3
How to correct your data types
4
How to convert your data types
5
How to validate your data types
6
How to format your data types
7
Here’s what else to consider
Data cleaning is the process of preparing your data for analysis by removing errors, inconsistencies, duplicates, and missing values. Excel is a popular tool for data cleaning, but it can also introduce some challenges when it comes to validating and formatting your data types. Data types are the categories of information that you store in your cells, such as numbers, text, dates, or logical values. In this article, you will learn how to check, correct, and convert your data types in Excel using some simple tips and tricks.
Key takeaways from this article
-
Use Power Query:
Power Query is a tool in your spreadsheet software that simplifies data type corrections. It can split or merge cells, change data types directly, and even handle complex tasks like pivoting tables.
-
Combine validation formulas:
In your spreadsheets, you can use custom formulas to validate data types. For instance, employ functions like "ISNUMBER" and "LEN" to ensure phone numbers have the correct length and area code, enhancing data quality.
This summary is powered by AI and these experts
- Sudheer S Agile | Scrum | Kanban | JIRA |…
- Juan José García Guevara Administración de Empresas | Análisis…
1 Why data types matter
Data types affect how Excel interprets and performs calculations on your data. For example, if you have a column of dates that Excel recognizes as text, you will not be able to sort, filter, or apply formulas based on those dates. Similarly, if you have a column of numbers that Excel recognizes as text, you will not be able to perform arithmetic operations or use functions that require numeric inputs. Therefore, it is important to ensure that your data types match the intended meaning and use of your data.
Help others by sharing more (125 characters min.)
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
This article is not precise when it says we cannot perform arithmetic operations on numbers recognized as text. If it's a column formatted as text, Excel forces the calculation and brings a numeric output. The real problem in this situation is when we try to use a VLOOKUP or similar techniques, since text and numbers will not match. But, of course, there are worse scenarios of numbers with spaces and other invalid characters, which imply a need for more detailed data cleansing.
LikeLike
Celebrate
Support
Love
Insightful
Funny
24
- Hugo Venturini Palestrante, Consultor e Instrutor | MVP Data Platform | MOS Excel
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
O mecanismo de cálculo das funções do Excel evoluiu, e no que diz respeito a cálculos aritméticos, agregações, não é mais um problema que os números estejam como texto. Mas, na utilização de outras funções, para outras operações, como busca e referência, por exemplo, o problema permanece. Zelar pela qualidade do dado que se utiliza no dia a dia envolve também pensar no próprio processo de input.
Translated
LikeLike
Celebrate
Support
Love
Insightful
Funny
4
- Ananya Canakapalli Actively Seeking Spring 2025 Internships | MS in Business Analytics and Artificial Intelligence @ The University of Texas at Dallas | Data Analysis | Data Visualization | SQL | R | Tableau | Power BI
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Specificity about data in the data cleaning process is the key. During my initial days as a data migration analyst, I was using the VLOOKUP function to match product IDs with their prices, but it wasn't working. Excel thought they were text, so the matches weren't working. That's when I learned that it is important to maintain the consistency of data types to get the desired results.
LikeLike
Celebrate
Support
Love
Insightful
Funny
3
- Georg Lasar 🤪Vom Zahlenchaos zum freien Kopf 😎: Excel spielend meistern – ohne Stress, gekonnt mit Spaß. 💡 Innovatives Training: GENIUS-Methode | 🎓 Microsoft zert. Excel Experte | 🏆 4x Award-Gewinner für digitale Bildungsmedien
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
A solid datastructure is essential if you want to run reliable analyses. Especially when it comes to analyzing data in Excel, e.g. with the help of pivot tables, you need consistent data.Before you create pivot tables in Excel, make sure:- to eliminate duplicates - to have one (!) data type per column (text, date, number)- to delete empty rows and merged cells
LikeLike
Celebrate
Support
Love
Insightful
Funny
2
- TARIQ EL YASSOURI Group Director - Customer Centricity. MIT Certified. Ex-Maserati, Ex-Mercedes-Benz.
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
The data types you use in Excel can greatly impact how the software interprets and processes your data. For instance, if you have a column of dates that Excel mistakenly recognizes as text, it can lead to limitations in functionality. When dates are treated as text, you may encounter issues sorting, filtering, or performing calculations based on those dates.
LikeLike
Celebrate
Support
Love
Insightful
Funny
2
Load more contributions
2 How to check your data types
One way to check your data types in Excel is to use the Data Type Gallery. This is a feature that allows you to assign data types to your cells based on predefined categories, such as Stocks, Geography, or Animals. You can access the Data Type Gallery from the Data tab on the ribbon. When you select a cell or a range of cells, you will see the Data Type icon on the right side of the formula bar. If the icon is grayed out, it means that Excel does not recognize the data type of your cells. If the icon is colored, it means that Excel has assigned a data type to your cells. You can click on the icon to see more information about the data type, such as the source, the fields, and the refresh options.
Help others by sharing more (125 characters min.)
- Sudheer S Agile | Scrum | Kanban | JIRA | Confluence | Bitbucket | Atlassian | Tableau | Power BI | Process Consultant | Transformation Expert | Automation | Mentoring | Reliability Status Clearance
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
I would use the Power Query in excel. It gives you clear idea on the data type. You can easily switch the data type using Power query.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
- Georg Lasar 🤪Vom Zahlenchaos zum freien Kopf 😎: Excel spielend meistern – ohne Stress, gekonnt mit Spaß. 💡 Innovatives Training: GENIUS-Methode | 🎓 Microsoft zert. Excel Experte | 🏆 4x Award-Gewinner für digitale Bildungsmedien
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
If you analyze a plain dataset within Excel, you can use different tools to check for data types. One approach is to use conditional formatting, which can evaluate cell content based on a formula. Excel offers a variety of functions, which can be applied to the conditional formatting. You can use functions like ISTEXT or ISNUMBER, ISFORULAR and the like. If the validation is TRUE, you can apply a visual warning, e.g. a red background color etc.If you run this logic on a list, use the filter function afterward to sort by color and have all inconsistent data types filtered, ready for cleanup.When applying conditional formatting, pay attention to correct cell references when writing your formulas.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
- Gerald Tesha Data Officer @ HETA | Data Visualization | Monitoring and Evaluation
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Inspect the troops! Use the "Data Types" feature in Excel. Select a cell, go to the "Data" tab, and choose "Data Types." Excel can auto-detect or let you choose.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
There are various ways one can check data type in Excel: one way is by highlighting the cell and looking at the number format in the toolbar.To view data types in a column, you can highlight the column, set conditional formatting rules and format only values that contain a particular data type.
LikeLike
Celebrate
Support
Love
Insightful
Funny
- Ali Muhammad Business Analyst | Delivering Actionable Insights and Driving Data-Driven Decision Making
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
To inspect data types, select a range and navigate to the "Data" tab. Use the "Data Types" dropdown to check if Excel correctly identifies your data. A green icon indicates a recognized data type. Incorrect identifications, marked with a yellow icon, require correction.
LikeLike
Celebrate
Support
Love
Insightful
Funny
Load more contributions
3 How to correct your data types
If Excel does not recognize or assigns the wrong data type to your cells, you can correct it using different methods. One method is to use the Text to Columns wizard. This is a feature that allows you to split your text data into multiple columns based on a delimiter, such as a comma, a space, or a dash. You can access the Text to Columns wizard from the Data tab on the ribbon. When you launch the wizard, you will have the option to choose the delimiter, the destination, and the data format of each column. By choosing the appropriate data format, such as General, Text, Date, or Number, you can convert your text data into the correct data type.
Help others by sharing more (125 characters min.)
- Sudheer S Agile | Scrum | Kanban | JIRA | Confluence | Bitbucket | Atlassian | Tableau | Power BI | Process Consultant | Transformation Expert | Automation | Mentoring | Reliability Status Clearance
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Power query provides you these features in a button click to correct the data type1. Split cells2. Change data type3. Merge cells4. Pivot/Unpivot table5. Add new columns6. Calculated fieldsIt is easy to correct the data types.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
- Georg Lasar 🤪Vom Zahlenchaos zum freien Kopf 😎: Excel spielend meistern – ohne Stress, gekonnt mit Spaß. 💡 Innovatives Training: GENIUS-Methode | 🎓 Microsoft zert. Excel Experte | 🏆 4x Award-Gewinner für digitale Bildungsmedien
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Older Excel versions provide the legacy data import wizard. Newer Excel versions will run a new import dialogue box as soon as you import data, like a CSV-file and the like. If you still want to use the legacy import wizard for importing CSV files, then you can enable the legacy import wizard in the Excel options. Go to "FILE" and then choose "options" to open the Excel options.I recommend getting comfortable with the new "import wizard", which leads you to power query eventually, where you can split your data, append and manipulate them as needed. Ideally, you don't need to enter the Power Query editor and Excel recognizes your data as needed, ready to be imported right away.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
- Gerald Tesha Data Officer @ HETA | Data Visualization | Monitoring and Evaluation
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Fix the lineup! If Excel misreads, correct it manually. Right-click on the cell, choose "Data Types," and pick the correct one. Keep your data army in uniform.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
- Ali Muhammad Business Analyst | Delivering Actionable Insights and Driving Data-Driven Decision Making
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
For accurate data types, use the "Data Types" dropdown to correct misidentifications manually. Alternatively, select the column, go to "Data" > "Data Types," and choose the appropriate category. Correcting data types ensures consistency and eliminates errors in subsequent calculations.
Like - Devendra Dabkar 🇮🇳
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
One handy tool is the Text to Columns wizard. It helps you split your text based data into different columns based on a separator, like a comma or a space.To use it, go to the Data tab in Excel and look for the Text to Columns option. When you click on it, a wizard pops up. It asks you to choose the delimiter, where you want the new columns to be and how you want Excel to treat the data in each column.if you have a column with both text and numbers and you want excel to recognize the numbers as numbers, you can choose the appropriate data format like Date or Number in the wizard. This helps excel convert your data into the right type so you can work with it more effectively.
LikeLike
Celebrate
Support
Love
Insightful
Funny
4 How to convert your data types
Another method to correct your data types is to use the VALUE, DATEVALUE, or TIMEVALUE functions. These are functions that convert text data into numeric, date, or time data respectively. For example, if you have a cell that contains the text "100", you can use the formula =VALUE(A1) to convert it into the number 100. Similarly, if you have a cell that contains the text "1/1/2021", you can use the formula =DATEVALUE(A1) to convert it into the date 1/1/2021. You can also use these functions in combination with other functions, such as LEFT, RIGHT, MID, or FIND, to extract and convert parts of your text data.
Help others by sharing more (125 characters min.)
- Gerald Tesha Data Officer @ HETA | Data Visualization | Monitoring and Evaluation
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Shape-shifters! Use functions like TEXT(), VALUE(), or CONVERT() to transform data types. Ensure smooth transitions without losing vital information.
LikeLike
Celebrate
Support
Love
Insightful
Funny
3
- Georg Lasar 🤪Vom Zahlenchaos zum freien Kopf 😎: Excel spielend meistern – ohne Stress, gekonnt mit Spaß. 💡 Innovatives Training: GENIUS-Methode | 🎓 Microsoft zert. Excel Experte | 🏆 4x Award-Gewinner für digitale Bildungsmedien
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Excel also gets more intelligent with each release, so you also can use automatic help by Excel, which is often indicated through a small green triangle within a cell. It appears, when Excel assumes inconsistency in your data set.If you see more of those green triangles, then select the data range first and then choose from the context menu of the first cell to apply a new format to all selected cells. The flash fill feature in Excel may also help to quickly turn data into the appropriate data type.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
- Ali Muhammad Business Analyst | Delivering Actionable Insights and Driving Data-Driven Decision Making
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
To convert data types, use Excel functions like VALUE() or DATEVALUE(). For instance, transform text-formatted dates to true date values. Convert text numbers to numerical values using VALUE(). This ensures compatibility for sorting, filtering, and mathematical operations.
LikeLike
Celebrate
Support
Love
Insightful
Funny
- Devendra Dabkar 🇮🇳
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
You can make sure your data is in the right format and fix any issues with it by using functions like VALUE, DATEVALUE or TIMEVALUE. Let's say you have some text in a cell that should be a number, date or time. You can use these functions to convert that text into the correct type.if you have the text 100 in a cell and you want it to be a number, you can use the formula =VALUE() in another cell to convert it to the number 100. Similarly, if you have the text 1/1/2021 in a cell and you want it to be a date, you can use the formula =DATEVALUE() to change it to the date.These functions are handy because they can take text data and turn it into numbers, dates or times, making your excel data more accurate and easier to work with.
LikeLike
Celebrate
Support
Love
Insightful
Funny
5 How to validate your data types
A final step to ensure that your data types are correct and consistent is to use the Data Validation feature. This is a feature that allows you to set rules and restrictions for the data that you enter or allow in your cells. You can access the Data Validation feature from the Data tab on the ribbon. When you open the Data Validation dialog box, you will have the option to choose the criteria, the input message, and the error alert for your data validation. For example, you can choose to allow only whole numbers, decimals, dates, times, text lengths, or lists in your cells. You can also choose to display a message when a cell is selected or an error when a cell violates the validation rule.
Help others by sharing more (125 characters min.)
- Gerald Tesha Data Officer @ HETA | Data Visualization | Monitoring and Evaluation
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Run the drill! Regularly check your data types. Use formulas or pivot tables to validate consistency. Identify any rogue data types and bring them into formation.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
On Excel you can validate data types by going to the data tab and selecting data validation.Secondly, one can also use formulas such as '=ISDATE()' to validate the type. In this case, the formula returns TRUE if the cell contains a date.
LikeLike
Celebrate
Support
Love
Insightful
Funny
- Ali Muhammad Business Analyst | Delivering Actionable Insights and Driving Data-Driven Decision Making
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Excel's "Data Validation" feature helps validate data types. Define rules specifying acceptable data ranges or formats, preventing entry errors. This ensures that only valid data, aligned with intended types, is entered, enhancing data accuracy.
LikeLike
Celebrate
Support
Love
Insightful
Funny
- Hugo Venturini Palestrante, Consultor e Instrutor | MVP Data Platform | MOS Excel
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Este artigo começa tratando do tipo primitivo do dado, ou seja, da definição do que ele é de fato, se texto, número, data, etc., muda drasticamente para uma abordagem de outro recurso, que são os Tipos de Dados Inteligentes, que tem aplicação muito específica, e termina retornando aos tipos primitivos, mas mencionando um outro recurso, que é a validação de dados. Embora eu acredite ser válida tal abordagem, considero não ser a mais adequada se o intuito era contribuir para o aprendizado e esclarecimento.
Translated
LikeLike
Celebrate
Support
Love
Insightful
Funny
- Devendra Dabkar 🇮🇳
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Formatting is like adding labels. You can tell Excel what kind of data each cell holds, like numbers, dates or text. This makes your spreadsheet look cleaner and helps excel perform calculations correctly.Data validation is like setting rules. You can restrict what type of data users can enter into specific cells. For example, you can limit a cell to only accepting whole numbers, dates or choices from a dropdown list. This helps prevent typos and ensures consistency in your data.Think of formatting as labeling your boxes in a filing cabinet and data validation as setting rules for what can go in each box. Both help you keep your information organized and accurate.
LikeLike
Celebrate
Support
Love
Insightful
Funny
6 How to format your data types
Once you have validated and converted your data types, you can format them to make them more readable and meaningful. You can format your data types using the Number group on the Home tab on the ribbon. This group contains various options to change the number format, the decimal places, the currency symbol, the percentage sign, the comma separator, and the date and time format of your cells. You can also use the Format Cells dialog box to access more advanced formatting options, such as custom number formats, alignment, font, border, and fill. By formatting your data types, you can enhance the appearance and clarity of your data.
Help others by sharing more (125 characters min.)
-
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Once you have validated and converted your data types, formatting them makes them more readable and meaningful. Use the Number group on the Home tab in the ribbon to change number formats, decimal places, currency symbols, percentage signs, comma separators, and date/time formats. For more advanced options, use the Format Cells dialog box to access custom number formats, alignment, font, border, and fill settings. By formatting your data types, you enhance the appearance and clarity of your data, making it easier to interpret and analyze.
LikeLike
Celebrate
Support
Love
Insightful
Funny
1
- Ali Muhammad Business Analyst | Delivering Actionable Insights and Driving Data-Driven Decision Making
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
Proper formatting enhances data visibility. Customize formats using the "Format Cells" option. Choose date formats, number styles, or text alignments that suit your data. Formatted data aids readability and provides a visual representation consistent with its intended interpretation.
LikeLike
Celebrate
Support
Love
Insightful
Funny
7 Here’s what else to consider
This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?
Help others by sharing more (125 characters min.)
- Juan José García Guevara Administración de Empresas | Análisis Estratégico | Planificación
- Report contribution
Thanks for letting us know! You'll no longer see this contribution
En mi experiencia, la validación de datos en Excel puede emplearse en conjunción con fórmulas personalizadas para realizar validaciones más avanzadas. Por ejemplo, en el caso que desees validar números de teléfono de clientes que cumplan con una longitud específica y que comiencen con un determinado código de área, podrías combinar las funciones "ESNUMERO", "LARGO", "IZQUIERDA" e "Y" para restringir datos inconsistentes, procurando así la calidad de datos durante la fase de alimentación. Esta combinación de herramientas fortalece el control y reduce al mínimo la probabilidad de errores en la fase temprana, así obtendremos coherencia en la información almacenada de la base de datos.
Translated
LikeLike
Celebrate
Support
Love
Insightful
Funny
3
Data Cleaning
Data Cleaning
+ Follow
Rate this article
We created this article with the help of AI. What do you think of it?
It’s great It’s not so great
Thanks for your feedback
Your feedback is private. Like or react to bring the conversation to your network.
Tell us more
Tell us why you didn’t like this article.
If you think something in this article goes against our Professional Community Policies, please let us know.
We appreciate you letting us know. Though we’re unable to respond directly, your feedback helps us improve this experience for everyone.
If you think this goes against our Professional Community Policies, please let us know.
More articles on Data Cleaning
No more previous content
- What are best practices for data cleaning quality assurance and testing? 25 contributions
- What are some best practices for data anonymization and encryption? 13 contributions
- What are some challenges of data cleaning without standards and conventions? 12 contributions
- How do you choose the appropriate imputation method for missing values in categorical data? 19 contributions
- How do you write clear and informative code comments for your data cleaning scripts? 21 contributions
- How do you perform data cleaning on streaming or real-time data? 15 contributions
- How do you merge and join data from different sources and formats? 18 contributions
- How do you balance data cleaning and data analysis in your workflow? 17 contributions
- How do you ensure data quality after data cleaning? 22 contributions
- What are the latest trends and developments in data deduplication methods and algorithms? 9 contributions
- How do you handle different data formats and sources in your data cleaning projects? 67 contributions
- How do you apply data validation and protection rules in Excel to prevent data entry errors? 15 contributions
No more next content
More relevant reading
- Problem Solving How can you effectively clean data in Excel?
- Data Visualization How can you create a scatter plot with multiple data series in Excel?
- Business Intelligence How can you combine multiple Excel functions to enhance your data analysis?
- Business Intelligence How can you leverage Excel's VLOOKUP function for data analysis?