How to Add Data Types in Excel: Streamline Your Workbook With Smart Formatting

I spent years manually formatting Excel columns until I discovered data types. This one feature transformed how I handle dates, currencies, and stock symbols in spreadsheets.

With a few clicks in the Data tab, you can convert plain text into rich, connected data. Excel recognizes patterns and applies intelligent formatting automatically—no more manual entry errors.

You’ll get live data connections that update in real-time, cleaner visuals, and formulas that just work. My financial reports now pull current stock prices without any extra effort.

Works in Excel 365 or 2019. Here’s how to set it up properly from the start.

What Youll Need

Instructions

Here’s the procedure I follow to integrate smart formatting into any workbook.

Open Excel and navigate to the Data tab. In the Data Types group, you’ll see the icon for adding structured data categories. If this option is grayed out, ensure you’re using Excel version 1905 or later—earlier builds lack this feature entirely.

I always verify my Office subscription status first. Click File > Account and confirm you have an active Microsoft 365 subscription, as this functionality is restricted to subscription versions of Excel.

Highlight the cells containing your raw data. For this to work effectively, your data should be consistent—don’t mix product names with stock numbers in the same column. Click the Data Types button and select a category like Stocks, Currency, or Geography.

Excel will attempt to match your entries to its database. Watch for the small icon that appears in each cell—a tiny stock chart for financial data, or a globe for geographic entries. This visual cue confirms the data type has been applied successfully.

Once the data type is applied, you’ll notice a small icon appears to the right of your selected cells. Click this icon to reveal a menu of additional properties you can extract. For a Stocks data type, you might see options like Price, Change, or Market Cap.

Select the property you want to add, and Excel will create a new column with that information. The data remains linked to its source, so if you update your original entries, the extracted properties will update automatically within a few seconds.

Review your data for any cells marked with a question mark icon. This indicates Excel couldn’t match your entry to its database. Hover over the icon to see suggested alternatives. You can either select the correct match from the list or ignore the suggestion if your data is correct as-is.

For geographic data, I’ve found that using standard abbreviations (like CA for California) works better than full names. If you’re working with international data, ensure you’re using the correct country codes as recognized by Excel’s database.

Data types in Excel are dynamic, but they don’t update in real-time. To refresh your data, click the Data Types button in the Data tab and select Refresh All. This ensures you’re working with the most current information, especially important for stock prices or currency rates.

If you share your workbook with others, they’ll need to have the same data types enabled in their version of Excel. The data will still display correctly, but they won’t be able to refresh or extract additional properties without the proper Excel version and subscription.

Tips and Common Mistakes

These insights will help you avoid common pitfalls when working with data types in Excel.

Version Verification: Always check your Excel version before starting—this feature only works in version 1905 or later and requires an active Microsoft 365 subscription. I learned this the hard way after spending hours troubleshooting on an older version that simply didn’t support the feature.

Data Consistency: In Step 2, ensure your data is uniform in each column. Mixing different types of entries (like product names with stock numbers) will confuse Excel’s matching algorithm. The cleaner your data, the more accurate the automatic matches will be.

Update Timing: Remember that data types don’t refresh in real-time. In Step 5, when you click Refresh All, allow a few seconds for the updates to propagate through your workbook. This is especially important for time-sensitive data like stock prices.

Geographic Data Tip: For geographic entries in Step 4, standard abbreviations (like CA for California) often work better than full names. This matches Excel’s database format more reliably and reduces those frustrating question mark icons.

Frequently Asked Questions

Adding data types in Excel is a game-changer for organizing and analyzing data efficiently. 💡 With just a few clicks, you can transform raw data into structured, actionable insights. Ready to take your Excel skills further? Try experimenting with linked data types to pull real-time information directly into your spreadsheets!