MS Excel for Charted Accountant by Sunil B Gabhamalla - HTML preview

ANNEXURES

1 ANNEXURE A: KEYBOARD SHORTCUTS

Key Alone Shift Ctrl Alt F1 Help
F2 Edit Mode F3 Paste Name Formula F4 Repeat Action F5 Goto
F6 Next Pane F7 Spell Check F8 Extend Selection F9 Calculate All

F10 Activate Menu F11 New Chart F12 Save As

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z

` (~)
1 (!)
2 (@)
3 (#)
4 ($)
5 (%)

What's This Help Edit Comment Paste Function

Find Again Find
Prev Pane

Add To Selection Calculate Worksheet Context Menu New Worksheet Save
Define Name Close Window Restore Window Size Next Workbook Move Window Resize Window Minimize Workbook Restore Workbook New Macro Sheet Open
Select All
Bold
Copy
Fill Down
Insert Chart Sheet Save As

Exit
Macro List
VB Editor

Data Menu
Edit Menu Find File Menu Goto
Replace Italics Help Menu Insert Menu

Insert Hyperlink
New Workbook Open Workbook Format Menu Print
Fill Right Save
Tools Menu Underline

Paste Close Workbook Window Menu Cut

Repeat Active
Undo
Toggle Formula View

Cell Format
Strkethru

Key Alone Shift Ctrl Alt
6 (^)
7 (&)
8 (*) Outline
9 (() Hide Rows
0 ()) Hide Columns
- Delete Selection
= (+) Formula Auto Sum [ Direct Dependents ] All Dependents ; (semicolon) Insert Date ' (apostrophe) : (colon)
/
\
Insert
Delete
Home
End
Page Up Page Down Left Arrow Right Arrow Up Arrow
Down Arrow Space Bar Tab
BackSpace Enter

Insert Mode Clear
Begin Row End Row Page Up
Page Down Move Left
Move Right Move Up
Move Down Space
Move Right Select Left Select Right Select Up Select Down Select Row Move Left

Next Cell Previous Cell

Style Insert Time
Select Array

Select Differences

Delete To End Of Line Start Of Worksheet End Of Worksheet

Previous Worksheet Next Worksheet

Select Column Next Window Goto Active Cell Fill Entire Selection Left 1 screen Right 1 screen

Control Box Next Application Next Line in Same Cell

2 ANNEXURE “B” IMPORTANT EXCEL FUNCTIONS

Name Description ABS
ACCRINT

ACCRINTM

 

ADDRESS

 

AVERAGE CEILING

 

CELL

 

CHOOSE

CLEAN
COMBIN
CONCATENATE COUNT

COUNTBLANK

 

COUNTIF

 

CUMIPMT CUMPRINC

 

DATE

 

DATEDIF

 

DAY

 

EDATE

 

EFFECT

 

EOMONTH

EVEN EXACT FIND
Returns the absolute value of a number
Returns the accrued interest for a security that pays periodic interest
Returns the accrued interest for a security that pays interest at maturity
Creates a cell address as text based on given row and column numbers
Returns the average (arithmetic mean)
Rounds a number (away from zero) to the nearest integer or to the nearest multiple of significance
Returns information about the formatting , location or contents of the cell or upper left cell of the reference
Uses a specified index number to select one from up to 29 specified values
Removes all nonprintable characters from text
Returns the number of combinations for a given number of objects Joins several text items into one text item
Counts the number of cells that contain numbers and also numbers within the list of arguments
Counts the number of non-empty cells and the values within the list of arguments
Counts the number of cells that meet the criteria specified in the argument
Returns the cumulative interest paid between two periods Returns the cumulative principal paid on a loan between two periods
Returns the sequential Excel date / time serial number that represents a particular date
Calculates differences between two dates in terms of specified units and assumptions
Converts an Excel date / time serial number to the day of a month Returns the Excel date / time serial number of the date that is the indicated number of months before or after the specified number of months from the start date
Returns the effective annual interest rate of a given nominal rate with its compounding frequency
Returns the Excel date / time serial number of the last day of the month before or after a specified number of months from start date Rounds a number away from zero to the nearest even integer Checks to see whether two text values are identical
Finds one text value within another (case sensitive)
FIXED FLOOR

FV

 

FVSCHEDULE

GCD
GETPIVOTDATA HLOOKUP

IF

 

INDEX

INDIRECT INT
IPMT

IRR
LCM
LEFT
LEN
LOOKUP

LOWER MAX

 

MID

 

MIN

 

MINUTE MOD

 

MONTH MROUND

 

NOMINAL

 

NOW

NPER NPV
Formats a number as text with a fixed number of decimals Rounds a number down towards 0 to the nearest integer or to the nearest multiple of significance
Returns the future value of an investment
Returns the future value of an initial principal after applying a series of compound interest rates
Returns the greatest common divisor
Returns data stored in a pivot table
Looks in the top row of a table or array and returns the value of the indicated cell
Returns one value if the specified condition evaluates to TRUE and another value if it evaluates to FALSE
Returns a value from a table of values based on references to rows and columns.
Returns a reference indicated by a value provided as text Rounds a number away from 0 to the nearest integer
Returns the amount of the interest element in a payment for an investment for a given period
Returns the internal rate of return for a series of cash flows Returns the least common multiple
Returns the left most characters from a text value
Returns the number of characters in a text string
Looks up values in a one row or column range and returns a value in a second one row or column range.
Converts text to lowercase
Returns the maximum value in a list of arguments ignoring logical values and text
Returns a specific number of characters from a string starting at a specified position
Returns the minimum value in a list of arguments ignoring logical values and text
Converts an Excel date / time serial number to a minute Returns the remainder from division with the result having the same sign as the divisor
Converts an Excel date / time serial number to a month number Returns a number rounded to the desired multiple. Midway points are rounded away from 0
Returns the nominal rate equivalent to a given annual effective with a given compounding frequency for the nominal rate Returns the Excel date / time serial number of the current date and time
Returns the number of periods for an investment
Returns the net present value of an investment based upon a series of periodic cash flows and a discount rate where the first cash flow PERMUT

PMT PPMT

PRODUCT PROPER
PV
QUOTIENT RAND

RANDBETWEEN

RATE
REPLACE REPT
RIGHT
ROMAN ROUND

ROUNDDOWN

 

ROUNDUP

 

SEARCH

SQRT
SUBSTITUTE SUBTOTAL SUM
SUMIF
TODAY
TRANSPOSE TRIM
UPPER
VLOOKUP

WEEKDAY

YEAR
YEARFRAC is received at the end of the first period
Returns the number of permutations for a given number of objects that can be selected from a number of objects without replacement Returns the periodic payment for an annuity
Returns the amount of principal element in a payment for an investment for a given period
Multiplies together 1 - 30 numbers
Capitalizes the first letter in each word of a text value Returns the present value of an investment
Returns the integer portion of a division
Returns an evenly distributed random number greater than or equal to 0 and less than 1
Returns a random number between (and inclusive of) two specified numbers
Returns the interest rate per period of an annuity
Replaces characters within text
Repeats text a given number of times
Returns the rightmost characters from a text value
Converts an arabic number to Roman, as text
Rounds a number to a specified number of digits to the left (-) or right (+) of the decimal point. The midway digit 5 is rounded away from 0.
Rounds a number down towards 0 to a specified number of digits to the left (-) or right (+) of the decimal point
Round a number up away from 0 to a specified number of digits to the left (-) or right (+) of the decimal point
Finds one text value within another (not case sensitive) and returns the number of the starting position
Returns a positive square root
Substitutes new text for old text in a text string
Returns the sutotal in a list or database
Adds its arguments
Add the cells specified by a given criteria
Returns the Excel date / time serial number of today's date Returns the transpose of an array
Removes all spaces from text except single spaces between words Converts text to uppercase
Locates a specified value in the leftmost column of a specified table, and returns the value in the same row from a specified column in the table
Converts an Excel date / time serial number to the number of the day of the week based upon counting system of return_type Converts an Excel date / time serial number to a year
Returns the difference between start_date and end_date expressed as a number of years including decimal fraction of a year.

3 ANNEXURE “C”: COMMON ERROR CODES

Error Type Possible Causes Recommended Solutions ####### Column is not wide enough 1) Increase the width to display the content (text of the column entries however overflow or 2) Shrink the contents get truncated without to fit the column displaying this error 3) Apply a different message) number format Dates and times are Check the formulae. negative numbers
#NULL Use of incorrect range 1) Use colon (:) to refer operator to a contiguous range of cells
2) Use comma (,) to refer to two ranges of cells which do not intersect
#N/A Missing Data
Inappropriate Value in the
lookup function
Replace with data Use a single cell as the lookup value and not a range of cells
Lookup an unsorted table Sort the table first

available #NAME? Using a range name which
does not exist
Using labels in formula Omitting a required Check the function syntax argument from a function and supply all the necessary
arguments
Using a customised Make sure the workbook function which is not containing the customised
function is open
Define the range name or
check the spelling
Turn on the feature (Tools,
Options, Calculation Tab)

Correct the spelling Mis-spelling the name of a

function
Entering text in formula Surround the text with without double quotation double quotation marks marks
Omitting a colon in a range reference
Reference to another sheet not enclosed in single quotation marks
Select the range by pointing

Select the range by pointing

Error Type Possible Causes Recommended Solutions #NUM! Using non-numeric Check the function syntax argument for a function and supply all the necessary which requires numeric arguments
arguments
Function cannot derive a Check the values provided value (IRR or RATE for logical mistakes. functions)
#REF! Cell reference is not valid

#VALUE! Range is supplied where a single value is required
#DIV/0! A cell reference which 1) contains either a zero or a blank value is used as a 2) divisor
If cell was deleted, correct the formula or restore the deleted cell

Check the arguments

Enter a value other than zero as the divisor
Prevent the error by using the IF function with the formula
3) Enter #N/A as the divisor cell reference to display #N/A in preference to #DIV/0!

4 ANNEXURE “D” LIST OF ADD-INS PROVIDED WITH MS-EXCEL

Add In

 

Access Links

Analysis
ToolPak
AutoSave Conditional Sum
HTML
Internet
Assistant
VBA
Lookup
Wizard
MS Query

ODBC Add in Report
Manager

Solver

Template
Utilities
Update Add In Links

Utility
Provides Microsoft Access forms and reports for use with Excel data lists
Gives several worksheet functions and macro functions for financial and scientific data analysis for use in a workbook Automatically saves your workbook at specified intervals Assists you in summing data in lists

Helps in HTML functions
Allows developers to publish Excel data to the Web by using Excel 97 Internet Assistant Syntax

Allows creation of formulas to find data in Excel tables

Converts external data ranges in Microsoft Excel 97/2000 format to Excel 95 format. Also allows the use of macros created in VBA in previous versions of Excel
Provides drivers for working with SQL based data sources Lets you format information in worksheets, workbooks and scenarios into organized, uniform reports by combining spreadsheet views with data scenarios
Lets you determine the answers to complex what if questions or equations by analyzing cells and determining the optimum value adjustments required to arrive at the desired result
Provides utilities that are automatically used by the Excel’s built in templates
Updates the links to Excel add in functions to access the built in functionality in higher versions