<center>https://theexcelclub.com/wp-content/uploads/2020/06/1_rich-data-types-in-Excel.png</center> <br/>Rich Data types in Excel are now included in Excel 365. These Rich data types are connections to live data and host a suite of information and data types within the one cell. Yes, that's right, loads of data and data types in one cell! And yes, this is still Excel.
The detailed information is contained within cards and this information is easily extracted into the Excel worksheet.
At present, there are two rich data types available. Stocks and Geography. These can be located on the Data ribbon under the grouping Data Types.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/1_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="371" height="148">
Using AI, Excel will recognize different location types such as Countries, Cities and even counties under the Geography data type. With the Stock data type, Excel will recognize stocks on major exchanges, some currency pairs, and even some cryptocurrency pairs.
https://youtu.be/G6RGNthmw1I
<h3><a name="_Toc42616052"></a>How to use Rich Data Types</h3>
Converting data to rich data types is straight forward. Add text to the cell that released to either stock, currency pairs or Geography. Then from the Data ribbon select either Stock or Geography.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/2_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="763" height="365">
You will recognize a rich data type by the icon beside the text. For stock and currency related data you will see columnar building icon on the left side of the cell. For Geography data you will see a flag icon on the left side of the cell.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/3_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="318" height="159">
If Excel is unable to find a rich data type to match the cell contents, a question mark will be shown on the left of the cell. By clicking the question mark a data selector pane will open. From here you can select between different data sources for your search.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/4_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="316" height="489">
Once you have converted data to a rich data type, you can view the information from the card by hovering over the icon and left clicking on the mouse. You can also use the keyboard shortcut Ctrl + Shift + F5 to show the card.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/5_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="534" height="417">
You can also quickly add data from the card to your spreadsheet. This is achieved by clicking on the icon or the insert data field. All fields contained within this card can be extracted to your worksheet.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/6_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="514" height="550">
For Example, selecting the 52 week high and the 52 week low will add the data to the next cells in the worksheet.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/7_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="492" height="139">
When you select a cell that contains a field from a rich data type, you will note from the formula bar, the data is extracted using a formula.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/8__rich-data-types-in-Excel.png" alt="rich data types in Excel" width="537" height="205">
<h3><a name="_Toc42616053"></a>Using Formulas with rich data types</h3>
As you get more familiar with working with these rich data types and the fields available you can add the fields using a formula instead of using the icon or insert field option.
To add a field from a rich data type using a formula, select the sell you want to see the data in. Enter = and the cell reference that contains the rich data type. A drop down will appear showing you all the fields available.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/9_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="691" height="299">
Select the required field, press tab to enter the field to the formula and press enter.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/10_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="675" height="302">
You will note that the fields are show in square brackets.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/12__rich-data-types-in-Excel.png" alt="rich data types in Excel" width="708" height="174">
This way of extracting fields from a rich data type cell is known as the Dot formula type.
There is also a new function, FIELDVALUE that will allow you extract card data from a rich data type cell.
The syntax for FIELDVALUE is
=FIELDVALUE(value, field_name)
Where the value is the rich data type cell, and the field name is the field you want to extract.
When using FIELDVALUE, you must place the field name in quotes. For example
=FIELDVALUE(A2, “52 week high”)
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/11_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="429" height="180">
By using either the DOT formula type or the FIELDVALUE function, you can use field data for calculations. For example, if you wanted to calculate the difference between the 52 week high and the 52 week low you could use either of the following
=FIELDVALUE(A2,"52 week high") - FIELDVALUE(A2, "52 week low")
Or
=A2.[52 week high]-A2.[52 week low]
Where A2 contains the rich data type which is of type Stock.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/13_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="886" height="136">
<h3><a name="_Toc42616054"></a>Using Tables with Rich Data types</h3>
Excel tables give many benefits over using data in a non-table format. These benefits are applied also to rich data types.
In the examples we have looked at so far, our data has not been in table format. When we extract card information while not in table format, no headers appear telling us what data field we have extracted. However, when we use a table format, the headers do appear.
To convert data to table format, the keyboard shortcut is CTRL + T. Once is table format, when fields are extracted from the Rich data type cell, headers appear in the table allowing you quickly see what each data point relates to.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/19_rich-data-types-in-Excel.png" alt="" width="522" height="140">
<h3><a name="_Toc42616055"></a>Currency Pairs</h3>
The stock options for rich data types also recognized foreign currency paid and even some cryptocurrency pairs. Not all currency pairs are available yet, and when you are looking for currency pair data, you need to enter your data in the correct format of first currency/second currency.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/14__rich-data-types-in-Excel.png" alt="rich data types in Excel" width="476" height="143">
<h3><a name="_Toc42616056"></a>Updating Rich Data types</h3>
Rich data types are live connections. This means you can keep ensure you data has the most recent information with ease. To update the values from the rich data types, from the Data tab on the ribbons select Refresh All.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/15__rich-data-types-in-Excel.png" alt="rich data types in Excel" width="212" height="127">
<h3><a name="_Toc42616057"></a>Discussion – Rich Data types in Excel</h3>
<strong>Why don’t you explore some rich data types?</strong> We have looked at both Stocks and Currency pairs. However, there is a third type, and this is Geography. Depending on the location type will depend on the data fields that become available. For Example, if you select a county fields will include Admin Divisions, Area, Population and Time Zones.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/16__rich-data-types-in-Excel.png" alt="rich data types in Excel" width="491" height="371">
However, if you enter a country, fields will include Largest City, Households and much more.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/17_rich-data-types-in-Excel.png" alt="rich data types in Excel" width="186" height="406">
It is also possible to extract further rich data type fields from an existing rich data type.
For example, if we enter London and convert this to a rich data type and select Admin Division (state/province), a new rich data type cell will be populated. In this case, England. You can then extract further information about the secondary rich data type.
<img src="https://theexcelclub.com/wp-content/uploads/2020/06/18__rich-data-types-in-Excel.png" alt="rich data types in Excel" width="680" height="104">
Open a blank Excel workbook and enter the county you live into a blank cell. Select the data type Geography and then extract the Area and Population. What values do you get?
Now using a formula from the Rich data type cell, calculate the Population per Sq KM. (area). What answers did you get? Which formula do you prefer to use, the Dot formula or the function FieldValue?
If you had a large workbook and you wanted to locate rich data types, how would you go about this?
<p><center><strong>Before you watch the video below showing you how to do this, give it a try and leave a comment below.</strong></center></p>
https://youtu.be/1OgWhrSATuM
<h3><center>Take A FREE course with us Today!</center></h3>
<a href="https://courses.theexcelclub.com/courses/practical-beginner-excel-365"><img src="https://theexcelclub.com/wp-content/uploads/2019/01/beginner-excel-259x300.png" alt="FREE beginner excel training" width="259" height="300"></a>
<center>The Ultimate Excel Formulas Course</center></h3>
* includes XLOOKUP and will soon include Dynamic Arrays
<a href="https://courses.theexcelclub.com/courses/the-ultimate-excel-formulas-course-excel-365"><img src="http://theexcelclub.com/wp-content/uploads/2019/09/excel-365-formulas-course-254x300.png" alt="" width="254" height="300"></a>
<center>Become a Power Pivot Hero</center></h3>
<a href="https://courses.theexcelclub.com/courses/power-pivot-excel-365-from-zero-to-hero" data-elementor-open-lightbox=""><img src="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?fit=441%2C513&ssl=1" sizes="(max-width: 441px) 100vw, 441px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?w=441&ssl=1 441w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=258%2C300&ssl=1 258w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=410%2C477&ssl=1 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=100%2C116&ssl=1 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=275%2C320&ssl=1 275w" alt="Power Pivot online training course" width="441" height="513" data-lazy-loaded="1"></a>
<center>GET and TRANSFORM DATA like a PRO</center></h3>
<strong>Power Query Excel 365</strong>
<a href="https://courses.theexcelclub.com/courses/power-query-excel-365-get-and-transform-data" data-elementor-open-lightbox=""><img src="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?fit=432%2C455&ssl=1" sizes="(max-width: 432px) 100vw, 432px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?w=432&ssl=1 432w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=285%2C300&ssl=1 285w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=410%2C432&ssl=1 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=100%2C105&ssl=1 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=275%2C290&ssl=1 275w" alt="" width="432" height="455" data-lazy-loaded="1"></a>
<center>Learn DAX for Power Pivot and Power BI</center></h3>
<a href="https://courses.theexcelclub.com/courses/master-dax-fundamentals-power-bi-amp-power-pivot" data-elementor-open-lightbox=""><img src="https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?fit=434%2C460&ssl=1" sizes="(max-width: 434px) 100vw, 434px" srcset="https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?w=434&ssl=1 434w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=283%2C300&ssl=1 283w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=410%2C435&ssl=1 410w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=100%2C106&ssl=1 100w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=275%2C291&ssl=1 275w" alt="DAX for powerpivot course" width="434" height="460" data-lazy-loaded="1"></a>
<h4><center><strong>Best Value Excel and Excel Power Tool Learning. Access All Areas, Unlimited Learning Subscription</strong></center></h4>
<a href="https://courses.theexcelclub.com/courses/access-all-areas"><img src="http://theexcelclub.com/wp-content/uploads/2019/09/access-all-areas-262x300.png" alt="" width="262" height="300"></a>
<center><strong>SIGN UP FOR OUR NEWSLETTER TODAY – GET EXCEL TIPS TRICKS AND LEARN AND EARN ACTIVITIES TO YOUR INBOX</strong></center></h4>
<a role="button" href="https://theexcelclub.newzenler.com/f/email-signup">SIGN UP</a>
<h3> </h3>
<h3> </h3>
<h3> </h3>
<center>Take A FREE course with us Today!</center></h3>
<a href="https://courses.theexcelclub.com/courses/practical-beginner-excel-365"><img src="http://theexcelclub.com/wp-content/uploads/2019/01/beginner-excel-259x300.png" alt="FREE beginner excel training" width="259" height="300"></a>
<center>The Ultimate Excel Formulas Course</center></h3>
* includes XLOOKUP and will soon include Dynamic Arrays
<a href="https://courses.theexcelclub.com/courses/the-ultimate-excel-formulas-course-excel-365"><img src="http://theexcelclub.com/wp-content/uploads/2019/09/excel-365-formulas-course-254x300.png" alt="" width="254" height="300"></a>
<center>Become a Power Pivot Hero</center></h3>
<a href="https://courses.theexcelclub.com/courses/power-pivot-excel-365-from-zero-to-hero" data-elementor-open-lightbox=""><img src="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?fit=441%2C513&ssl=1" sizes="(max-width: 441px) 100vw, 441px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?w=441&ssl=1 441w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=258%2C300&ssl=1 258w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=410%2C477&ssl=1 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=100%2C116&ssl=1 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=275%2C320&ssl=1 275w" alt="Power Pivot online training course" width="441" height="513" data-lazy-loaded="1"></a>
<center>GET and TRANSFORM DATA like a PRO</center></h3>
<strong>Power Query Excel 365</strong>
<a href="https://courses.theexcelclub.com/courses/power-query-excel-365-get-and-transform-data" data-elementor-open-lightbox=""><img src="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?fit=432%2C455&ssl=1" sizes="(max-width: 432px) 100vw, 432px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?w=432&ssl=1 432w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=285%2C300&ssl=1 285w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=410%2C432&ssl=1 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=100%2C105&ssl=1 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=275%2C290&ssl=1 275w" alt="" width="432" height="455" data-lazy-loaded="1"></a>
<center>Learn DAX for Power Pivot and Power BI</center></h3>
<a href="https://courses.theexcelclub.com/courses/master-dax-fundamentals-power-bi-amp-power-pivot" data-elementor-open-lightbox=""><img src="https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?fit=434%2C460&ssl=1" sizes="(max-width: 434px) 100vw, 434px" srcset="https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?w=434&ssl=1 434w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=283%2C300&ssl=1 283w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=410%2C435&ssl=1 410w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=100%2C106&ssl=1 100w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=275%2C291&ssl=1 275w" alt="DAX for powerpivot course" width="434" height="460" data-lazy-loaded="1"></a>
<center><strong>Best Value Excel and Excel Power Tool Learning. Access All Areas, Unlimited Learning Subscription</strong></center></h4>
<a href="https://courses.theexcelclub.com/courses/access-all-areas"><img src="http://theexcelclub.com/wp-content/uploads/2019/09/access-all-areas-262x300.png" alt="" width="262" height="300"></a>
<center><strong>SIGN UP FOR OUR NEWSLETTER TODAY – GET EXCEL TIPS TRICKS AND LEARN AND EARN ACTIVITIES TO YOUR INBOX</strong></center></h4>
<a role="button" href="https://theexcelclub.newzenler.com/f/email-signup">SIGN UP</a>
<h4><em>New to Excel? Check out our </em><a href="https://theexcelclub.com/excel-101-getting-started-with-excel-365/"><em>Ultimate beginner Excel Guide here</em></a><em>.</em></h4>
<h4><em>New to Excel Power Tools? </em><a href="https://theexcelclub.com/excel-power-tools-the-future-of-excel/"><em>Check out these amazing Power Pivot, Power Query and Power view demonstrations</em></a></h4>
<h4><em>New to DAX for Power Pivot and Power BI? </em><a href="https://theexcelclub.com/dax-sum-and-sumx-functions/"><em>Let us help you get started</em></a></h4>
<h3><strong>Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox</strong> <strong>with Earn and Learn activities.</strong></h3>
<a href="https://theexcelclub.newzenler.com/f/email-signup"><strong>SIGN UP NOW</strong></a>
<center>
<strong>Now there is value in Learning with The Excel Club and our Learn and Earn activities.</strong>
<strong>We are the first Excel, PowerBI and DAX blog in the world where you can Earn tokens while you Learn.</strong>
<h3>To <a href="https://theexcelclub.com/learn-and-earn-steem-activities/">Find out more now and start earning while you are learning Excel and Power BI</a></h3>
<h2>Enjoyed this Excel Article? Before you leave, it’s only fair that you share</h2>
<br /><center><hr/><em>Cross posted from my blog with <a href='https://wordpress.org/plugins/steempress/'>SteemPress</a> : https://theexcelclub.com/rich-data-types-in-excel/ </em><hr/></center>
hiveblocks