Introduction to Data Types in Excel

Availability : Excel for Microsoft 365, Excel for the Web, and Excel for Microsoft 365 for Mac

Data types are a real game changer in how we think about Excel. Being able to store many fields of data in a single cell is quite radical. These additional hidden fields of data can still be accessed using formulas in Excel while remaining hidden on the sheet.

There are a few different data types in Excel. These include data types that are connected to an online data source to pull in live data, such as currency exchange rates, and the ability to create your own custom data types using Power Query or Power BI.

The data types available to you are dependent upon a few factors including your version of Excel, what has been enabled by your IT administrators, and the language that you use for Excel.

This is a very new feature to Excel that is expanding fast with new data types and further language support becoming available.

Figure 14-1 shows the Data Types gallery on the Data tab in Excel.

Accessing the Data Type Fields with Formulas

Let’s get into the exciting stuff and write some formulas. We will see examples that extract and analyze data using the Geography, Currencies, Stock, and Power Query data types in this chapter. We will start with the Geography data type, but the examples shown apply to all data types.

When converting text values into a linked data type, this will often happen automatically as you type data onto a worksheet. In Figure 14-2, I am asked if I would like to convert the country names written in range B2:B4 to the Geography data type.

Otherwise, you can convert values to a data type by selecting the range of values and clicking the data type you need on the Data tab of the Ribbon.

These data types can store many fields in a single cell. We can access the fields of a data type using formulas, without having to display the field on the sheet. However, let’s start by looking at how we can insert some chosen fields onto the grid of Excel.

This can be done easily using the UI of Excel. Simply click a cell containing a data type and use the Insert Data button that appears in the top-right corner of that cell (Figure 14-3).

A list of the available fields is displayed (Figure 14-4). Simply click the field that you want to add. Excel writes the formula for you when you use the Insert Data button.

The same list of fields can be accessed using formulas. In Figure 14-5, the formula to access the list of fields for a data type is shown. Typing a period (.) after the reference to a cell containing a data type shows the list of available fields.

Click the field you want to use to insert the data. In Figure 14-6, the [Calling Code] field has been added using a formula. Because the name of the field contains a space, the square brackets [ ] are used to enclose the field name.

Further fields that you insert can themselves be a data type. In Figure 14-7, the [Capital/Major City] field has been added for each country. These cities are automatically recognized as Geography data types by Excel.

The range has also been formatted as a table. The data does not need to be in a table to utilize data types. However, there are many advantages to managing and working with data formatted as a table.

In Figure 14-8, the population for each [Capital City] has been returned with a simple formula. When referring to a data type, you refer to the cell in the same manner as we would if it contained a standard text or numeric value.

In this example, notice that the reference to the [Population] field in the formula does not contain square brackets []. This is because the field name does not contain a space or other illegal character such as the forward slash “/” used in the [Capital/Major City] field.

Aggregating the Values in a Data Type Field

Let’s move on to using functions with data type fields and begin with an aggregation function.

Now, a data type field does not need to be visible on the sheet to be used. In Figure 14-9, the following formula uses the AVERAGE function on the [Population] field of the data type in the [capital city] column of the table:

=AVERAGE(tblCountries[Capital City].Population)

FIELDVALUE Function

Availability: Excel for Microsoft 365, Excel for the Web, and Excel for Microsoft 365 for Mac

The FIELDVALUE function is used to extract a value from a specified field of a data type. We have just seen examples of how to achieve this using the “.” operator following a cell that contains a data type, for example, B2.[Calling Code] or [@Country].Population. So, you might be thinking why we would use the FIELDVALUE function.

The simple formula to extract a field from a data type does not work in all scenarios. The most common of these scenarios is when performing conditional calculations.

We will see an example using the FIELDVALUE within an IF function now and within a SUM function to perform a conditional sum later in this chapter.

This is the syntax of the FIELDVALUE function:

=FIELDVALUE(value, field_name)

• Value: A reference to the data type. It returns all fields from the referenced data type.

• [field name]: Name or names of the fields from which to extract the value. The field name must be entered within double quotes (“ ”).

In Figure 14-10, the following formula uses a simple IF function to display the text “Large” if the population of the capital city is greater than or equal to 3.5 million. Otherwise, the text “Small” is shown.

=IF(

FIELDVALUE([@[Capital City]],"Population")>=3500000,

"Large","Small")

The FIELDVALUE function is used in the logical test to extract the population of the capital city for use in the conditional test.

Using Functions with Data Type Data

Let’s now explore how some of our favorite Excel functions, such as XLOOKUP, SUM, and FILTER, can be used to extract and calculate data from these rich data types.

The technique for using formula and functions with data types applies for all kinds of data type. For the next few examples, we will demonstrate the functions with Power Query data types.

Although this book is about formulas, I feel it would be remiss of me to fully disregard a demonstration of how to create a Power Query data type. However, this will be brief, as it is not the focus of the chapter.

Creating a Custom Data Type with Power Query

Figure 14-11 shows a table of data about company stores named [tblStores]. This includes information such as the store name, region, last year’s revenue, and the name of the top performing sales representative.

This table can be found in the [data-types.xlsx] workbook, but one of the great strengths of Power Query is to pull data from external sources. This is not a Power Query guide, so we will briefly cover how to convert the data into a custom data type and then use the functions to work with that data type. We will not go into detail on any Power Query topic.

1. 1.

   Click the [tblStores] table and click Data ➤ From Table/Range to load it into Power Query.

    
2. 2.

   Select the columns to be used in the data type.

    
3. 3.

   Click Transform ➤ Create Data Type (Figure 14-12).

    

1. 4.

   In the Create Data Type window, type a name for the data type in the Data type name box and specify the Display column from the list (Figure 14-13). Click OK.

    

1. 5.

   Close & Load to a table in Excel.

    

Figure 14-14 shows the table loaded to cell A1 of a sheet named [Stores]. The table is named [StoresData], and the [Store Name] field is displayed on the cell as that was specified as the Display column in the Create Data Type window (Figure 14-13). The data type icon is shown to the left of the store name.

In cell C2, the beginning of a formula is shown to demonstrate that all fields of the custom data type can be accessed in the same manner as shown with the Geography linked data type.

XLOOKUP with Data Types

For the first function example, let’s see the XLOOKUP being used to search for and return a value from the data type based on a cell value.

In Figure 14-15, the following formula is entered in cell B2 to return the region for the store code stated in cell A2 and copied down:

=XLOOKUP(A2,StoresData[Stores].[Store Code],StoresData[Stores].Region)

And the following formula is entered in cell C2 to return the store name. Using XLOOKUP with data in a data type is simple:

=XLOOKUP(A2,StoresData[Stores].[Store Code],StoresData[Stores].[Store Name])

Conditional SUM Function with Data Types

Now, let’s use the incredible SUM function to count and sum values of the data type based on conditions.

In Figure 14-16, two SUM function examples are demonstrated. In cell C3, the following formula is used to count the number of stores where last year’s revenue was greater than or equal to 35,000. The last year’s revenue threshold to test against is stated in cell B3.

=SUM(--(FIELDVALUE(StoresData,"Last Year Revenue")>=B3))

The FIELDVALUE function is used to return the last year’s revenue value for testing. An array for TRUE and FALSE values is returned as results for the conditional test. To sum these values, the double unary (--) is used to convert the TRUE and FALSE values to 1 and 0.

The following formula is entered in cell C6 to sum the [No of Staff] values for stores in the region stated in cell B6 only. In Figure 14-16, the results for the region of the West are shown.

=SUM(

(FIELDVALUE(StoresData,"Region")=B6)*(FIELDVALUE(StoresData,"No of Staff"))

)

Note

Using the SUM function with arrays as demonstrated in these examples was explained in Chapter 10. Visit that chapter if you are interested in understanding how these formulas work in more detail.

FILTER Function with Data Types

For the final example of using some of our favorite functions with data types, we will see the FILTER function .

In Figure 14-17, the following formula is used to return results for the region stated in cell B3 only:

=SORT(

FILTER(CHOOSE({1,2,3},

StoresData.[Store Name],StoresData.[Last Year Revenue],StoresData.[Top Sale Rep]),

StoresData[Stores].Region=B3,""),

2,-1)

The CHOOSE function is used to specify the return of the [Store Name], [Last Year Revenue], and [Top Sale Rep] fields only. FILTER returns the rows for the South region only. And SORT then orders the results in descending order by column 2, the [Last Year Revenue] column.

Currency Conversion Using Data Types

With the Currencies data type in Excel, you can return live currency data such as exchange rates. If you do not see a Currencies data type in your version of Excel, the Stocks data type can be used instead. Both data types pull their information from the Stocks source.

Figure 14-18 shows the exchange rate being returned for two currency pairs – US Dollars to Euros and Danish Krone to Euros.

The [Price] field is used to return the exchange rate. The [Last Trade Time] field can be used to display the date that the [Price] was last updated.

To create the Currencies or Stocks data type, simply enter the two ISO currency codes separated by a forward slash (EUR/GBP), a colon (EUR:GBP), or by nothing at all (EURGBP). Select the cells containing the currency pairs and click Data ➤ Currencies or Data ➤ Stocks.

The values returned from the Currencies data type can be used within a formula to convert currencies. In Figure 14-19, the following formula is used to convert different currencies to the British Pound (GBP):

=[@Codes].Price*[@Total]

The exchange rate data being pulled into Excel can be updated by clicking Data ➤ Refresh All. The refresh schedule for Stocks data can be changed, if required, to set an automatic refresh schedule or to update when the workbook is opened:

1. 1.

   Right-click a cell containing the data type.

    
2. 2.

   Click Data Type ➤ Refresh Settings.

    
3. 3.

   Click the Stocks data type in the Data Types Refresh Settings pane to expand the refresh settings (Figure 14-20).

    
4. 4.

   Click the refresh option you want to use from Automatically every 5 minutes, On file open, or Manually.

    

