USING CTRL AND OTHER KEYS
Ctrl+; To enter current date
Ctrl+: To enter current time
Ctrl+Spacebar To select entire column
Shift+Spacebar To select entire Row
Ctrl++ To insert row
Ctrl+- To delete row
Ctrl+9 Hide row
Ctrl+0 Hide column
Ctrl+SHift ( Unhide row
Ctrl+Shift ) Unhide column
Ctrl+Pageup Go to previous worksheet
Ctrl+Pagedown Go to next worksheet
Ctrl+Home Go to top left corner (Cell A1)
Ctrl+End Go to End of data table ?
Ctrl+ <- (back key) Move one word to left
Ctrl+ -> (forward key) Move one word to right
Ctrl+ Up key Move up to blank cell
Ctrl+ Down key Move down till blank cell
Ctrl+ Shift+ <- (back key) Move to left most data area.
Ctrl+Shift -> (forward key) Move to right most data area.
Ctrl+Shift+ Up key Move up to top most data cell
Ctrl+ Shift+ Down key Move to bottom most data cell
Ctrl+ . Move to data area corners in clockwise direction.
Ctrl+ Del Delete to end of word.
Tuesday, June 27, 2017
Thursday, June 22, 2017
KEYBOARD SHORTCUTS - 1
Key board shortcuts using Ctrl Key and the Alphabets.
Ctrl+A To select the data area.(First position the cursor inside data area)
Ctrl+B To change entry into Bold Font
Ctrl+C Copy selection
Ctrl+D Fill copied data/format, in the cells below.(First select the cell with data and the empty cells below)
Ctrl+F Find entered value
Ctrl+G Go to menu dialog box
Ctrl+H Paste value
Ctrl+I Change font to Italic
Ctrl+K Add hyperlink
Ctrl+L Display create table dialog box
Ctrl+N Open new file(workbook).
Ctrl+O Open existing file dialog box
Ctrl+P Print menu
Ctrl+R Fill copied data,in the cells to the right.(First select the cell with data and the empty cells on the right.)
Ctrl+S Save file
Ctrl+T Open tool option
Ctrl+U Underline selection
Ctrl+V Paste Value
Ctrl+W Close file
Ctrl+X Cut selection
Ctrl+Y Redo last command
Ctrl+W Close file
Ctrl+Z Undo last command
A sample set of different combination of the above commands, used in Excel, is given below:
a) To change font to Bold/Italic/Underlined:
Select data area using Ctrl+A, then Ctrl+B or Ctrl+I or Ctrl +U.
b) To add border area to data:
Select data area using Ctrl+A, then Border with mouse click.
c) To add back ground colour:
Select data area using Ctrl+A, then Colour Bucket, choose desired colour using mouse cLick.
d) To copy and paste data:
Select required data, press Ctrl+C to copy, move to another cell press Ctrl+V to paste.
e) To cut and paste data:
Select required data, press Ctrl+X to cut, move to another cell press Ctrl+V to paste.
f) To open/close file:
Press Ctrl+N to open a new work book, Ctrl+O to open a existing workbook and Ctrl+W to close the workbook.
g) To do and undo commands:
Select data in a cell. Press Del to delete data, press Ctrl+Z to undo and later, Ctrl+Y to redo.
h) To find and replace data:
Enter Bangalore in an Excel file in different cells. go to the top left corner by pressing Ctrl+Home key.
Press Ctrl+F to open find dialog box and enter Bangalore. Press Ctrl+H to enter the new place as Bengaluru. Select find and replace all.
i) Let us say you have different files which you use on a daily basis which are stored in different folder or drives.We can have an excel file in which we can store the file name in column 1 and the corresponding hyperlink in column 2.
Add the file names in column 1. Against each file name go to column 2 and press Ctrl+K which will open the hyperlink dialog box. Go to location where the particular file is located and click on it. Its hyperlink will appear in column 2. Repeat same procedure for all files. A sample file is shown below.
j) To open print menu :
Press Ctrl+P.
k) To open Tool option :
Press Ctrl+T
Ctrl+A To select the data area.(First position the cursor inside data area)
Ctrl+B To change entry into Bold Font
Ctrl+C Copy selection
Ctrl+D Fill copied data/format, in the cells below.(First select the cell with data and the empty cells below)
Ctrl+F Find entered value
Ctrl+G Go to menu dialog box
Ctrl+H Paste value
Ctrl+I Change font to Italic
Ctrl+K Add hyperlink
Ctrl+L Display create table dialog box
Ctrl+N Open new file(workbook).
Ctrl+O Open existing file dialog box
Ctrl+P Print menu
Ctrl+R Fill copied data,in the cells to the right.(First select the cell with data and the empty cells on the right.)
Ctrl+S Save file
Ctrl+T Open tool option
Ctrl+U Underline selection
Ctrl+V Paste Value
Ctrl+W Close file
Ctrl+X Cut selection
Ctrl+Y Redo last command
Ctrl+W Close file
Ctrl+Z Undo last command
A sample set of different combination of the above commands, used in Excel, is given below:
a) To change font to Bold/Italic/Underlined:
Select data area using Ctrl+A, then Ctrl+B or Ctrl+I or Ctrl +U.
b) To add border area to data:
Select data area using Ctrl+A, then Border with mouse click.
c) To add back ground colour:
Select data area using Ctrl+A, then Colour Bucket, choose desired colour using mouse cLick.
d) To copy and paste data:
Select required data, press Ctrl+C to copy, move to another cell press Ctrl+V to paste.
e) To cut and paste data:
Select required data, press Ctrl+X to cut, move to another cell press Ctrl+V to paste.
f) To open/close file:
Press Ctrl+N to open a new work book, Ctrl+O to open a existing workbook and Ctrl+W to close the workbook.
g) To do and undo commands:
Select data in a cell. Press Del to delete data, press Ctrl+Z to undo and later, Ctrl+Y to redo.
h) To find and replace data:
Enter Bangalore in an Excel file in different cells. go to the top left corner by pressing Ctrl+Home key.
Press Ctrl+F to open find dialog box and enter Bangalore. Press Ctrl+H to enter the new place as Bengaluru. Select find and replace all.
i) Let us say you have different files which you use on a daily basis which are stored in different folder or drives.We can have an excel file in which we can store the file name in column 1 and the corresponding hyperlink in column 2.
Add the file names in column 1. Against each file name go to column 2 and press Ctrl+K which will open the hyperlink dialog box. Go to location where the particular file is located and click on it. Its hyperlink will appear in column 2. Repeat same procedure for all files. A sample file is shown below.
j) To open print menu :
Press Ctrl+P.
k) To open Tool option :
Press Ctrl+T
Wednesday, June 14, 2017
PROFESSIONAL DATA ENTRY GUIDELINES
In the earlier posting, the concept of entity was explained. An entity is a thing of importance about which we need to know some information.An employee, a department, sales order, delivery items(by courier), supplies received(in a store) etc. Each entity is linked with a table(two dimensional chart)where you fill in the data. In MS Excel you create the table on a spread sheet.
Each entity may have a number of attributes and characteristics or qualities linked with it. An employee entity may have attributes like name, age, gender, date of birth, father's name, mother's name etc.
When you create a table for an entity the column heads should be based on different attributes you can think of, based on the data you want to enter.
Let us say you want to enter name and address of some employees. Having only two column names like NAME and ADDRESS will not serve the real purpose. If NAME="PRITHVI" and ADDRESS=" No 450, 10th cross, 6th Main, Indiranagar, Bangalore -560038" do not enter the complete address under the ADDRESS column. But Excel will allow you to enter the complete address in one cell under the ADDRESS column. Instead we need to split and create different columns for address like, HOUSE-NO, STREET, CROSS,AREA, CITY-TOWN and PIN-CODE.
The right column names could be NAME, HOUSE-NO, STREET, CROSS,AREA, CITY-TOWN and PIN-CODE.
The simple rule is: If it is a seperate attribute, give a seperate column to it. In case you still feel at a later date, the data entered in two columns are actually required to be under a single column we can always merge it. But if you enter data which should ideally go under different column heads under a single column it will prove to be a real pain to put the data, seperated under different columns.
One more piece of guidance you must follow while entering data into Excel is that follow a particular style through out the file. If you are entering the name, it is better you enter the name first and the intial after the name. Do not include salutations like Ms/Mr/Mrs/Sri/Smt along with the name under the NAME column. If you think this piece of information is important, create a seperate column and enter the data under that column. If you are entering house no, if the typical data of different houses is like 540, 45-A, 12/3, No 2345, # 302 etc, better follow an uniform style. Enter only the house number with out prefix like No, No. , Num, # etc. If you are entering the city name of say, Bangalore, do not keep changing between Bangalore and Bengaluru. To enter the PIN code, as a six digit number enter it as 560098, not as 560 098. Entering mobile numbers again, do not changing between entering the 10 digit number, adding a prefix 91 or +91.Follow an uniform style. If you need to enter data having City, State and Country details, create three seperate column for them.
One more line of caution we must bear in mind is, if more than one person is entering data into the same file, all of them must follow the same style and format.Otherwise we are waiting for trouble.
The reason we are giving so much importance to this concept is because this is the foundation to build your database file. If you do no take enough care at this stage it is going to be trouble some later.Always remember that data entered is meant for use and used repeatedly over long period. The data is going to accumulate and the file size will grow with every passing month or year.
Data should be in a readily useable format like in MS Excel. The same data available in a text format or as hard copies will not be readily useable because of the lack of ease in retrieving such data, using it and storing it for later use. If you do not follow the above guidelines the data you have entered, cannot be used to its full potential.
PROFESSIONAL'S WAY OF ENTER DATA IN MS EXCEL
In the earlier posting, the concept of entity was explained. An entity is a thing of importance about which we need to know some information.An employee, a department, sales order, delivery items(by courier), supplies received(in a store) etc. Each entity is linked with a table(two dimensional chart)where you fill in the data. In MS Excel you create the table on a spread sheet.
Each entity may have anumber of attributes and characteristics or qualities linked with it. An employee entity may have attributes like name, age, gender, date of birth, father's name, mother's name etc.
When you create a table for an entity the column heads should be based on different attributes you can think of, based on the data you want to enter.
Let us say you want to enter name and address of some employees. Having only two column names like NAME and ADDRESS will not serve the real purpose. If NAME="PRITHVI" and ADDRESS=" No 450, 10th cross, 6th Main, Indiranagar, Bangalore -560038" do not enter the complete address under the ADDRESS column. But Excel will allow you to enter the complete address in one cell under the ADDRESS column. Instead we need to split and create different columns for address like, HOUSE-NO, STREET, CROSS,AREA, CITY-TOWN and PIN-CODE.
The right column names could be NAME, HOUSE-NO, STREET, CROSS,AREA, CITY-TOWN and PIN-CODE.
Each entity may have a number of attributes and characteristics or qualities linked with it. An employee entity may have attributes like name, age, gender, date of birth, father's name, mother's name etc.
When you create a table for an entity the column heads should be based on different attributes you can think of, based on the data you want to enter.
Let us say you want to enter name and address of some employees. Having only two column names like NAME and ADDRESS will not serve the real purpose. If NAME="PRITHVI" and ADDRESS=" No 450, 10th cross, 6th Main, Indiranagar, Bangalore -560038" do not enter the complete address under the ADDRESS column. But Excel will allow you to enter the complete address in one cell under the ADDRESS column. Instead we need to split and create different columns for address like, HOUSE-NO, STREET, CROSS,AREA, CITY-TOWN and PIN-CODE.
The right column names could be NAME, HOUSE-NO, STREET, CROSS,AREA, CITY-TOWN and PIN-CODE.
The simple rule is: If it is a seperate attribute, give a seperate column to it. In case you still feel at a later date, the data entered in two columns are actually required to be under a single column we can always merge it. But if you enter data which should ideally go under different column heads under a single column it will prove to be a real pain to put the data, seperated under different columns.
One more piece of guidance you must follow while entering data into Excel is that follow a particular style through out the file. If you are entering the name, it is better you enter the name first and the intial after the name. Do not include salutations like Ms/Mr/Mrs/Sri/Smt along with the name under the NAME column. If you think this piece of information is important, create a seperate column and enter the data under that column. If you are entering house no, if the typical data of different houses is like 540, 45-A, 12/3, No 2345, # 302 etc, better follow an uniform style. Enter only the house number with out prefix like No, No. , Num, # etc. If you are entering the city name of say, Bangalore, do not keep changing between Bangalore and Bengaluru. To enter the PIN code, as a six digit number enter it as 560098, not as 560 098. Entering mobile numbers again, do not changing between entering the 10 digit number, adding a prefix 91 or +91.Follow an uniform style. If you need to enter data having City, State and Country details, create three seperate column for them.
One more line of caution we must bear in mind is, if more than one person is entering data into the same file, all of them must follow the same style and format.Otherwise we are waiting for trouble.
The reason we are giving so much importance to this concept is because this is the foundation to build your database file. If you do no take enough care at this stage it is going to be trouble some later.Always remember that data entered is meant for use and used repeatedly over long period. The data is going to accumulate and the file size will grow with every passing month or year.
Data should be in a readily useable format like in MS Excel. The same data available in a text format or as hard copies will not be readily useable because of the lack of ease in retrieving such data, using it and storing it for later use. If you do not follow the above guidelines the data you have entered, cannot be used to its full potential.
PROFESSIONAL'S WAY OF ENTER DATA IN MS EXCEL
In the earlier posting, the concept of entity was explained. An entity is a thing of importance about which we need to know some information.An employee, a department, sales order, delivery items(by courier), supplies received(in a store) etc. Each entity is linked with a table(two dimensional chart)where you fill in the data. In MS Excel you create the table on a spread sheet.
Each entity may have anumber of attributes and characteristics or qualities linked with it. An employee entity may have attributes like name, age, gender, date of birth, father's name, mother's name etc.
When you create a table for an entity the column heads should be based on different attributes you can think of, based on the data you want to enter.
Let us say you want to enter name and address of some employees. Having only two column names like NAME and ADDRESS will not serve the real purpose. If NAME="PRITHVI" and ADDRESS=" No 450, 10th cross, 6th Main, Indiranagar, Bangalore -560038" do not enter the complete address under the ADDRESS column. But Excel will allow you to enter the complete address in one cell under the ADDRESS column. Instead we need to split and create different columns for address like, HOUSE-NO, STREET, CROSS,AREA, CITY-TOWN and PIN-CODE.
The right column names could be NAME, HOUSE-NO, STREET, CROSS,AREA, CITY-TOWN and PIN-CODE.
BASIC TERMS USED IN DATABASE MANAGEMENT SYSTEMS
Entity : Any person or thing which has some characteristics or attributes is called an entity, A person would have different attributes like, name, age,gender, weight, date of birth, place of birth, father's name etc.
A thing may be a TV which may have attributes like, make , model no, serial no, year of manufacture,price, date of sale, warranty period etc.
Table : A table is a chart having multiple columns and rows. Each column shall have a column name. Each row shall hold data related to a particular entity. The picture below shows a typical table. Each table will have data related to a particular aspect of data.
Data type : Date can be in the following forms. Some frequently used forms of data are listed below:
i) Characters: Example - a,b,c X ,Y Z etc
ii) Strings: Example - Name, Age, Weight etc
iii) Numbers : Integers Example - 1,4, 56 etc. Real numbers Example - 1.23,
45.789 etc Exponential numbers 2.34X10 3
iv) Date: Example - 23/05/2017, Jan, 23, 1978 etc
Database: A Database will have multiple tables. If you consider a person who is working in a Company we can think of the following tables:
Employee table : Data held- name, age,gender, weight, date of birth, place of birth, employee ID no,father's name etc, date of joining.
Department table: Department name, department number, employee ID no, designation etc
Attendance table : date, employee name, employee ID no,present/absent/leave.
Education and training: SSLC marks, class , year of passing, PUC marks, class, year of passing, diploma/degree marks,
class, year of passing, addition training undergone, duration, dates etc.
GETTING STARTED
A typical executive, working in the current day business environment is sure to have familiarity with MS Excel and he would be using it for few activities concerning his immediate area of interest. A convenient trap into which such executives fall is that they believe familiarity with Excel is their exceptional competence. Familiarity and Expertise/ Competence are two different things. This clarity is essential to make a beginning.
THE BACKGROUND
MS Excel coming as part of the MS Office, installed on practically all computers working on windows OS can be used in a variety of business environment cutting across different domains like Marketing and Sales,HR, Banking and Finance, Accounting, Inventory Management and Logistics.
The common thread running through all these domains is that today's businesses are run based on activities driven by decisions arrived at by analysis of huge volume of data. An executive at the entry, mid or senior level, all work with data as part of their daily work.
The point that we need to realize and emphasis is that, the daily data handling in an office, at the individual level, largely happens on MS Excel. Most operations are repetitive and time consuming.
An executive who has a competent level of knowledge in using MS Excel has a distinct advantage over the average MS Excel user. Over a period of time, his exceptional skill with MS Excel would give him a definite lead over his colleagues and propel him on the track to a faster career growth.
This blog endeavors to create a whole lot of Excel Gurus, each being a leader in his Company. Any data crunching to be done in his area of work, he must be the go to man.
THAT MAN SHOULD BE YOU - THE EXCEL-GURU.
If you connect with this life changing thought, get started. This blog will lead you along, step by step, towards your dream destination.
The common thread running through all these domains is that today's businesses are run based on activities driven by decisions arrived at by analysis of huge volume of data. An executive at the entry, mid or senior level, all work with data as part of their daily work.
The point that we need to realize and emphasis is that, the daily data handling in an office, at the individual level, largely happens on MS Excel. Most operations are repetitive and time consuming.
An executive who has a competent level of knowledge in using MS Excel has a distinct advantage over the average MS Excel user. Over a period of time, his exceptional skill with MS Excel would give him a definite lead over his colleagues and propel him on the track to a faster career growth.
This blog endeavors to create a whole lot of Excel Gurus, each being a leader in his Company. Any data crunching to be done in his area of work, he must be the go to man.
THAT MAN SHOULD BE YOU - THE EXCEL-GURU.
If you connect with this life changing thought, get started. This blog will lead you along, step by step, towards your dream destination.
Subscribe to:
Posts (Atom)