FORMULA OPERATORS

There are four different types of calculation operators: arithmetic, comparison, text concatenation and reference.

Arithmetic Operators

To perform basic mathematical operations such as addition, subtraction or multiplication, combine numbers and produce numeric results, arithmetic operators are used.

ArithmeticOperator Meaning
+ (plus sign)Addition
– (minus sign)Subtraction Negation
* (asterisk)Multiplication
/ (forward slash)Division
% (per cent sign)Per cent
* (caret)Exponentiation

Comparison Operators

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE. 

Comparison OperatorMeaning
= (equal sign)Equal to
> (greater than sign)Greater than
< (less than sign)Less than
> = (greater than or equal to sign)Greater than or equal to
< = (less than or equal to sign)Less than or equal to
<>, ! = (not equal to sign)Not equal to

Text Concatenation Operator

Use the ampersand (&) to concatenate (combine) one or more text strings to produce a single piece of text.

Text OperatorMeaning
& (ampersand)Connects or concatenates, two strings to produce one continuous text value.

Reference Operators

To combine the range of cells for various calculations reference operator are used.

Reference OperatorMeaning
: (colon)Range operator, which produces one reference to all the cells between two references, including the two references.
, (comma)Union operator, which combines multiple references into one reference.
(space)Intersection operator, which returns a reference to the cells common to the ranges in the formula. In this example cell C7 is found in both ranges, so it is the intersection.

Order of Evaluation of Formulas

Results provided by the formulas, are dependent on the preferences of operators. Preferences of operators are as follows:

: Range reference

– Negative Number

% Percentage

^ Exponentiation

* Multiplication

/ Division

+ Addition

– Subtraction

& Concatenation

= < > or < = or >= or > or < Comparison 

Displaying Formulas

To show the formula which is entered in a cell, the steps are as follows:

Step 1 Select the cell where formula has entered. ‘

Step 2 Click Formulas tab → Formula Auditing group → Show Formulas. 

Functions

A function is a predefined formula that performs calculations using specific values in a particular order, e.g. SQRT (), LOWER (), etc. Functions make it easy to perform common calculations on data.

In order to use these functions correctly, you need to understand the different parts of functions.

The Parts of Function

Each function has a specific order called “syntax”, which must be followed for the function to work correctly. The basic syntax to create a formula with function is:

• to insert an equal sign (=).

• a function name (for e.g. SUM).

• an argument that contain the information related to formula to calculate such as range of cell references.

Function Categories

In MS-Excel, functions are categorised into following groups:

1. Text Functions These functions are used to remove extra characters e.g. TRIM, CLEAN, etc.

2. Logical Functions These functions are performed boolean operations on given data e.g. AND, OR, NOT. etc.

3. Date and Time Functions These functions are used to create date and time.

e.g. DATE, TIME, DATEVALUE, etc.

4. Financial Functions These are used to calculate financial calculations such as investment valuations, interest rate.

e.g. FV, PV, RECEIVED, etc.

5. Maths and Trigonometry These functions perform mathematical calculation including arithmetic, sum, products and trigonometry.

e.g. PRODUCT, MOD, TAN, etc.

6. Information Functions These functions provide information about error in formula or data value.

e.g. ISERROR, ISNA, etc.

7. Engineering Functions These functions are used to perform calculations related to complex numbers, bessel functions.

etc. e.g. CONVERT, BESSELI, etc.

8. Lookup and References Functions These functions are used to perform operation on data array.

e.g. VLOOKUP, MATCH, etc.

9. Database Functions These functions are used to calculate the average, maximum or minimum data from the database list.

e.g. DMAX, DCOUNT, etc.

10. Statistical Functions These functions are used to find the largest and the smallest value from a list of data values.

e.g. MAX A, LARGE, etc.

Some useful functions in Excel are listed below:

FunctionDescription
SUMReturns the sum of a list of supplied numbers.
AVERAGEReturns the average of a list of supplied numbers.
MAXReturns the largest value from a list of supplied numbers.
MINReturns the smallest value from a list of supplied numbers.
FACTReturns the factorial of a given number.
INTRounds a number down to the next integer.
ROUNDRounds a number up or down, to a given number of digits.
TRIMRemoves duplicate spaces and spaces at the start and end of a text string.
FIXEDRounds a supplied number to a specified number of decimal places and then converts this into text.
VALUEConverts a text string into a numeric value.
MIDReturns a specified number of characters from the middle of a supplied text string.
ANDTests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE or FALSE otherwise.
ORTests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE or FALSE otherwise.
FALSEIF Tests a user-defined condition and returns one result if the condition is TRUE and another result if the condition is FALSE.
DATEReturns a date, from a user-supplied year, month and day.
TIMEReturns a time, from a user-supplied hour, minute and second.
VLOOKUPLooks up a supplied value in the first column of a table and returns the corresponding value from another column.
INDEXReturns a reference to a cell (or range of cells) for requested rows and columns within a supplied range.
TRANSPOSEPerforms a transpose transformation on a range of cells (i.e. transforms a horizontal range of cells into a vertical range and vice-versa).
SUMIFAdds the cells in a supplied range, that satisfy a given criteria.
SUBTOTALPerforms a specified calculation (e.g. the sum, product, average, etc.) for a supplied set.

