VideoPhoto

31 August, 2021

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:

27 August, 2021

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:

26 August, 2021

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). 

25 August, 2021

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:



 

23 August, 2021

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.

22 August, 2021

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:

20 August, 2021

Custom Excel Worksheet / Workbook Design

Have a specific problem in using Excel?

Maybe I could help...

The tools Excel provides for its users allow creating the abundance of solutions for entering, analysing, presenting and storing of datasets. However, this usually requires more or less in-depth knowledge of the software. Sometimes you may need to create a specific solution to a complex computational task, but your experience isn't sufficient to do that. In such situations you may need to look for help. I could be able to provide some no-obligation advice or assistance in solving your problems related to organizing your data and analysing them according to your needs. You could probably save some time, improve your design, etc..

Define your specific problem or your custom design needs and let me know about them using the Contact box located at the right side of this page. I may be able to provide some assistance in design details and analysis of data or actually design a spreadsheet for you.

Here are just couple of examples, fragments of worksheet designs, I've created some time ago for my own needs.

19 August, 2021

TIPS for CLICKS: Working with Functions and Formulas

Excel FUNCTIONS = Predefined formulas used for specific values in a specific order. They operate usually on data entered in range of cells and increase the Excel users productivity. Total number of the functions available in Excel is close to 700.

Some of the most important of them (most frequently used) are: SUM, IF, MIN, MAX, AND, OR, AVERAGE, COUNT, DAYS, VLOOKUP, DATE, ... etc. Examples of functions working with text: UPPER, LOWER, PROPER,... etc.

Excel FORMULAS = Expressions operating on values in a range of cells and operators. Can utilize several Excel functions and work with both numeric and textual data. ARRAY formulas are a special type of formulas, and can be very complex.

Here is the list and description of some of the most useful shortcuts used in conjunction with Functions and Formulas. I hope you'll find them helpful.

16 August, 2021

TIPS for CLICKS: Formatting / Editing / Moving around in a Workbook

Excel is equipped with hundreds of formatting and editing tools, key codes and alternative handling of data. Some of them are worth more useful than others. The following list presents some of the most helpful and time saving tips for frequent users.

Format Painter > If you double-click the Painter in the ribbon you can copy formatting of your selection (cell, range) repeatedly or singularly into multiple disparate cells or ranges. Click Format Painter once to quit it, if necessary.

Multiple lines in a cell > Two or more lines can be inserted into a cell by pressing ALT+ENTER after entering some value in the original line. It's just another way of wrapping text in a cell.

Borders >  If you select (in Home tab) Borders>More Borders..., you can select and add diagonal lines in active cell or range of cells to divide them e.g. in two parts. Then, you can add some value/text into the cell and press ALT+ENTER to type another value/text in the second line. Alignment of the entries can be fixed using spacebar, if needed. Example:

15 August, 2021

TIPS for CLICKS: Using drop-down lists for data entry

This is an excellent helper for data entry in Excel. When we create worksheets with tables of data or databases, in many cases there are text entries, expressions or phrases that we have to enter repeatedly many times. In such situations we can create lists of entries, like people names, phone numbers, cities, days of the week, months, whatever is included in tables, and then create drop-down lists based on the entries. Later, instead of typing the entries one by one in our table we just select the entries from the drop-down list.

Here is a simple example, based on the list of fruits. Follow the steps below to create the list.

14 August, 2021

TIPS for CLICKS: Data entry

There are plenty of shortcuts provided in Excel program for data entry. Their purpose is twofold:

  • to reduce data entry time, and
  • to reduce data entry mistakes, to avoid errors

I don't think that average Excel user can remember or need to use all of them. However, several of those shortcuts are helpful for almost all users and are worth remembering.

Here is my very short list of shortcuts for data entry and basic editing.

08 August, 2021

Workbook design: basic principles

Design your workbook for both EFFICIENCY and LOOK & FEEL.
  • First of all - plan it well for your specific purpose, in order to avoid complications down the road. In cases where there is complexity of data and calculations, use multiple worksheets within the workbook and label them properly (name them by their purpose) to separate clearly data input, assumptions, calculations, output, tables, charts, etc.
  • Use highlighting/shading to differentiate, in particular, data entry parts (input) from outcome (results, report).
  • If you design the workbook for multiple users, add instructions and documentation, record of changes, wherever needed.
  • Use Comments or Callouts features to add meaningful explanatory notes to some cells, e.g. to explain more complex calculations, especially for external users.
  • Break formulas involving complex calculations into multiple cells to make any troubleshooting much easier. Use cell references rather than numbers (constants) to avoid errors related to input. Any assumptions must be clearly visible in worksheet layout.

05 August, 2021

How to highlight space between two plotted curves / lines

If you want to enhance your Excel charts, to fill with color some target range of the data, this procedure may be helpful.

I'm providing here two examples of such enhanced charts. In addition, I've included procedure for calculation of the surface area bounded by the two curves/lines.

Chart #1

01 August, 2021

Weather forecast in Excel

If you need to keep your eye on weather conditions in several or more places in your country or in the world, it may be convenient to have an easy access to actual detailed forecasts in Excel workbook. It's relatively easy to create Excel worksheet with access to forecasts for locations of your specific interest. Here is a fragment of such worksheet I've created for my personal use:

The QR codes refer to weather forecasts for locations shown underneath. You just click on selected QR square to display the forecast for the location. For example: