How do you use the stock data type in MS Excel?

This is the easiest way to work on your stock holdings using Excel. The stock data type is awesome. Just one entry in a cell gives everything you want to know about your stock. You can build applications with it, but I will show you how to do it in three simple steps.

Step 1. Open a cell and enable it for stock data type using the Data menu.

Step 2: Open a cell or cells (I am just showing ROKU in one cell). You can type in stock ticker symbols in any number of rows in the same column. Highlight the column each row of which contains a ticker symbol and click Stocks in the Data Menu. By the way, I just typed ROKU and it changes to Roku Inc.(XNAS:ROKU)

Step 3: Now you pick the items you want to see in the little icon with a + in green. I choose Price (scrolling down this list) and in the adjoining column the current price will be displayed. You can see other items like 52 Week high, 52 Week low, Industry, etc. 

I have added 5% down, 15% down of the stock I sold on a particular date, etc. if you are interested in using it for trading. you could also have a link to your investment account and trade as easily.

How do you hide and unhide worksheets?

You can hide and unhide all columns in an Excel worksheet as shown in the following images:

I will hide all columns in HideAll.xlsx by clicking in the corner as shown. All cells are highlighted.

Now I go to the VIEW menu and click Hide.

Now everything is hidden. Notice even the worksheet name.

 Now I go back to VIEW and click on View to activate the Unhide submenu item.

 I click the Unhide button and I get the window for unhiding where I can choose to unhide the file.

Click on HideAll.xlsx. The HideAll.xlsx is displayed showing all columns and rows.

How do you query data in a MS Excel Worksheet?

Excel has a tool called Power Query which can be used to query data from many data sources, external data sources, or a source in an Excel worksheet, such as a table.

Here are the types of external data you can query using Excel.
Power Query Data Sources

Web page

Excel or CSV file

XML file

Text file

Folder

SQL Server database

Microsoft Azure SQL Database

Access database

Oracle database

IBM DB2 database

MySQL database

PostgreSQL Database

Sybase Database

Teradata Database

SharePoint List

OData feed

Microsoft Azure Marketplace

Hadoop File (HDFS)

Microsoft Azure HDInsight

Microsoft Azure Table Storage

Active Directory

Microsoft Exchange

Facebook

In the present post, you can see how you can query data in a worksheet, using Power query.

The following three steps show how you may do it.

 In the above worksheet, I created a table. Inserted three rows of data and highlighted them. Then I clicked, Insert and, picked up Create Table from the drop-down. This gave me the second dialog Create Table pre-filled with my data. I then clicked OK to create a table as shown in the bottom of the above.

Now I clicked Data and Query and Connections. I then clicked the option From/Table Range to Open the Power Query Editor. 

Now it is straight forward to query the Table in Excel worksheet.

More on Power Query here:

https://support.office.com/en-us/article/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605