• 512-991-7197
  • This email address is being protected from spambots. You need JavaScript enabled to view it.
  • Always Open

Welcome to Microsoft Excel Tips & Tricks

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
To calculate the age of a person, a pet, a project or even some device, you need to compute a number of complete years. The issue is that it doesn’t enough to calculate a year’s difference. Excel proposes very useful formula that can help you to calculate that very fast.

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
When you fill data in some spreadsheet, it can be necessary to create dependent drop-down lists. The organization of these drop-down lists can be different – they can be filled step-by-step from left to right in the same row (like in the example below), cascading or even computed (see How to create a computable drop-down list).

For example, if you have a spreadsheet with groups of expenses:

1

In this spreadsheet, there are different groups of expenses, where each group proposes different lists of expenses. To create dependent lists of expenses, do the following:

   1.   For every dependent drop-down list, create a named range (see Creating and using named ranges), for example:

2

Note: To simplify future customization, name these ranges as named groups. If your group named with two or more words, use symbol “_” (as “Fees & Charges”).

   2.   Create the first drop-down list (see Creating a Drop-Down List in a Cell):

   2.1.   On the Data tab, in the Data Tools group, click Data Validation:

1 1

   2.2.   In the Data Validation dialog box, on the Settings tab:

2 1
  • In the Allow drop-down list, select List.
  • In the Source box, specify the range that contains the items.
  • Make sure that the In-Cell Dropdown option is checked.

You will see the drop-down list:

3

   3.   Create the dependent drop-down list:

4

   3.1.   On the Data tab, in the Data Tools group, click Data Validation.

   3.2.   In the Data Validation dialog box, on the Settings tab:

5
  • In the Allow drop-down list, select List.
  • In the Source box, specify the range:

    = INDIRECT (<cell with name of range>).

    Note: If it is necessary to make any changes for the name, add other formulas, for example:

    = INDIRECT (SUBSTITUTE (<cell with name of range>, <old text>, <new text>))

    For example, to find the range Fees_Charges:

6
  • Make sure that the In-Cell Dropdown option is checked.

   4.   Click OK:

7

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
One of the essential parts of any work with data is presenting results. You can use many different charts, diagrams in Microsoft Excel or PowerPoint.

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
Many financial and non-financial companies like Yahoo provide investments portfolio tracking services but do not offer the ability to change the appearance of the charts. Microsoft Excel gives you full flexibility by providing different types of customizable charts. E.g., there are a few variations of Area chart for tracking the value and costs of your portfolio.

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
When you work with data in Excel, often you need to add more and more of new data. However, it takes some time to update formulas, charts, tables, when you add new data. Excel proposes functions that can help to customize your data and avoid routine updating. This tip is about creating an automatically extended (also known as dynamically updated or just dynamic) drop-down lists.

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
There are countless reasons why you might need to have stock prices in your Excel spreadsheet. In the past, you had to copy and paste from Yahoo Finance or another financial service, but at the moment, Microsoft offers a new way to add and update some stock information such as stock prices, changes of price, etc.

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
When you analyze quiz answers, you need to see the numbers and the difference between all negative and positive answers. This is not easy, if you have more than 3 possible answers - positive, neutral and negative. However, you can create a chart that shows the whole picture:

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
For the analysis of the investment portfolio, it is helpful to see the unrealized gain or loss. These amounts show how many you will receive or lose if you realize all available stocks right now. Sure, there are some fees for the operations that can decrease gain or increase loss, but, at least, you want to see the least approximate amounts:

 

 

© 2019 Austin Laptop [AustinLaptop.Com]. All Rights Reserved.