Microsoft Excel
• Jamil Saudagar
Jamil Saudagar 1 of 23
Timeline and Introduction to Microsoft Excel
• What is Microsoft Excel ?
• 1961 : Professor Richard Mattessich was the first to develop a computerised spreadsheet
• 1978 : Robert Frankston and Dan Bricklin invented Visicalc. It cam out with the Apple II Computer.
Basic calculations could be performed.
• 1980 : DIF formats were introduced. Data became portable and could be shared with other programs.
• 1980 to 1983 : Some other spreadsheet programs were released (Example : SuperCalc)
• 1983 : Lotus 123 was introduced. It let users chart information and cell identification became possible.
• 1985 : Lotus # 2 was introduced
• 1987 : MS Excel and Quattro Pro were introduced
Jamil Saudagar 2 of 23
Microsoft Excel : Hierarchy
• Microsoft Excel Workbook
• Microsoft Worksheet
• Microsoft Range / Cell
Jamil Saudagar 3 of 23
Microsoft Excel : Working with an MS Excel Workbook
Action Path Keyboard Image
Shortcut
Create a new File >> New Ctrl + N
Workbook
Open a File >> Open Ctrl + O
Workbook
Save a Workbook Ctrl + S / F12
Cycle between Ctrl + Tab
Workbooks
Close a File >> Close Ctrl + W
Workbook
Jamil Saudagar 4 of 23
Microsoft Excel : Working with an MS Excel Workbook
Action Path Keyboard Image
Shortcut
Securing a • F12 >> Tools
Workbook >> General
Options
• Enter
appropriate
passwords
Jamil Saudagar 5 of 23
Microsoft Excel : Working with an MS Excel Sheet
Action Path Keyboard Shortcut Image
Create a new Worksheet • Home >> Insert >> Shift + F11
Insert Sheet
OR
• Hover the mouse over a
Sheet tab
• Right Click >> Insert
Activate a Worksheet • Hover over the Sheet • Shift Down :
tab and Ctrl + PgDown
• Click the mouse • Shift Up :
Ctrl + Pgup
Delete a Worksheet • Home >> Delete >> • Alt + E; L
Delete Sheet
OR
• Hover the mouse over a
Sheet tab
• Right Click >> Insert
Jamil Saudagar 6 of 23
Microsoft Excel : Working with an MS Excel Sheet
Action Path Keyboard Shortcut Image
Moving a Worksheet • Home >> Format >> Alt + E; M
Move / Copy a Sheet
OR
• Hover the mouse over a
Sheet tab
• Right Click >> Move or
Copy
Hiding a Worksheet • Home >> Format >> • Hide
(Temporary) Hide & Unhide >> Hide • Alt + O, H, H
Sheet • Unhide
OR • Alt + O, H, U
• Hover the mouse over
a Sheet tab
• Right Click >> Hide
Hiding a Worksheet Through VBA Code
(Permanent)
Jamil Saudagar 7 of 23
Microsoft Excel : Working with an MS Excel Sheet
Action Path Keyboard Shortcut Image
Renaming a Worksheet • Home >> Format >> Alt + O; H; R
Rename Sheet
• Double Click on the
Sheet Name and type
the new sheet name
Jamil Saudagar 8 of 23
Microsoft Excel : Working with an MS Excel Range (Cells)
Action Path Keyboard Shortcut Image
Entering and Editing Data F2
Inserting Rows • Home >> Insert >>
Insert Sheet Rows
OR
• Select the row, above
• Shift + Space Bar
which you want to add
a new row
• Add the row • Ctrl + ‘+’
Inserting Columns • Home >> Insert >>
Insert Sheet Columns
OR
• Select the column • Ctrl + Space Bar
before which you want
to add a new column
• Add the row • Ctrl + ‘+’
Jamil Saudagar 9 of 23
Microsoft Excel : Working with an MS Excel Range (Cells)
Action Path Keyboard Shortcut Image
Deleting Rows • Home >> Delete >>
Delete Sheet Rows
OR
• Select the row(s) to • Shift + Spacebar
delete
• Delete Rows • Ctrl + ‘-’
Deleting Columns • Home >> Delete >>
Delete Sheet Columns
OR
• Select the column(s) to • Ctrl + Spacebar
delete
• Delete Columns • Ctrl + ‘-’
Hiding Rows • Home >> Format >> • Ctrl + 9
Hide & Unhide >> Hide (below function
Rows keys)
Jamil Saudagar 10 of 23
Microsoft Excel : Working with an MS Excel Range (Cells)
Action Path Keyboard Shortcut Image
Hiding Columns • Home >> Format >> • Ctrl + 0
Hide & Unhide >> Hide (below function
Columns keys)
Unhide Rows • Select the previous and • Ctrl + Shift + (
the next row (below function
• Home >> Format >> keys)
Hide & Unhide >>
Unhide Rows
Unhide Columns • Select the previous and • Ctrl + Shift + )
next column (below function
• Home >> Format >> keys)
Hide & Unhide >>
Unhide Columns
Jamil Saudagar 11 of 23
Microsoft Excel : Working with an MS Excel Range (Cells)
Action Path Keyboard Shortcut Image
Using Autofill option to fill • Home > Fill
up data
Managing the row height • Home >> Format >>
Row Height
Managing the column • Home >> Format >>
width Column Width
Auto-set Column width • Select the column(s) • Ctrl + Spacebar
• Home >> Format >> • Alt + O; C; A
Autofit Column Width
Switching off the gridlines • View >> Gridlines • Alt + P; V; G
Jamil Saudagar 12 of 23
Microsoft Excel : Working with an MS Excel Range (Cells)
Action Keyboard Shortcut Image
Moving around in a A1 : Ctrl + Home
dataset E13 : Ctrl + End
A1 -> E1 : Ctrl + Right Arrow
(Pressing the Shift key E1 -> A1 : Ctrl + Left Arrow
along with the Ctrl key will A1 -> A13 : Ctrl + Down Arrow
select all cells in between) E13 -> E1 : Ctrl + Up Arrow
Jamil Saudagar 13 of 23
Microsoft Excel : Working with an MS Excel Range (Cells)
Action Path Keyboard Shortcut Image
Selecting the Entire Ctrl + A
dataset
Go to a certain Cell • Ctrl + G and
then type the
cell address
Paste Special Features • Copy the data set • Ctrl + Alt + V
• Move to the target area
• Press the shortcut key
and paste
Sorting Data • Select Data Set • Ctrl + A
• Data >> Sort • Alt + D; S
Jamil Saudagar 14 of 23
Microsoft Excel : Working with an MS Excel Range (Cells)
Action Path Keyboard Shortcut Image
Filtering Data • Data >> Filter • Alt + D + F; F
Freeze Panes • View >> Freeze Panes • Alt + W; F
Jamil Saudagar 15 of 23
Microsoft Excel : Formatting Data
Action Path Keyboard Shortcut Image
Changing the back colour, • Select the cell(s) which
fore colour, font and font has to be formatted
size of data
Making data bold, • Select the cell(s) which
Italicized etc.. has to be formatted
Adding borders • Select the cells where
borders have to be
applied
• Bring up the Format
Cells window
OR
• Home >> Format >>
Format Cells || Border
Jamil Saudagar 16 of 23
Microsoft Excel : Formatting Data
Action Path Keyboard Shortcut Image
Formatting numbers • Select the Cell(s) • Ctrl + 1
• Home >> Format >>
Format Cells || Number
Formatting Dates • Select the Cell(s) • Ctrl + 1
• Home >> Format >>
Format Cells || Number
Setting the text alignment • Select the Cell(s) • Ctrl + 1
• Home >> Format >>
Format Cells ||
Alignment
Wrapping and Merging • Home >> Wrap text
Cells • Home >> Merge &
Center
Jamil Saudagar 17 of 23
Microsoft Excel : Formatting Data
Action Path Keyboard Shortcut Image
Commenting Cells (and • Select the Cell, to add
managing it) comment
• Bring up the comment
window by right-
clicking the mouse and
selecting ‘Insert
Comment’
Deleting Comments • Select the Cell, to
delete the comment
• Bring up the comment
window by right-
clicking the mouse and
selecting ‘Delete
Comment’
Jamil Saudagar 18 of 23
Microsoft Excel : Printing Data
Action Path Keyboard Shortcut Image
Changing your page view • View • Alt + W
The Quick Print option • Ctrl + P
• Working with Page
setup and Print
settings
• Printing row and
column titles
• Adding header and • Insert >> Header and
footer options Footer
• Other header and
footer options
Jamil Saudagar 19 of 23
Microsoft Excel : Other Features
Action Path Keyboard Shortcut Image
Data Validation • Data >> Data Validation Alt + A; V; V
Conditional Formatting • Home Alt + H; L
Jamil Saudagar 20 of 23
Microsoft Excel : Other Features
Action Path Keyboard Shortcut Image
Workbook Protection • Review || Protect
Workbook
Sheet Protection
Jamil Saudagar 21 of 23
Microsoft Excel : Other Features
Action Path Keyboard Shortcut Image
Pivot Tables • Insert >> Pivot Table Alt + N; V
(It is always advisable to
convert the data set to a table
before creating a Pivot Table)
Jamil Saudagar 22 of 23
References and Resources
• Excel 2016 Bible by John Walkenbach
• [Link]
Jamil Saudagar 23 of 23