Historical Stock Data with STOCKHISTORY

Availability : Excel for Microsoft 365, Excel for the Web, and Excel for Microsoft 365 for Mac

The STOCKHISTORY function returns historical stock price and currency rates based on a given symbol and date range. It can return a dynamic array of results from a single formula.

Simple STOCKHISTORY Example

Let’s start with a simple STOCKHISTORY example that pulls stock prices for a specified stock in the London Stock Exchange.

In Figure 14-21, cell B2 contains a drop-down list of four different stocks. The following formula is entered in cell B4 to return the daily close price for the last 15 days including today’s date. Trading does not occur at weekends and some holidays, so the number of dates listed by the formula will vary.

=SORT(STOCKHISTORY(B2,TODAY()-14,TODAY()),,-1)

The default values are used for the interval, headers, and properties arguments. So, the interval is set as daily, headers are inserted as part of the array, and the default properties of Date and Close are used.

The SORT function is added to order the array by the [Date] column in descending order.

Adding Properties to STOCKHISTORY

STOCKHISTORY provides six properties that can be returned by the function. These properties are entered in the order that you would like them to be returned.

In Figure 14-22, the following formula is entered in cell B7. It returns the stock data for the stock and exchange stated in cell B2. The date range for the information returned is specified in cells B5 and C5.

=SORT(STOCKHISTORY(B2,B5,C5,2,,0,5,2,1),,-1)

In this formula, the interval is set as monthly, and four properties are returned: Date, Volume, Open, and Close.

Inserting a Stock Chart Using STOCKHISTORY Data

The data returned by STOCKHISTORY can be presented in a chart for a greater visual of the stock trend over time.

When using STOCKHISTORY in its default manner, which returns the Date and Close price data only, a line chart would be great. But to play around more with STOCKHISTORY, let’s insert one of the stock charts available in Excel.

Excel provides four different stock charts natively (there are chart techniques to create your own variations). We will insert the Open-High-Low-Close stock chart.

When looking at the different stock charts in the Insert Chart window, the description of the Open-High-Low-Close stock chart states clearly that the columns need to be in that order (Figure 14-23). It also states that a label column is required such as dates or stock names. We will use the date column for the category labels in our chart.

In Figure 14-24, the following formula is entered in cell B4 to return the required information:

=STOCKHISTORY(B2,TODAY()-14,TODAY(),,,0,2,3,4,1)

It returns the properties of Date, Open, High, Low, Close (0,2,3,4,1) in that order, as requested by the stock chart description.

Data for the stock stated in cell B2 is returned and within the range of the last 15 days. The headers are also returned with the data.

To insert the stock chart

1. 1.

   Select range to be used for the chart data.

    
2. 2.

   Click Insert ➤ Insert Waterfall, Funnel, Stock, Surface, or Radar Chart button ➤ Open-High-Low-Close.

    

The chart is inserted and can be refined further as desired. Figure 14-25 shows the Open-High-Low-Close stock chart inserted in this example. The chart title has been linked to the value in cell B2.

In this stock chart, the vertical line represents the range between the High and Low values. The floating bar represents the range between the Open and Close values. If the close price is higher than the open price, the floating bar is filled with a color (white by default), and if it is lower, a different fill color is used (black by default). These colors can be changed.

Returning Historical Exchange Rates with STOCKHISTORY

As we saw earlier in this chapter, exchange rate data comes from the Stocks data type, even though a Currencies type is provided for simplicity. The STOCKHISTORY function can return historical exchange rate data just as it does for stocks.

For a demonstration of this, we will return historical exchange rate data for multiple currency pairs and for a specified five-day period.

In Figure 14-26, the following SEQUENCE formula is entered in cell D3 to return the sequence of five dates from the date stated in cell C1:

=SEQUENCE(,5,C1)

The following formula is then entered in cell D4 to return the exchange rate data. The range of currency pairs listed in range B4:B10 is used for the stock argument, and the reference to the spill range in D3 is used for the start date. No end date is provided.

=STOCKHISTORY(B4:B10,D3#,,,0,1)

In this example, the headers are disabled, as they are not required. And a single property of the close price is requested.

Line Sparklines are inserted in range C4:C10 for a quick visual of the exchange rate trend.

Summary

In this chapter, we learned how to use formulas to access and analyze data stored in rich data types.

The chapter began with an introduction to what data types are and the different data types available in Excel. We then saw how to use some popular functions such as SUM, FILTER, and IF to work with data types. We also covered a couple of new functions – FIELDVALUE and STOCKHISTORY.

Data types are a new feature of Excel, only a few years old, and it is exciting to see how they grow and mature as a tool.

In the next chapter, we will learn lots of new Excel functions – LET, LAMBDA, and the seven LAMBDA helper functions.

The LET function is a fantastic addition that makes complex formulas more efficient. It improves their calculation speed and readability.

With LAMBDA, we can create our own custom functions. This is so much fun. We now have the tools to create the functions that we want from within the Excel experience. These functions can then be distributed to others.