Want To Know How To Work With COMMENTS?

While working with Excel it may be necessary to brief at times why the value went up or down. In some places you may want to write some hints about the value for your own reference text. But such references will lead lot of rework while preparing instant reports. Moreover you may not like if they are visible to everyone. Microsoft Excel allows you to create short notes on each cell using a command Comment.

You can find the command in the Insert menu. You can even find it in the pop-up menu. The pop-up menu opens either right clicking your mouse or by pressing Shift key and F10 key at the same time.

Tips: By pressing Shift key and F3 key, you can directly create comment and start writing your note. You can customize the text box size, font, font size and its location. You need to type the text and press enter

You will find a red mark now on the top right corner of the cell. While moving mouse pointer nearer to the cell, Excel displays comments whatever you typed just like the above pictures.

Let us know how to manage these comments now.

To edit a comment use Shift Key and F3 function key at the same time.

You can even do by opening the pop-up menu and selecting ‘Edit Comment’ option to make necessary correction. Select ‘Delete comment’ option to delete the existing comment. Select ‘show comment’ option to keep displaying the comment in the screen.

There is a tool bar specially meant for working with comments. You can activate it by selecting comments from View menu.

Info: Always ensure that comments are not disabled in your Excel environment settings. Otherwise you may not able to see any comments even you create.

You can check the current settings for comments from the following path.

Excel => Tool menu => Options => View Tab => Comments => Comment indicator only.

Be A Master In Using VLOOKUP Function

While working with large number of data it’s really a difficult task and time-consuming process to search data related to a specific field and use it for another purpose. We can use VLOOKUP function in such condition to fetch data related to a particular field from large number of records.

This function can be used effectively in following circumstances.

When an organization has entire details about employees in a worksheet, it is possible to fetch available related data in the worksheet about any employee. Based on employee name or employee ID, VLOOKUP can fetch other existing details like age, date of joining, designation, experience, salary etc.

If a library has the entire details about books, it is possible to fetch existing related data about any book. Based on book name or book ID, VLOOKUP can fetch other existing details like author name, published date, location, member ID to whom the book was issued, issued date etc.

If a Company has entire details about products, it is possible to fetch existing related data about any product. Based on product name product ID, VLOOKUP fetches other details like dimension details, manufacturing details, stock details, price details etc.

The value fetched by VLOOKUP can be effectively used to do another task. If you change the source details, VLOOKUP automatically replaces it over the old value.

Let us discuss VLOOKUP briefly with an example. Please find below picture in which a small part of worksheet is displayed. There are thousands of rows and few columns have details about a product and the range of cells we see in this page is only between A1 to E5.

We are going to fetch price details of a particular product without searching manually and pasting the value.

Syntax for VLOOKUP

=VLOOKUP(KEY FIELD, SOURCE TABLE, COLUMN ID, ACCURACY)

Here key field is the matching data to be searched. Source table is the database. Column ID is the reference column right from the matching key field; Accuracy is the level of accuracy need.

Let us find the price details for a given product in the below example. The format is simple and made with an objective that once we type any Product ID in the cell M9, Excel has to search automatically its price, display the price value and further consider the value to calculate with number of units.

Please look at the syntax here

=VLOOKUP(M9,$A$1:$E$5,4,FALSE)

M9 is the key field to be searched. $A$1:$E$5 is the source table. The number 4 is the column index just right from the key field column. False is the accuracy level.

Here false is meant for accurate matching of M9 in the source table. If true is mentioned then Excel finds a value that is approximate matching to M9. (Choose false by default)

Column Index is based on source table (1=CODE, 2=MODEL, 3=MAKE, 4=PRICE and 5=SIZE). The column index we given 4 is meant for price.

Based on M9 value, the result O9 returns value. Check the right side picture, where the code given is ‘10000288’. Excel searches exact matching word in the source table ($A$1:$E$5) and get the 4^th column index value ($4,999.00) in the cell O9.

Fetched value is the price of single unit and P9 is the price of number of units given. You can copy and paste the formula in the below cells to get more product prices.

Tips: Insert $ symbols in source table cell range. This keeps the range of cells unchanged while dragging or pasting into another cell.

Advanced method:

If key field is blank, VLOOKUP returns error message (#N/A). To avoid displaying such messages please change the formula a slightly using IF function. (To know more about IF FUNCTION please go through archives)

Following is the modified syntax

=IF(M9<>0,VLOOKUP(M9,$A1:$E$5,4,FALSE),””)

In case M9 equals zero, IF function returns to blank. Otherwise VLOOKUP will be carried out and excel fetches price value.

=IF(N9<>0,N9*O9,””)

In case the number of product N9 is zero, IF function returns to blank. Else calculates price value for number of products.