There are four different types of calculation operators: arithmetic, comparison, text concatenation and reference.
To perform basic mathematical operations such as addition, subtraction or multiplication, combine numbers and produce numeric results, arithmetic operators are used.
|+ (plus sign)||Addition|
|– (minus sign)||Subtraction Negation|
|/ (forward slash)||Division|
|% (per cent sign)||Per cent|
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.
|= (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.
|& (ampersand)||Connects or concatenates, two strings to produce one continuous text value.|
To combine the range of cells for various calculations reference operator are used.
|: (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
= < > or < = or >= or > or < Comparison
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.
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.
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:
|SUM||Returns the sum of a list of supplied numbers.|
|AVERAGE||Returns the average of a list of supplied numbers.|
|MAX||Returns the largest value from a list of supplied numbers.|
|MIN||Returns the smallest value from a list of supplied numbers.|
|FACT||Returns the factorial of a given number.|
|INT||Rounds a number down to the next integer.|
|ROUND||Rounds a number up or down, to a given number of digits.|
|TRIM||Removes duplicate spaces and spaces at the start and end of a text string.|
|FIXED||Rounds a supplied number to a specified number of decimal places and then converts this into text.|
|VALUE||Converts a text string into a numeric value.|
|MID||Returns a specified number of characters from the middle of a supplied text string.|
|AND||Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE or FALSE otherwise.|
|OR||Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE or FALSE otherwise.|
|FALSE||IF Tests a user-defined condition and returns one result if the condition is TRUE and another result if the condition is FALSE.|
|DATE||Returns a date, from a user-supplied year, month and day.|
|TIME||Returns a time, from a user-supplied hour, minute and second.|
|VLOOKUP||Looks up a supplied value in the first column of a table and returns the corresponding value from another column.|
|INDEX||Returns a reference to a cell (or range of cells) for requested rows and columns within a supplied range.|
|TRANSPOSE||Performs a transpose transformation on a range of cells (i.e. transforms a horizontal range of cells into a vertical range and vice-versa).|
|SUMIF||Adds the cells in a supplied range, that satisfy a given criteria.|
|SUBTOTAL||Performs a specified calculation (e.g. the sum, product, average, etc.) for a supplied set.|
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:
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.
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 2007||Menus in Excel 2013||Purpose|
|Microsoft Button||File||• To open a new file• To open an existing file• To save a file• To print a file, etc|
|Home||Edit+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|
|Insert||Insert||• To insert table, pivot table• To insert picture, ClipArt, shapes, Smart Art• To insert various types of chart• To insert hyperlink and text|
|Page Layout||Window||• To set themes• To set-up the page, scale to fit• To arrange all the objects|
|Formulas||Tools||• It includes function library• Used for defined names• For formula auditing|
|Data||Data||• To get external data• To set connections• To sort and filter the database• Provide various data tools• Group or ungroup the data|
|Review||Tools+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 + I||Applies 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 + 9||Hides the selected rows.|
|Ctrl + 0||Hides the selected columns in Microsoft Excel.|
|Ctrl + B||Applies or removes columns in Microsoft Excel.|
|Ctrl + C||Copies the selected cells. Ctrl + C followed by another Ctrl + C displays the Clipboard.|
|Ctrl + D||Uses the Fill Down command to copy the contents and format of the topmost cell a selected range into the cells below.|
|Ctrl + I||Applies or removes italic formatting in Microsoft Excel.|
|Ctrl + N||Creates a new, blank workbook.|
|Ctrl + O||Displays the Open dialog box to open or find a file. Ctrl + Shift + 0 selects all cells contain comments.|
|Ctrl + P||Displays the Print dialog box.|
|Ctrl + R||Uses 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 + S||Save the active file with its current file name location and file format.|
|Ctrl + U||Applies or removes underlining.|
|Ctrl + W||Closes the selected workbook window.|
|Ctrl + X||Cuts the selected cells.|
|Ctrl + Y||Repeats the last command or action, if possible in Microsoft Excel.|
|Ctrl + Z||Uses the Undo command to reverse the last command or to delete the last entry that you typed.|
Other Useful Shortcut Keys
|Arrow Keys||Move one cell up, down, left or right in a worksheet.|
|Backspace||Deletes one character to the left in the formula bar.|
|Delete||Removes the cell contents (Data and formulas) form selected cells without affecting cell formats or comments.|
|Esc||Cancels an entry in the cell or formula bar.|
|Home||Moves to the beginning of a row in a worksheet.|
|Page down||Moves one screen down in a worksheet.|
|Page up||Moves one screen up in a worksheet.|
|Tab||Moves one cell to the right in a worksheet.|