In the previous post we looked at the first five most basic things of Excel that every Excel user must know. Let’s now continue with the next five important points that Excel users should have knowledge about.
(6.) Cell Address, Range and Reference
Most excel users are confused with all these words!Cell Address:Cell Address is as simple as the word “place address”, which we use to indicate a particular place. Similarly, on spreadsheet, it indicates the current place of cell cursor. It is a combination of name of current column and number of current row. E.g. if cell cursor is in 5th row of ‘G’ column then the cell address will be ‘G5’, which will appear in Name-box.
Range: Range contains more than one cell addresses. The range may be vertical (a group of cells from only one column), horizontal (a group of cells from only one row) or diagonal (a group of cells from both rows and columns). The diagonal range is expressed from top left cell to bottom right cell of the selection/range. E.g. ‘B2:K42’. Colon (:) and Space are range operators. The colon indicates continuous range, while space returns an intersection cell within the expressed range. In most formulas and functions the colon operator is used.
Reference:When a cell address or a range is used in formula and/or function, it is called reference. It is as simple as that! It refers an area to read value(s) from that cell or range. Sometimes, reference is also used to put values into mentioned cell.
(7.) Type of References
References are used in functions or formulas. A dollar ‘$’ sign is used to define nature of the reference.
Mainly there are two types of references. They are Relative and Absolute. In Relative reference column name and row number get changed dynamically according to the formula cell. This is default type of reference for formula/function. An absolute reference fixes the mentioned part of cell address regardless of formula cell.
There are four combinations of this reference type:
Relative Column and Relative Row (e.g. A1):
Column name and Row number both get changed accordingly formula cell.
Absolute Column and Relative Row (e.g. $A1):
In this combination, column is fixed. Means it will remain same regardless of formula cell. Only row number will get changed.
Relative Column and Absolute Row (e.g. A$1):
In this combination, Row is fixed. Means it will remain same regardless of formula cell. Only column name will get changed.
Absolute Column and Absolute Row (e.g. $A$1):
In this combination, column and row both are fixed. Means they will remain same regardless of formula cell.
“F4” function key automatically inserts a dollar ($) sign into reference. It is toggle key for reference types. On the very first time it makes both part of an address absolute. Second time it makes only row number absolute. Third time it makes only column name absolute. Fourth time it makes the reference relative (means removes all ‘$’ signs).
(8.) Naming the Range and using it
It becomes easier to remember and express a named location. Similarly, a name can be defined to the range to identify and remember it easily. It can be used as a direct parameter in lieu of range in formulas or function. In non-ribbon versions, a name to range can be assigned through Insert >>> Names >>> Define. In ribbon versions, it can be done through ‘Name Manager’ located on ‘Formulas’ ribbon.
e.g. In a payroll workbook, there is a worksheet named ‘EmpMast’ and data range is from “A1:L500”. Now in ‘Salary Calculation’ sheet, this range has been used to fetch relative data.
To do this, the simple formula will be like this >>> “=VLOOKUP(E2,EmpMast!$A$1:$L$500,5,0)”. Suppose the data range on ‘EmpMast’ has been named as ‘Employee_Master’ then the formula will be >>> “=VLOOKUP(E2, Employee_Master,5,0)”. Thus, the named range increases readability and understandability of the formula.
A user can work very well without defining names, but creating, understanding, and maintaining formulas will be much easier with names.
(9.) Dialog Box Launcher and Context Menus
In non-ribbon versions, there are definite dialog boxes to do more with any functionality. These dialog boxes have more variants and facilities related to that feature. In ribbon versions, they can be opened with the help of dialog box launcher. In ribbon versions, there is a small button on bottom-right corner of a group having diagonal down arrow symbol, which lets user to open dialog box of that feature. Note that only some groups like Font, Alignment and Page Setup etc. are having dialog box launcher.
Context menus also help to increase speed by providing most applicable commands to that particular area. Context menu can be opened with option (usually right) click. Option click may be right click if the user is righty or left click if the user is lefty. A context menu offers limited available commands in the current context of application/selected area. When you option click a cell or selection of cells, a cell context menu gets opened. The Row and Column context menus are displayed when you option click the row or column header. Context menus are also available for selected WordArt, ClipArt, Drawing Object or AutoShapes. Perhaps this is the fastest and easiest way to get information and commands to perform particular tasks.
(10.) How Excel Manages Values
There are dozens of formats to display values in various ways. Sometimes calculation returns unexpected value and result looks ‘wrong’. At that moment user feels – ‘excel has performed something wrong!’ Actually excel calculates and stores exactly what it should be but displayed value misguides the user! So, here it is clear that excel evaluates stored values, not displayed. To avoid such situation, returned values can be handled and controlled with the help of some functions like ABS(), ROUND(), ROUNDUP(), ROUNDDOWN(), CEILING(), FLOOR() etc.
In this post, we glanced at the next five important things, every user should know for working efficiently in Excel. The last set of five essential points giving you knowledge of Excel’s basic things will be published soon. So visit our blog again!
