TIPS for CLICKS : Summarizing large data sets with Pivot Tables

Are you working with large tables in Excel and preparing summary reports? If so, you need to use just some mouse clicks to make interactive summary of your records efficiently. This is it: Pivot Tables tool.

This is mainly for starters. Let's assume, you're creating a large table of data, several columns wide, thousands of rows. It's a good practice to organize the table  in such a way that it

• has unique and relevant headers,
• doesn't contain any blank cells and subtotals, and
• is named (by typing the name in the Table Name box, located under the Table Design tab in the ribbon, at the upper left corner of your worksheet)

Now your table is ready for converting (if not converted yet) into the Excel Table. It might look like in this small sample:

STOCK PORTFOLIOS Tracking with Yahoo Finance in Excel - Part 3

After completing Excel tables (in Part 2) for tracking trades in your account, it's time for creating some summary of your Portfolio performance. For this purpose you can create another Excel Table (e.g. called "Portfolio Status") and a chart based on the table content. This will allow you to conduct a quick and easy visual evaluation of your investment positions.

Start with adding a new worksheet named e.g. "Performance". First, create the table. Here is an exemplary format and content:

As in previous tables the cells filled with green colour indicate data entry cells. The remaining cells contain formulas filled automatically after entering all necessary data. Cells in column E are conditionally formatted, so that cells showing gains are filled with yellow background.

STOCK PORTFOLIOS Tracking with Yahoo Finance in Excel - Part 2

In Part 1 I've created exemplary Excel Table for your Portfolio of stocks and provided steps you need to follow for updating the table as you'll need from time to time.

Now add a new worksheet in your workbook. Let's call it "Trades".

In this worksheet, based on the raw data in your created Portfolio table you can create Excel Tables for keeping track of your stock trading, buying and selling. 

Let's say, you just started with couple of records in your Yahoo Finance portfolio and created this Excel Table:

Now you'll reference this table and create two tables for keeping track of your trades. Here is an example of such tables, you can follow if you like:

STOCK PORTFOLIOS Tracking with Yahoo Finance in Excel - Part 1

Do you want to manage your stock portfolio in Yahoo Finance and follow its performance in your private Excel workbook rather than somewhere in a cloud? If so, I'll show here, step by step, how to do it.

I assume that you are actual user of the Yahoo Finance site. If not, then sign up there with user Id. and password.

Create your own portfolio, if you don't have any yet, by clicking on Create Portfolio in the menu, entering portfolio name (e.g. "BestView") and selecting the currency. Click on Submit button.

At this stage you can select either one of the available Views, e.g. "DayWatch", or use Create New View option to make your own selection of portfolio columns you want to include in your View. Having done the selections, now enter your stock symbols you need to follow.

When you finish, your portfolio could look like e.g. this in the "BestView" (View using your own selections of columns: 

If you change your mind, you can still edit the table after clicking on Edit View). 

TIPS for CLICKS: Working with Excel Tables

Excel Tables are one of those basic, most useful features used for data recording and analysis. Excel makes its tables smarter, more versatile, cognitive, and easy to use all the time.

However, if you spend lots of your time working with Excel tables it's worth to remember all keyboard and mouse shortcuts available for tables. They help to use the tables more efficiently and save some time. Here is my collection of the most frequently used shortcuts:

 

How to create MUTUALLY DEPENDENT CELLS

Mutual dependence of Excel cells means that some two cells in your workbook refer to each other. If the value of one of the two dependent cells changes it causes change of value in the other cell dependent on it. This normally leads to a problem called "circular reference". However, it is possible to set up mutually dependent cells by following steps shown in my example below.

TIPS for CLICKS: Custom Lists

Using Excel Custom Lists

What are Custom Lists used for? They are used mainly for SORTING or FILLING any regular Excel lists and tables in a specific user-defined order. Using the lists may save you a lot of time when frequent sorting or filling your lists is needed when working with Excel data.

Excel provides couple of built-in lists by default, as you can see below: 

 

How you can use them? Here is the way (if you haven't used them never before):

• in your selected cell type in any entry from the custom list, e.g. Jan, or Mon, etc.
  
• click on the small box at the bottom right corner and drag it any way you want to - down, up, right, left - to populate any number of cells, in rows or columns, with the list items.

There are also many "hidden" custom-like lists you can built and use easily as needed. Below are some examples of such lists that Excel accepts and readily auto fills for you: