Computer Applications in
Management
Text, Date and Time Functions in MS-EXCEL
Learning Outcome
Understanding of:
Text functions
Date and Time functions
TEXT FUNCTIONS
LEFT function
The function returns the first k characters
in a text string.
Syntax:LEFT(text,[num_chars])
RIGHT function
The function returns the last k characters
in a text string.
Syntax: RIGHT(text,[num_chars])
MID function
Returns the characters from the middle of
a text string, given a starting position and
length.
Syntax: MID(text,start_num,num_chars)
TRIM function
Removes all spaces from the text string
except for single space between words
Syntax: TRIM(text)
LEN function
Displays the length of a text string
Syntax: LEN(text)
FIND and SEARCH function
Returns the starting position of one text
string within another text string
Syntax: FIND(find_text, within_text,
[start_num])
REPT function
Repeats a text a given number of times
Syntax: REPT(text, number_times)
CONCATENATE function
The function can be used to join up to 30
strings.
Syntax: CONCATENATE(text1,
text2,text3,....,text30)
REPLACE function
Replaces part of a text string with a
different text string.
Syntax: REPLACE(old_text,
start_num,num_chars,new_text)
UPPER and LOWER functions
Changes the text string into upper or
lower case.
Syntax: UPPER(text)
LOWER(text)
Proper function
The string argument whose first character
in each word will be converted to
uppercase and all remaining characters
converted to lowercase.
Syntx: PROPER( text )
DATE FUNCTIONS
One can enter the date in the following
formats in MS-EXCEL (January 4, 2004)
1/4/2004
4-Jan-2004
January 4, 2004
1/4/04
DATEVALUE function
Returns the serial format for the date
Syntax: DATEVALUE(“date_text”)
TODAY function
Automatically displays today’s date
Syntax: TODAY()
Range name
https://spreadsheeto.com/named-ranges/#range
It’s simple: a range is a collection of two or more cells.
They could be vertical Or horizontal
Ranges can be rectangular, too or They can even include non-
adjacent cells:
The definition is simple: a name is a word or series of
characters that’s applied to a cell or a range of cells.
Naming a range is just as easy. Select the cells you want to
name, and type a new name in the box.
Paste Special
Paste Special when copying from Excel. Use the Paste
Special dialog box to copy complex items from a Microsoft
Office Excel worksheet and paste them into the same
worksheet or another Excel worksheet using only specific
attributes of the copied data, or a mathematical operation that
you want to apply to the copied data ...
If you want to paste only a specific aspect of the copied data
like its formatting or value, you would use one of the Paste
Special options. After you’ve copied the data, press
Ctrl+Alt+V, or Alt+E+S to open the Paste Special dialog.
You can also click Home > Paste > Paste Special.
Cick this option To Keyboard shortcut
All Paste all cell contents and formatting. Press A
Paste only the formulas as entered in the
Formulas Press F
formula bar.
Values Paste only the values (not the formulas). Press V
Formats Paste only the copied formatting. Press T
Comments Paste only comments attached to the cell. Press C
Paste only the data validation settings
Validation Press N
from copied cells.
Paste all cell contents and formatting from
All using Source theme Press H
copied cells.
All except borders Paste all cell contents without borders. Press X
Paste only column widths from copied
Column widths Press W
cells.
Paste only formulas and number formats
Formulas and number formats Press R
from copied cells.
Paste only the values (not formulas) and
Values and number formats Press U
number formats from copied cells.
Sorting
To sort a range:
Select the cell range you want to sort. ...
Select the Data tab on the Ribbon, then click the
Sort command.
The Sort dialog box will appear. ...
Decide the sorting order (either ascending or
descending). ...
Once you're satisfied with your selection, click OK.
The cell range will be sorted by the selected
column.
Conditional formatting
Conditional formatting quickly highlights important information in a
spreadsheet. But sometimes the built-in formatting rules don’t go quite
far enough. Adding your own formula to a conditional formatting rule
gives it a power boost to help you do things the built-in rules can’t do.
Create conditional formatting rules with formula
For example, let’s say a doctors' office wants to track their patients’
birthdays to see whose birthday is coming up and then mark them as
having received a Happy Birthday greeting from the office.
In this worksheet, we see the information we want by using conditional
formatting, driven by two rules that each contain a formula. The first
rule, in column A, formats future birthdays, and the rule in column C
formats cells as soon as “Y” is entered, indicating that the birthday
greeting has been sent.
Goal Seek
How to Use Excel Goal Seek
Create a spreadsheet in Excel that has your data.
Click the cell you want to change. ...
From the Data tab, select the What if Analysis… ...
Select Goal seek.. ...
In the Goal Seek dialog, enter the new “what if” amount
in the To value text box. ...
We also need to tell Excel which cell to change. ...
Click OK.
Pivot Table
How to Create a Pivot Table
Enter your data into a range of rows and
columns.
Sort your data by a specific attribute.
Highlight your cells to create your pivot table.
Drag and drop a field into the "Row Labels"
area.
Drag and drop a field into the "Values" area.
Fine-tune your calculations.
Charts and Graphs
https://www.slideshare.net/
HimadriShekhar2/charts-and-graphs-
47417340
Table
A table typically contains related data in a series of worksheet rows
and columns that have been formatted as a table.
Steps
Select a cell in the list of data that you prepared.
On the Ribbon, click the Insert tab.
In the Tables group, click the Table command.
In the Create Table dialog box, the range for your data should
automatically appear, and the My table has headers option is
checked. ...
Click OK to accept these settings.