Charts

Charts are the graphical and pictorial representation of worksheet data. Microsoft Excel supports charts, graphs or histograms generated from specified groups of cells. The generated graphic component can either be embedded within the current sheet, or added as a separate object.

Different types of chart are as follows:

1. Column

2. Line

3. Pie

4. Area

5. Scatter

6. Stock

7. Surface

8. Doughnut

9. Bubble

10. Radar 

Components of a Chart

A chart consists of the following components:

X-axis is a horizontal axis known as category axis.

Y-axis is a vertical axis known as values axis.

Data Series is the set of values you want to plot in the chart.

Chart Area is the total area of the chart.

Legends is a unique color or pattern, which helps you to identify an individual data series.

Chart Title is the descriptive text aimed at helping user identify the chart.

Axis Titles are the titles given to three axis i.e. X, Y, Z.

Gridlines are horizontal and vertical lines which are inserted in the chart.

Data Label provides additional information about a data marker.

Creating Chart

Steps to create a chart are as follows:

Step 1 Select the data you want to summarise visually and then click on the Insert tab.

Step 2 In the charts group, click the type of chart you want to create, to display the available chart subtypes.

Step 3 Then, select anyone of them by clicking it.

Step 4 After selecting click OK button. 

Menu Item in Excel

Tabs in Excel 2007Menus in Excel 2013Purpose
Microsoft ButtonFile• To open a new file• To open an existing file• To save a file• To print a file, etc
HomeEdit+Insert+Format• To cut, copy, paste the selected item• To format the font• To set the alignment• To format the cell, row, columns, sheet• To insert cell, row, column, sheet
InsertInsert• To insert table, pivot table• To insert picture, ClipArt, shapes, Smart Art• To insert various types of chart• To insert hyperlink and text
Page LayoutWindow• To set themes• To set-up the page, scale to fit• To arrange all the objects
FormulasTools• It includes function library• Used for defined names• For formula auditing
DataData• To get external data• To set connections• To sort and filter the database• Provide various data tools• Group or ungroup the data
ReviewTools+View• To check spellings• Provide research, thesaurus, translation, etc• To add new comment ,• To protect sheet and workbook from unwanted changes
View View+Window• To view the worksheet in normal view• To view the document as it will appear on the printed page• To view a preview where pages will break when the document is printed• To show/hide the formula bar, heading gridlines, etc• To arrange windows• To view the list of macros

Keyboard Shortcuts for MS-Excel 2007

Ctrl Combination Shortcut Keys

Ctrl + Shift + (Unhides any hidden rows with the selection.
Ctrl + Shift + )Unhides any hidden columns within the selection.
Ctrl + Shift ~Applies the General number format in Microsoft Excel.
Ctrl + Shift + $Applies the Currency format with two decimal places.
Ctrl + Shift + %Applies the Percentage format with no decimal places.
Ctrl + Shift + ^Applies the Exponential number format with two decimal places.
Ctrl + Shift + #Applies the Date format with the day, month and year.
Ctrl + Shift + @Applies the Time format with the hour and minute, and AM or PM.
Ctrl + Shift + IApplies the Number format with two decimal places, thousands separator and minus sign (-) for negative values.
Ctrl + Shift +Copies the value from the cell above the active cell into the cell or the Formula Bar.
Ctrl + ’Copies a Formula from the cell above the active cell into the cell or the Formula Bar.
Ctrl + 9Hides the selected rows.
Ctrl + 0Hides the selected columns in Microsoft Excel.
Ctrl + BApplies or removes columns in Microsoft Excel.
Ctrl + CCopies the selected cells. Ctrl + C followed by another Ctrl + C displays the Clipboard.
Ctrl + DUses the Fill Down command to copy the contents and format of the topmost cell a selected range into the cells below.
Ctrl + IApplies or removes italic formatting in Microsoft Excel.
Ctrl + NCreates a new, blank workbook.
Ctrl + ODisplays the Open dialog box to open or find a file. Ctrl + Shift + 0 selects all cells contain comments.
Ctrl + PDisplays the Print dialog box.
Ctrl + RUses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
Ctrl + SSave the active file with its current file name location and file format.
Ctrl + UApplies or removes underlining.
Ctrl + WCloses the selected workbook window.
Ctrl + XCuts the selected cells.
Ctrl + YRepeats the last command or action, if possible in Microsoft Excel.
Ctrl + ZUses the Undo command to reverse the last command or to delete the last entry that you typed.

Other Useful Shortcut Keys

Arrow KeysMove one cell up, down, left or right in a worksheet.
BackspaceDeletes one character to the left in the formula bar.
DeleteRemoves the cell contents (Data and formulas) form selected cells without affecting cell formats or comments.
EscCancels an entry in the cell or formula bar.
HomeMoves to the beginning of a row in a worksheet.
Page downMoves one screen down in a worksheet.
Page upMoves one screen up in a worksheet.
TabMoves one cell to the right in a worksheet.

Leave a comment

Your email address will not be published. Required fields are marked *